loading a cell with 9/30/2010 gives me 40451?

Topics: User Forum
Oct 4, 2010 at 7:29 PM
I open up Book1.xls in Excel and see that cell A1's value is 9/30/2010.  I try to load that cell with PHPExcel and get 40451 as its value.  Why?

Here's the script I'm using:


<?php include('PHPExcel.php'); $excel = PHPExcel_IOFactory::load('Book1.xls'); echo $excel->getActiveSheet()->getCell('A1')->getValue(); ?>
Coordinator
Oct 4, 2010 at 9:08 PM

Excel stores dates/times as the number of days since 1st January 1900 (or 1st January 1904 if your workbook uses the Mac 1904 calendar). This is why the cell value is 40451... that's it's value in Excel, and in PHPExcel. The only thing that differentiates this from any other number is the formatting mask applied to the cell.

You can identify if a cell contains a date using:

PHPExcel_Shared_Date::isDateTime($objPHPExcel->getActiveSheet()->getCell('A1'));

which will return a boolean true if cell A1 contains a value that is formatted as a date.

 

To read the cell value as a PHP date, you can use the date conversion functions:

$phpDate = PHPExcel_Shared_Date::ExcelToPHP($objPHPExcel->getActiveSheet()->getCell('A1'));

to convert an Excel/PHPExcel date to a PHP date/timestamp, and then use standard PHP date() function for formatting

 

Alternatively, to read the cell value as a formatted string,

$dateString = PHPExcel_Style_NumberFormat::toFormattedString($objPHPExcel->getActiveSheet()->getCell('A1'), 'MM-DD-YYYY');

 

 

Oct 5, 2010 at 11:56 PM

Nice, thanks Mark!  I'm new to PHPExcel and was having the same problem.  

In addition to the date problem I am also getting a time such as "11:30 AM" in the excel file read in using getValue() as 0.4791666666666667.  I'm guessing I can probably find a similar solution by digging around in the API, but I'm also curious if there is a simple way to read in the value of every cell as plain text instead of having to test if something is a date, time or other numeric field?

Thanks,

John

Coordinator
Oct 6, 2010 at 6:21 PM

Have a look at the toArray() method of the worksheet object. This has been modified in the latest SVN code to add a lot of additional options.

/**
 * Create array from worksheet
 *
 * @param mixed $nullValue            Value returned in the array entry if a cell doesn't exist
 * @param boolean $calculateFormulas  Should formulas be calculated?
 * @param boolean $formatData         Should formatting be applied to cell values?
 * @param boolean $returnCellRef      False - Return a simple array of rows and columns indexed by number counting from zero
 *                                    True - Return rows and columns indexed by their actual row and column IDs
 * @return array
 */

The argument that is probably of particular interest to you is the $formatData option.

Oct 6, 2010 at 8:03 PM

Great tip.  I see the current version 1.7.4 doesn't have all of those parameters in the toArray() method yet: 

    /**
     * Create array from worksheet
     *
     * @param mixed $nullValue Value treated as "null"
     * @param boolean $calculateFormulas Should formulas be calculated?
     * @return array
     */
   public function toArray($nullValue = null, $calculateFormulas = true) {

I will definitely keep an eye out for updates ;)

In the meantime I'm just converting dates and times back to the formats I need them in, working great.  Thanks again!

Coordinator
Oct 6, 2010 at 8:52 PM
missingdigit wrote:

Great tip.  I see the current version 1.7.4 doesn't have all of those parameters in the toArray() method yet: 

I will definitely keep an eye out for updates ;)

 The new parameters will be in the 1.7.5 release, or are available from the daily downloads under the "source code" tab here on phpexcel.codeplex.com (absolutely latest revision, because I made some other changes to the toArray() method today).