Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister
 
PerlMonks  

Create an interactive Excel web page

by jmcnamara (Monsignor)
on Apr 02, 2005 at 01:16 UTC ( #444339=snippet: print w/ replies, xml ) Need Help??

Description: An example of how to create an interactive Excel web page that takes advantage of Microsoft's Office Web Components on the client side.

Here is an example of how to create an interactive Excel web page that takes advantage of Microsoft's Office Web Components on the client side.

MS Office Web Components are lightweight COM versions of parts of the MS office suite. The user doesn't require an MS Office licence to install them.

The spreadsheet web page is created by embedding an Excel XML file in a Html file as shown below.

If the user has Excel installed they can edit the spreadsheet and save it. If they don't have it installed they can still view it via a web component plugin.

Needless to say this is specific to Internet Explorer (and probably Windows). I'm not advocating this, I'm just showing how it can be done.

Here are examples of the page generated by the code below for Excel 2002 and Excel 2003. For interested parties without IE here is a screenshot of the resulting file (note cell B4 is being edited).

#!/usr/bin/perl -w

# Create a MS Spreadsheet Web Component based Spreadsheet.
# This is basically a ExcelXML document embedded in a html document.
# Only works with MSIE.
#
# reverse('©'), April 2005, John McNamara, jmcnamara@cpan.org


use strict;
use Spreadsheet::WriteExcelXML;


# Write the ExcelXML spreadsheet to a scalar.
open my $fh, '>', \my $xml_str or die "Failed to open filehandle: $!";


# Create a simple Spreadsheet::WriteExcelXML spreadsheet.
my $workbook   = Spreadsheet::WriteExcelXML->new($fh);

die "Couldn't create new Excel file: $!.\n" unless defined $workbook;

my $worksheet  = $workbook->add_worksheet();
my $bold       = $workbook->add_format(bold => 1);
my $currency   = $workbook->add_format(num_format => '$#,##0.00');
my $total1     = $workbook->add_format(bold       => 1, top => 6);
my $total2     = $workbook->add_format(bold       => 1,
                                       top        => 6,
                                       num_format => '$#,##0.00');


$worksheet->write('A1', 'Quarter',    $bold  );
$worksheet->write('A2',         1,    $bold  );
$worksheet->write('A3',         2,    $bold  );
$worksheet->write('A4',         3,    $bold  );
$worksheet->write('A5',         4,    $bold  );
$worksheet->write('A6', 'Total',      $total1);



$worksheet->write('B1', 'Sales',       $bold    );
$worksheet->write('B2',   10000,       $currency);
$worksheet->write('B3',   12000,       $currency);
$worksheet->write('B4',    9000,       $currency);
$worksheet->write('B5',   11000,       $currency);
$worksheet->write('B6', '=SUM(B2:B5)', $total2  );


$workbook->close();


# Escape the XML characters in the ExcelXML file.
for ($xml_str) {
        s/&/&/g;
        s/</&lt;/g;
        s/>/&gt;/g;
        s/"/&quot;/g; # "
        s/\r/&#13;/g;
        s/\n/&#10;/g;
}


# Insert the ExcelXML code into a Html doc using a simple template.
# Use HTML::Template or the Template::Toolkit for real applications.
#
my $excel_version = 2003;
my $clsid;

$clsid = "CLSID:0002E541-0000-0000-C000-000000000046" if $excel_versio
+n == 2002;
$clsid = "CLSID:0002E559-0000-0000-C000-000000000046" if $excel_versio
+n == 2003;

my $template =  do {local $/; <DATA>};
   $template =~ s/__EXCEL_XML_DATA__/$xml_str/;
   $template =~ s/__SPREADSHEET_CLSID__/$clsid/;
   $template =~ s/__EXCEL_VERSION__/$excel_version/;

print $template;



__END__
<html xmlns:o="urn:schemas-microsoft-com:office:office"
      xmlns:x="urn:schemas-microsoft-com:office:excel"
      xmlns="http://www.w3.org/TR/REC-html40">

    <body>
        <div id="Spreadsheet" align=center x:publishsource="Excel">
            <object id="Spreadsheet" classid="__SPREADSHEET_CLSID__">
                <param name=DisplayTitleBar value=false>
                <param name=Autofit  value=true>
                <param name=DataType value=XMLData>
                <param name=XMLData  value="__EXCEL_XML_DATA__">
                <p>
                    To use this Web page interactively, you must have 
+Microsoft®
                    Internet Explorer 5.01 Service Pack 2 (SP2) or lat
+er and
                    the Microsoft Office __EXCEL_VERSION__ Web Compone
+nts.
                </p>
                <p>
                    See the <a href="http://r.office.microsoft.com/r/r
+lidmsowcpub?clid=1033&amp;p1=Excel">
                    Microsoft Office Web site</a> for more information
+.
                </p>
            </object>
        </div>
    </body>
</html>

Comment on Create an interactive Excel web page
Download Code
Re: Create an interactive Excel web page
by Anonymous Monk on Apr 21, 2005 at 11:52 UTC
    Thanks John - tis is a really useful example - particularly as I can never find the right CLSID's when I need them.

    You can make this example a little more general by embedding a 2002 object inside a 2003 object - which means you can send it to a client browser without needing them to pick the version.

    The browser will then cascade to the one it can handle. (Although you have to send the data twice.)

    64 # Insert the ExcelXML code into a Html doc using a simple temp +late. 65 # Use HTML::Template or the Template::Toolkit for real applica +tions. 66 # 67 my $clsid2002 = "CLSID:0002E541-0000-0000-C000-000000000046" ; 68 my $clsid2003 = "CLSID:0002E559-0000-0000-C000-000000000046" ; 69 70 my $template = do {local $/; <DATA>}; 71 $template =~ s/__EXCEL_XML_DATA__/$xml_str/g; 72 $template =~ s/__SPREADSHEET_CLSID2002__/$clsid2002/; 73 $template =~ s/__SPREADSHEET_CLSID2003__/$clsid2003/; 74 75 print $template; 76 77 78 79 __END__ 80 Content-type: text/html 81 82 83 <html xmlns:o="urn:schemas-microsoft-com:office:office" 84 xmlns:x="urn:schemas-microsoft-com:office:excel" 85 xmlns="http://www.w3.org/TR/REC-html40"> 86 87 <body> 88 <div id="Spreadsheet" align=center x:publishsource="Ex +cel"> 89 <object id="Spreadsheet" classid="__SPREADSHEET_CL +SID2003__"> 90 <param name=DisplayTitleBar value=false> 91 <param name=Autofit value=true> 92 <param name=DataType value=XMLData> 93 <param name=XMLData value="__EXCEL_XML_DATA__ +"> 94 <object id="Spreadsheet" classid="__SPREADSHEET_CL +SID2002__"> 95 <param name=DisplayTitleBar value=false> 96 <param name=Autofit value=true> 97 <param name=DataType value=XMLData> 98 <param name=XMLData value="__EXCEL_XML_DATA__ +"> 99 <p> 100 To use this Web page interactively, you mu +st have Microsoft Internet Explorer 5.01 Service Pack 2 (SP2) or late +r and the Correct V ersion of Microsoft Office Web Components. 101 </p> 102 <p> 103 See the <a href="http://r.office.microsoft +.com/r/rlidmsowcpub?clid=1033&amp;p1=Excel"> 104 Microsoft Office Web site</a> for more inf +ormation. 105 </p> 106 </object> 107 </object> 108 </div> 109 </body> 110 </html>
      what if you dont want to enable editing in the file and also display a image by just passing the url from a cell.One image per one line or row. Is it possible?? Thanks for the above example.
Re: Create an interactive Excel web page
by Anonymous Monk on Apr 15, 2010 at 02:44 UTC
    would this work for folks who have the sun microsystems free spreadsheet open office??
Reaped: Re: Create an interactive Excel web page
by NodeReaper (Curate) on May 25, 2013 at 14:00 UTC

Back to Snippets Section

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: snippet [id://444339]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others romping around the Monastery: (5)
As of 2014-12-29 03:08 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (184 votes), past polls