Beefy Boxes and Bandwidth Generously Provided by pair Networks
Problems? Is your data what you think it is?
 
PerlMonks  

Handy wrapper for Spreadsheet::WriteExcel

by nop (Hermit)
on Dec 02, 2000 at 00:02 UTC ( #44448=sourcecode: print w/ replies, xml ) Need Help??

Category: Win32 Stuff
Author/Contact Info nop
Description: This is a small wrapper around Spreadsheet::WriteExcel. It lets you quickly open a workbook, and spit data into it by row by column (analogous to writing a tab delim text file, or using write and writeln in Pascal). Sometimes it is easier to think of generating a report by row by column, rather than popping all around the sheet.

The package also supports multiple tabs (sheets) nicely, setting them up automatically the first time a new tab is referenced. The package also supports common formats, and allows you to create "metaformats" (ala cascading style sheets).

Here's a example that shows the module in action:
use strict; use warnings; use diagnostics; use Excel; my $excel = Excel->new("test_excel1.xls"); $excel->writeln('tab1', "Header"); $excel->writeln('tab1'); $excel->writeln('tab1', qw(1 3 4 5 6)); $excel->writeln('tab1', qw(A B C D E F)); $excel->writeln('tab1'); $excel->writeln('tab1', qw(1.1 1000 101011.33 -14 -14.555 .000001)); $excel->writeln('tab1', map {[$_, 'money']} qw(1.1 1000 101011.33 -14 +-14.555 .000001)); $excel->writeln('tab1', map {[$_, 'bigmoney']} qw(1.1 1000 101011.33 - +14 -14.555 .000001)); $excel->writeln('tab1', map {[$_, 'pct']} qw(1.1 1000 101011.33 -14 -1 +4.555 .000001)); $excel->writeln('tab1', "VErrry long striiiiiiiiiiiiiiiiiiiiiiiiiiing" +); $excel->writeln('tab1', ['hello', 'bold'], [.003, 'pct','bold']); foreach (qw(1 2 3 4 5)) { $excel->write('tab2', [$_, 'money', 'bold']); } $excel->writeln('tab2', 'hello', [.003, 'pct','bold'], 'goodbye', 9999 +9999999999); foreach (qw(red blue yellow green orange)) { $excel->writeln('tab3', 'hello', [$_, $_, 'bold']); } $excel->writeln('tab4', ['i am a header', 'header']); $excel->writeln('tab4', ['i am a header', 'header', 'sumline']); $excel->writeln('tab4', ['i am a header', 'header', 'size20']);
package Excel;
use strict;
use warnings;
use diagnostics;
use Spreadsheet::WriteExcel;

sub new {
    my $proto = shift;
    my $class = ref($proto) || $proto;
    my $self  = {};
    bless ($self, $class);
    $self->init(@_);
    return $self;
}

sub init {
    my ($self, $fname) = @_;
    $self->{workbook} = Spreadsheet::WriteExcel->new($fname);
}

sub getformat {
    my ($self, @fmts) = @_;
    my $format = $self->{workbook}->addformat();
    # metafmts allows you define combinations of formats, like CSS
    # may be nested, may not be circular
    my %metafmts = (
            header => [qw(bold blue)]
            );
    while (@fmts) {
    $_ = shift @fmts;
    if ($metafmts{$_}) {push(@fmts, @{$metafmts{$_}}); delete $metafmt
+s{$_}; next;} 
    if (/default/) {
        $format->set_format(3); # commas, no decimals
    } elsif (/bigmoney/) {
        $format->set_format(5); # dollar sign, commas, no decimals
    } elsif (/money/) {
        $format->set_format(7); # dollar sign, commas, pennies
    } elsif (/pct/) {
        $format->set_format(10); # percent, two digit
    } elsif (/bold/) {
        $format->set_bold(1); # bold on
    } elsif (/sumline/) {
        $format->set_bottom(6); # double underline
    } elsif (/border/) {
        $format->set_border(1); # border all around
    } elsif (/size(\d+)/) {
        $format->set_size(0+$1); # size
    } elsif (/italic/) {
        $format->set_italic(1); # italic
    } elsif (/underline/) {
        $format->set_underline(1); # text underline
    } elsif (/left|right|center/) {
        $format->set_align($_); # alignment
    } elsif (/black|blue|red|green|purple|silver|yellow|gray|orange/) 
+{
        $format->set_color($_); # colors
    } else {
        die "unknown or circular format string: $_";
    }
    }
    return $format;
}

sub writecell {
    my ($self, $worksheet, $row, $col, $value) = @_;
    my ($val, @formats);
    if (!ref($value)) {
    $val = $value; @formats = qw(default);
    } elsif (ref($value) eq 'ARRAY') {
    $val = shift(@{$value});  @formats = @{$value};
    } else {die ref($value);}
    my $fmtobj = $self->getformat(@formats);
    $self->{worksheet}{$worksheet}{ptr}->write($row,$col, $val, $fmtob
+j);
    
}

sub write {
    my ($self, $worksheet, @vals) = @_;
    # if this is a new tab, create it
    if (!defined($self->{worksheet}{$worksheet}{ptr})) {
    $self->{worksheet}{$worksheet}{ptr} =
        $self->{workbook}->addworksheet($worksheet);
    $self->{worksheet}{$worksheet}{row} = 0;
    $self->{worksheet}{$worksheet}{col} = 0;
    }
    my $row = $self->{worksheet}{$worksheet}{row};
    my $col = $self->{worksheet}{$worksheet}{col};
    foreach (@vals) {
    $self->writecell($worksheet,$row,$col,$_);
    $col++;
    }
    $self->{worksheet}{$worksheet}{col} = $col;
}

sub writeln {
    my ($self, $worksheet, @vals) = @_;
    $self->write($worksheet, @vals);
    $self->{worksheet}{$worksheet}{row}++;
    $self->{worksheet}{$worksheet}{col} = 0;
}

1;
    

Comment on Handy wrapper for Spreadsheet::WriteExcel
Download Code
Re: Handy wrapper for Spreadsheet::WriteExcel
by growlf (Pilgrim) on Oct 24, 2001 at 13:17 UTC
    Awesome - and timely as well!.

    I truely needed this example of how to use the spreadsheet MOD to export data from a MySQL database to an MS EndUser who cannot fathom tools like Mascon or Cygwin for reports and etc.

    It is hapily chugging along spitting out tables for him now with one minor change - the newer version of Spreadsheet::WriteExcel has deprecated the use of set_format in favor of set_num_format. Other than that - thanks!
Re: Handy wrapper for Spreadsheet::WriteExcel
by Nomad (Pilgrim) on Feb 23, 2004 at 22:41 UTC

    Yes, I've just found this very useful as well! But apart from changing set_format to set_num_format. I was getting an error on close. I found that the workbook had to be explicitly closed. So in Excel.pm put:

    sub close { my ($self) = @_; $self->{workbook}->close; }

    And then your code should end:

    $excel->close;

    before exiting.

Re: Handy wrapper for Spreadsheet::WriteExcel
by Jonathan (Curate) on Dec 15, 2004 at 16:07 UTC
    Nice idea! It's saved me time and thinking.

    I didn't change much but I did add a method to change the column widths (using the 'A:A' version of the set_column method).

    sub setwidth { my ($self, $worksheet, $col, $width) = @_; if (defined($self->{worksheet}{$worksheet}{ptr})) { $self->{worksheet}{$worksheet}{ptr}->set_column($col,$width); } }

Back to Code Catacombs

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others drinking their drinks and smoking their pipes about the Monastery: (10)
As of 2014-08-21 19:15 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (143 votes), past polls