in reply to Re: Get Cell Value while Reading Excel file and not the formula in thread Get Cell Value while Reading Excel file and not the formula
Thank you for the quick responses. Following is my code:
1st program  To compute formula and write it to an excel.
#!/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>
Re^3: Get Cell Value while Reading Excel file and not the formula by PBegin (Initiate) on Apr 14, 2013 at 07:00 UTC 
Also, Is there a module to write and read the same excel file in the same program? Thanks!  [reply] 

What I think is happening is the following:
Your "write" program generates an Excel file containing some formulae.
These formulae never get calculated unless you open the file in Excel and calculate.
If I then use your "read" program to read the file, I get "0.00%" values from the formula cells.
If I first save the spreadsheet in Excel, then the "read" program returns proper values.
My interpretation is that by using Spreadsheet::WriteExcel, formulae will never be calculated, which makes sense as no Excel is present necessarily. Your dilemma now is that you will not have the results from these formulae before you open the spreadsheet in Excel and have it calculated and saved. In which case using "Win32::OLE" would be the better solution. Then Perl would always interact with the spreadsheet through Excel and you can read/write as you like.
I have looked at the module documentation but I found nothing that confirms or contradicts my hypothesis.
Just for completeness, here is my reduced "write" script:
#!/usr/bin/perl
use 5.010;
use strict;
use warnings;
use Spreadsheet::WriteExcel;
my $WriteWorkbook = Spreadsheet::WriteExcel>new('Write.xls');
my $WriteWorksheet = $WriteWorkbook>add_worksheet('Data');
my $formula = $WriteWorksheet>store_formula('=1(F1/E1)');
my $f_change = $WriteWorkbook>add_format();
$f_change>set_num_format('0.00%');
for my $row ( 1..4 ) {
for my $col ( 4..5 ) {
$WriteWorksheet>write($row, $col, rand());
}
$WriteWorksheet>repeat_formula($row, 13, $formula, $f_change, 'F1',
+'F'.($row+1), 'E1', 'E'.($row+1));
}
and my "read" script:
#!/usr/bin/perl
use 5.010;
use strict;
use warnings;
use Spreadsheet::ParseExcel;
my $parser = Spreadsheet::ParseExcel>new();
my $ReadWorkbook = $parser>parse('write.xls');
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);
print "$row,$col,",$cell>value(),"\n" if defined($cell);
}
}
}
 [reply] [d/l] [select] 

 [reply] 

