PHP Excel Calculate Formulas

Question

Im trying print Excel file data on a page. To do it i used PHPExcel lib, all works good, beside printing formulas, i have simple example with such formula =SUM(C2:C5)

I print values in a such way:

 $val = $cell->getValue();
 echo '<td>' . $val . '</td>';

how can i check if $val is a formula?

PHPExcel_Cell_DataType::dataTypeForValue($val); told me that it is a just another one string in my $val

Ofc i can calculate it in a loop, and chek if it`s a last row - insert needed info by hands, but how i can calculate it easy way?

Will be pleased to hear your advice. Thanks.

Answer 1

PHPExcel_Cell_DataType::dataTypeForValue($val); will always tell you string for a formula, because a formula is a string. Being a formula is related to the cell, not the data. The getDataType() method of the cell object will return an 'f' for formula.

If you use getCalculatedValue() rather than getValue(), then PHPExcel will determine whether the cell contains a formula or not. If it does, then it will calculate the result and return that, otherwise it will simply return whatever would have been returned by getValue(); The other method you might consider is getFormattedValue() which will return a string, formatted according to whatever rules are set for the cell. And if it was a formula cell, then it will have done the calculation as well. Particularly useful to get a date string rather than a numeric value from cells formatted as dates.

You can also avoid looping of the cells by using the toArray() or rangeToArray() methods, which will return an array of cell values, though you'd still need to loop the array. Both of these methods have arguments allowing you to set whether formulae should be calculated, and whether formatting should be applied.

Answer 2

"how can i check if $val is a formula?"

Every formula in excel start with = so :

<?php
$String = 'SUM(B3:B8)';

# See http://www.php.net/manual/en/language.types.string.php
# $String{} is deprecated as of PHP 6.
if($String[0] == '='){
    echo 'Yes';
} else {
    echo 'No';
}
?>

OR

<?php
function IsExcelFormula($String=null){
    if(!$String OR strlen($String) <= 0){
        return false;
    }

    $First = $String[0];

    return ($First == '=' ? true : false);
}

var_dump(IsExcelFormula('=SUM(B8:B9)')); // bool(true)
var_dump(IsExcelFormula('TOTAL(B3:B6)')); // bool(false)
?>