Transform DataTable to Excel not include headercells

Question

I am trying to transform a datatbale to Excel using this funtion

    internal static bool DataTableToExcel(System.Data.DataTable dataTable, string path)
    {
        _Excel.Application objXL = objXL = new _Excel.Application();
        _Excel.Workbook objWB = null;
        _Excel.Worksheet ExcelWorkSheet = null;
        try
        {
            objWB = objXL.Workbooks.Add(_Excel.XlWBATemplate.xlWBATWorksheet);
            ExcelWorkSheet = objWB.Worksheets[1];

            for (int i = 0; i < dataTable.Rows.Count; i++)
            {
                for (int j = 0; j < dataTable.Columns.Count; j++)
                {
                    ExcelWorkSheet.Cells[i + 1, j + 1] = dataTable.Rows[i][j].ToString();
                }
            }

            objWB.SaveAs(path);
            objWB.Close();
            objXL.Quit();
            return true;
        }
        catch(Exception ex)
        {
            objWB.Close();
            objXL.Quit();
            return false;
        }
    }

But the final result is missing the header cells in the document: I am only obtaining the date but i need to add the header cells in the excell document too.

You could loop through your `dataTable.Columns` collection and spit out the names? as the first row, before you enter the data loop. Just a thought. — Simon Wilson, Feb 03 '21 at 16:46
Have you looked at the answers here: https://stackoverflow.com/questions/8207869/how-to-export-datatable-to-excel — Ocean Airdrop, Feb 03 '21 at 16:49

score 0 · Accepted Answer · edited Feb 03 '21 at 17:01

0

In this case I believe you are missing adding the columns names before start the loop in your rows. Try something like this for sheet's first row:

ExcelWorkSheet.Cells[0,0] = datatable.Columns["columnName"].ColumnName

edited Feb 03 '21 at 17:01

Dharman

30,962
25
85
135

answered Feb 03 '21 at 16:55

Uriel Gonçalves

26
3

Transform DataTable to Excel not include headercells

1 Answers1