Re: Excel Formatting

For setting the column format, try this:

my $trans_format = $workbook->add_format();
$trans_format->set_num_format('$0.00');
#...
$worksheet->set_column('I:I', undef, $trans_format);
[download]

For column J, just don't write to the column:

my $row = 2;
while (<FH>) {
    chomp;
    @keepers = split /,/, $_, 10;
    pop @keepers; # only get first 9 columns
    $worksheet->write_row($row, 0, \@keepers, $cell_format);
    $row++;
}    
$worksheet->write_formula('I1', '=SUM (I3:I262)');
$worksheet->set_column('I:I', undef, $trans_format);
[download]

Update: replaced write() with write_row()

Comment on Re: Excel Formatting Select or Download Code

Replies are listed 'Best First'.
Re^2: Excel Formatting by PerlSufi (Friar) on Apr 22, 2013 at 19:42 UTC
Hi Count, thanks so much for the input! However, I am still getting the column J and not getting my currency format when I try that..?	[reply]
Re^3: Excel Formatting by CountOrlok (Friar) on Apr 22, 2013 at 20:46 UTC
You are applying the currency format at the end, after you have written all the data? (before closing the file, of course).	[reply]
Re^4: Excel Formatting by PerlSufi (Friar) on Apr 22, 2013 at 20:54 UTC
Yes. I have tried declaring `my $trans_format = $workbook->add_format(); $trans_format->set_num_format('$0.00');` [download] where I set my other formats and after the while loop. I have also done `$worksheet->set_column('I:I', undef, $trans_format);` after the while loop. I can't figure it out..	[reply] [d/l] [select]
Re^3: Excel Formatting by PerlSufi (Friar) on Apr 22, 2013 at 19:46 UTC
UPDATE:Actually replacing that while statement with my previous one worked to remove J column, but I still don't have the currency format.	[reply]