Beefy Boxes and Bandwidth Generously Provided by pair Networks
Come for the quick hacks, stay for the epiphanies.
 
PerlMonks  

comment on

( [id://3333]=superdoc: print w/replies, xml ) Need Help??
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 ); } }

In reply to Re: Re: writing with WriteExcel in OO style by axelrose
in thread writing with WriteExcel in OO style by axelrose

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • 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.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others goofing around in the Monastery: (4)
As of 2024-04-13 09:02 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found