XML FeedsFor a long time I have had to make web reports for our company that could be exported to excel. This is a fairly easy process. All you have to do is put your report in an HTML table and add the following 2 lines of code:
- Response.ContentType = "application/ms-excel";
- Response.AddHeader("content-disposition", "attachment; filename=test.xls");
However, when the excel file is generated it has a nasty side effect of having all the gridlines hidden. They can easily be turned back on in excel by the following: Tools > Options > click gridlines checkbox.
Until today I put up with the gridlines being hidden. When I tried to search for a solution via google, most people suggested that it just wasn't possible to generate the excel report with gridlines. Other people offered solutions that required you to run a COM object on the server to start an instance of excel in the background to create the file. However, after searching through a bunch of garbage and piecing together bits and pieces of non-working solutions, I finally got it to work. The trick is to set up your own custom XML settings, and add the "Panes" worksheet option. Here was the working solution:
- using System;
- using System.Data;
- using System.Configuration;
- using System.Collections;
- using System.Web;
- using System.Web.Security;
- using System.Web.UI;
- using System.Web.UI.WebControls;
- using System.Web.UI.WebControls.WebParts;
- using System.Web.UI.HtmlControls;
- namespace excelGridlineTest
- {
- public partial class _Default : System.Web.UI.Page
- {
- protected void Page_Load(object sender, EventArgs e)
- {
- Response.ContentType = "application/ms-excel";
- Response.AddHeader("content-disposition", "attachment; filename=test.xls");
- Response.Write("<html xmlns:x=\"urn:schemas-microsoft-com:office:excel\">");
- Response.Write("<head>");
- Response.Write("<meta http-equiv=\"Content-Type\" content=\"text/html;charset=windows-1252\">");
- Response.Write("<!--[if gte mso 9]>");
- Response.Write("<xml>");
- Response.Write("<x:ExcelWorkbook>");
- Response.Write("<x:ExcelWorksheets>");
- Response.Write("<x:ExcelWorksheet>");
- //this line names the worksheet
- Response.Write("<x:Name>gridlineTest</x:Name>");
- Response.Write("<x:WorksheetOptions>");
- //these 2 lines are what works the magic
- Response.Write("<x:Panes>");
- Response.Write("</x:Panes>");
- Response.Write("</x:WorksheetOptions>");
- Response.Write("</x:ExcelWorksheet>");
- Response.Write("</x:ExcelWorksheets>");
- Response.Write("</x:ExcelWorkbook>");
- Response.Write("</xml>");
- Response.Write("<![endif]-->");
- Response.Write("</head>");
- Response.Write("<body>");
- Response.Write("<table>");
- Response.Write("<tr><td>ID</td><td>Name</td><td>Balance</td></tr>");
- Response.Write("<tr><td>1234</td><td>Al Bundy</td><td>45</td></tr>");
- Response.Write("<tr><td>9876</td><td>Homer Simpson</td><td>-129</td></tr>");
- Response.Write("<tr><td>5555</td><td>Peter Griffin</td><td>0</td></tr>");
- Response.Write("</table>");
- Response.Write("</body>");
- Response.Write("</html>");
- }
- }
- }
Got a web related question? Discuss it in the forums: http://forum.lessthandot.com/
Sweet, will have to try this out soon.
You should wrap that into a simple reusable object. 
Great post, thanks! 
Nice post kaht! I'm having my developers read it today or they are fired! :P 
Thanks!!! You really helped me with this post. 
Great help man...I've looking for something for this since a long time and you gave me what I wanted exactly...
Hi,
Great post. Thank you. I’m currently working with PHP and I was facing problems with exporting my report data to excel with the grid lines visible. Now I can easily export my report to excel from the print-friendly view. 
I spent all morning looking for a solution for this, then stumbled on this post. This worked perfectly. Thank you! 
Great solution. I have to export a web page to Excel and I thought I was going to have to build the excel file from scratch. This solved the problem. 
Solved my problem, thank you very very much!:) 
Thanks for the info. You confirmed what I suspected: some of the magic is done via XML. You saved me some experimenting. But for those who want to experiment more, try creating a VERY simple .xls file in excel. Then save it AS an xml file. You'll see that lots of stuff gets generated for you. Change one item, like color a cell, and then save again as a different xml file. You'll be able to see how your change was rendered my excel. So, if you want to script a more complex spreadsheet, including multiple worksheets, gradually add more items and check out the generated XML. It's not TOO difficult if you take it slow. 
Hi I generate html code from source code and i want to export it in an excel file, any suggestions? 
Great Post, how do you create another worksheet and add another html table to it? 
Thanks a lot. Actually I was looking for and searching a lot for my asp application. It works.......... Thanks once again. 
Man, I spent two days looking for this.
LTD Social Sitings
Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.