How to write excel file from datatable in c#

Question

bear pu · Answer

Post is removed by the Admin.

garry macin · Answer

You 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 · Answer

Hi,You can use this Excel .NET component to write excel file from a datatable.The C# code below shows how to do this:

robert galp · Answer

You can use OLEDB http://csharp.net-informations.com/excel/csharp-excel-oledb.htm rogal.

Anonymous User · Answer

Hello 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.

forum

How to write excel file from datatable in c#

Can you answer this question?

5 Answers