Home > Code > C# > Export to excel from DataTable using C#.NET

Export to excel from DataTable using C#.NET

by Narain Siddharth   on May 09, 2011   Category: C#   |  Views: 49469    |  Points: 25   |  Silver 


Export datatable to excel is a frequent requirement, there are lots of ways we can upload data from datatable to excel, and here I’m going to show one simple common method to export.

Load the data into datatable

protected void Button1_Click(object sender, EventArgs e)
{
DataTable dtRecords = new DataTable();
dtRecords.Columns.Add("FIRSTNAME");
dtRecords.Columns.Add("LASTNAME");
dtRecords.Columns.Add("JOB");
DataRow rw1 = dtRecords.NewRow();
rw1[0] = "JHON";
rw1[1] = "SMITH";
rw1[2] = "MANAGER";
dtRecords.Rows.Add(rw1);
DataRow rw2 = dtRecords.NewRow();
rw2[0] = "MICH";
rw2[1] = "KEN";
rw2[2] = "SR MANAGER";
dtRecords.Rows.Add(rw2);

UploadDataTableToExcel(dtRecords);

}

Export into excel method.
protected void UploadDataTableToExcel(DataTable dtRecords)
{
string XlsPath = Server.MapPath(@"~/Add_data/test.xls");
string attachment = string.Empty;
if (XlsPath.IndexOf("\\") != -1)
{
string[] strFileName = XlsPath.Split(new char[] { '\\' });
attachment = "attachment; filename=" + strFileName[strFileName.Length - 1];
}
else
attachment = "attachment; filename=" + XlsPath;
try
{
Response.ClearContent();
Response.AddHeader("content-disposition", attachment);
Response.ContentType = "application/vnd.ms-excel";
string tab = string.Empty;

foreach (DataColumn datacol in dtRecords.Columns)
{
Response.Write(tab + datacol.ColumnName);
tab = "\t";
}
Response.Write("\n");

foreach (DataRow dr in dtRecords.Rows)
{
tab = "";
for (int j = 0; j < dtRecords.Columns.Count; j++)
{
Response.Write(tab + Convert.ToString(dr[j]));
tab = "\t";
}

Response.Write("\n");
}
Response.End();
}
catch (Exception ex)
{
//Response.Write(ex.Message);
}
}





Post Code  |  Code Snippet Home

User Responses


  Re :Export to excel from DataTable using C#.NET   
Posted by Tikna98
on 24/07/2013
Points : 5

I have seen many code to do the same ,but this worked perfect according to my requirement. Thanks
  Re :Export to excel from DataTable using C#.NET   
Posted by Garth
on 29/01/2014
Points : 5

You can import data into excel file from datatable using c# with Aspose.Cells for .NET Library. Below is the code for importing data from datatable into excel but you need Aspose.Cells for .NET Library to run this code:

//Instantiating a "Products" DataTable object
DataTable dataTable = new DataTable("Products");

//Adding columns to the DataTable object
dataTable.Columns.Add("Product ID", typeof(Int32));
dataTable.Columns.Add("Product Name", typeof(string));
dataTable.Columns.Add("Units In Stock", typeof(Int32));

//Creating an empty row in the DataTable object
DataRow dr = dataTable.NewRow();

//Adding data to the row
dr[0] = 1;
dr[1] = "Aniseed Syrup";
dr[2] = 15;

//Adding filled row to the DataTable object
dataTable.Rows.Add(dr);

//Creating another empty row in the DataTable object
dr = dataTable.NewRow();

//Adding data to the row
dr[0] = 2;
dr[1] = "Boston Crab Meat";
dr[2] = 123;

//Adding filled row to the DataTable object
dataTable.Rows.Add(dr);

//Importing the contents of DataTable to the worksheet starting from "A1" cell,
//where true specifies that the column names of the DataTable would be added to
//the worksheet as a header row
worksheet.Cells.ImportDataTable(dataTable, true, "A1");

  Re :Export to excel from DataTable using C#.NET   
Posted by Leo_21
on 03/04/2014
Points : 5

Another solution:
Step 1, Load Data Information
private void btnLoad_Click(object sender, EventArgs e)
{
using(OleDbConnection oleDbConnection = new OleDbConnection())
{
oleDbConnection.ConnectionString = this.textBox1.Text;
OleDbCommand oleDbCommand = new OleDbCommand();
oleDbCommand.CommandText = this.textBox2.Text;
oleDbCommand.Connection = oleDbConnection;
using(OleDbDataAdapter da = new OleDbDataAdapter(oleDbCommand))
{
DataTable dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;
}
}
}

Step 2, Set Export into Excel
private void btnRun_Click(object sender, EventArgs e)
{
Spire.DataExport.XLS.CellExport cellExport = new Spire.DataExport.XLS.CellExport();
Spire.DataExport.XLS.WorkSheet worksheet1 = new Spire.DataExport.XLS.WorkSheet();
worksheet1.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
worksheet1.DataTable = this.dataGridView1.DataSource as DataTable;
worksheet1.StartDataCol = ((System.Byte)(0));
cellExport.Sheets.Add(worksheet1);
cellExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
cellExport.SaveToFile("20110223.xls");
}

These codes are baesd on .NET library.More details plz refer this article:http://www.e-iceblue.com/Knowledgebase/Spire.DataExport/Program-Guide/How-to-Export-Datatable-to-Excel-through-DataGridView.html
  Re :Export to excel from DataTable using C#.NET   
Posted by Alex.pulver
on 24/04/2014
Points : 5

You can use EasyXLS Excel library for .NET.

Here is a piece of code:
DataSet ds = new DataSet();
ds.Tables.Add(dataTable);

ExcelDocument xls = new ExcelDocument();
xls.easy_WriteXLSFile_FromDataSet("datatable.xls", ds, new ExcelAutoFormat(Styles.AUTOFORMAT_EASYXLS1), "DataTable");


Check also this link about how to export datatable to excel in C#:
http://www.easyxls.com/manual/FAQ/export-datatable-to-excel.html
  Re :Export to excel from DataTable using C#.NET   
Posted by Dietmarpaul
on 12/03/2015
Points : 5

I use this DLL from here: http://www.export2excel.net/excel/

It is incredibly tiny and fast!

Submit feedback about this code snippet

Please sign in to post feedback

Latest Posts