bobdc.blog

I used to think that a website's "download as spreadsheet" button triggered some back end process that created a binary Excel spreadsheet on the server and sent that to your browser, much like many "download as PDF" links do. It turns out that it's much, much simpler than that.

The key is that the server doesn't really send the file as a spreadsheet. It sends it as HTML with a MIME media type of "application/vnd.ms-excel". In other words, the server adds an HTTP header field that tells your browser "here comes an Excel spreadsheet, so display this with whatever program you use to view those", and Excel can open up HTML files. (Tell OpenOffice Calc to open an HTML file and it opens it up in OpenOffice Writer, the word processing program.) This way, users think that they're downloading spreadsheets. Lots of HTML formatting is preserved and numbers get treated as numbers, so that if after downloading you add a function like =sum() in a cell that references other cells, it does the math properly. You'll want your HTML to have one or more tables in it—there's not much point in sending a Shakespeare play to Excel.

The first few times I did this, I wrote perl and python CGI scripts to send the HTTP header identifying the MIME type to the browser with the file, but if you're using an Apache web server, an .htaccess file gives you an easier way to do it. Among other things, an .htaccess file lets you say "for files in this directory with extension foo, the web server should deliver them with MIME type bar." I first learned about these files when someone told me that this weblog's Atom feed wasn't being delivered with the correct MIME type, and he recommended that I fix it with an .htaccess file. I created the following one-line file in my http://www.snee.com/bobdc.blog directory:

AddType application/atom+xml .atom

To deliver HTML tables as spreadsheets with no CGI coding, I created a http://www.snee.com/xml/wks directory and put the following .htaccess file in it:

AddType application/vnd.ms-excel .html

This tells the Apache server to deliver any files with an extension of html that are in this directory (I certainly wouldn't want this to apply to all HTML files!) with a MIME type of "application/vnd.ms-excel". You can see a test result by looking at http://www.snee.com/xml/wks/wks1.html. I threw in two copies of a table and some other HTML elements to try to confuse Excel, but it wasn't confused. To see what the file really looks like, see http://www.snee.com/xml/wks/wks1.xml, an exact copy whose delivery is unaffected by the .htaccess file.

So, if your website includes tables and you'd like to offer viewers the option to download them as spreadsheets, you can keep HTML copies in a directory with an .htaccess file like the one shown above and point your "download as spreadsheet" links there. Or, if you have a more complex system that generates pages on the fly, the content generation routines hopefully give you some way to set the Content-type in the HTTP header to "application/vnd.ms-excel" for selected HTML output.

If I was writing this from home instead of a hotel room, I'd try it on our Mac and Linux machines to see if the file opens up in OpenOffice Calc. Of course, if I was at home, I'd have other things to do besides playing with MIME media type tricks.