100% Easy, 100% Free, 100% Best Way of Producing Simple Excel Spreadsheets From the Web

alex turner

Rate me:

3.00/5 (8 votes)

1 Mar 2007CPOL1 min read

50.1K

273

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).

Download sample - 141 KB

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:

Excel HTML 1

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

<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':

Excel HTML 2

HTML

<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:

Excel HTML 3

HTML

<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:

Excel HTML 4

HTML

<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:

Excel HTML 5

HTML

<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!

Excel HTML 6

HTML

<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

<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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Written By

alex turner

Web Developer

United Kingdom

I am now a Software Systems Developer - Senior Principal at Micro Focus Plc. I am honoured to work in a team developing new compiler and runtime technology for Micro Focus.

My past includes a Ph.D. in computational quantum mechanics, software consultancy and several/various software development and architecture positions.

For more - see

blog: http://nerds-central.blogspot.com

twitter: http://twitter.com/alexturner

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

100% Easy, 100% Free, 100% Best Way of Producing Simple Excel Spreadsheets From the Web

Introduction

License

Comments and Discussions