Sending Excel 97/XP/2000/2003/2007 Output from ASP.NET 2.0

  • Article

I know there are several articles on the internet describing varous methods to send excel output from ASP.net.   Additional, there are many great third party tools as well.   I needed to do something simple and lightweight so I expanded on some examples.   I needed to do some formating and embed and image.   But first there is some setup to do.

Setup Web.config to enable XLS to be compiled and executed as ASP.NET code by the runtime

Under the <system.web> section:

<

compilation debug="true">
<buildProviders>
<add extension=".xls" type="System.Web.Compilation.PageBuildProvider"/>
</buildProviders>
</compilation>

<httpHandlers>
<add verb="*" path="*.xls" type="System.Web.UI.PageHandlerFactory" validate="true"/>
</httpHandlers>

Create Excel Output page

  • Add a web form name ExcelOut.aspx
  • Remove all html code, just leave the <@ Page ...> directive line
  • In code behind add the following code in the page load event (please note that html generated is incomplete and is only to show as a sample, please follow all html best practices in a production envrionment)

Response.Clear();
Response.Buffer =

true;
Response.ContentType = "application/vnd.ms-excel";
Response.Charset = "";
this.EnableViewState = false;
System.IO.StringWriter ExcelStringWriter = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(ExcelStringWriter);
ExcelStringWriter.Write("<table border=1><tr>");
ExcelStringWriter.Write("<td><b>blah</b></td>");
ExcelStringWriter.Write("<td width=\"100\"><img src=\"https://search.msdn.microsoft.com/search/masterpages/items/msdn.jpg\" /></td>");
ExcelStringWriter.Write("<td>1</td><td style=\"mso-number-format: _(&quot;$&quot;* #,##0.00_);_(&quot;$&quot;* \\(#,##0.00\\);_(&quot;$&quot;* &quot;-&quot;??_);_(@_)\">5000</td>");
ExcelStringWriter.Write("</tr></table>");
Response.Write(ExcelStringWriter.ToString());
Response.End(); ****

Create a web form then rename to XLS extension

Create the page to call ExcelOut

  • Add a new web page called TestExcel.aspx

  • Add a hyperlink in the page like

    <

    a href="Excelout.xls">Excel output</a>

Compile and run and you should something similar to ...

Even though the example below is an HTML table please note that the output is in excel and each item is in a excel cell.  The "blah" is in cell A1 with font style of bold,  "1" is in cell c1, "5000" is in cell d1 with a format on the cell as "$#,##0.00".

blah 1 $5,000.00

Summary

This is just an example how to export to Excel from ASP.NET using a simple html snippet that includes images and text formatting.   Let me know what you think or any code improvements.