导出后PHPExcel单元格格式不起作用

Question

I have a PHP code that will export an array of data to excel. It is working fine but the formatting isn't working on the output. For example, I have an array value of 0:13:00 (it is in 24 hour format). What I want is to format it in excel to [mm]:ss so the output should be 13:00 after the export but the value remain as it is while when I look on the cell formatting in excel, the format is already in [mm]:ss. I also tried changing the value to 12 hour format (12:13:00 AM) but it also remain as it is after export. Can someone help me with this please.
Here is my code in formatting the cell using PHPExcel.

$sheet = $objPHPExcel->getActiveSheet();
$sheet->getStyle('C3:N199')->getNumberFormat()->setFormatCode('[mm]:ss');

Answer 1

You mean you have a string containing "0:13:00"? You need to convert it to an MS Excel Serialized timestamp for a number format code like [mm]:ss to have any meaning.... use

$objPHPExcel->getActiveSheet()
    ->setCellValue('A1', 
        PHPExcel_Calculation_DateTime::TIMEVALUE('00:13:15')
    );
$objPHPExcel->getActiveSheet()
    ->getStyle('A1')
    ->getNumberFormat()
    ->setFormatCode('[mm]:ss');

as described in the PHPExcel documentation and examples