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]

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.