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();
Be Sociable, Share!

    Category: Csharp - C# - Comments Off on Insert DataTable to Excel Sheet

    Comments are closed.

    Back to top