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:
- Advanced Macromedia ColdFusion MX 7 Application Development
- Microsoft Office 2000 Technical Articles & Microsoft® Office HTML and XML Reference & Microsoft Excel 2002 Technical Documentation
- A StackOverflow Article
- A Telerik Blog Article
- A Discussion Thread on the ASP.NET Forums
The bottom line is that the developer is advised to Import the HTML to Excel, and then to save in XML (or HTML) format, and then use notepad to view the source and copy the useful code.
I found this to be partially useful, but in many instances the saved code was completely useless for the task at hand. In hindsight the problem was that I am using Excel 2007. After much research I tried an old version of Excel 2002 and happily I was able to get the information I needed.
I am not sure why, but from what I can see Microsoft has changed the syntax and methodology of integrating HTML, XML, and CSS in in their latest versions of MS Office (2007+ ).
I plan to research this further in the future to see what options the newer versions of MS Office provide to generate and manage spreadsheets and documents, but for now I will continue coding in the mixed HTML, XML, CSS syntax since I find this to be most efficient.
I provide the exact code and methodology I used to format the Excel document in Part 2 of my blog series on Generating Excel documents through HTML, XML, and CSS .
Also if you are interested in generating Excel documents from a direct database call on your Web page, then you can read more about Generating an Excel Report on the Fly Using the Interop.Excel Namespace.