Re^2: Get Cell Value while Reading Excel file and not the formula

[PBegin]

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>

[PBegin]

Also, Is there a module to write and read the same excel file in the same program? Thanks!

[hdb]

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);
    }
  }
}

[Anonymous Monk]

What I think is happening is the following:

Could be :)

perldoc Spreadsheet::ParseExcel |grep -i formula

This module cannot read the values of formulas from files created with Spreadsheet::WriteExcel unless the user specified the values when creating the file (which is generally not the case). The reason for this is that Spreadsheet::WriteExcel writes the formula but not the formula result since it isn't in a position to calculate arbitrary Excel formulas without access to Excel's formula engine.