Cell Format Style Date Bug

Topics: Developer Forum, Project Management Forum, User Forum
Jun 29, 2010 at 5:11 AM

Hello PHPExcel Developer Team,

 

i want to convert some data from grid to excel

on my grid i have a column that it's filled a date data

 

when i convert to excel , i see the format cell not work

example : i have data Jun 29,2010 when i convert to excel that format cell should be like mmm dd,yyyy. but it didn't work

it's work when i double click that cell (value has been change to Jun 29,2010 from 2010/06/29)

 

my code like that :

    for($i=0;$i<count($KdCompln);$i++) {   
        $objPHPExcel->getActiveSheet()->getStyle('A'.$rowC)->getNumberFormat()->setFormatCode('@');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(0,$rowC,$KdCompln[$i]);
        $objPHPExcel->getActiveSheet()->getStyle('B'.$rowC)->getNumberFormat()->setFormatCode('mmm dd,yyyy');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1,$rowC,date("Y/m/d",strtotime($DateCompln[$i])));
        $objPHPExcel->getActiveSheet()->getStyle('C'.$rowC.':'.chr(64+count($headerwidth)).$rowC)->getNumberFormat()->setFormatCode('@');
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(2,$rowC,$UserGrid[$i]);
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(3,$rowC,$CustName[$i]);
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(4,$rowC,$Contact[$i]);
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(5,$rowC,$Menu[$i]);
        $objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(6,$rowC,$Desc[$i]);
        $rowC++;
    }

 

in that code i convert value to yyyy/mm/dd  from grid

in the excel cell i got yyyy/mm/dd format, when i double click the cell it has been mmm dd,yyyy format

 

that is a bug or my code has wrong?

 

 

 

 

Best Regards,

zenixgrace

Coordinator
Jun 29, 2010 at 9:13 AM

If this

$objPHPExcel->getActiveSheet()->setCellValueByColumnAndRow(1,$rowC,date("Y/m/d",strtotime($DateCompln[$i])));

is where you're setting the date value for the cell, then you need to change it and set an Excel date. What you're doing here is setting the cell to be a string containing "2010/06/29".

Excel holds dates as a positive floating point numeric value, the number of days since 1st January 1900, with the fractional part representing the time. PHP holds dates as a signed integer value, the number of seconds since/before January 1 1970 00:00:00 GMT, or as a SateTime object. When storing a value to be used as a date using PHPExcel, you need to store the numeric value that Excel will recognise: when reading a date from Excel, you need to convert it to a PHP date/timestamp or DateTime object. Functions to perform these conversions are built into the PHPExcel_Shared_Date class.

PHPExcel_Shared_Date::ExcelToPHP($excelDate)

PHPExcel_Shared_Date::ExcelToPHPObject($excelDate)

PHPExcel_Shared_Date::PHPToExcel($PHPDate)

PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day, $hours=0, $minutes=0, $seconds=0)

Jun 29, 2010 at 12:20 PM
Edited Jun 29, 2010 at 12:21 PM

thank you for ur explanation mark

i solved my problem..

i'm used use PHPExcel_Shared_Date::stringToExcel() and it's work fineee...

 

i have one problem again ..

when i set the print area, print area show on excel after i'm klik print preview on excel

that is a bug sir?

 

 

once again thanks for ur explanation sir,

 

 

 

Best Regards,

zenixgrace