How to read excel cells with time/date masks and using Iterator.php

Topics: Developer Forum, User Forum
Aug 12, 2012 at 11:15 PM
Edited Aug 12, 2012 at 11:17 PM

Hello,

I've been developing something for my volleybal club. The commity's are working with Excel because most of these know how to work with this.

There are different excel file's but there is one with a memberlist and there is a excel with all the teams.

The memberlist has a column with birthdate/start membership date like: 15-1-1998

The team excel has the start-/stoptime from the training like: 19:00

I read this excel rows row by row and put them in a MySQL database. The reading of the text-/integer/float cells is working fine, but the cell with the date or time not. (btw. it goes already wrong by reading the value, it has nothing todo with the MySQL column definition! It's already wrong before I write it to the database)

For example:

Date value is something like:; 19098

time value is something like: 0,81944444444444 and a few of them has also the value as I expected like: 22:00

Here is a peace of the code:

 

foreach ($cellIterator as $cell) {
			if (!is_null($cell)) {
				//echo '        Cell - ' , $cell->getCoordinate() , ' - ' , $cell->getCalculatedValue() , PHP_EOL;
				echo '<td>', $cell->getCalculatedValue(), '</td>' , PHP_EOL;
				//echo '<td>', $worksheet->getCell('G2')->getCalculatedValue(), '</td>' , PHP_EOL;
				$imp_data[$ic] = $cell->getCalculatedValue();
				$ic = $ic + 1;
			}
		}

 

I have also set: $objReader->setReadDataOnly(false); , and I have put it to true as well but it make's no difference. I have read the documentation but maybe this has nothing todo with the getCalculateValue() method?

Has anybody an idea how to read cell value's from cells with date or time masks? Or do I have todo it in a complete different way/solution?

Coordinator
Aug 13, 2012 at 7:41 AM

Either you use getFormattedValue() instead of getValue(), which will give you a string matching the format mask of that cell value; or you pass the result of getValue() to the date/time conversion methods in the PHPExcel_Shared_Date class (such as the ExcelToPHP() or ExcelToPHPObject() methods) which will convert 0.81944444444444 to its PHP equivalent timestamp value or DateTime object, and then you can use normal PHP date formatting methods.

Thereis a whole section of the PHPExcel Function Reference documentation devoted to working with dates and times, which also explains how Excel uses a value like 0.81944444444444 as a time value.

Aug 13, 2012 at 9:15 AM

Hello Mark,

Thanks  a lot, with the getFormattedValue at least the time is working and all the other fields are also still right. The time is most important.

The date is for this moment less important but is not correct due the format.

The Excel value is for example: 14-04-1952. The getFormattedValue() is: 14 April 1952.. My database field is 'DATE' standard value: none --> but all the values are 0000-00-00 in the database. So I have to format the value with the date statement. But this has nothing todo with PHPExcel itself.

 

Thanks so far.