PHPExcel doesn't give me results

Topics: Developer Forum
Sep 16, 2013 at 3:36 PM

I've something like this into my xls file:
(i hope function are right, i've translated them from my french excel):


Excel give me a result, PHPExcel no (an empty string).
If i test dummy formula (=1+1 or ="toto"), i read the right result.
So, the problem is formula and not my php code.

Is it a PHPExcel bug ? Maybe PHPExcel can calculate my formula ?

Thanks for help.
Sep 16, 2013 at 3:43 PM
SEARCHV is a bad translation, understand VLOOKUP instead.
Sep 16, 2013 at 7:14 PM
Unless you've set a locale for formulae, then you need to use English language function names and a comma (,) as a function argument separator rather than a semi-colon (;), as described in section 4.6.4 of the developer documentation. You can use non-English language and locale argument separator only if you've set the locale language for the calculation engine as described in section 4.6.5 of the developer documentation.
Sep 17, 2013 at 7:28 AM
I want to read a file written with Excel software. So my script doesn't write formulae.
I understood setlocale was only for writes. Am I wrong ?

In spite of, i tested it:
$x = new PHPExcel_Reader_Excel5();
$o = $x->load($my_id);
$xls_sheet = $o->getSheetByName('Feuil1');
$value = $xls_sheet->getCell('A1')->getCalculatedValue('A1');
$value is still empty.

Any idea ?

Sep 17, 2013 at 10:08 AM
I have a feeling that I'm misunderstanding your question.

Internally in Excel, formulae are held in English (BIFF holds tokenised, but OfficeOpenXML actually holds the English language version of the formula): it's the MS Excel GUI that translates it into function names and separators appropriate to your locale.

So your cell should contain a value of the English language version of that formula if you load it into PHPExcel (which you should be able to see if you use PHPExcel's getValue() to retrieve the value of the cell. That is, it should contain a English language names for functions, and use a comma separator for function arguments.

You only need to use locale settings if you are setting/changing a formula and wish to use French language names for functions and a ; argument separator, that's what I thought you were trying to do.

Looking at your formula though, it looks as though it should be an array formula, which isn't supported yet by PHPExcel
Sep 17, 2013 at 12:26 PM
Edited Sep 17, 2013 at 12:26 PM
Thanks for the tip in order to get formula with getvalue().
I've this formula:


The problem was located into bold part.
I hope PHPExcel will support it soon :)

Thanks for help.