Sending Excel 97/XP/2000/2003/2007 Output from ASP.NET 2.0
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: _("$"* #,##0.00_);_("$"* \\(#,##0.00\\);_("$"* "-"??_);_(@_)\">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.
Comments
Anonymous
February 20, 2007
How do you solve the green triangle problem with this method? I've used it before, even with the mso: declarations, and it randomly get green triangles with the error "This number is stored as text!". It's completely random too, it only works for some data sets of a particular size, and the starting point is random as well. The only foolproof way I could think of working around it was outputing and redirecto to .csv files.Anonymous
May 10, 2007
Hi i know it's a bit late but when i was solving the same problem you have with the green triangle i found a solution and so here it is: <td align=right style='width:46pt' x:num> any_number </td>Anonymous
July 25, 2007
Please modify the title to "Sending Excel 97/XP/2000/2003/2007 Output from ASP.NET 2.0". ...some of use are still working with the 1.1 Framework. :)Anonymous
July 27, 2007
Apologies for not being clear. In light of all the changes -> .Net 2.0, 3.0, and upcoming 3.5 it is important to understand which version. You could achieve a similar affect using .net 1.1 it just takes a little more effort :)Anonymous
December 26, 2007
Mark, what can be done to 'easily' eliminate the msgbox that appears when trying to extract data from a website to excel? It all worked just fine in xp, but vista has issues. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1772697&SiteID=1