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

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

License

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

About the Author

alex turner

Web Developer

United Kingdom

Member

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

Article Top

Poor

Excellent

Add a reason or comment to your vote: x
Votes of 3 or less require a comment

Comments and Discussions

You must Sign In to use this message board. (secure sign-in)

Search this forum
	Profile popups Noise Layout Per page

How to creat two sheet??

amber.chang

20:10 15 Aug '07

where is the grid?

hen100

8:34 29 Apr '07

how to set page margin of excel and print style [modified]

dddd218

0:25 24 Feb '07

how to set page margin of excel,such as left margin, top margin. how to set print style,such as vertical or horizontal print. -- modified at 4:01 Sunday 25th February, 2007
Sign In·View Thread·Permalink

Re: how to set page margin of excel and print style

alex turner

2:48 25 Feb '07

Frankly, I don't know. I never really expected this technique to be able to do anything very complex with Excel, there will quickly come a point where you cannot go any further with a method as simple as this. If you find a solution, please let us know! nerds-central.blogspot.com - welcomes all nerds
Sign In·View Thread·Permalink

also to mention

dvhh

2:10 20 Feb '07

stylesheet are also supported but they must be embedded (inline or style tag) and excel only support 1 style per cell plus by analysing html export from excel you can learn a lots of things (like custom office style like rotation for text, custom attribute for number formatting, etc .... )
Sign In·View Thread·Permalink

Re: also to mention

alex turner

2:20 20 Feb '07

Thanks, I could not get stylesheets to work with html inside a .xls file . Can you post a brief example for us? Many thanks AJ nerds-central.blogspot.com - welcomes all nerds
Sign In·View Thread·Permalink

Formulae And Further Layout Techniques

alex turner

0:50 20 Feb '07

I have added a further page to my blog with formulae and more layout and inclusion feature explained for this technique: http://nerds-central.blogspot.com/2007/02/100-simple-excel-from-web-advanced.html. nerds-central.blogspot.com - welcomes all nerds
Sign In·View Thread·Permalink

Formulas & Functions

bdlogic

8:06 19 Feb '07

This is very nice, thank you. How can you add formulas or functions or even some of the other complex capabilities to the cells? -alam
Sign In·View Thread·Permalink

Re: Formulas & Functions

n'Anders

21:00 19 Feb '07

You can write simple functions just like you would in Excel. I.e.		=SUM(A1:A10)
Sign In·View Thread·Permalink	5.00/5 (1 vote)

Re: Formulas & Functions

alex turner

0:54 20 Feb '07

I have written some more stuff up here: http://nerds-central.blogspot.com/2007/02/100-simple-excel-from-web-advanced.html. nerds-central.blogspot.com - welcomes all nerds
Sign In·View Thread·Permalink

Forgot something...

Leftend

6:08 19 Feb '07

You forgot to mention that you have to change the MIME type of the page for the browser to recognize it as an Excel document. Like so: Response.ContentType = "application/vnd.ms-excel"
Sign In·View Thread·Permalink	4.00/5 (1 vote)

Re: Forgot something...

alex turner

23:08 19 Feb '07

Thanks! I did mention it - but too briefly. I'll accumulate all comments and corrections and update the post in one hit. nerds-central.blogspot.com - welcomes all nerds
Sign In·View Thread·Permalink	2.00/5 (1 vote)