Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

Re: writing with WriteExcel in OO style

by axelrose (Scribe)
on Apr 08, 2002 at 22:15 UTC ( [id://157585]=note: print w/replies, xml ) Need Help??


in reply to writing with WriteExcel in OO style

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

[download]
(please change #!perl -l015 for your needs, I'm on MacPerl in the moment, writemany() taken from John's example)

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

    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]
Re: Re: writing with WriteExcel in OO style
by axelrose (Scribe) on Apr 11, 2002 at 21:47 UTC
    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 );
    }
}

    [download]
[reply]
[d/l]

      That looks good.

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

      --
      John.

[reply]

In Section Seekers of Perl Wisdom

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?

www.com | www.net | www.org
Node Status?
node history
Node Type: note [id://157585]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? • Last hour • Other CB clients
Other Users?
Others exploiting the Monastery: (8)
As of 2024-04-16 10:14 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found