No value returned on sum/if value cells

Topics: Developer Forum, User Forum
Aug 9, 2013 at 11:56 AM
I'm using an .XLS spreadsheet to calculate some data, inputting/updating numerous fields with a base value and having excel calculate the sums for me.

I am able to save all the data to a new .XLS file, and all calculations are correct.

When opening the created file, and accessing the cells for the required values a null value is being returned.
$objPHPExcel = PHPExcel_IOFactory::load(ABSPATH .'wp-content/plugins/folder/UKPAYE-2013-2014v1-work-base.xls');
    
$objPHPExcel->getActiveSheet()->setCellValue('C5', $options['class_2_ni']);

$nowtime = time();  
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel5");
$objWriter->save(ABSPATH .'wp-content/plugins/folder/UKPAYE-2013-2014-'.$nowtime.'.xls');

//Load file
$objReader = new PHPExcel_Reader_Excel5();
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load(ABSPATH .'wp-content/plugins/folder/UKPAYE-2013-2014-'.$nowtime.'.xls');

$total = $objPHPExcel->getActiveSheet()->getCell('G7')->getCalculatedValue();
echo $total;
The cell that holds the required value is populated from many cells. Some of which contain the following
=IF(C18>=G21,IF(C18>=I21,I21F21,C18F21),0)
=IF((C18-I21)>0,C18-I21,0)

If this is down to the sums, is it possible to save the data as a flat .xls?

Thanks
Coordinator
Aug 9, 2013 at 12:46 PM
Could you explain what a flat .xls is? I've never heard of it before
Aug 9, 2013 at 4:43 PM
Sorry, that's not really a technical term. I meant having a .xls file that was created that didn't have the formula in it. Anyway, I've figured out what the problem was.

Some of my cells were percentage values, however phpexcel didn't understand the cell format and was entering them as a string. To resolve this issue I change the following:

From:
$objPHPExcel->getActiveSheet()->setCellValue('F21', $options['tax_1'].'%'));
To:
$objPHPExcel->getActiveSheet()->setCellValue('F21', ($options['tax_1'] / 100));
$objPHPExcel->getActiveSheet()->getStyle('F21')->getNumberFormat()->setFormatCode('0.00%');
Regards