Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PHPExcel issue for reading date field from excel file

Tags:

php

phpexcel

I am facing a problem with the PHPExcel class while i am using that class to read a excel file in my php code.

// Reading the excel data
$objReader = new PHPExcel_Reader_Excel2007();
$objReader->setReadDataOnly(true);
$objPHPExcel = $objReader->load('test.xlsx');
$objPHPExcel->setActiveSheetIndex(0);
$dataArray = $objPHPExcel->getActiveSheet()->toArray();

I am using this code, Now problem is that when I create a excel file and insert a cell value like a date say 01/09/2012

and reads the full excelsheet using the toarray() function of PHPExcel class, then it do not returns the date value. it's returning a numeric value.

What I found that when we create the Excel file using the MS OFfice application and insert a date into any cell the excel converts the cell to a date format. So now I want to read a date format cell using the PHPExcel class.

like image 906
Ram Avatar asked Sep 12 '12 18:09

Ram


1 Answers

Rule one:

It's not a good idea to set $objReader->setReadDataOnly(true); when you'll be working with dates. If you do that, then PHPExcel cannot tell you whether a cell contains a date or a number.

Rule two:

That number is an Excel date value as stored by Excel. The Excel numberFormat Mask then is used to convert that number to a formatted "human readable" date at display-time. (If you've used $objReader->setReadDataOnly(true); then you haven't loaded the cell's numberFormat mask. If you've loaded the numberFormat masks, then you can use the getFormattedValue() method to return the formatted "human-readable" date as a string.

Rule three:

To convert an Excel numeric date value to a unix date/time stamp or to a PHP DateTime object, you can use the built-in PHPExcel_Shared_Date::ExcelToPHP() or PHPExcel_Shared_Date::ExcelToPHPObject() methods.

Rule four:

All of this is fully documented. But I guess I'm going to have to write a special manual dedicated to Dates with the word DATES in big red letters in the filename.

like image 59
Mark Baker Avatar answered Oct 12 '22 01:10

Mark Baker