Dealing intelligently with Excel files through HTML tables.

This came about from the chatterbox.

Thanks go to one4k4 for the original question and tye, and ar0n for their input
This snippet is a proof that XL95 or higher will parse an HTML fragment and attempt to push it into a spreadsheet. When excel encounters a table, it'll deal with it (surprisingly) intelligently.
This trick relies on (I believe) Office 95 or higher, and a browser that understands that "Content-disposition" is important...
Please post what versions of excel you test on, if you do.
Obviously, there's a lot more you can do with this type of thing; this is merely to show that it works -- this should reduce the amount of time people spend dickering with excel::spreadsheet. Note that because of the way excel translates the HTML, you'll only get one spreadsheet per workbook, so if that's a limitation, you're back to excel::spreadsheet...
Also interesting is the formula in the third row; excel Does The Right Thing with it; I imagine this could be made to be as complex as excel allows, up to and including things like :
=SUM(foo!$AO$4,foo!$A$177:$A$219,foo!$AO$177:$AO$219,4).

Again, please comment with experiences.


use strict;
use CGI qw(:all);
print header(-type=>"application/excel\nContent-disposition: attachmen
+t; filename=myfile.xls") ;
print "<TABLE border=1>
  <TBODY>
  <TR>
    <TD>1
    <TD>2
    <TD>3
  <TR>
    <TD>1
    <TD>2
    <TD>3
  <TR>
    <TD>1
    <TD>2
    <TD>3
  <TR>
    <TD>1
    <TD>2
    <TD>=a1*b1*c1
  <TR>
    <TD>1
    <TD>2
    <TD>3 </TR></TBODY></TABLE>";
print end_html;
[download]

update

It appears that Excel will generate (I think) it's own style sheet when exporting to html. This (among other things) determines how the spreadsheet will look to Excel, but leaves it pretty plain looking in the browser.

<head>
<meta http-equiv=Content-Type content="text/html; charset=windows-1252
+">
<meta name=ProgId content=Excel.Sheet>
<meta name=Generator content="Microsoft Excel 9">
<link rel=File-List href="./trash_files/filelist.xml">
<style id="trash.html_16110_Styles">
<!--table
    {mso-displayed-decimal-separator:"\.";
    mso-displayed-thousand-separator:"\,";}
.xl1516110
    {padding-top:1px;
    padding-right:1px;
    padding-left:1px;
    mso-ignore:padding;
    color:windowtext;
    font-size:10.0pt;
    font-weight:400;
    font-style:normal;
    text-decoration:none;
    font-family:Arial;
    mso-generic-font-family:auto;
    mso-font-charset:0;
    mso-number-format:General;
    text-align:general;
    vertical-align:bottom;
    mso-background-source:auto;
    mso-pattern:auto;
    white-space:nowrap;}
-->
</style>
</head>
[download]

Comment on Dealing intelligently with Excel files through HTML tables. Select or Download Code

Replies are listed 'Best First'.
Re: Dealing intelligently with Excel files through HTML tables. by jmcnamara (Monsignor) on Mar 14, 2001 at 02:34 UTC
Excel 2000 also accepts multiple worksheets as HTML. The Excel HTML specification is here, but it is easier to just save a workbook as HTML and duplicate the files (a HTML doc for the workbook, one for each worksheet, a style sheet and an XML file). The HTML is a bit hairy but it would be an ideal candidate for HTML::Template or the Template Toolkit. The above specification also details the Excel XML format. John. -- Never trust a man in a blue trenchcoat, never drive a car when you're dead.	[reply]

Replies are listed 'Best First'.

Re: Dealing intelligently with Excel files through HTML tables.
by jmcnamara (Monsignor) on Mar 14, 2001 at 02:34 UTC

here

Never trust a man in a blue trenchcoat, never drive a car when you're dead.

[reply]