Click here to Skip to main content
15,888,610 members
Search within:


Articles / Programming Languages / Javascript
 
Article

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

Rate me:
Please Sign up or sign in to vote.
3.00/5 (8 votes)
1 Mar 2007CPOL1 min read 50.2K   273   17   12
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
<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 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

Comments and Discussions

 
First Prev Next
QuestionHow to creat two sheet?? Pin
amber.chang15-Aug-07 20:10
amber.chang15-Aug-07 20:10 
Questionwhere is the grid? Pin
hen10029-Apr-07 8:34
hen10029-Apr-07 8:34 
I tried that, but where is the grid of the sheet gone?
Is there a way to add it as a new sheet in an existing worksheet?
Also working on a little project and that gave me an idea. Be very thankful if you can help me, I'll just write it down:
Lets say this web site I need to download tables from is www.cnn.com
Inside this site lets say there are 10 links to internal pages, and in those pages there are the tables I need (besides many text that I don't).
I need to download all the tables from those internal pages to excel. I would like to have a macro button in excel that will create a new sheet with all that data.
I saw that if I just copy the html code between .....
and just rename it in a new word pad file X.xls, and open it, I get almost a nice table in excel (almost - somehow with no grids between the cells of the entire sheet).
Knowing that I can run a script (vbscript) to look for the string "table" and than copy all the data until the string . My problems:
1) I write a script, but how do I call to a different web site, so my script will run on i?
2) Lets complicate: I know only the link to the index page (www.cnn.com), from there I need my script to look for all the tables inside the internal pages. (or all the tables, starting with a very specific word), and each one is on a different web page (different link). (do I write a loop which look for "href .... "? but how do I run over an entire html file? I know there is " InStr" , but I guess it won't work in this case.
3) I want to copy between until
. But there is a lot of data between and string is a limited variable. How do I do that.
4) After fixing those two, how will I run my script from the excel (but this, I guess will be much easier).
Thanks!
Hanan.
Questionhow to set page margin of excel and print style [modified] Pin
dddd21824-Feb-07 0:25
dddd21824-Feb-07 0:25 
AnswerRe: how to set page margin of excel and print style Pin
alex turner25-Feb-07 2:48
alex turner25-Feb-07 2:48 
Generalalso to mention Pin
dvhh20-Feb-07 2:10
dvhh20-Feb-07 2:10 
GeneralRe: also to mention Pin
alex turner20-Feb-07 2:20
alex turner20-Feb-07 2:20 
GeneralFormulae And Further Layout Techniques Pin
alex turner20-Feb-07 0:50
alex turner20-Feb-07 0:50 
GeneralFormulas & Functions Pin
bdlogic19-Feb-07 8:06
bdlogic19-Feb-07 8:06 
GeneralRe: Formulas & Functions Pin
n'Anders19-Feb-07 21:00
n'Anders19-Feb-07 21:00 
GeneralRe: Formulas & Functions Pin
alex turner20-Feb-07 0:54
alex turner20-Feb-07 0:54 
GeneralForgot something... Pin
Leftend19-Feb-07 6:08
Leftend19-Feb-07 6:08 
GeneralRe: Forgot something... Pin
alex turner19-Feb-07 23:08
alex turner19-Feb-07 23:08 
Last Visit: 31-Dec-99 18:00     Last Update: 29-Apr-24 20:28Refresh1

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
Cookies
Terms of Use
Layout: fixed | fluid

Posted 19 Feb 2007

Article Copyright 2007 by alex turner
Everything else Copyright © CodeProject, 1999-2024

Web02 2.8:2024-04-02:1