PHP Excel Error when entering SUM function to a cell

0
votes

I'm having this issue, when generating an EXCEL file with sum formulas.

PHP code:

$objPHPExcel->getActiveSheet()->setCellValue("I".$fila,"=SUMA(I17:I".($fila-1).")");

Note: SUMA is "sum" in spanish.. Works like that for spanish config

When I open Excel file, it shows values as : ######

But the formula is correct, when I just type enter after the formula it gets re-calculated and correct.

3
phpsumformulaphpexcel
If it shows as ###### what does it display if you increase the column width? - Andy G
@Rodrigo Carvalho, it seems, that your code works fine and cells just have not enough width. - Alex.K.

3 Answers

2
votes

PHPExcel uses English language internally: if you're using Spanish language for your formula functions, you have to set the local to Spanish; and can then translate them to English to inject into the cell. See section 4.6.5 of the developer documentation (entitled "Locale Settings for Formulae").

$locale = 'es';
$validLocale = PHPExcel_Settings::setLocale($locale);
if (!$validLocale) {
    echo 'Unable to set locale to '.$locale." - reverting to en_us<br />\n";
}

$formula = "=SUMA(I17:I".($fila-1).")";
$internalFormula = 
    PHPExcel_Calculation::getInstance()->translateFormulaToEnglish($formula);
$objPHPExcel->getActiveSheet()->setCellValue("I".$fila, $internalFormula);
1
votes

You can use the formula functions in English. 

$objPHPExcel->getActiveSheet()->setCellValue("I".$fila,"=SUM(I17:I".($fila-1).")");

open the document in excel, you can see the total calculated and in function formula "=SUMA()"

0
votes

Only a corretion:

$internalFormula = PHPExcel_Calculation::getInstance()->_translateFormulaToEnglish($formula);