Insert DataTable to Excel Sheet

Your datatable must have captions for better view.

Excel.Application xlApp = new Excel.Application();
 
xlApp.Visible = false;
 
Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
 
Excel.Workbook wb = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
 
Excel.Worksheet ws = (Excel.Worksheet)wb.Sheets[1];
 
//capion work you can delete it if you want
for (int r = 0; r < dtExcel.Columns.Count; r++)
{
    Excel.Range field = (Excel.Range)ws.Cells[1, r + 1];
    field.Formula = dtExcel.Columns[r].Caption;
}
 
for (int i = 0; i < dtExcel.Rows.Count; i++)
{
    for (int j = 0; j < dtExcel.Columns.Count; j++)
    {
        Excel.Range field = (Excel.Range)ws.Cells[i + 2, j + 1];
        field.Formula = dtExcel.Rows[i][j].ToString();
    }
}
 
ws.Cells.EntireColumn.AutoFit();
 
wb.SaveAs(fileName, Missing.Value, Missing.Value,
Missing.Value, Missing.Value, Missing.Value,
Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange,
Missing.Value, Missing.Value, Missing.Value,
Missing.Value, Missing.Value);
 
wb.Close(true, Missing.Value, Missing.Value);
 
xlApp.Quit();
  • Facebook
  • Twitter
  • StumbleUpon
  • del.icio.us
  • Digg

Category: Csharp - C# - Comment »


Leave a Reply



Back to top