Excel Formatting

[PerlSufi]

Hi Monks, I need help reformatting an excel document in a couple ways. I need the 8th column(I) to be two decimal places if there is only one, and I need the 9th column to be removed. For reformatting the 8th column, my method below has not worked and for removing the J column my method has also not worked.

open (FH, "<file.csv") or die "Cannot open file: $!\n";
#my $parser = Parse::CSV->new( file=> 'file.csv');



#create excel file
my $workbook = Spreadsheet::WriteExcel->new('report.xls');
my $worksheet = $workbook->add_worksheet();

#set format for column headers
my $format = $workbook->add_format();
$format->set_bold();
my $date_format = $workbook->add_format();
$date_format->set_num_format('mm/dd/yy');
my $trans_format = $workbook->add_format();
$trans_format->set_num_format('I:I','$0.00');
my $cell_format = $workbook->add_format();
$cell_format->set_text_wrap();


#write the column headers
$worksheet->set_column(0,0,20);
$worksheet->set_column(0,1,10);
$worksheet->set_column(0,2,20);
$worksheet->set_column(0,5,10);


$worksheet->write(0,0, 'total',$format);
$worksheet->write(1,0, 'transaction_no', $format);
$worksheet->write(1,1, 'eff_date',$format);
$worksheet->write(1,2, 'city_name',$format);
$worksheet->write(1,3, 'state',$format);
$worksheet->write(1,4, 'permit_no',$format);
$worksheet->write(1,5, 'permit_type',$format);
$worksheet->write(1,6, 'class',$format);
$worksheet->write(1,7, 'pieces',$format);
$worksheet->write(1,8, 'trans_amt',$format, $trans_format);





my ($x, $y) = (2,0);
while (<FH>) {
    chomp;
    @keepers = split /,/, $_;
    foreach my $line  (@keepers) {
    
        $worksheet->write($x, $y++,$line,$cell_format);
        
    
        
    }
    $x++;$y=0;
    }    
$worksheet->write_formula('I1', '=SUM (I3:I262)');
$worksheet->write_blank('J:J');






    close(FH);
    $workbook->close();



print "Data written to an excel file named REPORT in current working d
+irectory\n";




#print the content (for debugging)
#print $mech->content;

Thanks in advance for your efforts.. PerlSufi

[CountOrlok]

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);

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);

Update: replaced write() with write_row()

[PerlSufi]

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..?

[CountOrlok]

You are applying the currency format at the end, after you have written all the data? (before closing the file, of course).

[PerlSufi]

UPDATE:Actually replacing that while statement with my previous one worked to remove J column, but I still don't have the currency format.