Cell Formatting issue

Topics: Developer Forum, User Forum
Oct 31, 2012 at 4:05 PM

Hello,

Somehow i need to open the excell file and doubleclick a formatted cell before the formatting becomes active, also i can "edit" the field and press enter to activate the formatting. ofc this is not what i want! I allready tried to format after adding the time to the cell but same result... Any idea's to fix this issue ? worked for weeks on this script but can't use it till this is fixed.

This is what my code for the formatting and adding the data to the cell looks like :

 $objPHPExcel->getActiveSheet()->getStyle("A" . $counter)->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_DATETIME)
    
    
    $objPHPExcel->setActiveSheetIndex($page) 
     ->setCellValue("A" . $counter, $mytime)

 

Best regards,

Neutje

Coordinator
Oct 31, 2012 at 5:19 PM
Edited Oct 31, 2012 at 5:19 PM

So what's the value of $mytime? Is it a valid Excel date/timestamp value?

Nov 2, 2012 at 9:08 AM

The value is

"28/10/2012  22:20"

Format is  "dd/mm/yyyy hh:mm"

I guess its a valid date/timestamp since it works after "activating" the field, how can i make sure it is ?

Coordinator
Nov 2, 2012 at 12:34 PM

"28/10/2012 22:20" looks like a string value to me: Excel (like PHP itself) uses a serailised datetimestamp value for dates (though based on number of days since 1st January 1900 (PHP uses number of seconds since 1st January 1970.

So you need to convert your date/time strings to Excel timestamps:

Use the standard PHP date functions to convert your string to a PHP timetsamp value via the strtotime() function or DateTime::createFromFormat() to create a PHP DateTime object.

These can then be passed to PHPExcel's PHPExcel_Shared_Date::PHPToExcel() method to return an Excel timestamp that you can then store in that cell

Nov 5, 2012 at 9:31 AM

Thanks MarkBaker !

That was the issue indeed, once i converted the date/time to a PHP Timestamp and then used the PHPExcel_Shared_Date::PHPtoExcel() method to store the datetime in the cell it worked as intended. Never noticed that excel uses a serialised datetimestamp