Re: Re: writing with WriteExcel in OO style

[axelrose]

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