big number excel formatting

[PerlSufi]

Hello Monks,
I want to comma format some very large numbers(tens of millions) in an excel file which is made from a database query. For some reason, I can get all of the cells in my desired column (column B) to format except for the first cell. Here is my code that uses strict,warnings, DBI and Spreadsheet::WriteExcel.

# query prepared and executed here...

my $xWB = Spreadsheet::WriteExcel->new('spreadsheet.xls');

my $xWS2 =  $xWB->add_worksheet('Report');

my $big_num_format = $xWB->add_format();
$big_num_format->set_num_format('##,###,##0');

my $row = 0; my $col = 0;
$xWS2->write($row,$col++, $_) for @{$sth2->{NAME}};

while (my $array_ref = $sth2->fetchrow_arrayref) {
    ++$row; $col = 0;
         $xWS2->write($row, $col++, $_) for @$array_ref;
         if ($col == 1) { $xWS2->write_number( 0, 1, $_, $big_num_form
+at) for @$array_ref;
         $xWS2->write_number( 1, 1, $_, $big_num_format) for @$array_r
+ef;
         }
}

I also tried manually formatting that cell(1,1) in the if statement. Any suggestions as to how I can fix this would be greatly appreciated.
Thanks,
PerlSufi

[dorko]

I think you're incrementing $row too early.

I'd suggest try moving ++$row; to be the last statement in your while loop...

Cheers,

Brent

[PerlSufi]

Ah, good call dorko.. thanks. I'll that a try :)