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]);
[download]

--
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]);
    }
[download]

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.

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]

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]

That looks good.

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

--
John.

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.

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

}
[download]