How to write excel file from datatable in c# Pravesh Singh1830610-Jan-2012Hello Everyone, Can anyone tell me how to write excel file from datatable in C#.Net. Please resolve my problem as soon as possible.Thanks in advance!!c#c# Updated on 03-Apr-2019
bear pu
26-Jan-2019garry macin
08-Jan-2019You can turn a DataTable into an Excel worksheet with some very readable code:
XLWorkbook wb = new XLWorkbook();
DataTable dt = GetDataTableOrWhatever();
wb.Worksheets.Add(dt,"WorksheetName");
Richard Boolman
14-Feb-2013robert galp
19-Apr-2012You can use OLEDB
http://csharp.net-informations.com/excel/csharp-excel-oledb.htm
rogal.
Anonymous User
10-Jan-2012Hello Pravesh Singh,
Check this code...
// Create sql connection string
string conString = @"Data Source = ServerName ; Initial Catalog = MyDatabase; User Id = XXXXXX ; Password = XXXXXX;";
SqlConnection sqlCon = new SqlConnection(conString);
sqlCon.Open();
// Fill data into MyTable
SqlDataAdapter da = new SqlDataAdapter("select * from MyTableName", sqlCon);
System.Data.DataTable dtMainSQLData = new System.Data.DataTable();
da.Fill(dtMainSQLData);
DataColumnCollection dcCollection = dtMainSQLData.Columns;
// Export Data into EXCEL Sheet
Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
ExcelApp.Application.Workbooks.Add(Type.Missing);
// Write data into excel sheet cells
for (int i = 1; i < dtMainSQLData.Rows.Count + 1; i++)
{
for (int j = 1; j < dtMainSQLData.Columns.Count + 1; j++)
{
if (i == 1)
ExcelApp.Cells[i, j] = dcCollection[j - 1].ToString();
else
ExcelApp.Cells[i, j] = dtMainSQLData.Rows[i - 1][j - 1].ToString();
}
}
// save excel file
ExcelApp.ActiveWorkbook.SaveCopyAs("SaveAsFilePath");
ExcelApp.ActiveWorkbook.Saved = true;
ExcelApp.Quit();
It might be resolve your problem.