How to export DataTable into Excel file in ASP.NET

There is a two simple function written below, which takes datatable as a input parameter and give it popup to user to save the file as an excel file.

1. This method work well in page.

Copy and paste file in your code, and call this function on Event where it required.

public void ExportToExcel(DataTable dt)

{

if (dt.Rows.Count > 0)

{

string filename = “Test.xls”;

string excelHeader = “Quiz Report”;

System.IO.StringWriter tw = new System.IO.StringWriter();

System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(tw);

DataGrid dgGrid = new DataGrid();

dgGrid.DataSource = dt;

dgGrid.DataBind();

// Report Header

hw.WriteLine(“<b><u><font size=’3′> ” + excelHeader + ” </font></u></b>”);

//Get the HTML for the control.

dgGrid.RenderControl(hw);

//Write the HTML back to the browser.

//Response.ContentType = “application/vnd.ms-excel”;

Response.ContentType = “application/vnd.ms-excel”;

Response.AppendHeader(“Content-Disposition”, “attachment; filename=” + filename + “”);

this.EnableViewState = false;

Response.Write(tw.ToString());

Response.End();

}

}

Once click on the event and call the function, it gives you popup window to save file into your local drive.

Now user has choice to open and save the file into excel format.

 

1. The second method work well in user control.

Simple Copy and paste the code with enough and call the function on Event where it required.

public void ExportToExcel(DataTable dt)
{
Table table = ConvertDatatabletoTable(dt);
StringWriter sw = new StringWriter();
HtmlTextWriter htw = new HtmlTextWriter(sw);

table.RenderControl(htw);

HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader(“content-disposition”, string.Format(“attachment; filename={0}”, “test.xls”));
HttpContext.Current.Response.ContentType = “application/ms-excel”;
HttpContext.Current.Response.Cache.SetCacheability(HttpCacheability.NoCache);
HttpContext.Current.Response.Write(sw.ToString());
HttpContext.Current.Response.End();
}

public Table ConvertDatatabletoTable(DataTable dtSource)
{
Table tbl = new Table();
tbl.CellPadding = 0;
tbl.CellSpacing = 0;
bool AddedColumnName = false;

foreach (DataRow dtRow in dtSource.Rows)
{
TableRow row = new TableRow();
foreach (DataColumn col in dtSource.Columns)
{
if (AddedColumnName == false)
{
TableCell cell = new TableCell();
cell.Text = col.ColumnName;
row.Cells.Add(cell);
}
else
{
TableCell cell = new TableCell();
cell.Text = dtRow[col].ToString();
row.Cells.Add(cell);
}
}
tbl.Rows.Add(row);
AddedColumnName = true;
}
return tbl;
}

Advertisements

18 thoughts on “How to export DataTable into Excel file in ASP.NET

  1. An exception of type ‘System.Threading.ThreadAbortException’ occurred in mscorlib.dll but was not handled in user code

    i am getting the above error ….. m using it in Sharepoint

  2. Very great post. I just stumbled upon your weblog and wanted to mention that I have truly enjoyed browsing
    your blog posts. In any case I will be subscribing in your feed and I’m hoping you write once more soon!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s