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:
12 thoughts on “ASP.NET Export to Excel: Set the Default Print Paper Size Programmatically”
I really couldn’t ask for more from this atrlcie.
Thanks for your feedback, I’m glad the information in the article was useful to you.
Some genuinely choice articles on this site, saved to fav.
Thanks for the good feedback, I’m glad you found some of the articles useful.
Full of salient pontis. Don’t stop believing or writing!
Thanks for the encouragement! Programming for Excel is often a task developers face, and the best way of doing so can be situational. I hope this article has been of use!
We’re a bunch of volunteers and opening a brand new scheme in our community. Your site provided us with valuable information to work on. You’ve done an impressive activity and our entire group will probably be grateful to you.
Admiring the time and effort you put into your blog and detailed information you present. It’s nice to come across a blog every once in a while that isn’t the same out of date rehashed information. Fantastic read! I’ve saved your site and I’m including your RSS feeds to my Google account.
Thanks for the great feedback, I’m glad you like this blog! I will keep trying to provide fresh & relevant content as best I can!
I don’t know whether it’s just me or if everybody else encountering problems with your site.
It seems like some of the written text within your content are running off the screen.
Can someone else please comment and let me know if this is happening to them
too? This might be a issue with my internet browser because I’ve had this happen before. Cheers
Some of the code samples do run over the width of the page, but you should be seeing a scrollbar below the sample that will let you see the full lines of code. I’ll take a look into extending the page widths so more of the code is visible by default. Cheers