phpexceltimephpexcelphpexcelreader

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

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.

enter image description here

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.

Solution

  • 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/