Jerry Nixon on Windows: In ASP.Net: export results to Excel

Jerry Nixon on Windows

Wednesday, January 14, 2009

In ASP.Net: export results to Excel

Seriously, who isn't asked to perform this task?

The general technique is this: Excel renders HTML just fine - pass Excel your HTML and you have what you want. The trick: ContentType.

Here's how:

// setup for excel
//
Response.Clear();
Response.Charset = "";
Response.ContentType = "application/vnd.ms-excel";
//
// fetch data, fill the grid (any HTML)
//
var _Query = from Result in new DataContext().Employees
select Result;
GridView _GridView;
_GridView = new GridView();
_GridView.DataSource = _Query;
_GridView.DataBind();
//
// write it out
//
System.IO.StringWriter _StringWriter;
_StringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter _HtmlTextWriter;
_HtmlTextWriter = new HtmlTextWriter(_StringWriter);
_GridView.RenderControl(_HtmlTextWriter); // key!
Response.Write(_StringWriter.ToString());
Response.End();