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 ( $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.