Dear all,
I got a template excel file which contains several worksheets. Each worksheet contains some formulas in the cells.
I am using the perl script to read a CSV file and then put the data of CSV into template excel file(first worksheet) and then save it as new file name using Spreadsheet::ParseExcel::SaveParser successfully.
But I find that all the formulas are missing so my effort in puting the CSV data into excel using perl is futile.
My perl code is as shown below. I also attch my CSV file, template excel file and the perl script.
Can any body help if I got something wrong?
--------------------------------
my $source_csv = shift @ARGV or die "invocation: $0 <Source CSV> <xls template file>";
my $destname = shift @ARGV or die "invocation: $0 <Source CSV> <xls template file>";
###my $dest_book = Spreadsheet::WriteExcel->new("$destname") or die "Could not create a new Excel file in $destname: $!";
###my $dest_sheet = $dest_book->addworksheet($sheet);
# Open an existing file with SaveParser
my $dest_excel = Spreadsheet::ParseExcel::SaveParser->new();;
my $dest_book = $dest_excel->Parse($destname)
or die "Could not open Dest Excel file $destname: $!";
#Get the first worksheet from dest excel file
my $worksheet = $dest_book->worksheet(0);
my $row = 1;
my $col = 0;
open (FH,"<","$source_csv") or die "Can not open csv file \n $! \n";
while (<FH>) {
chomp;
@newflds=split(/","/);
foreach (@newflds) {
$worksheet->AddCell($row,$col++,$_);
}
$row++;
$col=0;
}
##$dest_book->close();
# Write over the existing file or write a new file.
$dest_book->SaveAs('newfile.xls');
#$dest_book->SaveAs($destname);
close(FH);
print "done!\n";
--------------------------------