Click here to Skip to main content
13,057,089 members (43,554 online)
Click here to Skip to main content
Add your own
alternative version

Stats

36.5K views
17 bookmarked
Posted 19 Feb 2007

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

, 1 Mar 2007
Rate this:
Please Sign up or sign in to vote.
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).

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)

Share

About the Author

alex turner
Web Developer
United Kingdom 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

You may also be interested in...

Pro

Comments and Discussions

 
QuestionHow to creat two sheet?? Pin
amber.chang15-Aug-07 20:10
memberamber.chang15-Aug-07 20:10 
Questionwhere is the grid? Pin
hen10029-Apr-07 8:34
memberhen10029-Apr-07 8:34 
Questionhow to set page margin of excel and print style [modified] Pin
dddd21824-Feb-07 0:25
memberdddd21824-Feb-07 0:25 
AnswerRe: how to set page margin of excel and print style Pin
alex turner25-Feb-07 2:48
memberalex turner25-Feb-07 2:48 
Generalalso to mention Pin
dvhh20-Feb-07 2:10
memberdvhh20-Feb-07 2:10 
GeneralRe: also to mention Pin
alex turner20-Feb-07 2:20
memberalex turner20-Feb-07 2:20 
GeneralFormulae And Further Layout Techniques Pin
alex turner20-Feb-07 0:50
memberalex turner20-Feb-07 0:50 
GeneralFormulas & Functions Pin
bdlogic19-Feb-07 8:06
memberbdlogic19-Feb-07 8:06 
GeneralRe: Formulas & Functions Pin
n'Anders19-Feb-07 21:00
membern'Anders19-Feb-07 21:00 
GeneralRe: Formulas & Functions Pin
alex turner20-Feb-07 0:54
memberalex turner20-Feb-07 0:54 
GeneralForgot something... Pin
Leftend19-Feb-07 6:08
memberLeftend19-Feb-07 6:08 
GeneralRe: Forgot something... Pin
alex turner19-Feb-07 23:08
memberalex turner19-Feb-07 23:08 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

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

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.170728.6 | Last Updated 1 Mar 2007
Article Copyright 2007 by alex turner
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid