Yii Framework Forum: Phpexcel Date Problem - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Phpexcel Date Problem Rate Topic: ***** 1 Votes

#1 User is offline   Rajeev R 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 29-June 12
  • Location:India

Posted 08 January 2013 - 04:28 AM

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!
0

#2 User is offline   AustinGeek 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 173
  • Joined: 22-September 12

Posted 18 January 2013 - 12:09 PM

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.
0

#3 User is offline   Ziggi 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 95
  • Joined: 14-August 12

Posted 18 January 2013 - 02:27 PM

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.
0

#4 User is offline   Rajeev R 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 29-June 12
  • Location:India

Posted 21 January 2013 - 05:30 AM

View PostZiggi, 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);

0

#5 User is offline   Ziggi 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 95
  • Joined: 14-August 12

Posted 21 January 2013 - 07:03 AM

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.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users