php - PHPExcel not calculating formulas
So im kinda new to phpExcel and im making a sheet with data from a form(not done but will come) but i have a problem my formulas wont calculate i have tested preset formulas and import formulas from the code but none wont work all i get is 0 and protected mode and if i click editsheet and click on the cell where the formula is and hit enter it works and calculate it
<?php
/** Error reporting */
error_reporting(E_ALL);
/** PHPExcel */
include 'Classes/PHPExcel.php';
/** PHPExcel_Writer_Excel2007 */
include 'Classes/PHPExcel/Writer/Excel2007.php';
include 'Classes/PHPExcel/IOFactory.php';
// Load file if it doesn't exists
if (file_exists('Exceltime.xlsx'))
{
$objPHPExcel = PHPExcel_IOFactory::load('Exceltime.xlsx');
}
else
{
$objPHPExcel = new PHPExcel();
}
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('C4', '10:00')
->setCellValue('D4', '18:00')
->setCellValue('E4', '01:30')
->setCellValue('F4', '=(D4-C4-E4)');
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('C5', '10:00')
->setCellValue('D5', '18:00')
->setCellValue('E5', '01:30')
->setCellValue('F5', '=(D5-C5-E5)');
$objPHPExcel->getActiveSheet()->setTitle('TidsRapport Namn');
header('Content-Type: application/vnd.openxmlformats- officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="Tidsrapport Namn.xlsx"');
header('Cache-Control: max-age=0');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->setPreCalculateFormulas(TRUE);
$objWriter->save('php://output');
?>
So all it should do is import work clock hours Start/end/break and then the formula is calculating how many hours/minutes you work
also if there is a better way or something i should do im free to try it :)
Answer
Solution:
A better answer is to actually use MS Excel date/time values rather than hoping that PHPExcel/MS Excel will guess that your string values like (
'10:00'
) are supposed to be times.As per the PHPExcel documentation