Excel Total Hours Calculation using PHPExcel in HH:MM format with HH greater than 24 hours

Question

I want to plot total hours in Excel using PHPExcel which is not subjected only to 24 hour format i.e. there can be hours which is greater than 24 hours eg. 49:20.

Currently I'm able to plot and display hours below 24 hours format. As well as display sum of all the hours in excel. i.e. refer the below screen-shot.

But I'm not able to plot time having hour greater then 24 hour. In the above screen-shot I have used the below script to plot the data.

Column A and B plot the require data but they are in string format which cannot be used while sum the cell value in Excel.

For Column A below is the sample code :

$objPHPExcel->getActiveSheet()->setCellValue('A'.$row_count, $time);
$objPHPExcel->getActiveSheet()->getStyle('A1:A'.$row_count)->getNumberFormat()->setFormatCode("[h]:mm:ss");

For Column B below is the sample code :

$objPHPExcel->getActiveSheet()->setCellValue('B'.$row_count,$time);
$objPHPExcel->getActiveSheet()->getStyle('B1:B'.$row_count)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIMEMK);

Column C plots time below 24:00 and I can even sum the values in Excel as well. But I cannot plot time having time greater then 24:00.

Column C sample code :

$objPHPExcel->setActiveSheetIndex()->setCellValue('C'.$row_count, PHPExcel_Calculation_DateTime::TIMEVALUE($time));
$objPHPExcel->getActiveSheet()->getStyle('C1:C'.$row_count)->getNumberFormat()->setFormatCode('[HH]:MM');

Below code which generate the report whose Screen-shot is mentioned above.

require('PHPExcel.php');
//ini_set('memory_limit', '1024M');
require_once 'PHPExcel/Cell/AdvancedValueBinder.php';
$filename ="test.xlsx";
include 'Classes/PHPExcel/IOFactory.php';
include 'PHPExcel/Cell.php';
include 'PHPExcel/Style/NumberFormat.php';
$inputFileType = 'Excel2007';



ob_end_clean();
// create new PHPExcel object
$objPHPExcel = new PHPExcel;

$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
                 ->setLastModifiedBy("Maarten Balliauw")
                 ->setTitle("Office 2007 XLSX Test Document")
                 ->setSubject("Office 2007 XLSX Test Document")
                 ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                 ->setKeywords("office 2007 openxml php")
                 ->setCategory("Test result file");

$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);

$time_array = Array('20:40','22:40','24:00','24:60','50:60','256:60');
    $row_count=1;
    foreach( $time_array as $time )
    {
        $objPHPExcel->getActiveSheet()->setCellValue('A'.$row_count, $time);
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$row_count,$time);
        $objPHPExcel->setActiveSheetIndex()->setCellValue('C'.$row_count, PHPExcel_Calculation_DateTime::TIMEVALUE($time));
        $row_count++;
    }
    $objPHPExcel->getActiveSheet()->getStyle('A1:A'.$row_count)->getNumberFormat()->setFormatCode("[h]:mm:ss");
    $objPHPExcel->getActiveSheet()->getStyle('B1:B'.$row_count)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_TIMEMK);
    $objPHPExcel->getActiveSheet()->getStyle('C1:C'.$row_count)->getNumberFormat()->setFormatCode('[HH]:MM');


ob_end_clean();
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel2007");

$objPHPExcel->getSheet(0)->setTitle('Sheet');
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);

header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");;
header("Content-Disposition: attachment;filename=".$filename.""); 
header("Content-Transfer-Encoding: binary ");
$objWriter->save('php://output');

Note : const FORMAT_DATE_TIMEMK = 'hhh:mm:ss';

Thank you in advance.

Answer 1

I was able to generate required report by making some changes in the script logic. After understanding the Excel functions for calculating the time which is more than 24 hour, we needed to use the Excel Formula to calculate time i.e. =(HOUR/24+TIME(0,MINUTES,0).

Below is sample script

require('PHPExcel.php');
//ini_set('memory_limit', '1024M');
require_once 'PHPExcel/Cell/AdvancedValueBinder.php';
$filename ="test.xls";
include 'Classes/PHPExcel/IOFactory.php';
include 'PHPExcel/Cell.php';
include 'PHPExcel/Style/NumberFormat.php';
$inputFileType = 'Excel5';



ob_end_clean();
// create new PHPExcel object
$objPHPExcel = new PHPExcel;

$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
                 ->setLastModifiedBy("Maarten Balliauw")
                 ->setTitle("Office 2007 XLSX Test Document")
                 ->setSubject("Office 2007 XLSX Test Document")
                 ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
                 ->setKeywords("office 2007 openxml php")
                 ->setCategory("Test result file");

$objPHPExcel = new PHPExcel();
$objPHPExcel->setActiveSheetIndex(0);
$row_count=1;
$objPHPExcel->getActiveSheet()->setCellValue('A'.$row_count, "Hours");
$objPHPExcel->getActiveSheet()->setCellValue('B'.$row_count,"Minutes");
$objPHPExcel->getActiveSheet()->setCellValue('C'.$row_count,"Time");
$row_count++;
$time_array = Array('20:40','22:40','24:00','24:60','50:60','256:60');

    foreach( $time_array as $time )
    {
        $time_slice = Array();
        $time_slice = explode(":",$time);
        $objPHPExcel->getActiveSheet()->setCellValue('A'.$row_count, $time_slice[0]);
        $objPHPExcel->getActiveSheet()->setCellValue('B'.$row_count,$time_slice[1]);
        // $objPHPExcel->getActiveSheet()->setCellValue('C'.$row_count,$time_slice[1]);
        $objPHPExcel->getActiveSheet()->setCellValue('C'.$row_count, '=(A'.$row_count.'/24+TIME(0,B'.$row_count.',0))');//Fixed 
        $objPHPExcel->getActiveSheet()->setCellValue('D'.$row_count, '=('.$time_slice[0].'/24+TIME(0,'.$time_slice[1].',0))');//Fixed 
        $row_count++;
    }
    // $objPHPExcel->getActiveSheet()->setCellValue('C2', "=(A2/24+TIME(,B2,0))");//Fixed

    $objPHPExcel->getActiveSheet()->getStyle('C2:C'.$row_count)->getNumberFormat()->setFormatCode("[h]:mm:ss");
    $objPHPExcel->getActiveSheet()->getStyle('D2:D'.$row_count)->getNumberFormat()->setFormatCode("[h]:mm:ss");


ob_end_clean();
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, "Excel5");

$objPHPExcel->getSheet(0)->setTitle('Sheet');
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);

header("Pragma: public");
header("Expires: 0");
header("Cache-Control: must-revalidate, post-check=0, pre-check=0"); 
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");;
header("Content-Disposition: attachment;filename=".$filename.""); 
header("Content-Transfer-Encoding: binary ");
$objWriter -> setPreCalculateFormulas(true);
$objWriter->save('php://output');

Excel Functionality logic link : https://theexceltrainer.co.uk/excels-10000-hour-limitation/

Answer 2

1. The screenshot looks like you're using OpenOffice on Ububntu rather then excel. I guess you can check the original office package - it might be slightly different.

2. Why not formatting/calculating in php and then just passing the text to the cell?

3. All 3 columns have different format. What is the reason for that?