C# .NET - Bulk Export datatable to excel - Asked By Anandh Ramanujam on 14-Dec-10 07:28 AM

Hi,

How to export datatable to excel in bulk.

Can anybody provide c# code regarding this.

Thanks.
Nowshad M replied to Anandh Ramanujam on 14-Dec-10 07:31 AM
Hi,
Use this code

public static void ExportToSpreadsheet(DataTable table, string name)
{
   HttpContext context = HttpContext.Current;
   context.Response.Clear();
   foreach (DataColumn column in table.Columns)
   {
    context.Response.Write(column.ColumnName + ";");
   }
   context.Response.Write(Environment.NewLine);
   foreach (DataRow row in table.Rows)
   {
    for (int i = 0; i < table.Columns.Count; i++)
    {
   context.Response.Write(row[i].ToString().Replace(";", string.Empty) + ";");
    }
    context.Response.Write(Environment.NewLine);
   }
   context.Response.ContentType = "text/csv";
   context.Response.AppendHeader("Content-Disposition", "attachment; filename=" + name + ".csv");
   context.Response.End();
}

Vivek Jagga replied to Anandh Ramanujam on 14-Dec-10 07:33 AM
Hi Anandh,

Visit
1- http://www.codedigest.com/Articles/ASPNET/130_Export_to_Excel_in_ASPNet_20_%E2%80%93Gridview_to_Excel_DataTable_to_Excel.aspx
2- http://www.dotnetspider.com/resources/31094-Export-datatable-excel.aspx
3- http://www.eggheadcafe.com/community/aspnet/2/3954/export-datatable-to-excel.aspx

This will resolve your problem....
Anoop S replied to Anandh Ramanujam on 14-Dec-10 02:25 PM
Try this code
// Create new ExcelFile.
var ef = new ExcelFile();
// Imports all the tables from DataSet to new file.
foreach (DataTable dataTable in dataSet.Tables)
{
// Add new worksheet to the file.
var ws = ef.Worksheets.Add(dataTable.TableName);
// Insert the data from DataTable to the worksheet starting at cell "A1".
ws.InsertDataTable(dataTable, "A1", true);
}
// Save the file to XLS format.
ef.SaveXls("DataSet.xls");

refer this also
http://www.eggheadcafe.com/articles/20050404.asp
http://itsrashid.wordpress.com/2007/05/14/export-dataset-to-excel-in-c/