[Rajeev R]

I am using PHPExcel extension for read/write excel files. Now I have a problem with reading the date format from excel file. In the excel file, for the date column, the data is "12/31/2012". ie "mm/dd/yyyy".

Yii::import('application.extensions.PHPExcel');
		$objPHPExcel = new PHPExcel();
		$inputFileName = 'uploads/'.$filename;
		$objPHPExcel = PHPExcel_IOFactory::load($inputFileName);
		$sheetData = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);

This is the code I am using to read the excel file. When I print the $sheetData array, the date is showing as "12-31-12". ie, "mm-dd-yy"

Note that, only some excel files are showing this strange result.
Can anybody tell me, "12/31/2012". ie "mm/dd/yyyy" field in Excel will become "12-31-12". ie, "mm-dd-yy" when I parse it. Anyone please help!

[AustinGeek]

excelview problem...I posted problem where I was experiencing urls converted as well. Not sure why it thinks the URL is a date.
I would expect the behaviour to act on date columns but it affects strings as well.

[Ziggi]

This is plain simple. Day/Time information are stored in Excel as plain numeric values. The only thing what makes them dates/times is cell formatting.

So, what's happening in the background?

PHPExcel reads a cell and finds a numeric value in cell and data/time formatting information attached to it. Ah so! - PHPExcel thinks - this number is a date/time value in fact!

So, what PHPExcel is doing at this moment?

$cellValue = {the numeric value read from Excel cell}
$dateTime = new DateTime($cellValue);

Only this! So, what formatting will be applied to this new DateTime object?

The answer is - default formatting! And in case of PHP that is dependent on default_time_zone value of the WWW server!

So, either you have to ensure server time zone information is appropriate to your needs or you have to format your output manually using appropriate DateTime object formatting.

[Rajeev R]

Ziggi, on 18 January 2013 - 02:27 PM, said:

This is plain simple. Day/Time information are stored in Excel as plain numeric values. The only thing what makes them dates/times is cell formatting.

So, what's happening in the background?

PHPExcel reads a cell and finds a numeric value in cell and data/time formatting information attached to it. Ah so! - PHPExcel thinks - this number is a date/time value in fact!

So, what PHPExcel is doing at this moment?

$cellValue = {the numeric value read from Excel cell}
$dateTime = new DateTime($cellValue);

Only this! So, what formatting will be applied to this new DateTime object?

The answer is - default formatting! And in case of PHP that is dependent on default_time_zone value of the WWW server!

So, either you have to ensure server time zone information is appropriate to your needs or you have to format your output manually using appropriate DateTime object formatting.

Will check what you have suggested above. Meanwhile I found a solution for this ,

I have set up the custom date format in the NumberFormat.php file like this,

const FORMAT_DATE_XLSXCUSTOM			= 'mm/dd/yyyy';

And, Used this code, It works.

PHPExcel_Cell::setValueBinder( new PHPExcel_Cell_AdvancedValueBinder() );
		$highestRow = $objPHPExcel->getActiveSheet()->getHighestRow();
		$objPHPExcel->getActiveSheet()
             ->getStyle('K1:K'.$highestRow)
            ->getNumberFormat()
            ->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_XLSXCUSTOM);

[Ziggi]

Oh yes - sure - here you've defined custom formatting in another way, but that's the case - date/time formatting is not retained from Excel but has to be re-declared in PHP.