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.

dialogik · Accepted Answer · 2014-11-27 08:36:57Z

11

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.

edited Nov 27, 2014 at 8:36

dialogik

9,59218 gold badges79 silver badges123 bronze badges

answered Mar 22, 2012 at 7:43

Mark Baker

213k34 gold badges354 silver badges391 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mark Baker Over a year ago

Sample code for what? For testing if a cell contains a formula? Getting the calculated value for a formula cell? or using any of the toArray() type methods?

Krzysztof Przygoda Over a year ago

Instead of checking against 'f' use type PHPExcel_Cell_DataType::TYPE_FORMULA. All cell types are declared in class PHPExcel_Cell_DataType in DataType.php

David Bélanger · Accepted Answer · 2012-03-21 17:34:20Z

3

"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)
?>

edited Mar 21, 2012 at 17:34

answered Mar 21, 2012 at 17:22

David Bélanger

7,4485 gold badges42 silver badges55 bronze badges

6 Comments

Anton Sementsov Over a year ago

Yeap, you are right. But if my table became more wider and tomorrow i will have more then one formula, you think i should write the class with parsing all formulas? =) I think thay already been written by developers, but i can`t find them.

David Bélanger Over a year ago

Yes you may need to parse everything... But if you parse a single string (with all data from excel file) you may use regex for it.

Anton Sementsov Over a year ago

Ok, i don`t know exactly how many records i should have. Suppose that table have 1000 records, and i need 1 loop to print table and another one loop to get sum, beside that if i need some other function like average of records or something like that, what will be happen with memmory?

David Bélanger Over a year ago

Put a timer and see by yourself. It shouldn't be a problem at all. I mean, it only check if first character is equal to =. It's like having a loop and saying true or false ?

Mark Baker Over a year ago

The danger is an actual string that begins with an = character... it's possible in Excel

|

Collectives™ on Stack Overflow

PHP Excel Calculate Formulas

2 Answers 2

2 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related