Fatal error: Uncaught PHPExcel_Calculation_Excepti

Question

I am trying to read a xml file with PHP but I have some problems.

First of all, if my cell is: 20/11/2016, I get that in PHP with getFormattedValue(), but its result is: 42370. Why that?

And the other and more important is that I want the result in a cell of a formula: For example I have in a cell: 128, but the formula is:

=SI(O(INDICE(Precios;COINCIDIR(C$1;Tipos;0);COINCIDIR($A326;Días;0))="";INDICE(Precios;COINCIDIR(C$1;Tipos;0);COINCIDIR($A326;Días;0))=Caracter_Cierre);"";MAX(INDICE(Precios;COINCIDIR(C$1;Tipos;0);COINCIDIR($A326;Días;0));INDICE(Precios_Minimos;COINCIDIR(C$1;Tipos_Precios_Minimos;0);3)))

So, I get that with: getCalculatedValue() but then I got a error like that:

Fatal error: Uncaught PHPExcel_Calculation_Exception: Tesipro!C2 -> Formula Error:

I don't know what to do.

Answer 1

First of all, if my cell is: 20/11/2016, I get that in php with getFormattedValue() but its result is: 42370. Why that?

42370 is an MS Excel serialized timestamp. Internally, MS Excel maintains dates/times as a numeric timestamp; a float value holding the number of days since 1st January 1900 (or 1st January 1904 if the Mac version of Excel), with time as the fractional part of the day. It then uses a number format mask to display that value in human-readable form as a date.

Normally, getValue() will return the raw cell value, the serialised timestamp in this case; and getFormattedValue() will apply the number format mask too convert that value to a string formatted for display as it appears in the MS Excel GUI. If you're using getFormattedValue() and still seeing the 42370 value, then in all likelihood you've loaded the file with readDataOnly set to true, which tells PHPExcel not to load the number format masks, so it then cannot apply the format mask to convert that serialized timestamp value to a displayable date format.

---

And for the formula problem, I can't do much to identify the cause without access to the file itself, to analyse the execution details of the calculation engine when trying to calculate the value; but there is a gist here that shows how to run the calculation in debug mode. The information from this would make it easier to try and diagnose the problem.