#!/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//>/g; s/"/"/g; # " s/\r/ /g; s/\n/ /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_version == 2002; $clsid = "CLSID:0002E559-0000-0000-C000-000000000046" if $excel_version == 2003; my $template = do {local $/; }; $template =~ s/__EXCEL_XML_DATA__/$xml_str/; $template =~ s/__SPREADSHEET_CLSID__/$clsid/; $template =~ s/__EXCEL_VERSION__/$excel_version/; print $template; __END__