|
axelrose has asked for the wisdom of the Perl Monks concerning the following question:
Dear Monks,
I'm trying to encapsulate Spreadsheet::WiteExcel functionality
in a larger script. It may be a silly mistake but in the moment
I cannot succeed writing some cells...
I get an empty Excel worksheet. The module itself works.
Writing in the new() constructor $worksheet->write( 1, 0, 42 )
yields the expected result. Using my writerow() method though
seems useless.
Can you please point me to my misunderstanding?
Many thanks, Axel.
#!perl -w
use strict;
my $tmpl = ExcelWriter->new();
my @row = ( 1, 2, 3 );
$tmpl->writerow( \@row );
BEGIN {
package ExcelWriter;
use Spreadsheet::WriteExcel;
my $tmpfile = sprintf( "/tmp/%d.%d.xls",
time % 1000, int rand(1000) );
sub new {
my $class = shift;
my $workbook =
Spreadsheet::WriteExcel->new($tmpfile)
or die $!;
my $worksheet = $workbook->addworksheet('Data');
bless \$worksheet, $class;
}
sub writerow {
my ( $self, $row ) = @_;
my $worksheet = $$self;
for ( 0 .. @$row - 1 ) {
$worksheet->write( 0, $_, $row->[$_] );
}
}
}
To check you need to open the resulting tmpfile. I'm testing with MacPerl5.6.1 btw. |
Re: writing with WriteExcel in OO style
by jmcnamara (Monsignor) on Mar 25, 2002 at 23:46 UTC
|
When you encapsulate Spreadsheet::WriteExcel objects or sub-objects like this you have to explicitly call the workbook close() method to ensure that the sub-objects are destroyed in the correct order. This is due to what I believe is a bug in DESTROY.
Also, the functionality that you are implementing via your writerow() method is already available through the Spreadsheet::WriteExcel write_row() or write() methods if you have version 0.34 or later. ;-)
@array = ('awk', 'gawk', 'mawk');
$array_ref = \@array;
$worksheet->write_row(0, 0, $array_ref);
# The above example is equivalent to:
$worksheet->write(0, 0, $array[0]);
$worksheet->write(0, 1, $array[1]);
$worksheet->write(0, 2, $array[2]);
--
John.
| [reply]
[d/l] |
|
|
Thanks for your help, John, and of course for writing the module!!
I know about write_row() and write_col() but they don't fit my need.
The data from a database comes in rows. I want to write them immediately
and need a different format for each column. So I planned to extend my writerow()
method to accept not only an arrayref to the data but also a second arrayref
for the format of ach column.
Do I reinvent the wheel?
| [reply] |
|
|
Do I reinvent the wheel?
No. :-)
The set_column() method should allow you to set the format for a column and then have that applied to all data written to the column. However, it doesn't currently work like that and it should really be fixed.
An easy way of adding new methods to the Worksheet class, or to any of the other classes, is to do something like this:
#!/usr/bin/perl -w
use strict;
use Spreadsheet::WriteExcel;
my $workbook = Spreadsheet::WriteExcel->new("test.xls");
my $worksheet = $workbook->addworksheet();
$worksheet->write ('A1', "Hello");
$worksheet->uc_write('A2', "Hello"); # New method
#
# The following will be appended to
# Spreadsheet::WriteExcel::Worksheet
#
package Spreadsheet::WriteExcel::Worksheet;
#
# uc_write: A worksheet method that does ...
#
sub uc_write {
my $self = shift;
$self->write($_[0], uc $_[1]);
}
In the example directory of the Spreadsheet::WriteExcel distro the write_many.pl and comments.pl programs use this methodology.
However, this is only suitable for simple or one-off solutions. If you need to do anything more complicated or maintainable it would probably be better to subclass the Worksheet class.
--
John.
| [reply]
[d/l] |
Re: writing with WriteExcel in OO style
by axelrose (Scribe) on Apr 08, 2002 at 22:15 UTC
|
I checked the "writemany.pl" example and found it easier to achieve my goal by
writing just a "formatmany" method. Eventually what I really need is formatting
in one go with predefined formats.
Below is the code I have so far (work in progress!)
The problem I have with this - the format doesn't seem to have any effect
in the resulting file no matter what style settings I select.
Perhaps some monk sees the problem I can't find ...
Thanks for your time, Axel.
#!perl -l015
use strict;
use Spreadsheet::WriteExcel;
my ( @arr, @format, $rows, $cols );
@arr = (
[ "a11", "12", "a13" ],
[ "a21", "22", "a23" ],
[ "a31", "32", "a33" ],
);
my $outputfile = "output.xls";
my $workbook = Spreadsheet::WriteExcel->new($outputfile);
my $worksheet = $workbook->addworksheet("a test");
my $number = $workbook->addformat(
font => "Helvetica",
size => 18,
num_format => "000000.00",
align => "right",
);
my $string = $workbook->addformat(
font => "Helvetica",
size => 18,
align => "left",
bold => 1,
);
@format = ( [ $string, $number, $string ] );
$worksheet->writemany( 0, 0, \@arr );
$worksheet->formatmany(@format);
$workbook->close();
chomp( my $pwd = `pwd` );
my $file = $pwd . $outputfile;
$^O =~ /Mac/ and MacPerl::DoAppleScript( <<eos );
tell application "Microsoft Excel"
open "$file"
activate
end tell
eos
package Spreadsheet::WriteExcel::Worksheet;
sub formatmany {
my ( $self, @format ) = @_;
my ( $rows, $cols ) = $self->checkdim(@format);
print "DEBUG: got ( $rows, $cols ) format elements";
return if $cols == 0 or $rows == 0;
# colums only
if ( $rows == 1 ) {
print "DEBUG: setting columns only";
for ( 0 .. $cols - 1 ) {
my $colformat = $format[0]->[$_];
die "no format object found"
unless ref $colformat eq
"Spreadsheet::WriteExcel::Format";
$self->set_column( $_, $_, 20, $colformat );
}
}
elsif ( $cols == 0 ) {
# yadda-yadda ... $self->set_row();
}
else {
# yadda-yadda ...
}
}
sub writemany {
my ( $self, $row, $col, $ref, $options ) = @_;
# If this is an arrayref, go through it
if ( ref($ref) eq "ARRAY" ) {
# Work out the direction we're going
my $direction = $options->{direction} || "row";
# Work out the converse direction
my $otherdirection = {
row => "col",
col => "row"
}->{$direction};
# Cycle through
for (@$ref) {
$self->writemany(
$row,
$col,
$_,
{
direction => $otherdirection,
format => $options->{format} || undef
}
);
$direction eq "row" ? $row++ : $col++;
}
}
else {
# It's a simple scalar value (or something that we don't
# handle), so pass it through to write
$self->write( $row, $col, $ref,
$options->{format} );
}
}
sub checkdim {
my ( $self, @arr ) = @_;
my $rows = 0;
for (@arr) { $rows++ }
my $maxcols = 0;
for ( 0 .. $rows - 1 ) {
my $row = $_;
my $cols = 0;
for ( 0 .. $#{ $arr[$row] } ) { $cols++ }
$maxcols = $cols > $maxcols ? $cols : $maxcols;
}
return ( $rows, $maxcols );
}
(please change #!perl -l015 for your needs, I'm on MacPerl in the moment,
writemany() taken from John's example) | [reply]
[d/l] |
|
|
The problem seems to be that you are using set_col() to set the format of cells that you have writing seperately.
Unfortunately, in the current implementation of the set_col() method doesn't work like this.
This is documented in the set_col() section of the documentation with the promise that it "will be fixed in a future release".
It will be fixed but in the meantime the best way to work around this is to store the column formats in a hash where the key is the column number. Then in your write_many() method use the appropriate column format if an explicit cell format isn't specified.
--
John.
Update: This behaviour was fixed in version 0.37 of Spreadsheet::WriteExcel.
| [reply] |
|
|
next iteration...
I call the set_column() method now immediately after the write() which
sets the format as received in the parameter list.
The interface is not very robust yet but the usage seems easy. What do you think?
#!perl -l015
use strict;
use Spreadsheet::WriteExcel;
my ( $x, $y ) = ( 0, 0 );
my $outputfile = "output.xls";
my $workbook = Spreadsheet::WriteExcel->new($outputfile);
my $worksheet = $workbook->addworksheet("a test");
my @arr = (
[ "a11", "12", "a13" ],
[ "a21", "22", "a23" ],
[ "a31", "32", "a33" ],
);
my $number = $workbook->addformat(
font => "Helvetica",
size => 18,
num_format => "000000.00",
align => "right",
);
my $string = $workbook->addformat(
font => "Helvetica",
size => 18,
align => "left",
bold => 1,
);
# reference to an array of array references containing
# array references with pairs of format objects and column widths
my $format = [
[ [ $string, 20 ], [ $number, 30 ], [ $string, 20 ] ],
# [ [ $number, 20 ], [ $string, 30 ], [ $number, 20 ] ],
# [ [ $string, 20 ], [ $number, 30 ], [ $string, 20 ] ],
];
$worksheet->writemany( $x, $y, \@arr,
{ formatarr => $format } );
$workbook->close();
chomp( my $pwd = `pwd` );
my $file = $pwd . $outputfile;
$^O =~ /Mac/ and MacPerl::DoAppleScript( <<eos );
tell application "Microsoft Excel"
open "$file"
activate
end tell
eos
package Spreadsheet::WriteExcel::Worksheet;
sub formatmany {
my ( $self, @format ) = @_;
my ( $rows, $cols ) = $self->checkdim(@format);
print "DEBUG: got ( $rows, $cols ) format elements";
return if $cols == 0 or $rows == 0;
# colums only
if ( $rows == 1 ) {
print "DEBUG: setting columns only";
for ( 0 .. $cols - 1 ) {
my $colformat = $format[0]->[$_];
die "no format object found"
unless ref $colformat eq
"Spreadsheet::WriteExcel::Format";
$self->set_column( $_, $_, 20, $colformat );
}
}
elsif ( $cols == 0 ) {
$self->set_row();
}
else {
}
}
sub writemany {
my ( $self, $row, $col, $ref, $options ) = @_;
# options understood:
# direction => "row"|"col"
# formatall => a format object, applied to all cells
# formatarr => reference to a multidim. array of format objects
# takes precedence over formatall parameter
# If this is an arrayref, go through it
if ( ref($ref) eq "ARRAY" ) {
# Work out the direction we're going
my $direction = $options->{direction} || "row";
$direction =~ /^(row)|(col)$/ or $direction = "row";
# Work out the converse direction
my $otherdirection =
{ row => "col", col => "row" }->{$direction};
# Cycle through
for (@$ref) {
$self->writemany(
$row,
$col,
$_,
{
direction => $otherdirection,
formatall => $options->{formatall}
|| undef,
formatarr => $options->{formatarr}
|| undef
}
);
$direction eq "row" ? $row++ : $col++;
}
}
else {
if ( ref $options->{formatarr} eq "ARRAY" ) {
# print "DEBUG: using formatarr\n";
# get the format
my ( $format, $width ) =
$self->getformat( $options->{formatarr}, $row,
$col );
# check the return value
die "no format object found"
unless ref $format eq
"Spreadsheet::WriteExcel::Format";
# write with it
$self->write( $row, $col, $ref, $format );
$self->set_column( $col, $col, $width );
}
else {
$self->write( $row, $col, $ref,
$options->{formatall} );
}
}
}
sub getformat {
my ( $self, $ref_formatarr, $row, $col ) = @_;
my ( $rows, $cols ) = $self->checkdim($ref_formatarr);
print "DEBUG: got a ($rows, $cols) matrix";
if ( $rows == 1 ) {
return (
$ref_formatarr->[0][$col][0],
$ref_formatarr->[0][$col][1]
);
}
elsif ( $cols == 1 ) {
return (
$ref_formatarr->[$row][0][0],
$ref_formatarr->[$row][0][1]
);
}
else {
return (
$ref_formatarr->[$row][$col][0],
$ref_formatarr->[$row][$col][1]
);
}
print "DEBUG: internal error - no format determined";
return (
$ref_formatarr->[0][0][0],
$ref_formatarr->[0][0][1]
);
}
# a closure (?) to avoid recomputation of the fixed dimension
my ( $checkedrows, $checkedcols ) = ( 0, 0 );
sub checkdim {
if ( $checkedrows > 0 and $checkedcols > 0 ) {
return ( $checkedrows, $checkedcols );
}
else {
print "DEBUG: computing dimension...";
my ( $self, $refarr ) = @_;
my @arr = @$refarr;
my $rows = 0;
for (@arr) { $rows++ }
my $maxcols = 0;
for ( 0 .. $rows - 1 ) {
my $row = $_;
my $cols = 0;
for ( 0 .. $#{ $arr[$row] } ) { $cols++ }
$maxcols = $cols > $maxcols ? $cols : $maxcols;
}
( $checkedrows, $checkedcols ) =
( $rows, $maxcols );
return ( $rows, $maxcols );
}
}
| [reply]
[d/l] |
|
|
| [reply] |
Re: writing with WriteExcel in OO style
by axelrose (Scribe) on May 13, 2002 at 21:17 UTC
|
Thanks to John the set_column() method is now much easier to use. Nonetheless
I discarded all approaches I developed in this thread and found the solution below which is much easier to comprehend, I believe.
#!perl -l015
use strict;
use Spreadsheet::WriteExcel;
my $arr = [
[ "a11", "12", "a13" ],
[ "a21", "22", "a23" ],
[ "a31", "32", "a33" ],
];
my $outputfile = "output.xls";
my $workbook = Spreadsheet::WriteExcel->new($outputfile);
my $worksheet = $workbook->addworksheet("a test");
my $number = $workbook->addformat(
font => "Helvetica",
size => 18,
num_format => "0.00",
align => "right",
);
my $string = $workbook->addformat(
font => "Helvetica",
size => 18,
align => "left",
bold => 1,
);
my $format =
[ [ $string, $number, $string ], [ 16, 12, 16 ] ];
my $line;
for my $row (@$arr) {
$worksheet->write_formatted_row( 3 + $line++,
0, $row, $format );
}
$workbook->close();
chomp( my $pwd = `pwd` );
my $file = $pwd . $outputfile;
$^O =~ /Mac/ and MacPerl::DoAppleScript( <<eos );
tell application "Microsoft Excel"
open "$file"
activate
end tell
eos
package Spreadsheet::WriteExcel::Worksheet;
my $width_is_set = 0;
sub write_formatted_row {
my ( $self, $rowstart, $colstart, $ref, $format ) = @_;
my @arr = @$ref;
unless ($width_is_set) {
for my $col ( 0 .. $#{ @$format->[1] } ) {
my $colwidth = $format->[1][$col];
$self->set_column(
$colstart + $col,
$colstart + $col,
$colwidth, undef
);
}
$width_is_set = 1;
}
for my $col ( 0 .. $#arr ) {
my $colformat = $format->[0][$col];
die "parameter error: no format objects specified\n"
unless ref $colformat eq
'Spreadsheet::WriteExcel::Format';
$self->write(
$rowstart, $colstart + $col,
$arr[$col], $colformat
);
}
}
| [reply]
[d/l] |
|
|
