Generating Excel documents through HTML, XML, and CSS – Part 2

Overview:

The latest versions of Excel (2007, 2010) differ from Excel 2003 in their support of streaming a HTML document containing Excel-specific markup. Although core markup features still work, some of the previously supported functionality has now been removed.

This directly affects the ability in code to stream a table to Excel and then use MSO tags to format the layout.

In the more recent versions of Excel (2007, 2010) Microsoft has reworked their support for streaming Excel information on the Web and has added refined control over the ability to create an Excel document with a series of worksheets. But sadly with each new version of Excel they are removing much of their support for streaming HTML information to the browser that worked so well for Web developers in the past.

Instead, when using the newer Microsoft syntax, a temporary file must be saved to the server before it can be sent to the browser. Further, using the new Microsoft method one cannot simply send an HTML table, but must re-query the database, and then re-generate the entire document.

In this article I cover the tried-and-true method of streaming Excel directly to a client’s browser and explain what Excel markup still works, and what doesn’t.  I will cover the newer method of doing so in a future article.

If you are interested in further background on this topic or useful links please see Part 1 of this article at: Generating Excel documents through HTML, XML, and CSS – Part 1

How to Stream the Excel Document Directly:

I recently had the requirement to output an ASP.NET GridView report in Excel, but additionally with the following Excel formatting requirements:

• Print in Landscape layout
• Add a detailed header to each printed page
• Add a detailed footer to each printed page
• Show Excel Rows and Columns on each printed page
• Freeze the header table row when paging through Excel
• Show the frozen header table row on each printed page

Response.AddHeader("content-disposition", "attachment; filename=abc.xls")
Response.ContentType = "application/ms-excel"

Then while rendering the page, add general CSS styling to the page header:

<style type="text/css">
table {mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
col{mso-width-source:auto;}
td{font-size:8pt;font-family:Arial;}
th{font-size:8pt;font-family:Arial;}
.hiddenDataField{background-color:red;}

One important style to note is the @Page style directive that controls the print options such as Landscape layout print, margins, and custom headers and footers.

@Page{

The following CSS sets the page print layout to landscape:

mso-page-orientation:landscape;

The following CSS sets the leftmost & center headers to bold custom header text, and sets the rightmost header text to show the printed page #

mso-header-data:"&L&BInternational&B&C&BAsia Pacific&B&R&BPage &P&B";

The following CSS sets the leftmost footer to custom text, and sets the rightmost footer text to show the current date (I generate this programmatically in code)

mso-footer-data:"&L&022Arial022Asia Pacific&R2011-09-08";
margin:1.0in .75in 1.0in .75in;
mso-header-margin:.5in;
mso-footer-margin:.5in;
}

The following section is MS Excel 2003 Markup that applies formatting to the HTML rendered in the worksheet. The markup below functions in post 2003 versions of Excel (2007 & 2010)

</style>
<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Int Asi 2011-09-08</x:Name>
<x:WorksheetOptions>
<x:Print>
<x:ValidPrinterInfo/>
<x:PaperSizeIndex>5</x:PaperSizeIndex>
<x:HorizontalResolution>600</x:HorizontalResolution>
<x:VerticalResolution>600</x:VerticalResolution>
</x:Print>
<x:Selected/>
<x:ProtectContents>False</x:ProtectContents>
<x:ProtectObjects>False</x:ProtectObjects>
<x:ProtectScenarios>False</x:ProtectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:WindowHeight>12780</x:WindowHeight>
<x:WindowWidth>19035</x:WindowWidth>
<x:WindowTopX>0</x:WindowTopX>
<x:WindowTopY>15</x:WindowTopY>
<x:ProtectStructure>False</x:ProtectStructure>
<x:ProtectWindows>False</x:ProtectWindows>
</x:ExcelWorkbook>
</xml><![endif]-->
</head><body>

In contrast to the preceding section, I am including below markup for Excel 2003 with sections that render in Excel 2007+, but cause errors when printing. I have highlighted in green the sections that are no longer compatible with Excel.

<!--[if gte mso 9]><xml>
<x:ExcelWorkbook>
<x:ExcelWorksheets>
<x:ExcelWorksheet>
<x:Name>Int Asi 2011-09-08</x:Name>
<x:WorksheetOptions>
<x:Print>
<x:ValidPrinterInfo/>

The x:PaperSizeIndex tag below uses a ‘5’, which forces Excel to use Legal size paper for the default print size. This tag works well, whereas the CSS to set the paper size seems to be relatively ignored by all versions of Excel.

<x:PaperSizeIndex>5</x:PaperSizeIndex>
<x:HorizontalResolution>600</x:HorizontalResolution>
<x:VerticalResolution>600</x:VerticalResolution>

The x:RowColHeading tag below tells Excel to print Row numbers and Column letters at the top and left side when the document is printed. This tag causes printing errors when I order a print in Excel 2007 & Excel 2010, but works well in Excel 2003.

<x:RowColHeading/>
</x:Print>
<x:Selected/>

The x:FreezePanes and x:Panes markup in green below tells Excel to Freeze the first row of the document. This is not printing related and only freezes the row when editing the Excel spreadsheet. This tag causes printing errors when I order a print in Excel 2007 & Excel 2010, but works well in Excel 2003.

<x:FreezePanes/>
<x:FrozenNoSplit/>
<x:SplitHorizontal>1</x:SplitHorizontal>
<x:TopRowBottomPane>7</x:TopRowBottomPane>
<x:ActivePane>2</x:ActivePane>
<x:Panes>
<x:Pane>
<x:Number>3</x:Number>
</x:Pane>
<x:Pane>
<x:Number>2</x:Number> 
<x:ActiveRow>0</x:ActiveRow> 
</x:Pane>
</x:Panes>
<x:ProtectContents>False</x:ProtectContents>
<x:ProtectObjects>False</x:ProtectObjects>
<x:ProtectScenarios>False</x:ProtectScenarios>
</x:WorksheetOptions>
</x:ExcelWorksheet>
</x:ExcelWorksheets>
<x:WindowHeight>12780</x:WindowHeight>
<x:WindowWidth>19035</x:WindowWidth>
<x:WindowTopX>0</x:WindowTopX>
<x:WindowTopY>15</x:WindowTopY>
<x:ProtectStructure>False</x:ProtectStructure>
<x:ProtectWindows>False</x:ProtectWindows>
</x:ExcelWorkbook>

The x:ExcelName section markup below tells Excel to Freeze the first row of the document. This is only printing related and is not visible when editing the Excel spreadsheet. This tag causes printing errors when I order a print in Excel 2007 & Excel 2010, but works well in Excel 2003.

<x:ExcelName> 
<x:Name>Print_Titles</x:Name> 
<x:SheetIndex>1</x:SheetIndex> 
<x:Formula>='strWorkBookName'!$1:$1</x:Formula> 
</x:ExcelName>

</xml><![endif]-->
</head><body>

I hope this information has helped you and answered some questions about the process of  outputting HTML table data to Excel and then formatting the Excel document. I will continue to research possible alternatives and will post my findings if I make any further progress.

Here are the related articles I have written that cover various techniques of generating Excel documents on your ASP.NET Web pages:

• Generating Excel documents through HTML, XML, and CSS – Part 1
• ASP.NET Export to Excel: Set the Default Print Paper Size Programmatically
• Generating an Excel Report on the Fly Using the Interop.Excel Namespace