Justin Cooney

Web Development Tips and Examples

about

I am a Senior Applications Programmer / Analyst with years of experience developing enterprise solutions using the Microsoft technology stack including C#, VB.NET, ASP.NET, AJAX, IIS and SQL Server. I specialize in Web application development with a focus on building secure systems, integrating applications, and designing robust database structures.

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

2011-08-31

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:

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

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

Generating Excel documents through HTML, XML, and CSS – Part 2 « Justin Cooney – Senior Programmer / Analyst

2011-09-08

[…] ASP.NET Export to Excel: Set the Default Print Paper Size Programmatically Eco World Content From Across The Internet. Featured on EcoPressed What's the greenest way to eat a hotdog? Advertisement LD_AddCustomAttr("AdOpt", "0"); LD_AddCustomAttr("Origin", "other"); LD_AddCustomAttr("theme_bg", "f0f0f0"); LD_AddCustomAttr("theme_border", "cccccc"); LD_AddCustomAttr("theme_text", "555555"); LD_AddCustomAttr("theme_link", "008DCF"); LD_AddCustomAttr("theme_url", "008DCF"); LD_AddCustomAttr("LangId", "1"); LD_AddCustomAttr("Autotag", "technology"); LD_AddCustomAttr("Autotag", "education"); LD_AddCustomAttr("Autotag", "science"); LD_AddCustomAttr("Tag", "asp-net"); LD_AddCustomAttr("Tag", "c"); LD_AddCustomAttr("Tag", "excel-software"); LD_AddCustomAttr("Tag", "excel-2003"); LD_AddCustomAttr("Tag", "excel-2007"); LD_AddCustomAttr("Tag", "excel-2010"); LD_AddCustomAttr("Tag", "html"); LD_AddCustomAttr("Tag", "programming"); LD_AddCustomAttr("Tag", "software"); LD_AddCustomAttr("Tag", "vb-net"); LD_AddCustomAttr("Tag", "web-development"); LD_AddSlot("wpcom_below_post"); LD_GetBids(); Share this:TwitterLike this:LikeBe the first to like this post. […]

Reply
1. Hallie
  
  2012-01-12
  
  I really couldn’t ask for more from this atrlcie.
  
  Reply
  1. Justin Cooney
    
    2012-01-12
    
    Thanks for your feedback, I’m glad the information in the article was useful to you.
    
    Reply
Sharyl Verrill

2012-01-11

Some genuinely choice articles on this site, saved to fav.

Reply
1. Justin Cooney
  
  2012-01-11
  
  Thanks for the good feedback, I’m glad you found some of the articles useful.
  
  Reply
Melloney

2012-01-12

Full of salient pontis. Don’t stop believing or writing!

Reply
1. Justin Cooney
  
  2012-01-12
  
  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!
  
  Reply
tree removal Delaware

2012-01-22

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.

Reply
Winfred Barnaba

2012-02-01

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.

Reply
1. Justin Cooney
  
  2012-02-02
  
  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!
  
  Reply
Ravi Deshmukh

2012-08-28

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

Reply
1. Justin Cooney
  
  2012-08-28
  
  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
  
  Reply