php - PHPExcel not calculating formulas

365

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 :)

565

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

$objPHPExcel->setActiveSheetIndex(0)
        ->setCellValue('C4', PHPExcel_Calculation_DateTime::TIMEVALUE('10:00'))
        ->setCellValue('D4', PHPExcel_Calculation_DateTime::TIMEVALUE('18:00'))
        ->setCellValue('E4', PHPExcel_Calculation_DateTime::TIMEVALUE('01:30'))
        ->setCellValue('F4', '=(D4-C4-E4)');
$objPHPExcel->getActiveSheet()->getStyle('C4:E4')
    ->getNumberFormat()
    ->setFormatCode('hh:mm');
$objPHPExcel->getActiveSheet()->getStyle('F4')
    ->getNumberFormat()
    ->setFormatCode('[hh]:mm');

People are also looking for solutions to the problem: php - fetching more user details in twitter

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.