Issue with formula when reading Excel data

Topics: Developer Forum, User Forum
Jun 5, 2012 at 8:45 PM

I have an application that takes user input and fills this data into the necessary cells of an Excel file template, once a submit button is clicked.  This is done successfully using PHPExcel as follows:


/** Error reporting - report all - useful for troubleshooting PHP related errors */

/** Require once the IOFactory.php, this is a required line to make the loading of Excel workbook successful */
require_once '../../Classes/PHPExcel/IOFactory.php';
require_once '../../Classes/PHPExcel.php';

if (!file_exists("sim1.xlsx")) 
	exit("Error - file does not exist in directory.\n");

$Reader = PHPExcel_IOFactory::createReader('Excel2007');
$excel2 = $Reader->load('sim1.xlsx'); // Empty Sheet

$excel2->getActiveSheet()->setCellValue('B1000', $phVali)
        ->setCellValue('B1009', $geVali)
        ->setCellValue('B1013', $ppOpti)
	->setCellValue('B1016', $priceVal)
	->setCellValue('B1021', $podBAvail)
	->setCellValue('B1022', $phValiB)
	->setCellValue('B1031', $geValiB)
	->setCellValue('B1035', $ppOptiB)
	->setCellValue('B1038', $priceValB)
	->setCellValue('B1040', $podCAvail)
	->setCellValue('B1041', $phValiC)
	->setCellValue('B1050', $geValiC)
	->setCellValue('B1054', $ppOptiC)
	->setCellValue('B1057', $priceValC)
	->setCellValue('B1059', $podDAvail)
	->setCellValue('B1060', $phValiD)
	->setCellValue('B1069', $geValiD)
	->setCellValue('B1073', $ppOptiD)
	->setCellValue('B1076', $priceValD)
	->setCellValue('B1078', $podEAvail)
	->setCellValue('B1079', $phValiE)
	->setCellValue('B1088', $geValiE)
	->setCellValue('B1092', $ppOptiE)
	->setCellValue('B1095', $priceValE);
$objWriter = PHPExcel_IOFactory::createWriter($excel2, 'Excel2007');   





My goal is to then open & read the newly created excel file (newsim1.xlsx) and grab the values of a few AVERAGE formula cells (calculated based off the user's input) and display them in a HTML table

Example: =AVERAGE(AJ3:AJ211)


When I echo the results of the new cell values for the cells I just altered, I am given the correct values.  This test ensures that the values for being written to the file successfully.   

However, the cells with the AVERAGE formulas do not echo the correct values (echo a very different value than the ones shown if I open up the newly created spreadsheet in the directory). 



$objReader = PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel = $objReader->load("newsim1.xlsx");

$objWorksheet = $objPHPExcel->getActiveSheet();

$podAshare = $objWorksheet->getCell('AJ1106')->getCalculatedValue();
$podAshare1 = round($podAshare * 100, 1) . '%';

$podBshare = $objWorksheet->getCell('AK1106')->getCalculatedValue();
$podBshare1 = round($podBshare * 100, 1) . '%';

$podCshare = $objWorksheet->getCell('AL1106')->getCalculatedValue();
$podCshare1 = round($podCshare * 100, 1) . '%';

$podDshare = $objWorksheet->getCell('AM1106')->getCalculatedValue();
$podDshare1 = round($podDshare * 100, 1) . '%';

$podEshare = $objWorksheet->getCell('AN1106')->getCalculatedValue();
$podEshare1 = round($podEshare * 100, 1) . '%';

$podNshare = $objWorksheet->getCell('AO1106')->getCalculatedValue();
$podNshare1 = round($podNshare * 100, 1) . '%';


I'm not sure what I'm doing wrong or where the values it is displaying are coming from.  Any help would be greatly appreciated!


Jun 5, 2012 at 9:46 PM

Can you please indicate the formula, the values involved (from the cells referenced in the formula), the expected result and the actual result. AVERAGE is a pretty basic function, and I've never had any problems with it before