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);
}