Data Table Header is Missing while export DT to excel using interop

Sujeet Raman

Hi,

I am trying to export a datatable to excel file.I am able to get that and excel is downloaded but I am missing the headers.Please help me to find the mistake

OdbcConnection OConn1 = new OdbcConnection(odbcConnectString1);
OConn1.ConnectionTimeout = 0;
OdbcDataAdapter odbcDataAdapter1 = new OdbcDataAdapter(sQury1, OConn1);
odbcDataAdapter1.SelectCommand.CommandTimeout = 180;
System.Data.DataTable dt1 = new System.Data.DataTable();
odbcDataAdapter1.Fill(dt1);
DataSet ds1 = new DataSet();
ds1.Tables.Add(dt1);
CreateExcel(ds1, excelPath);

//below code is export to excel
public static void CreateExcel(DataSet ds, string excelPath)
{
    object misValue = System.Reflection.Missing.Value;
    Excel.Application xlApp = new Excel.Application();
    Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);
    Excel._Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Sheets[1];
    Excel.Range xlRange = xlWorkSheet.UsedRange;
    for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
    {
        for (int j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
        {
            xlWorkSheet.Cells[i + 1, j + 1] = ds.Tables[0].Rows[i].ItemArray[j].ToString();
        }
    }
    //  save as ne excel
    xlWorkBook.SaveAs(excelPath);
    //cleanup 
    GC.Collect();
    GC.WaitForPendingFinalizers();
    //release com objects to fully kill excel process from running in the background 
    Marshal.ReleaseComObject(xlRange);
    Marshal.ReleaseComObject(xlWorkSheet);
    //close and release 
    xlWorkBook.Close();
    Marshal.ReleaseComObject(xlWorkBook);
    //quit and release 
    xlApp.Quit();
    Marshal.ReleaseComObject(xlApp);
}

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Uehara Ayaka4y ago
Alternatively, you can try Spire.XLS to export data from datatable to an Excel worksheet. I am sure the header will be successfully exported.

https://www.e-iceblue.com/Tutorials/Spire.XLS/Spire.XLS-Program-Guide/Data-Export-/Import-Export-Datatable-to-Excel-from-Database.html
Sachin Singh4y ago
bafore adding data to Excel sheet, you need to add column names as
// column headings
for (var i = 0; i < ds.Tables[0].Rows.Count; i++)
{
xlWorkSheet.Cells[1, i + 1] = ds.Tables[0].Columns[i].ColumnName;
}
// now the data
for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{ for (int j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
{
xlWorkSheet.Cells[i + 1, j + 1] = ds.Tables[0].Rows[i].ItemArray[j].ToString();
}
}
Jignesh Kumar4y ago
Hi,

Please use this one

public static void CreateExcel(DataSet ds, string excelPath)
{
object misValue = System.Reflection.Missing.Value;
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);
Excel._Worksheet xlWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)xlWorkBook.Sheets[1];
Excel.Range xlRange = xlWorkSheet.UsedRange;
int colIndex = 0;
foreach (DataColumn dc in ds.Tables[0].Columns)
{
colIndex++;
xlWorkSheet.Cells[1, colIndex] = dc.ColumnName;
}
for (int i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
{
for (int j = 0; j <= ds.Tables[0].Columns.Count - 1; j++)
{
xlWorkSheet.Cells[i + 1, j + 1] = ds.Tables[0].Rows[i].ItemArray[j].ToString();
}
}
// save as ne excel
xlWorkBook.SaveAs(excelPath);
//cleanup
GC.Collect();
GC.WaitForPendingFinalizers();
//release com objects to fully kill excel process from running in the background
Marshal.ReleaseComObject(xlRange);
Marshal.ReleaseComObject(xlWorkSheet);
//close and release
xlWorkBook.Close();
Marshal.ReleaseComObject(xlWorkBook);
//quit and release
xlApp.Quit();
Marshal.ReleaseComObject(xlApp);
}