I’ve been working on adding the ability to export to Excel format to reports that I have written in ASP.NET. While doing this I have received numerous feature requests including the ability to set by default the paper size to be printed to.
As a little bit of background on the topic: I use a traditional method of streaming the HTML version of the report to the browser with the content type set to MS Excel. This allows me to send Excel-specific markup to format parts of the Excel document.
One can set the default page settings for an Excel document using the Microsoft Excel XML Spreadsheet model (SpreadsheetML). Specifically, the XML hierarchy is:
<x:ExcelWorkbook> <x:ExcelWorksheets> <x:ExcelWorksheet> <x:WorksheetOptions> <x:Print> <x:PaperSizeIndex>
Although documentation exists on the Microsoft Excel XML Spreadsheet model, I have not actually seen any detailed description on the options available when setting the page sizes. Since these are simply numeric values their meaning is not immediately apparent to me so I have been testing what each value means by trial and error.
Here are my findings:
PaperSizeIndex: >4 = Unknown PaperSizeIndex: 5 = Legal (8.5" x 14") PaperSizeIndex: 6 = Statement (5.5" x 8.5") PaperSizeIndex: 7 = Executive (7.25" x 10.5") PaperSizeIndex: 8 = A3 (11.69" x 16.54" - 297 x 420mm) PaperSizeIndex: 9 = A4 (8.27" x 11.69" - 210 x 297mm) PaperSizeIndex: 10 = Unknown PaperSizeIndex: 11 = A5 (5.83" x 8.27" - 148 x 210mm) PaperSizeIndex: 12 = JIS B4 (10.12" x 14.33" - 257 x 364mm) PaperSizeIndex: 13 = JIS B5 (7.17" x 10.12" - 182 x 257mm) PaperSizeIndex: 14 = - (8.5" x 13") PaperSizeIndex: 15 = Unknown PaperSizeIndex: 16 = Unknown PaperSizeIndex: 17 = Tabloid (11" x 17") PaperSizeIndex: 18 = Unknown PaperSizeIndex: 19 = No. 9 Envelope (3.9" x 8.9") PaperSizeIndex: 20 = Envelope No. 10 (4.125" x 9.5") PaperSizeIndex: 21 = Unknown PaperSizeIndex: 22 = Unknown
So, for example, to set the default paper size to Legal the XML tag via ASP.NET would be:
Response.Write("<x:PaperSizeIndex>5</x:PaperSizeIndex>" & ControlChars.CrLf)
In this article I have covered how to set the default Print Paper Size. For further help with exporting HTML tables to Excel and formatting them using XML, CSS, and Excel Markup see my other two articles on the subject:
- Generating Excel documents through HTML, XML, and CSS – Part 1 (The Introduction)
- Generating Excel documents through HTML, XML, and CSS – Part 2 (The Code-Level Details)