Home > DeveloperSection > Forums > How to write excel file from datatable in c#
Pravesh Singh

Total Post:411

Points:2881
Posted on    January-10-2012 1:44 AM

 C# C# 
Ratings:


 3 Reply(s)
 13677  View(s)
Rate this:

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



Arun Singh

Total Post:68

Points:476
Posted on    January-10-2012 8:42 AM

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.


Modified On Jan-10-2012 08:44:04 AM

robert galp
robert galp

Total Post:1

Points:7
Posted on    April-19-2012 11:05 AM


Richard Boolman
Richard Boolman

Total Post:1

Points:7
Posted on    February-14-2013 12:20 AM

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

DataTable dt = new DataTable();
ExcelWorkbook Wbook = new ExcelWorkbook();
Wbook.Worksheets.Add("WSheet1").ReadFromDataTable(dt);
Wbook.WriteXLS(@"c:\output.xls");

Don't want to miss updates? Please click the below button!

Follow MindStick