sourcecode
nop
<code>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 $metafmts{$_}; 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, $fmtobj);
}
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;
</code>
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 <code>write</code> and <code>writeln</code> in Pascal). Sometimes it is easier to think of generating a report by row by column, rather than popping all around the sheet.<br><br>
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). <br><br>
Here's a example that shows the module in action:
<code>
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 -14.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', 99999999999999);
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']);
</code>
Win32 Stuff
[nop]