Beefy Boxes and Bandwidth Generously Provided by pair Networks
Don't ask to ask, just ask
 
PerlMonks  

writing with WriteExcel in OO style

by axelrose (Scribe)
on Mar 25, 2002 at 18:53 UTC ( #154176=perlquestion: print w/replies, xml ) Need Help??

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.

Replies are listed 'Best First'.
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.

      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?

        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.

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)

      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.

      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 ); } }

        That looks good.

        The next release of Spreadsheet::WriteExcel will correct the deficiency in set_column() that you have to work around here.

        --
        John.

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 ); } }

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others rifling through the Monastery: (4)
As of 2020-04-10 07:15 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    The most amusing oxymoron is:
















    Results (49 votes). Check out past polls.

    Notices?