For 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:

C#
1
2
Response.ContentType = "application/ms-excel";
Response.AddHeader("content-disposition", "attachment; filename=test.xls");
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:

C#
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
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>");
      }
   }
}
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/