Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine

Excel Formatting

by PerlSufi (Friar)
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

Replies are listed 'Best First'.
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..?
        You are applying the currency format at the end, after you have written all the data? (before closing the file, of course).
        UPDATE:Actually replacing that while statement with my previous one worked to remove J column, but I still don't have the currency format.

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1029944]
Approved by igelkott
and all is quiet...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (5)
As of 2017-06-24 07:44 GMT
Find Nodes?
    Voting Booth?
    How many monitors do you use while coding?

    Results (557 votes). Check out past polls.