Introduction
If you have a PHP, ASP, Ruby or whatever based website, it is usually a pain to get simple but good looking Excel spreadsheets out of the site.
This solution is too simple to be true (but it is).
I cannot lay claim to having found this out, it was my good friend Rudolph Wijburg. Basically, Microsoft put in a cool feature (the ability to read HTML out
of an XLS file) in Excel but forgot to tell anyone! So, you create a nice looking table as standard HTML, but give it a file name ending with
.xls and a mime type of 'application/excel', and away you go.
Here is a set of worked examples:
The simplest example has just a raw HTML table. Excel recognizes the difference between TH and TD and gives TH cells a bold font. The HTML for the above example is:
<html>
<body>
<table>
<tr><th>Col A</th><th>Col B</th><th>Col C</th></tr>
<tr><td>Val A1</td><td>Val B1</td><td>Val C1</td></tr>
<tr><td>Val A2</td><td>Val B2</td><td>Val C2</td></tr>
<tr><td>Val A3</td><td>Val B3</td><td>Val C3</td></tr>
</table>
</body>
</html>
Excel does not seem to respond to styling, but it does recognize the 'old fashioned' table style HTML attributes like 'border':
<html>
<body>
<table border=1>
<tr><th>Col A</th><th>Col B</th><th>Col C</th></tr>
<tr><td>Val A1</td><td>Val B1</td><td>Val C1</td></tr>
<tr><td>Val A2</td><td>Val B2</td><td>Val C2</td></tr>
<tr><td>Val A3</td><td>Val B3</td><td>Val C3</td></tr>
</table>
</body>
</html>
Individual cells can be given different background using the 'bgcolor' attribute:
<html>
<body>
<table border=1>
<tr><th bgcolor='#AAAAAA'>Col A</th><th bgcolor='#AAAAAA'>Col B</th>
<th bgcolor='#AAAAAA'>Col C</th></tr>
<tr><td>Val A1</td><td>Val B1</td><td>Val C1</td></tr>
<tr><td>Val A2</td><td>Val B2</td><td>Val C2</td></tr>
<tr><td>Val A3</td><td>Val B3</td><td>Val C3</td></tr>
</table>
</body>
</html>
You have some control over the font color and size using the font tag:
<html>
<body>
<table border=1>
<tr><th bgcolor='#AAAAAA'>Col A</th><th bgcolor='#AAAAAA'>Col B</th>
<th bgcolor='#AAAAAA'>Col C</th></tr>
<tr><td>Val A1</td><td>Val B1</td><td>Val C1</td></tr>
<tr><td>Val A2</td><td>Val B2</td><td>Val C2</td></tr>
<tr><td>Val A3</td><td>Val B3</td><td><font color='#FF0000'>Val C3</font></td></tr>
</table>
</body>
</html>
You can also mess with the font using the traditional i, u, b, etc. tags:
<html>
<body>
<table border=1>
<tr><th bgcolor='#AAAAAA'>Col A</th><th bgcolor='#AAAAAA'>Col B</th>
<th bgcolor='#AAAAAA'>Col C</th></tr>
<tr><td>Val A1</td><td>Val B1</td><td>Val C1</td></tr>
<tr><td>Val A2</td><td>Val B2</td><td>Val C2</td></tr>
<tr><td>Val A3</td><td>Val B3</td><td><font color='#FF0000'>
<b><i><u>Val C3</u></i></b></font></td></tr>
</table>
</body>
</html>
You can also even embed links using the A tag!
<html>
<body>
<table border=1>
<tr><th bgcolor='#AAAAAA'>Col A</th><th bgcolor='#AAAAAA'>Col B</th><th bgcolor='#AAAAAA'>Col C</th></tr>
<tr><td><a href='http://nerds-central.blogspot.com'>Val A1</a></td><td>Val B1</td><td>Val C1</td></tr>
<tr><td>Val A2</td><td>Val B2</td><td>Val C2</td></tr>
<tr><td>Val A3</td><td>Val B3</td><td><font color='#FF0000'><b><i><u>Val C3</u></i></b></font></td></tr>
</table>
</body>
</html>
Here is an example of HTML that, when put in a .xls file, will produce even more interesting results:
<html>
<body>
<h1>Excel HTML Examples</h1>
<h2>Simple With Formulea</h2>
Formulae work as you would expect, simply include them in =
notation, eg =SUM(A1:A84) etc.
<table border=1>
<tr><th>Col A</th><th>Col B</th><th>Col C</th></tr>
<tr><td>Val A1</td><td>Val B1</td><td>Val C1</td></tr>
<tr><td>=LEFT(A7,LEN(A7)-1) & (RIGHT(A7,1)+1)</td><td>Val B2</td><td>Val C2</td></tr>
<tr><td>=LEFT(A8,LEN(A8)-1) & (RIGHT(A8,1)+1)</td><td>Val B3</td><td>Val C3</td></tr>
</table>
<br>
<h2>Nested Tables & Differing Border Settings</h2>
It would appear that the outer table's border attribute is
ignored and the inner tables are collapsed together. But the
outer table's bgcolor attribute for the td elements works. We
can also see here that the colspan attribute of th and td works.
<table>
<tr><th bgcolor='#000000' colspan=2><font color='#FFFFFF'>Title Over To Cols</font></th></tr>
<tr>
<td bgcolor='#AAAAFF'>
<table border=1>
<tr><th>Col A</th><th>Col B</th><th>Col C</th></tr>
<tr><td>Val A1</td><td>Val B1</td><td>Val C1</td></tr>
<tr><td>=LEFT(A7,LEN(A7)-1) & (RIGHT(A7,1)+1)</td><td>Val B2</td><td>Val C2</td></tr>
<tr><td>=LEFT(A8,LEN(A7)-1) & (RIGHT(A8,1)+1)</td><td>Val B3</td><td>Val C3</td></tr>
</table>
</td>
<td bgcolor='#FFAAAA'>
<table border=4>
<tr><th>Col A</th><th>Col B</th><th>Col C</th></tr>
<tr><td>Val A1</td><td>Val B1</td><td>Val C1</td></tr>
<tr><td>=LEFT(A7,LEN(A7)-1) & (RIGHT(A7,1)+1)</td><td>Val B2</td><td>Val C2</td></tr>
<tr><td>=LEFT(A8,LEN(A7)-1) & (RIGHT(A8,1)+1)</td><td>Val B3</td><td>Val C3</td></tr>
</table>
</td>
</tr>
</table>
</body>
</html>
As always, for more tips like this, check out nerds-central.blogspot.com.
