Date Format in .xls

Topics: Developer Forum, User Forum
Feb 2, 2011 at 12:58 AM

Hi,

 

Using $document->getActiveSheet()->setCellValue("C1","2010-12-31"); to fill a formatted date field in a .xls looks like it was not a date, but a string when the .xls is opened in Excel. After clicking the cell (without editing), it becomes formatted correctly. But by default, it looks like it was set to '2010-12-31 - with a ' as initial char. The same issue if the string is date("d/m/Y",...).

Other formattings like decimal separator for numbers, % etc. work well, but the date (and also time) cells need are wrong by default :-/

What's wrong with my .xls or is setCallValue() missing additional information for date/time cells?

 

Thanks for advice

Coordinator
Feb 2, 2011 at 8:01 AM
Edited Feb 2, 2011 at 11:18 AM

Quoting from section 4.6.3 of the Developer Documentation:

Write a date into a cell

In Excel, dates are stored as numeric values counting the number of days elapsed since 1900-01-01. For example, the date '2008-12-31' is represented as 39813. You can verify this in Microsoft Office Excel by entering that date in a cell and afterwards changing the number format to 'General' so the true numeric value is revealed.

Writing a date value in a cell consists of 2 lines of code. Select the method that suits you the best. Here are some examples:

/* PHPExcel_Cell_AdvanceValueBinder required for this sample */ 
require_once 'PHPExcel/Cell/AdvancedValueBinder.php'; 
// MySQL-like timestamp '2008-12-31' 
PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() ); 
$objPHPExcel->getActiveSheet()->setCellValue('D1', '2008-12-31'); 
Note that setting the Advance Value Binder will affect all subsequent data written to cells, unless you subsequently reset to the Default Value Binder. There is more information about cell value binders in the documentation.
 
 
or
// PHP-time (Unix time) 
$time = gmmktime(0,0,0,12,31,2008); // int(1230681600) 
$objPHPExcel->getActiveSheet()->setCellValue('D1', PHPExcel_Shared_Date::PHPToExcel($time));
$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH); 
or 
// Excel-time
$objPHPExcel->getActiveSheet()->setCellValue('D1', 39813)
$objPHPExcel->getActiveSheet()->getStyle('D1')->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDDSLASH)

The above methods for entering a date all yield the same result. PHPExcel_Style_NumberFormat provides a lot of pre-defined date formats.

 

There is also an entire section of the Function Reference Documentation (section 3.2) dedicated to working with dates, converting between PHP dates and Excel dates, and the methods available in PHPExcel for doing this.

Feb 2, 2011 at 1:57 PM

Hi Mark,

 

Although I tried with PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() ); there is still an issue on the format.

The date column in the template.xls that I use is set to dd.mm.yyyy. After phpexcel fills the cells with date values, it looks like yyyy-mm-dd and the cell format is set to "custom" instead of "date". It's able to understand several date input/values, but why does it overwrite the format again?

 

Thanks and regards

Coordinator
Feb 2, 2011 at 4:07 PM
Edited Feb 2, 2011 at 4:10 PM

In Excel, when you type a value into a cell that looks like a date (such as 02-Feb-2011), then Excel automatically converts that input string to an Excel date/timestamp value, and sets the cell format mask to the default number format mask for dates (irrespective of any existing format mask for that cell). PHPExcel's Advanced Value Binder does exactly the same thing... it identifies a string that it believes is a date, converts the string value to an Excel date/timestamp value, and sets the number format mask to the default format mask for dates, overriding any existing number format mask.

If you've already set a number format mask for your date cells, then you don't want to use the Advanced Value Binder, but simply to convert your date string to an Excel date/timestamp using PHPExcel_Shared_Date::PHPToExcel($dateValue), PHPExcel_Shared_Date::FormattedPHPToExcel($year, $month, $day, $hours, $minutes, $seconds) or PHPExcel_Shared_Date::stringToExcel($dateValue); without overriding the number format mask that you've already set.

Note that there have been some changes to the PHPExcel_Shared_Date::stringToExcel() method since the 1.7.5 release to prevent PHPExcel from falsely recognising some string as dates.