ASP.NET - Datatable to excel

Asked By balaji on 08-Feb-12 01:27 AM
How to export data from data table to excel?
Suchit shah replied to balaji on 08-Feb-12 01:35 AM
With below way you can export datatable to excel

  private void toExcel()
    {
      Response.Clear();
      Response.Buffer = true;
      //Response.Charset = "UTF-8";  
      Response.AppendHeader("Content-Disposition", "attachment;filename=schoolsinfo.xls");
      Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
      Response.ContentType = "application/ms-excel";
      this.EnableViewState = false;
      System.Globalization.CultureInfo myCItrad = new System.Globalization.CultureInfo("EN-US", true);
      System.IO.StringWriter oStringWriter = new System.IO.StringWriter(myCItrad);
      System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
      GridView1.RenderControl(oHtmlTextWriter);
      Response.Write(oStringWriter.ToString());
    }
Venkat K replied to balaji on 08-Feb-12 01:39 AM
A very basic example is explained here:
http://www.codeproject.com/Articles/300648/Exporting-Data-From-DataGridView-or-Datatable-to-E
Somesh Yadav replied to balaji on 08-Feb-12 01:44 AM
HI balaji here I am providing a good link for you on How to export data from data table to excel?
it is explained in a step by step way for you to easily understand.

How to Import or Export DataTable to Excel or HTML files from your .NET applications?


http://www.gemboxsoftware.com/support/articles/import-export-datatable-xls-xlsx-ods-csv-html-net



DL M replied to balaji on 08-Feb-12 02:33 AM
Hi..

Show below code sample

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();
}
And Call this method and pass the DataTable and the filename as parameters.

ExportToSpreadsheet(table, "products");

Note:->you can use it anywhere in a web application. Put it on a page, a HTTP Handler, add it to the App_Code folder or stick it in a separate assembly.
dipa ahuja replied to balaji on 08-Feb-12 02:38 AM
protected void Button3_Click(object sender, EventArgs e)
{
  DataTable dt = Class2.GetData(); //fill the datatable
  ExportDataTable(dt);
 
}
public void ExportDataTable(DataTable dt)
{
  HttpContext.Current.Response.Clear();
  HttpContext.Current.Response.AddHeader("content-disposition", "attachment;filename=e1.xls");
  HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
  string sTab = "";
  foreach (DataColumn dc in dt.Columns)
  {
    HttpContext.Current.Response.Write(sTab + dc.ColumnName);
    sTab = "\t";
  }
  HttpContext.Current.Response.Write("\n");
  int i;
  foreach (DataRow dr in dt.Rows)
  {
    sTab = "";
    for (i = 0; i < dt.Columns.Count; i++)
    {
      HttpContext.Current.Response.Write(sTab + dr[i].ToString());
      sTab = "\t";
    }
    HttpContext.Current.Response.Write("\n");
  }
  HttpContext.Current.Response.End();
}
 
balaji replied to dipa ahuja on 08-Feb-12 03:33 AM
what is the need of sTab?