43

I have the following Excel file:

alt text

I read it in by looping over every cell and getting the value with getCell(...)->getValue():

$highestColumnAsLetters = $this->objPHPExcel->setActiveSheetIndex(0)->getHighestColumn(); //e.g. 'AK'
$highestRowNumber = $this->objPHPExcel->setActiveSheetIndex(0)->getHighestRow();
$highestColumnAsLetters++;
for ($row = 1; $row < $highestRowNumber + 1; $row++) {
    $dataset = array();
    for ($columnAsLetters = 'A'; $columnAsLetters != $highestColumnAsLetters; $columnAsLetters++) {
        $dataset[] = $this->objPHPExcel->setActiveSheetIndex(0)->getCell($columnAsLetters.$row)->getValue();
        if ($row == 1)
        {
        $this->column_names[] = $columnAsLetters;
        }
    }
    $this->datasets[] = $dataset;
}

However, although it reads in the data fine, it reads in the calculations literally:

alt text

I understand from discussions like this one that I can use getCalculatedValue() for calculated cells.

The problem is that in the Excel sheets I am importing, I do not know beforehand which cells are calculated and which are not.

Is there a way for me to read in the value of a cell in a way that automatically gets the value if it has a simple value and gets the result of the calculation if it is a calculation?

Answer:

It turns out that getCalculatedValue() works for all cells, makes me wonder why this isn't the default for getValue() since I would think one would usually want the value of the calculations instead of the equations themselves, in any case this works:

...->getCell($columnAsLetters.$row)->getCalculatedValue();

alt text

pnuts
  • 58,317
  • 11
  • 87
  • 139
Edward Tanguay
  • 189,012
  • 314
  • 712
  • 1,047
  • 8
    A problem when using `getCalculatedValue()` might be the automatic conversion of numeric values with leading zeros. E. g. '0600' will become calculated '600', whereas you need the string '0600' on PHP/database side. Therefore I am using `getValue()` at first to fetch the content of a cell, then identifying equations by means of a leading '=' to fetch their calculated value by `getCalculatedValue()`. – proximus May 17 '13 at 08:02
  • `PHPExcel` has now been superseeded by `PhpSpreadsheet` – Dennis Nov 28 '17 at 23:08
  • I'm using `PHPExcel 1.7.8` and looks like it gets the value of the formula automatically without doing anything special, I have tested it today on my development machine. Even so, a client reported that for him it does not work so I'm waiting to see what is the exact case that doesn't work. Type of formula? Type of Excel file? We'll see, for now, it strangely just works. Update: I'm using `$sheet->rangeToArray` method, that's why! – Alexandru Trandafir Catalin Sep 19 '19 at 09:30

6 Answers6

36

getCalculatedValue() seems to work for all cells, see above

Edward Tanguay
  • 189,012
  • 314
  • 712
  • 1,047
  • 6
    getCalculatedValue() will return the calculated value for a cell containing a formula, or the actual value for a non-formula cell. getValue() will always return the actual value for a cell, including the formula for a cell containing a formula. getFormattedValue() will return the calculated value for a formula cell, or the actual value for a non-formula cell, with any number formatting mask applied, as a string. – Mark Baker Jan 03 '11 at 21:57
  • 3
    As for why getValue() returns the formula, historic reasons. At some point, I'm planning on introducing getFormula()/setFormula() methods, deprecating getCalculatedValue() and changing getValue() to return the calculated value... but I need to do this gradually over several releases to allow for backward compatibility. – Mark Baker Jan 03 '11 at 22:01
  • 1
    I discovered one reason you why it is good that `getCalculatedValue()` is indeed not the default: after I changed it to `getCalculatedValue()`, the PHP process constantly was running out of memory processing the 3MB Excel since there are so many calculations, presumably with VLOOKUPs from 1..65535. But since I have a choice to calculate each cell or not, I can do "smart calculations" of a sheet e.g. calculate simple `SUM`s but not the resource intensive `VLOOKUP`s – Edward Tanguay Jan 04 '11 at 01:24
  • `getCalculatedValue` is now deprecated and returns unformatted result. Looks like `getFormattedValue` may be the one to use, but depends on user's needs. – Dennis Nov 28 '17 at 22:58
  • Not getCalculatedValue and getFormattedValue and getOldCalculatedValue() works in my case and throwing Formula Error: An unexpected error occurred always – Mahesh Yadav Oct 17 '18 at 07:27
  • This is might a problem with structured references in excel: https://github.com/PHPOffice/PhpSpreadsheet/issues/604 – Honsa Stunna May 23 '19 at 14:29
12

If you are unsure about the content of a cell (value or formula included), I recommend you to primarily do a check if the cell has a formula and then copy - paste accordingly. getOldCalculatedValue() is very helpful in this case. Here is an example of that:

$code = $sheet->getCell('A'.$y)->getValue();
if(strstr($code,'=')==true)
{
    $code = $sheet->getCell('A'.$y)->getOldCalculatedValue();
}
$objPHPExcel4->setActiveSheetIndex(0)
             ->setCellValue('A'.$l, $code);

For large data sets, getCalculatedValue() function is really cumbersome and lots of memory will be required to perform correctly.

pancy1
  • 491
  • 7
  • 16
7

Looks like getCalculatedValue() is deprecated. Try using getFormattedValue() instead.

J-who
  • 550
  • 5
  • 9
0

getCalculatedValue() seems to do the right job you wanted. It will return the correct value if the cell contains FBV ( formula based value ). If not then the normal value will be returned instead.

Mûhámmàd Yäsår K
  • 1,492
  • 11
  • 24
user2244112
  • 51
  • 3
0

getCalculatedValue

seems to work for all cells

$sheets = $spreadsheet->getAllSheets();
$priceCasegetCellByColumnAndRow = $sheet->getCellByColumnAndRow(14, ($key))->getCalculatedValue()
$priceCasegetCell = $sheet->getCell('O' . $key)->getCalculatedValue();
Mohammad Trabelsi
  • 3,308
  • 2
  • 10
  • 18
-2

I have never imported an excel file in PHP so this is just a stab in the dark.

Why not check the first character in the cell for an "="

If true getCalculatedValue()
if not getCell()

JoeOD
  • 129
  • 2
  • 5
  • 13
  • The PHPExcel library has a function for this exact problem, and masks the = sign. The getValue() call, does return the calculation which would allow your solution to work, but as answered by Mark Baker in the comments to Edward Tanquay, the proper functions do exist. – Steven Scott May 17 '13 at 19:00