#!/usr/bin/perl
use 5.010;
use strict;
use warnings;
use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel;
my $parser = Spreadsheet::ParseExcel->new();
my $ReadWorkbook = $parser->parse('Read.xls');
my $WriteWorkbook = Spreadsheet::WriteExcel->new('Write.xls');
my $WriteWorksheet = $WriteWorkbook->add_worksheet('Data');
my $formula = $WriteWorksheet->store_formula('=1-(F1/E1)');
for my $ReadWorksheet ($ReadWorkbook->Worksheet('Data')){
my ($row_min, $row_max) = $ReadWorksheet->row_range();
my ($col_min, $col_max) = $ReadWorksheet->col_range();
my $cell = $ReadWorksheet->get_cell($row, $col);
next unless $cell;
$WriteWorksheet->write($row, $col, $cell->value());
my $f_change = $WriteWorkbook->add_format();
$f_change->set_num_format('0.00%');
my $limit = 5;
for my $row($row_min..$limit){
$WriteWorksheet->repeat_formula($row, 13, $formula1, $
+f_change, 'F1','F'.($row+1), 'E1', 'E'.($row+1));
}
}
}
}
The above code works fine to write the calculated values in the excel 'write.xls'. But the following code to access the computed values from the "write.xls" file returns value 0 for the corresponding cells when get_cell function is called. 2nd program - To read data from 'write.xls' and apply some conditions xxx and write it to a new file 'final.xls' '<\p>
#!/usr/bin/perl
use 5.010;
use strict;
use warnings;
use Spreadsheet::ParseExcel;
#use Spreadsheet::WriteExcel;
my $parser = Spreadsheet::ParseExcel->new();
my $ReadWorkbook = $parser->parse('write.xls');
#my $WriteWorkbook = Spreadsheet::WriteExcel->new('final.xls');
#my $WriteWorksheet = $WriteWorkbook->add_worksheet('Data');
for my $ReadWorksheet($ReadWorkbook->Worksheet('Data')){
my ($row_min, $row_max) = $ReadWorksheet->row_range();
my ($col_min, $col_max) = $ReadWorksheet->col_range();
for my $row($row_min..$row_max){
for my $col ( $col_min..$col_max){
my $cell = $ReadWorksheet->get_cell($row, $col);
#condition XXX{
$WriteWorksheet->write($row, $col, $cell->value());
}
}
}
This code writes 0 into the cells which were previously computed by the 1st program. "gives a comment that the number has been written in text". I have tried 'pastespecial using OLE excel' to access the number format in computed cells (to avoid the formula) in 'write.xls' to do further calculations. It works the first time program opens the file. But when I close and open the file again, clicking on the computed cells shows the formula and not the number. How can I get the value of the cell in my program other than get_cell function which returns a "#value" of the cells and not the number value". Thanks again for the help!<\p>
|