Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Re: Re: writing with WriteExcel in OO style

by axelrose (Scribe)
on Apr 11, 2002 at 21:47 UTC ( #158432=note: print w/replies, xml ) Need Help??


in reply to Re: writing with WriteExcel in OO style
in thread writing with WriteExcel in OO style

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

Replies are listed 'Best First'.
Re: Re: Re: writing with WriteExcel in OO style
by jmcnamara (Monsignor) on Apr 12, 2002 at 08:13 UTC

    That looks good.

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

    --
    John.

Log In?
Username:
Password:

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

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (4)
As of 2020-04-03 21:00 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    The most amusing oxymoron is:
















    Results (32 votes). Check out past polls.

    Notices?