Problem with excel formula with PhpExcel 1.7.8

Topics: User Forum
Apr 3, 2013 at 3:05 PM
Hi,

I have an issue with some formulas in my excel file (2007).
I am reading the cells with getValue() and getCalculatedValue().
getValue() gives me the following formula =IF(OR(N23<>=4,0.46,IF(AO23="S",0.42,""))))) but the fomula in excel file is =IF(OR(N23<T23,O23<T23,P23<T23),"",IF(T23<=1,0.42,IF(T23<=3,0.44,IF(T23>=4,0.46,IF(AO23="S",0.42,""))))).
So, the calculated value is incorrect.
I tried to use the getOldCalculatedValue(), but it gives all values with 0. Is there a way to force excel to calculate the values (like manual open, save and close)? When I open the file manually, saves it and close it and use getOldCalculatedValue(), the values I get are correct.

Any idea? It can be how to fix the read of the formula so PhpExcel calculates the value or force MS Excel to open, save and close so it calculates the value and use getOldCalculatedValue() function.

Thanks!

Saludos,
Coordinator
Apr 3, 2013 at 3:24 PM
ocraw wrote:
I have an issue with some formulas in my excel file (2007).
I am reading the cells with getValue() and getCalculatedValue().
getValue() gives me the following formula =IF(OR(N23<>=4,0.46,IF(AO23="S",0.42,""))))) but the fomula in excel file is =IF(OR(N23<T23,O23<T23,P23<T23),"",IF(T23<=1,0.42,IF(T23<=3,0.44,IF(T23>=4,0.46,IF(AO23="S",0.42,""))))).
So, the calculated value is incorrect.
That's very strange. The PHPExcel Reader should read any formula from an OfficeOpenXML file exactly "as is". BIFF is more complex because it's tokenised in the file, and needs converting, but OfficeOpenXML formulae are stored as plain text. Can you upload an example file so that I can see what might be happening.
I tried to use the getOldCalculatedValue(), but it gives all values with 0. Is there a way to force excel to calculate the values (like manual open, save and close)? When I open the file manually, saves it and close it and use getOldCalculatedValue(), the values I get are correct.
The getOldCalculatedValue() method reads the result from the last time MS Excel itself executed its calculation of the formula, but this can be wrong for any number of reasons (formula calculation disabled in MS Excel, dependencies on external workbooks that have subsequently changed.)
The only way to be recalculate the formula is using the getCalculatedValue() function.
Any idea? It can be how to fix the read of the formula so PhpExcel calculates the value or force MS Excel to open, save and close so it calculates the value and use getOldCalculatedValue() function.
This isn't the way PHPExcel works. For PHPExcel to force MS Excel to do anything would be to create a dependency on MS Excel being available (and if that was the case you could use PHP's COM extension instead). The whole point of PHPExcel is that it should work without any dependency on MS Excel itself.
Apr 3, 2013 at 3:28 PM
Hi Mark,

Where can I upload the file?
Coordinator
Apr 3, 2013 at 3:36 PM
Apr 3, 2013 at 3:58 PM
Mark,

This is the name of the uploaded file. Copy of FORMATO PEDIDO.xlsx and this is my code.
The cells that are not being read correctly is U22 and K22

try
{
$objPHPexcel = PHPExcel_IOFactory::load('../Documentos/Copy of FORMATO PEDIDO.xlsx');
$objWorksheet = $objPHPexcel->setActiveSheetIndex(0);
$objWorksheet = $objPHPexcel->getActiveSheet(); 
echo $descripcion = $objWorksheet->getCell('U22')->getValue(); // Display what the formula is.
//echo $precio = $objWorksheet->getCell('J22')->getValue();
$objWorksheet->getCell('B22')->setValue('9135187'); 
$objWorksheet->getCell('F22')->setValue('80');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPexcel,'Excel2007'); 
$objWriter->setPreCalculateFormulas(false);
$objWriter->save('../Documentos/Copy of FORMATO PEDIDO.xlsx'); 

$objPHPexcel = PHPExcel_IOFactory::load('../Documentos/Copy of FORMATO PEDIDO.xlsx');
$objWorksheet = $objPHPexcel->setActiveSheetIndex(0);
$objWorksheet = $objPHPexcel->getActiveSheet();

echo '</br>Clave '.$clave = $objWorksheet->getCell('B22')->getValue();
echo '</br>Cantidad '.$cantidad = $objWorksheet->getCell('F22')->getValue();
echo '</br>Litros '.$litros = $objWorksheet->getCell('G22')->getCalculatedValue();
echo '</br>Pallets '.$pallets = $objWorksheet->getCell('H22')->getCalculatedValue();
echo '</br>Validacion '.$validacion = $objWorksheet->getCell('I22')->getCalculatedValue();
echo '</br>Precio '.$precio = $objWorksheet->getCell('J22')->getCalculatedValue();
echo '</br>Descuento '.$descuento = $objWorksheet->getCell('K22')->getCalculatedValue();
echo '</br>Descuento Referencia '.$descuento = $objWorksheet->getCell('U22')->getCalculatedValue();
echo '</br>Importe Unitario '.$importe_unitario_descuento = $objWorksheet->getCell('L22')->getCalculatedValue();
echo '</br>Importe '.$importe = $objWorksheet->getCell('M22')->getCalculatedValue();
} catch(Exception $e)
{
die('Error loading file: '.$e->getMessage());
}