Home > Deveploment and Programming, SharePoint Development > How to export DataTable into Excel file in ASP.NET

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
  1. dima
    31/05/2011 at 8:26 pm

    Thanks it’s like a charm)

  2. meera
    05/08/2011 at 4:52 pm

    can we save directly to D drive without asking them…

  3. 13/02/2012 at 8:04 pm

    can i store excel file in solution explorer and then make a zip file of it?????

  4. Helga
    03/03/2012 at 5:21 pm

    Amazing code, worked in the first shot! Thank you!

  5. shri
    26/03/2012 at 12:11 pm

    Thanks Man It saved my day.

  6. Rafael camacho
    24/08/2012 at 9:13 pm

    Thank you so much for your code it was very helpfull to me.
    it really works great.

  7. Sandeep
    18/09/2012 at 1:25 pm

    Thanks… useful code for me.

  8. 11/01/2013 at 2:35 pm

    thanx … very useful.

  9. sagar
    14/02/2013 at 4:16 pm

    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

  10. 24/04/2013 at 8:26 am

    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!

  11. Shiva
    07/06/2013 at 10:41 am

    I want to save the excel file into Local Disk . It is possible in this methods

    • 09/06/2013 at 11:27 am

      Yes it allow you to save the file into local disk.

  12. 23/08/2013 at 1:20 am

    I constantly spent my half an hour to read this blog’s articles every day along with a cup of coffee.

  13. Laney
    23/12/2013 at 2:26 pm

    You can export datatable into excel using C#/VB.NET by using Aspose.Cells for .NET Library. You can view the code here:

    http://www.aspose.com/docs/display/cellsnet/Exporting%20Data%20from%20Worksheets

  14. 30/04/2014 at 2:51 pm

    very informative. thanks

    I have found another example for the same refer to http://www.etechpulse.com/2014/04/exporting-div-dynamic-content-to-excel.html

  15. 23/10/2017 at 3:20 pm

    Here’s a similar which export gridview data to excel https://codepedia.info/export-gridview-to-excel-in-asp-net-c/

  1. No trackbacks yet.

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

%d bloggers like this: