I guess that we all have had to create some Excel files for doing reports from a web page at one time or another, and one quite common approach is to simply render the data as comma/tab-separated text and then specify some appropriate Response headers and contenttype to make it “appear” as an Excel file in the client browser.

While this simple approach usually works, there are some major drawbacks:

• You cannot spread your data over more than one sheet.
• You cannot include graphs/formulas/macros et.c. nor can you control formatting.
• Browser may show a warning that the actual file extension used doesn't match with the specified Content-Type thus conusing the user
• Browser might misinterpret the response headers so the user is faced with the option to download the file with a name like "GetFile.aspx?id=whatever" thus loosing the Excel file extension.

Another approach has been to invoke the Excel application on the server, then automate it through OLE to produce the Excel file which is then send it back to the client.

The obvious problem with this approach is performance. Firstmost, its quite slow and it consumes quite a lot of memory. Secondly, as Excel is considered a single user "desktop application" and was never intended to be executed server side, there's really no official "support" for it either. (Imagine for example what would happen if a lot of such "Excel requests" comes in at the same time...)

To get around all of these problems, I've started to take on another approach:
Generate "real" Excel 2007 files using the System.IO.Package and System.Xml classes and then "deliver" it through a custom VirtualPathProvider so the client browser really thinks it's downloading a physical Excel file with appropriate name and extension. As you may know, Excel2007’s native format (xlsx) really is nothimg but a .zipfile with a bunch of xmlstreams inside.

I usually use an existing Excel 2007 file as a template, either embedded as a resource in the assembly or located on disk, rather than create a new package from scratch. This template file may contain any macros, formatting, graphs et.c. which can easily be edited through Excel.

Here’s a small sample demonstrating its use:
http://labs.episerver.com/SampleExcel/default.aspx

The complete source code for the above sample can be downloaded from http://labs.episerver.com/PageFiles/111555/SampleExcel.zip
Unzip to your EPiServer CMS 5 web folder, open the project in VisualStudio 2008.
(The zip also contains a prebuilt SampleExcel.dll which will be placed in your /Bin folder).

To run it locally on your system simply point your browser to >/SampleExcel/default.aspx">http://<<yoursite>>/SampleExcel/default.aspx

The module works by adding a custom virtualpathprovider to the system registering to “take care” of all requests made to “/PageFiles/$$SampleExcel/*”. “Taking care” in this sense simply means returning the same custom virtualfile object which in its Open() method creates a Excel package (in a memorystream) from an embedded Excel file resource, fills some cells with random data, saves back the package and returns it.

Hope you’ll find it useful.

/johan