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;

[download]
Thanks in advance for your efforts.. PerlSufi

In reply to Excel Formatting by PerlSufi

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.