ASP.NET, Excel, Excel 2003, Excel 2007, Excel 2010, Programming, Software, Web Development

ASP.NET Export to Excel: Set the Default Print Paper Size Programmatically

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:

Advertisements

12 thoughts on “ASP.NET Export to Excel: Set the Default Print Paper Size Programmatically”

    1. 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!

  1. 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.

  2. 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.

  3. 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

    1. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s