Category: Excel 2003

ASP.NET, C#, Excel, Excel 2003, Excel 2007, Excel 2010, HTML, Programming, Software, VB.NET, Web Development

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

Justin Cooney

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
Using the Excel 2003 document markup tags I am able to make each of these requirements visible in Excel regardless of the version. However some of the requirements make the printing process fail when trying to print with Excel 2007 or Excel 2010 (Excel 2003 of course works just fine).

The requirements that cause the printing process to fail with Excel 2007 or Excel 2010 are:

  • 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
So without further ado, here is the code that still works in all versions of Excel Continue reading “Generating Excel documents through HTML, XML, and CSS – Part 2” →
Advertisement
ASP.NET, Excel, Excel 2003, Excel 2007, Excel 2010, Programming, Software, Web Development

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

Justin Cooney

I recently had the requirement to take the HTML from a batch of reports on a Web application and output these with very specific formatting requirements to Excel. I am familiar with the basic syntax of setting the content type to ‘application/ms-excel’ before streaming  the HTML content. However things get rapidly more complex if the requirements include actually setting presets in Excel.

I’m surprised at how little documentation there is for how to do this online although everyone has a need to do it. The lack of information from the Microsoft official articles is also quite amazing. Basically the developer is left to their own devices when writing code to create spreadsheets dynamically through HTML, XML, and CSS.

The most helpful information I was able to find was at the following sites: Continue reading “Generating Excel documents through HTML, XML, and CSS – Part 1” →

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

Justin Cooney

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. Continue reading “ASP.NET Export to Excel: Set the Default Print Paper Size Programmatically” →