HTML To Excel The Easy Way
27 July 2005 · Last updated: 15 October 2006
While experimenting with ASP recently I discovered it can output files as various types, such as Excel and even images. Take a look at the following example. If you save the code in a file with the extension .asp and run it from the server, it offers to download an Excel file for you.
<%response.ContentType="application/vnd.ms-excel"%><html><head><style type="text/css">td {padding:2em; background-color:yellow; border:1px dotted #000;}</style></head><body><table><tr><td>1</td><td>2</td><td>3</td><td>4</td></tr><tr><td>=sum(a1:d1)</td><td>6</td><td>7</td><td>8</td></tr></table></body></html>
I opened the resulting Excel file and found... the same code! Normally, Excel files are full of unreadable characters that you cannot decipher in a word processor. So I was very surprised to see that ASP had simply delivered the exact same code to create an Excel file. This means two things:
- Excel is capable of loading raw HTML!
- You don't need to use ASP to create Excel files from HTML. Just rename the file so it has the extension .xls!
I started to experiment with what you could put in HTML that would also work in Excel. The above code demonstrates some of what I managed to get working. I couldn't find a way to adjust the cell height or width, except by using padding. Borders work nicely though, without the need for collapsing the table cells via CSS. Solid borders are quite thick though.
Note the formula in one cell. It works! You have to expand the cell to see the result though.
Here's another test I developed separately when having problems with fonts on my system. I loaded it into Excel without changing it, just by renaming the file from .html to .xls. Each line was correctly styled in Excel with the right font.
<html><head><title>Font Test</title><style type="text/css">body {font-size:170%;}.f1 {font-family:helvetica;}.f2 {font-family:arial;}.f3 {font-family:univers;}.f4 {font-family:palatino;}.f5 {font-family:'palatino linotype';}.f6 {font-family:courier;}</style></head><body>
<p class="f1">Helvetica: This is a TEST for the font. 1234567890</p>
<p class="f2">Arial: This is a TEST for the font. 1234567890</p>
<p class="f3">Univers: This is a TEST for the font. 1234567890</p>
<p class="f4">Palatino: This is a TEST for the font. 1234567890</p>
<p class="f5">Palatino Linotype: This is a TEST for the font. 1234567890</p>
<p class="f6">Courier: This is a TEST for the font. 1234567890</p>
</body></html>
This could pave the way for offering Excel versions of tables on your website. Experiment yourself with the code and see what you can achieve.
Notes:
- The above information refers to using Excel 2003 on Windows XP. Other versions and platforms may differ.
- Word 2003 is also capable of loading HTML files renamed with the .doc extension. Like Excel though, the results will vary. To get full control over page layout and style, you'd have to use genuine Word XML formatting, which you can see by opening any Word document in a plain text editor, such as Notepad.
