Sujeet Raman

Sujeet Raman

  • 859
  • 927
  • 357.5k

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

Nov 24 2021 3:43 PM

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

 


Answers (3)