PHPExcel calculate formula cell and format currency

Question

Now I'm reading an excel file and showing it on a html table.
like this:

 <?php 
 function load_table(){
    require_once('Classes/PHPExcel.php');
    $objReader = PHPExcel_IOFactory::createReader('Excel2007');
    $objReader->setReadDataOnly(true);

    $objPHPExcel = $objReader->load("SampleData.xlsx");
    $objWorksheet = $objPHPExcel->getActiveSheet();
    $highestRow = $objWorksheet->getHighestRow(); // e.g. 10
    $highestColumn = $objWorksheet->getHighestColumn(); // e.g 'F'

    $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn); // e.g. 5

    echo '<table>' . "\n";
    for ($row = 1; $row <= $highestRow; ++$row) {
      echo '<tr>' . "\n";

      for ($col = 0; $col <= $highestColumnIndex; ++$col) {
        echo '<td>';
        echo  $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();     
        echo '</td>' . "\n";

      }

      echo '</tr>' . "\n";
    }
    echo '</table>' . "\n";
}
  ?>

Sometimes the cell content is a formula. I'd like to calculate it and echo the result (just like excel does). I tried the code below but I got the following error:
Warning: Illegal string offset 'value' in...

echo  $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();

I also tried something like:

echo  $objWorksheet->getCellByColumnAndRow($col, $row)->getOldCalculatedValue();

This one worked, but I do not want to use GetOldCalculatedValue because:

getOldCalculatedValues() reads the value as it was last calculated by MS Excel (if available) though you can't guarantee it will always be there, or be correct if it is (you can disable autocalculation in MS Excel, or it won't be set if loading a CSV file); while getCalculatedValue() actually recalculates the value within PHPExcel itself.

Also I used echo to print just the formula string and it appears to be just fine:

echo $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();

Fórmula printed:
=F2*E2 =F3*E3 =F4*E4 =F14*E14 ...

So how could I get the result of the formula and ALSO format it as currency ?

score 1 · Accepted Answer · answered Jun 01 '15 at 21:08

1

As regards cell formatting:

The cell won't have any format if you use

$objReader->setReadDataOnly(true);

because you're specifically telling PHPExcel not to load cell formats. Don't set readDataOnly to true if you want to read formats

As regards the other part of your question, if you're getting an error when you call

echo  $objWorksheet->getCellByColumnAndRow($col, $row)->getCalculatedValue();

then it might be useful to say what the formula is

echo  $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();

should tell you the formula, so perhaps you might consider adding that to your question, because it's quite useful to know if you want help

answered Jun 01 '15 at 21:08

Mark Baker

209,507
32
346
385

Hello Mark, what a honor ! when I echo $objWorksheet->getCellByColumnAndRow($col, $row)->getValue();` it prints the formula perfect. But I'd like to find a way to print its result instead. Already set `ReadDataOnly(false);`` but the error continues. Please help '-' – PlayHardGoPro Jun 01 '15 at 21:49
It's possible that there's a problem with the formula, if you're getting an error when you call `getCalculatedValue();`, so it might be useful to know what that formula is – Mark Baker Jun 01 '15 at 21:51
Examples: `=F2*E2 , =F3*E3, =F9*E9 ...`. When I open the excel file itself the results are fine. – PlayHardGoPro Jun 01 '15 at 21:55
Well those formulae shouldn't cause any problems: what version of PHPExcel are you running? – Mark Baker Jun 01 '15 at 22:28
I am using `PHPExcel 1.7.6`. Because when I download the file from github wich is the version 1.9, I cant find the `PHPExcel.php` inside the zip. – PlayHardGoPro Jun 01 '15 at 22:32
1

1.7.6 is pretty old, and shouldn't be used anymore: 1.8.1 is the latest tagged production release... Don't use the 1.9 branch, the big warning in the README should tell you that it is a highly unstable branch at the moment – Mark Baker Jun 01 '15 at 22:38

PHPExcel calculate formula cell and format currency

1 Answers1