CODEWITHSUNDEEP
c#excelwinformsdatatableexcel-interop
ehh
ehh

Reputation: 3480

Create Excel Sheet From a DataTable

I am facing a strange phenomenon. I wrote a method that copy values from datatable to an excel sheet. The problem is that the rows are copied to columns and columns to rows.

For example:

DataTable:

Item   Quantity    UnitPrice   TotalPrice
A      10          2           20
B      3           15          45
C      100         0.5         50

Excel Sheet:

Item   Quantity    UnitPrice   TotalPrice
A      B          C           
10     3          100          
2      15         0.5         
20     45         50

My code:

 private void PopulateDataWorkSheet()
        {
        xl.Workbooks xlWorkbooks = _xlApp.Workbooks;
        xl.Workbook xlWorkbook = xlWorkbooks.Open(_excelFileName);
        xl.Sheets xlSheets = xlWorkbook.Sheets;
        const int startRowIndex = 2;    // first row for columns title
        const int startColumnIndex = 1;

        xl.Worksheet xlDataWorkSheet = xlSheets[DATA_SHEET];
        xl.Range xlCells = xlDataWorkSheet.Cells;

        for (var rowindex = 0; rowindex < _datatable.Rows.Count; rowindex++)
        {
            for (var columnindex = 0; columnindex < _datatable.Columns.Count; columnindex++)
            {
                xlCells[startRowIndex + rowindex][startColumnIndex + columnindex] = _datatable.Rows[rowindex][columnindex];
            }
        }

        xlWorkbook.Save();
        xlWorkbook.Close();
        xlWorkbooks.Close();

        ReleaseExcelObject(xlCells);
        ReleaseExcelObject(xlDataWorkSheet);
        ReleaseExcelObject(xlSheets);
        ReleaseExcelObject(xlWorkbook);
        ReleaseExcelObject(xlWorkbooks);
    }

I can simply solve it by changing as following but I am not clear what is wrong:

xlCells[startColumnIndex + columnindex][startRowIndex + rowindex] = _datatable.Rows[rowindex][columnindex];

Did I miss something in my code?

Upvotes: 2

Views: 753

Answers (4)

swapnil chandankar
swapnil chandankar

Reputation: 87

You can use following code to overcome the problem.It works fine for me.

                xlApplication = new Microsoft.Office.Interop.Excel.Application();

                // Create workbook .  
                xlWorkBook = xlApplication.Workbooks.Add(Type.Missing);

                // Get active sheet from workbook  
                xlWorkSheet = xlWorkBook.ActiveSheet;
                xlWorkSheet.Name = "Report";
                xlWorkSheet.Columns.ColumnWidth = 30;


                xlApplication.DisplayAlerts = false;
                for (int rowIndex = 0; rowIndex < gridViewDataTable.Rows.Count; rowIndex++)
                {
                    for (int colIndex = 0; colIndex < gridViewDataTable.Columns.Count; colIndex++)
                    {
                        if (rowIndex == 0)
                        {
                            xlWorkSheet.Cells[rowIndex + 1, colIndex + 1] = gridViewDataTable.Columns[colIndex].ColumnName;
                            xlWorkSheet.Cells[rowIndex + 1, colIndex + 1].Interior.Color = System.Drawing.Color.LightGray;
                        }
                        xlWorkSheet.Cells[rowIndex + 2, colIndex + 1].Borders.Color = Color.Black;
                        xlWorkSheet.Cells[rowIndex + 2, colIndex + 1].Interior.Color = System.Drawing.Color.White;
                        xlWorkSheet.Cells[rowIndex + 2, colIndex + 1] = gridViewDataTable.Rows[rowIndex][colIndex];  
                    }
                }
                xlWorkBook.SaveAs(filename);
                xlWorkBook.Close();
                xlApplication.Quit();

Upvotes: 0

Ivan Stoev
Ivan Stoev

Reputation: 205589

I am facing a strange phenomenon. Did I miss something in my code?

Welcome to the world of Excel Range, dynamics, optional parameters and COM object Default member concept.

Excel Range is a strange thing. Almost every method/property returns another range. The indexer you used is something like this 

dynamic this[object RowIndex, object ColumnIndex = Type.Missing] { get; set; }

i.e. has required row index and optional column index.

Let take this call

xlCells[startRowIndex + rowindex][startColumnIndex + columnindex] = ...;

it's equivalent to something like this

var range1 = (Excel.Range)xlCells[startRowIndex + rowindex];
var range2 = (Excel.Range)range1[startColumnIndex + columnindex];
//                               ^^^^^^
// this is treated as row index offset from the range1
range2.Value2 = ...;

Shortly, you need to change it to

xlCells[startRowIndex + rowindex, startColumnIndex + columnindex] = ...;

Upvotes: 3

Kathi
Kathi

Reputation: 140

You need to change how you access your excel cells. cells[StartRowIndex+ rowindex, StartColumnIndex + columnindex] is Row first and then column. What you do is cells[StartRowIndex + rowindex][StartColumnIndex + columnindex] which takes the column as a first argument.

The order of the loops does not matter at all.. so the right solution would be:

  for (var rowindex = 0; rowindex < _datatable.Rows.Count; rowindex++)
        {
            for (var columnindex = 0; columnindex < _datatable.Columns.Count; columnindex++)
            {
                xlCells[startRowIndex + rowindex, startColumnIndex + columnindex] = _datatable.Rows[rowindex][columnindex];
            }
        }

Cheers!

Upvotes: 1

Nir Schwartz
Nir Schwartz

Reputation: 1753

You doing it wrong, you need to go like this:

                for (var columnindex = 0; columnindex < _datatable.Columns.Count; columnindex++)
                {
                    for (var rowindex = 0; rowindex < _datatable.Rows.Count; rowindex++)
                    {
                            xlCells[startRowIndex + rowindex][startColumnIndex + columnindex] = _datatable.Rows[rowindex][columnindex];
                    }
                }

you need the column and then the row.

Upvotes: 0

Related Questions