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;
-
Are you posting in the right place? Check out Where do I post X? to know for sure.
-
Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
<code> <a> <b> <big>
<blockquote> <br /> <dd>
<dl> <dt> <em> <font>
<h1> <h2> <h3> <h4>
<h5> <h6> <hr /> <i>
<li> <nbsp> <ol> <p>
<small> <strike> <strong>
<sub> <sup> <table>
<td> <th> <tr> <tt>
<u> <ul>
-
Snippets of code should be wrapped in
<code> tags not
<pre> tags. In fact, <pre>
tags should generally be avoided. If they must
be used, extreme care should be
taken to ensure that their contents do not
have long lines (<70 chars), in order to prevent
horizontal scrolling (and possible janitor
intervention).
-
Want more info? How to link
or How to display code and escape characters
are good places to start.