Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer

Excel Formatting

by PerlSufi (Pilgrim)
on Apr 22, 2013 at 18:00 UTC ( #1029944=perlquestion: print w/ replies, xml ) Need Help??
PerlSufi has asked for the wisdom of the Perl Monks concerning the following question:

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

Comment on Excel Formatting
Download Code
Re: Excel Formatting
by CountOrlok (Friar) on Apr 22, 2013 at 19:10 UTC
    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()
      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..?
        UPDATE:Actually replacing that while statement with my previous one worked to remove J column, but I still don't have the currency format.
        You are applying the currency format at the end, after you have written all the data? (before closing the file, of course).

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1029944]
Approved by igelkott
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (9)
As of 2014-08-30 17:53 GMT
Find Nodes?
    Voting Booth?

    The best computer themed movie is:

    Results (293 votes), past polls