Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl-Sensitive Sunglasses
 
PerlMonks  

Spreadsheet::WriteExcel

by abaxaba (Hermit)
on Jan 22, 2002 at 03:06 UTC ( [id://140516]=modulereview: print w/replies, xml ) Need Help??

Item Description: Create MS_Excel binaries

Review Synopsis:

This module allows for the creation of Excel binary files. Robust cell formatting, cell merges, multiple worksheets, formulae, printer specifications, The author has well documented this work, providing working examples that illustrate many of its features.

Why should you?
If you display a lot of data, and wish to allow users to export it in a readily usable format, this is the way to go.

Why Not?
Have never benchmarked it, but it does a lot of work, which takes a bit of time -- About 5 secs or so to create a a 2-worksheet spreadsheet about 15K. This is still under development, and does not currently support Macros. Requires 5.6.0, support for IEEE 64 bit float, Text:: and Parse:: packages.

How

This is from the examples that come with the distro:

#!/usr/bin/perl -w use strict; use Spreadsheet::WriteExcel; # Create a new workbook called simple.xls and add # a worksheet my $workbook = Spreadsheet::WriteExcel->new("simple.xls"); my $worksheet = $workbook->addworksheet(); # The general syntax is write($row, $column, $token). Note that row an +d # column are zero indexed # Write some text $worksheet->write(0, 0, "Hi Excel!"); # Write some numbers $worksheet->write(2, 0, 3); # Writes 3 $worksheet->write(3, 0, 3.00000); # Writes 3 $worksheet->write(4, 0, 3.00001); # Writes 3.00001 $worksheet->write(5, 0, 3.14159); # TeX revision no.? # Write some formulas $worksheet->write(7, 0, '=A3 + A6'); $worksheet->write(8, 0, '=IF(A5>3,"Yes", "No")'); # Wrte a hyperlink $worksheet->write(10, 0, 'http://www.perl.com/');

Edit Masem 2002-01-23 - Added CODE tags

Edit abaxaba 2003-12-23 - Corrected Type - thanks ff

Replies are listed 'Best First'.
Re: Spreadsheet::WriteExcel
by jmcnamara (Monsignor) on Jan 22, 2002 at 04:23 UTC

    Have never benchmarked it, but it does a lot of work, which takes a bit of time -- About 5 secs or so to create a a 2-worksheet spreadsheet about 15K.

    It should be faster than that, at least for text and numbers. Here is a benchmark for a 1MB file on a PIII 800. The average time is less than 5 seconds:

    WriteExcel: 49 wallclock secs (41.19 usr + 1.88 sys = 43.07 CPU) @ 0 +.23/s (n=10) 4.31 s/iter

    Formulas do slow it down however since they are parsed via Parse::RecDescent.

    This is still under development, and does not currently support Macros. Requires 5.6.0, support for IEEE 64 bit float, Text:: and Parse:: packages.

    Spreadsheet::WriteExcel requires Perl 5.005 and Parse::RecDescent. This in turn requires Text::Balanced but that is bundled with it. IEEE 64 bit floats are almost a given.

    Macros will probably never be supported. The format of VBA macros is even more undocumented than usual. :-)

    Here is a screenshot from the Freshmeat page

    Update: Added benchmark data.

    Note, there is also an older review of this module.

    --
    John.

Poor Man's Spreadsheet (was: Re: Spreadsheet::WriteExcel)
by Corion (Patriarch) on Jan 23, 2002 at 18:35 UTC

    Depending on your requirements, simple .csv files can also do parts of the job, as .csv files can also contain formulas. But there are certain pitfalls that have to be considered :

    • No Graphics, Links, fancy stuff
    • Different language/country settings make Excel behave differently in opening a file via the menu vs. opening a file via a double-click.
    • Embedded list-separators within cells will give you fun encoding.

    Here is a small sample .csv file for an Excel setup that matches mine, that is, ; is the list separator, , is the number group separator and . is the decimal separator. This setup is nonstandard for a german setup, as the german list separator is ;, but the german decimal separator is , and the german number group separator is .. You might have to munge the example to suit your needs, and you might also have to open this file in Excel via the File->Open method instead of just double-clicking on the file.

    "Cell A1";"Cell B1";"Cell C1" "Cell A2";"Cell B2";"Cell C2" 6;7;666 "Here comes a formula";=A3*B3;"This should be 42"
    perl -MHTTP::Daemon -MHTTP::Response -MLWP::Simple -e ' ; # The $d = new HTTP::Daemon and fork and getprint $d->url and exit;#spider ($c = $d->accept())->get_request(); $c->send_response( new #in the HTTP::Response(200,$_,$_,qq(Just another Perl hacker\n))); ' # web
Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others musing on the Monastery: (3)
As of 2024-03-29 06:27 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found