CODEWITHSUNDEEP
phpphpexcel
Edward Tanguay
Edward Tanguay

Reputation: 193312

How to automatically read in calculated values with PHPExcel?

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

Upvotes: 44

Views: 58699

Answers (6)

Mohammad Trabelsi
Mohammad Trabelsi

Reputation: 3650

getCalculatedValue

seems to work for all cells

$sheets = $spreadsheet->getAllSheets();
$priceCasegetCellByColumnAndRow = $sheet->getCellByColumnAndRow(14, ($key))->getCalculatedValue()
$priceCasegetCell = $sheet->getCell('O' . $key)->getCalculatedValue();

Upvotes: 0

user2244112
user2244112

Reputation: 51

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.

Upvotes: 0

pancy1
pancy1

Reputation: 501

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.

Upvotes: 12

J-who
J-who

Reputation: 550

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

Upvotes: 7

Edward Tanguay
Edward Tanguay

Reputation: 193312

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

Upvotes: 37

JoeOD
JoeOD

Reputation: 115

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()

Upvotes: -2

Related Questions