Formula error in PHPExcel

Question

i have a strange issue with phpexcel on a pair of formulas and can't figure the source of the problem. (Other simple math formulas work, only this fails).

Since the php report is quite long i'll get straight to the point.

Setting this formula like this

$objPHPExcel->getActiveSheet()->setCellValue("D$cuenta_empleados", "=$'asientos_title'.K$cuadro_row" );

Throws me this

Fatal error: Uncaught exception 'Exception' with message 'INPUT NOMINA Agosto!D8 -> Formula Error: An unexpected error occured' in /Users/PolCPP/Documents/Proyectos/Activos/beneficiat/php/inc/PHPExcel/Cell.php:293 Stack trace:
 #0 /Users/PolCPP/Documents/Proyectos/Activos/beneficiat/php/inc/PHPExcel/Writer/Excel5/Worksheet.php(455): PHPExcel_Cell->getCalculatedValue() 
 #1 /Users/PolCPP/Documents/Proyectos/Activos/beneficiat/php/inc/PHPExcel/Writer/Excel5.php(194): PHPExcel_Writer_Excel5_Worksheet->close() 
 #2 /Users/PolCPP/Documents/Proyectos/Activos/beneficiat/php/classes/Everything.class.php(2361): PHPExcel_Writer_Excel5->save('../../reports/1...') 
 #3 /Users/PolCPP/Documents/Proyectos/Activos/beneficiat/php/classes/Everything.class.php(3813): Everything->create_act_entry(Array, Array, Array, Array, Array) 
 #4 /Users/PolCPP/Documents/Proyectos/Activos/beneficiat/controllers/common/generar.php(68): Everything->gen_docs(Array, Array, Array) 
 #5 {main} thrown in /Users/PolCPP/Documents/Proyectos/Activos/beneficiat/php/inc/PHPExcel/Cell.php on line 293

So to debug it i remove the = to avoid it's calculation. I check it on openoffice. And i see

'ASIENTOS Agosto'.K4

And adding the = in front of it works.

The second formula i have issues it's a condition one (same issue, it can generate it but it works on php)

=IF(D22>O22;D22-O22;0)

Answer 1

The developer documentation states that you need to us US/UK separators in formulae.

Quoting:

4.6.4. Write a formula into a cell

Inside the Excel file, formulas are always stored as they would appear in an English version of Microsoft Office Excel, and PHPExcel handles all formulae internally in this format. This means that the following rules hold:

• Decimal separator is '.' (period)

• Function argument separator is ',' (comma)

• Matrix row separator is ';' (semicolon)

• English function names must be used

This is regardless of which language version of Microsoft Office Excel may have been used to create the Excel file.

so

=IF(D22>O22,D22-O22,0) 

rather than

=IF(D22>O22;D22-O22;0) 

The only exception that applies is if you have set locale settings for formulae as described in section 4.6.5 of that document

The separator for a worksheet in a cell reference is the exclamation mark:

'ASIENTOS Agosto'!K4 

not

'ASIENTOS Agosto'.K4