I received an email recently with a question about an old weblogs.asp.net\dburke post on sending output directly to Excel, with Excel opening in native mode. This is a great capability with ASP.NET and Microsoft Office 2003 and I wanted to provide the coding framework for others to follow if they were unaware of how to do this. The trick is to send any parameters to a new webpage (if generated dynamically based on user input), then send the HTTP response headers fresh from the new page. The body of the new page is ignored, with output generated to Excel. The code has been stripped to its essential logical elements. Follow the links from the original post if you want more background info.
private void Page_Load(object sender, System.EventArgs e)
{
if (!IsPostBack)
{
param = Request.QueryString["someparameter"].ToString();
cols = SomeInteger;
Generate();
}
}
public void Generate(string param)
{
WriteHeader();
WriteColHeadings();
WriteColumns();
WriteFooter();
}
private void WriteHeader()
{
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.Charset = " ";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=myrpt_.xls;");
HttpContext.Current.Response.Write("<table border=0>");
HttpContext.Current.Response.Write("<tr><td colspan=" + cols + " style='font-family:Arial; font-size:16pt'>MyHeader</td></tr>");
HttpContext.Current.Response.Write("<tr><td colspan=" + cols + " style='font-family:Arial; font-size:14pt'>MySubHeader</td></tr>");
HttpContext.Current.Response.Write("<tr><td colspan=" + cols + "> </td></tr>");
}
private void WriteColHeadings()
{
HttpContext.Current.Response.Write("<tr>");
HttpContext.Current.Response.Write("<td style='width:78pt; font-family:Arial; font-size:12pt'>Header Field 1</td>");
HttpContext.Current.Response.Write("<td style='width:78pt; font-family:Arial; font-size:12pt'>Header Field 2</td>");
....
HttpContext.Current.Response.Write("</tr>");
}
private void WriteColumns()
{
DataTable dt = oSomeClass.SomeMethod(param);
for (int i=0; i < dt.Rows.Count; i++)
{
HttpContext.Current.Response.Write("<tr valign=\"top\">");
HttpContext.Current.Response.Write("<td align=\"left\">" + dt.Rows[i]["fieldname"].ToString() + "</td>");
...
HttpContext.Current.Response.Write("</tr>");
}
}
private void WriteFooter()
{
HttpContext.Current.Response.Write("<tr height=\"20px\"><td colspan=" + cols + "></td></tr>");
HttpContext.Current.Response.Write("</table>");
HttpContext.Current.Response.End();
}