1

I do have the following code in perl that open an excel file and read row:column, what I want to get is the content of the cell not formula, the get_cell (0,1) and (1,1) and (2,1) has some formula, and value, I'm not interested in formula just the content of the cell or value that has been return by formula say if row:1:0 is 200 then I need to read the 200 not the formula behind the cell! can a few good people get back to me on this..thanks in advance..

            some code above
            use Spreadsheet::ParseExcel
    $workbook = $parser->parse("$dir/$file");
    $worksheet = $workbook->worksheet('Sheet1');

    $total = $worksheet->get_cell(0,1);
    $pass  = $worksheet->get_cell(1,1);
    $fail  = $worksheet->get_cell(2,1);

    # Print the cell value when not blank
    $total = $total->value();
    $pass = $pass->value();
            $fail = $fail->value();
psxls
  • 6,807
  • 6
  • 30
  • 50
user2912312
  • 155
  • 1
  • 3
  • 13

2 Answers2

1

The value() method returns as desired the result of a formula, not the formula itself. I've just tested it myself with a simple =1+1 formula in a cell and the value() returns 2.

If the Excel file you are reading is previously created by Spreadsheet::WriteExcel, then there is a known issue: 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.

psxls
  • 6,807
  • 6
  • 30
  • 50
  • Thanks @psxls.. I think the problem I do have is some of the files are xlsx, it works on files with extention "xls" but not "xlsx", how do I read "xlsx" files, any idea?..thanks – user2912312 Nov 05 '13 at 04:49
0

I found it using

      use Spreadsheet::XLSX;
      my $excel = Spreadsheet::XLSX->new("$filename");
      my ($sheet) = @{ $excel->{Worksheet} };
      print $sheet->{Cells}[0][1]{Val}, "\n";
user2912312
  • 155
  • 1
  • 3
  • 13