How to read just a date from excel into MySql?

Topics: Developer Forum, User Forum
Nov 11, 2012 at 7:44 PM
Edited Nov 11, 2012 at 7:45 PM


I'm searching this forum and the documentation but I do not understand how to implement of reading just a date.

I have version 1.7.7.

I maintain a volleybal site and I have different Excel file's for the council members to maintain members, commissions, teams etc. and import these data in a MySQL file.

I have moderate the file Iterator.php so that it is working for excel 5 and excel 2007. For text and a time value like '20:00' it is this working fine with:

$imp_data[$ic] = iconv("UTF-8", "ISO-8859-1", $cell->getFormattedValue());

But now I have a date in excel like '1-11-2012' and I have defined the cell properties with date '14-3-2001'.

I want to test a cell value if it is a date. If this is so then I want to import this into a mySQL database, for example like this:

$cell_tmp = 'A2';
if (PHPExcel_Shared_Date::isDateTime($cell_tmp)) {
   $imp_data[$ic]= PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), "M/D/YYYY");

But this is not working.


Nov 12, 2012 at 8:09 AM

That does not work?

The test always returns false? The conversion does not give the expected result? Import into MySQL generates errors or produce aberrant date?
To import in MySQL, does not use a local date format but rather the universal format (constant FORMAT_DATE_YYYYMMDD2 or in php Y-m-d).
PHPEXcel_Shared_Date have methods to convert a Excel date value to a php date/object.
Nov 12, 2012 at 12:06 PM
Edited Nov 12, 2012 at 2:09 PM

The rest of the code is working without the next lines:

$cell_tmp = 'A2';
if (PHPExcel_Shared_Date::isDateTime($cell_tmp)) {
   $imp_data[$ic]= PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), "M/D/YYYY");
Functional, when i click on import data, a dialog window pops up shows the data on that popup window what is imported. And this works fine.But if I add the lines as above, then the whole popup window stays blanc/empty! ==> PHP crashing

Or is it so that I have to include_once a certain php file of the PHPEcel library?
Or this morning I was thinking about the array '$imp_data[$ic]' is filled with text or standard data masks from excel.
Gives this a problem now I want to add a date '01-02-2001' format in one of the array elements?

At the top of the file, the default timezone is set.
O yeh, so as said as a kind of logging I put al the data which I read from the excel on popup window like:

echo '<td>'. iconv("UTF-8", "ISO-8859-1", $cell->getFormattedValue()).'</td>';

But the date field value which is shown on this popup window is 28-10-12 instead of 28-10-2012 which is the value in excel!

Maybe it is my fault, I have been reading yesterday evening in the developers reference guide, but I can't find a code snippet which reading a date value form excel. Maybe I have to include some php file in iterator.php file. Now I have only IOFactory.php included.

I have found something like "PHPExcel_Shared_Date::PHPToExcelObject()" (I don't have the documentation here with me). This explained what it does. Maybe I didn't look good but till now I couldn't find how to implement it with a example.

I hope that it is clear what I mean and what I have written.


Nov 12, 2012 at 2:42 PM

Ok, after opening my eyes ;-)

It's strange you don't see the exception throw by isDateTime : this mehod want a cell (object) not a cell coordinate, so rewrite your test using $cell (current cell object if i'm correct).

Next, you have "in your hand", a date formatted in your locale (i guess) but if your MySQL field is date's type, it's better to have a string formatted accordingly for your insert, so format the "number" using FORMAT_DATE_YYYYMMDD2.

Nov 12, 2012 at 3:04 PM
Edited Nov 12, 2012 at 3:10 PM

 oops. replied to wrong thread.

Nov 12, 2012 at 3:15 PM
lwol wrote:

Ok, after opening my eyes ;-)

It's strange you don't see the exception throw by isDateTime : this mehod want a cell (object) not a cell coordinate, so rewrite your test using $cell (current cell object if i'm correct).

Next, you have "in your hand", a date formatted in your locale (i guess) but if your MySQL field is date's type, it's better to have a string formatted accordingly for your insert, so format the "number" using FORMAT_DATE_YYYYMMDD2.

Ok, maybe stupid, but you mean this ,I suppose:

if (PHPExcel_Shared_Date::isDateTime($cell)) {
   $imp_data[$ic]= PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), "M/D/YYYY");
$cell is a object, because  "$cell->getFormattedValue(), this works in my existing code.

Then I have to replace the line:

$imp_data[$ic]= PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), "M/D/YYYY");
with something like "FORMAT_DATE_YYYYMMDD2"? I believe I have something seen in the reference guide. I hope that I can find a example of these syntax.

Nov 12, 2012 at 4:08 PM

Excuse my lack of clarity.

First point: Yes, exactly.
Second point: replaces "M/D/YYYY" by PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2
This format will give you a date like : 2012-11-12.
This change is useful if the MySQL field receiving date is date type.

You can see the list of the constants (for numbers) in the documentation of the API for the PHPExcel_Style_NumberFormat class.
Nov 13, 2012 at 9:08 PM
Edited Nov 14, 2012 at 8:02 AM

Iwol, I don't mind you're lack of clarity. I'm very glad that you are trying to help me.

Ok, I have changed the tes condition with $cell.

But then, the Excel cell has the date mask/format and my test condition is working now:. But beneath is my test code and it doesn't work to read the cell data and format it to MySQL date format



/** PHPExcel_IOFactory */
require_once '../../Classes/PHPExcel/IOFactory.php';

if (PHPExcel_Shared_Date::isDateTime($cell)) {
  //This prints the date according to the date in Excel. So far so good.
  echo '
$data: '
.PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), "M/D/YYYY").' --/-- '.PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), "YYYY/M/D").'
; //None of the next lines work! $data = $cell->getFormattedValue()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2).'
; $data = $cell->getCalculatedValue()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2).'
; $data = $cell->getValue()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2).'
; //echo '$data: '.$data.'

There is not a exception thrown. I've been reading documentation but I can't find a good example.Do I have to include the "NumberFormat.php" and to instanciate the "PHPExcel_Style_NumberFormat" class?


Btw, I don't know if it matters, but I have PHPExcel 1.7.7.

Nov 15, 2012 at 6:39 PM
For this problem, we use no new features, so version 1.7.7 is not the concern.
The constant exists in the two versions. No need to instantiate the class, you can access the constants statically, of course the class must be accessible, but automatic loading deal with this.

The lines that do not work... What amazes me the most is that none raises an exception (Check the settings in the php.ini file to find out what happens to the errors. It is annoying to not see these messages on a development machine).
And I don't understand their purpose. setFormatCode is to define the format of a cell (not by this way, see 4.6.19 for examples) in the workbook, while I thought you only wanted to read it.
Well... A portion of the line that works:
PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), "YYYY/M/D")

If you change it as :
PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), "yyyy-mm-dd")
Is that you get the result you want?
Nov 15, 2012 at 10:27 PM
Edited Nov 15, 2012 at 10:40 PM

It's my lack of knowledge, but I did right on the 'production' server for the volleybal. I'm the only one who is working with it and it is to maintain the site. I'm building something what the user normally never will see.

But I do it now on my localhost and then I get errors!! Sorry that I have not been thinking by myself to try it on the localhost. But here are my errors now. I'm already googling what this can mean.

$data = $cell->getFormattedValue()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2).'

Fatal error: Call to a member function setFormatCode() on a non-object in F:\htdocs\wsvvolleybal\wsv\php\admin\fileupload\28iterator.php on line 135

$data = $cell->getCalculatedValue()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2).;

Fatal error: Call to a member function setFormatCode() on a non-object in F:\htdocs\wsvvolleybal\wsv\php\admin\fileupload\28iterator.php on line 136

$data = $cell->getValue()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2).;

Fatal error: Call to a member function setFormatCode() on a non-object in F:\htdocs\wsvvolleybal\wsv\php\admin\fileupload\28iterator.php on line 137

$data = $cell->getValue()->setFormatCode(PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), "yyyy-mm-dd"));

Fatal error: Call to a member function setFormatCode() on a non-object in F:\htdocs\wsvvolleybal\wsv\php\admin\fileupload\28iterator.php on line 138

$data = $cell->getValue()->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);

Fatal error: Call to a member function getNumberFormat() on a non-object in F:\htdocs\wsvvolleybal\wsv\php\admin\fileupload\28iterator.php on line 139


I'm using this "$objReader->setReadDataOnly(false);" as well and I see in a another that this should not be used.

Nov 16, 2012 at 5:28 PM
With messages, it should talk to you a little more;-)

Tip : All variants of getValue() return a single value (integer, String, etc.), not an object, as a result, no chance to have a any method available.

But why trying to format these unfortunate cells? Unless I do not understand, you want to read the cells.

Return to what should now work smoothly:

If (PHPExcel_Shared_Date::isDateTime($cell)) {
$imp_data [$ ic] = PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), "M/D/YYYY");
This should be able to read all your cells "date" and store them in your table $imp_data as desired, except error of interpretation.
However the strings "date" are in the form "11/16/2012", and you want to "2012-11-16". To do this, modify the block of code above to have this :

If (PHPExcel_Shared_Date::isDateTime($cell)) {
$imp_data [$ ic] = PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), "yyyy-mm-dd");
Does this work?
Resolve this and we will return to the other lines if you want to.
setReadDataOnly (false) should not be a problem, set it to true would complicate things (and to keep it simple, I will not develop the rationale).
Nov 24, 2012 at 10:41 AM

Hello Iwol,

Thank you for helping me and the patient with me. I have it working now BUT what is left now that I have a problem with the field with a time mask.

Look, the way it goes is that there are different Excel file's which are used by members of different committee's. These members understand Excel. I'm the webmaster but I don't want to fill in all these data, which is already maintained by the different commitee's, in a database. But in these Excel file's there is all different kinds of data like text, date (01-03-2011), time (09:30:00). But maybe was this already clear.

Here is my code which is working for a fields with a date mask (if statement) and text/general mask (else) statement. If a field with a time mask is done in the else part then it is correct written into the database! But now the field with a time mask is running into the if part, whch is returning a date like 1970-01-01 or 2012-03-11.

So how can I distinguish a field with a date mask and a field with a time mask? The method isDateTime is not enough?

if (!is_null($cell)) {
	//The isDateTime function/method is only working for a field with a date mask! If the cell does have a time mask then
	//the isDateTime method recognize this as a DateTime field and the code is running into the if statement and returning
	//DATE--> which is wrong when the value is 09:30:00
	if (PHPExcel_Shared_Date::isDateTime($cell)) {
		//This prints the date according to the date in Excel. So far so good.
		//echo '
$data: '.PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), "M/D/YYYY").' --/-- '.PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), "YYYY-M-D").'
$data = PHPExcel_Style_NumberFormat::toFormattedString(iconv("UTF-8", "ISO-8859-1", $cell->getFormattedValue()), "yyyy-mm-dd"); $imp_data[$ic] = date("Y-m-d",strtotime($data)); //Why doesn't next lines not working? I should like that this construction works because then I'm using the formatting of PHPExcel //$data = $cell->getFormattedValue()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2).'
//$data = $cell->getCalculatedValue()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2).'
//$data = $cell->getValue()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2).'
//$data = $cell->getValue()->setFormatCode(PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), "yyyy-mm-dd")); //$data = PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(), "yyyy-mm-dd"); //$data = $cell->getValue()->getNumberFormat()->setFormatCode(PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2); //echo '$data: '.$ic.' --/--'.$data.'
} else { //echo ' Cell - ' , $cell->getCoordinate() , ' - ' , $cell->getCalculatedValue() , PHP_EOL; //echo '', $worksheet->getCell('G2')->getCalculatedValue(), '' , PHP_EOL; $imp_data[$ic] = iconv("UTF-8", "ISO-8859-1", $cell->getFormattedValue()); } echo '', $imp_data[$ic], '' , PHP_EOL; $ic = $ic + 1; } else { echo 'field is empty or not a date'; }

Thanks in advance,


Nov 25, 2012 at 4:54 PM
isDateTime returns true if you have a date (24/11/2012) an hour (16:10:00), or a date/time (24/11/2012 16:10:00) and variants of this (examples in my locales).

Therefore, it is not sufficient, except as a pre-filter: my value is a temporal data or not? If not, write it in text, ignore it... Your way.

If yes... One way to proceed is to read the format and compare it to known formats to act accordingly.

To determine these known formats, that are not necessarily what you might think, the easiest is the rule of thumb: you display relevant cells of one of your files format.

How to display them? Like this:
echo $objPHPExcel->getActiveSheet()->getStyle ('A1')->getNumberFormat()->getFormatCode();
Adapts the coordinates of the cell as needed.

Note: the PHPExcel_Shared_Date class provides two static methods that convert an Excel time value to its equivalent php: ExcelToPHP and ExcelToPHPObject.
This can avoid a step : ExcelValue->String->timestamp/object => ExcelValue->Timestamp/object. But, if you don't make tests on the timestamp/object, just doing a date_format/DateTime::Format to have a string for MySQL, take the short way : ExcelValue->MySQLString.
Your code snippet:
//Why doesn't next lines not working? I should like that this construction works because then I'm using the formatting of PHPExcel
//$data = $cell-> getFormattedValue()-> setFormatCode (PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2).'
Try to make a:
in the same place or not far away. The dump gives you something that looks like an object? I don't think so.
And the logical consequence: when you are trying to call a method, php screams.

setFormatCode is part of the Style_NumberFormat class, which is accessed by the class Style, collection of Style are in worksheet. A cell has no property that contains (or a method that returns) an object of this class, you must use the Worksheet object, possibly indirectly ($cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->setFormatCode ('my_code')).
However, setFormatCode allows you to change the format of a cell, I'm not sure this is the desired goal.
Nov 30, 2012 at 8:23 PM

Hello LWol,

 I do not understand everything what you are writing but that is more due to my English or that I something else expect from PHPExcel. I think I understand it so far that it is not possible what I want with PHPExcel and that I have to solve in another way around but then it's more difficult to maintain.

There are 6 or 7 excel files used by different committees. These excel file’s, I'm usting to fill a MySQL database! So only what I want for now is reading from excel file's (and not writing to excel a file). And if  understand it right then ‘FORMAT_DATE_YYYYMMDD2’ can only be used for writing a value with a date format into a excel cell. I was expecting that this can be used for reading as well.

But what I have made is more or less a piece of generic code for reading data from excel file's.

foreach ($cellIterator as $cell) {
	if (!is_null($cell)) {
		if (PHPExcel_Shared_Date::isDateTime($cell)) {
			$data = PHPExcel_Style_NumberFormat::toFormattedString(iconv("UTF-8", "ISO-8859-1", $cell->getFormattedValue()), "yyyy-mm-dd");
			$imp_data[$ic] = date("Y-m-d",strtotime($data));
		} else {
			$imp_data[$ic] = iconv("UTF-8", "ISO-8859-1", $cell->getFormattedValue());
		echo '', $imp_data[$ic], '' , PHP_EOL;
		$ic = $ic + 1;
	} else {
		echo 'field is empty or not a date';

//So if a new column is added with date time and I can distinguish columns by a mask
//then I need only to do is to add a field name!
$sql  = "INSERT INTO ".$tabel;
if ($tabel == 'tabel 1') {
	$sql .= " (`field1`,`field2`,`field3`,`field4`,`field5`)";
} elseif ($tabel == 'tabel 2') {
	$sql .= " (`field1`,`field2`,`field3`)";
} elseif ($tabel == 'tabel 3') {
	$sql .= " ( ...etc.

$sql .= " VALUES (";
for ($i = 0; $i < $ic; $i++) {
	if ($i == 0)
		$sql .= "'".mysql_real_escape_string($imp_data[$i])."'";
	 	$sql .= ",'".mysql_real_escape_string($imp_data[$i])."'";
$sql .= ")";

When I add a column between another column, then I only  have to add the new column field in the query insert definition, give the right mask definition in excel and it works.

But if it is not possible to distinguish a date, time and datetime fields from excelthenan is only another opportunity left.

Then I must make it more dedicated like to look which table is filled and to look at which column is iterated. This works also fine, but if there is a new column added then I have to change cellIterator as well.

I can live with it, but I should like a more generic solution..

Another thing is maybe that I not know the limitations of PHPExcel or that I expecting something that it should work in way I think, but that it works different or not and that I have to solve in another way.

Dec 4, 2012 at 4:49 PM

Ok, sorry for my bad english. So, just code. In your isDateTime test :

if (PHPExcel_Shared_Date::isDateTime($cell)) {
		echo 'format : '.$LeFormat;
		case 'mm-dd-yy':
			echo 'date only : '.PHPExcel_Style_NumberFormat::toFormattedString($Cell->getValue(),PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);
		case 'h:mm':
			echo 'time only : '.PHPExcel_Style_NumberFormat::toFormattedString($Cell->getValue(),PHPExcel_Style_NumberFormat::FORMAT_DATE_TIME3);
		case 'm/d/yy h:mm':
			echo 'date and time : '.PHPExcel_Style_NumberFormat::toFormattedString($Cell->getValue(),'yyyy-mm-dd h:mm');
			echo '???';
		} else {//not a date and/or time

Each case value is from a quick test file, but the snippet display the recorded format, you can add/modify if needed.

The result is just displayed but you can store it in your $imp_data.

Dec 9, 2012 at 5:25 PM

Hello LWol,

Friday I've been reading the documentation! and installing the version 1.7.8. I know a little more but I think there is a bug in PHPExcel or it is due my regional settings (I'm dutch) or....

In my excel I have the next column/row:

  • first row (=header) all columns are tekst  (english -> text).
  • next rows:
    • column 1: cell property: datum -> 14-3-2001  (english date -> 14-3-2001)
    • column 2: cell property: Tijd -> 13:30:55        (english time -> 13:30:55)
    • column 3 till 6: cell property: standaard          (english standard)

The next line is returning the format below:


//This code I have read PHP developer documentation.doc. But you're code LWol returns the same result with getParent.
echo $objPHPExcel ->getActiveSheet()->getStyle($cell->getCoordinate())->getNumberFormat()->getFormatCode(), EOL;

Below is the result:


Row number - 1 = header
@ @ @ @ @ @
Row number - 2
   Column1            Column2             Column3 Column4 Column5 Column6
mm-dd-yy [$-F400]h:mm:ss\ AM/PM  General   General   General   General
Row number - 3
mm-dd-yy [$-F400]h:mm:ss\ AM/PM General General General General
Row number - 4
mm-dd-yy [$-F400]h:mm:ss\ AM/PM General General General General

What is strange of course;

  • text is returned by @!? I should expect the word 'text' or 'tekst'.
  • date format 14-3-2001 is returned by mm-dd-yy, while I should expect dd-mm-yyyy!
  • time format 13:30:55 is returned by [$-F400]h:mm:ss. While I should expect h:mm:ss
  • standard format is returned by General. This is what I expect.

Altough I don't prefer a custom cell property, I have tried by making:

  • the first column custom with the property type -> m-d-yyyy
  • the second column custom with the property type -> u:mm:ss

returning value's are now:

  • Column one is still  returned by mm-dd-yy. This is still not what I expected. There is another remarkble thing with excel. After I have changed these cell to this property and I have saves the file, if I open the cell properties again then it is changed back to date with type 14-01-2001! Excel bug?
  • Column two is now returned by h:mm:ss. This is what I have expected

It make no difference if it is a xls or a xlsx file! I have both test it.

Ok I can work further but it doesn't give me a could feeling because I can not explain why I do get these results. And with such a case, I'm asking myself always "Am I the only one with this issue?"

  1. I can work now further but why do I not get the result what I expect? Does have somebody an explanation?
  2. Do I something wrong?
  3. Is it due to my regional settings that I'm dutch? (I have check the regional settings they are d-m-yyyy and h:mm:ss)


Dec 11, 2012 at 4:58 PM

Excel has its way to him to manage the formats.
PHPExcel has to do with and manage it in the same way.
Did this small test in Excel:
Creating a macro:
Function NF(C as Range) as Variant
NF = C.NumberFormat
End Function



Somewhere in your sheet, called the formula =NF(A1)
Replaces A1 with the coordinates of a cell that you want to inspect, and view the result...
You can see that the predefined formats are often internally, noted in US form to which sometimes added localization parameters: [$-F400] is one.
Dec 14, 2012 at 4:19 PM

Hi LWol,

Thanks again.

I did what you suggest this is more what I expected:

Excel Data cell range mask '14-03-2001, NF functions returns: m/d/yyyy,   PHPExcel returns: m/d/yy. I expect d/m/yyy, but I can live with m/d/yyy; US bigger than NL.

Excel Time cell range mask '*13:30:55, NF functions returns: [$-F400]h:mm:ss AM/PM,   PHPExcel returns: [$-F400]h:mm:ss AM/PM. I should expect h:mm:ss.

Excel Time custom cell range mask 'u:mm:ss, NF functions returns: h:mm:ss,   PHPExcel returns: u:mm:ss.

Maybe stuppid from me but ok [$-F400] is added due to predefined formats like US, but I do not understand is why a date format mask is returned by two year digits in stead of four year digits? Is this also that pHPExcel is using the US predifined formats?



Dec 19, 2012 at 8:43 AM
For the predefined formats, Excel not literally writes them to the file, he noted only one identifier. As a result, PHPExcel has a hard-coded list of predefined formats. There may be an error in this list, but I doubt it.
A number of predefined formats can be adapted to the local configuration... How... This should be written in a big book;-) but the explanation on the number of digits of the year should be there.
The letter "u" for hours is strange, however, find complete explanations is not easy (perhaps in the voluminous documentation of the Open XML formats, but I admit not watched).
Side Excel, the local format can be seen by the NumberFormatLocal property, but this is not written in the file, even for custom formats.
Dec 27, 2012 at 10:59 PM
Edited Dec 27, 2012 at 11:04 PM

for me it is dramatic with the formats in Excel. BTW it is always dramatic with datetime value's but that besides. I have learned some more in Excel and that is, if a date is chosen with the format *14-03-2001 then the '*' will say that if the local settings are changed the format mask in Excel is changed as well.

But there was another strange thing in Excel. I changed the format mask according to a format mask in another file. And when I read one file into the database then is was correct like '2012-11-28', so that works. But when I read the other file into the database then it was 1970/01/01 which is wrong. At the end I typed the value again in the Excel cell AND THEN IT BECAME LIKE THE FORMAT MASK as in the 'good' file. Don't ask me why?

But here is my way I have solve it to get it working and all because of the reason that not the right format mask is comming from Excel or not good returned by PHPExcel:

//test formaat bekijken
//echo 'Format: '.$LeFormat.'
switch($LeFormat){ case 'mm-dd-yy': /* if the formatted value is not empty, the is row nr. > 0 (is not header row) and formatted value is not null, then import the value as a date. The PHPExcel construction PHPExcel_Shared_Date::isDateTime($cell) doesn't work because the format mask is a date but if the cell is empty because it is not always mandatory to fill in a date.' */ if (($cell->getFormattedValue() !== '') && ($ir > 0) && (!is_null($cell->getFormattedValue())) ){ $ExcelDate = $cell->getFormattedValue(); // Make the format according to the MySQL date. $aExcelDate = explode('-', $ExcelDate); //echo 'Format: '.$LeFormat.' -- '.$aExcelDate[1].'-'.$aExcelDate[0].'-'.$aExcelDate[2].'
/* because of Excel is exporting or PHPExcel is importing not the full year. In stead of yyyy the year is yy. So if the excel year is higher then this year then it means the member is born in the 1900 century and otherwise in 2000 century. This gives problems if a member is older then 100 years! */ $Current_Year = substr(date('Y'), 2, 2); if ($aExcelDate[2] > $Current_Year) $aExcelDate[2] = '19'.$aExcelDate[2]; else $aExcelDate[2] = '20'.$aExcelDate[2]; $ExcelDate = date('Y/m/d', strtotime($aExcelDate[1].'-'.$aExcelDate[0].'-'.$aExcelDate[2])); $imp_data[$ic] = $ExcelDate; } else $imp_data[$ic] = iconv("UTF-8", "ISO-8859-1", $cell->getFormattedValue()); break; case 'h:mm:ss': case 'h:mm': $imp_data[$ic] = $cell->getFormattedValue(); break; case 'm/d/yy h:mm': $imp_data[$ic] = 'Eerst uitzoeken het format'; break; default: $imp_data[$ic] = iconv("UTF-8", "ISO-8859-1", $cell->getFormattedValue()); }



On this moment I have to less knowledge from Excel and PHPExcel to know where it is going wrong. But because of the year is in PHP only two digits, you have todo strange thing in the code.

But maybe maybe is the new Excel version working correctly or is fixed in PHPExcel....


LWol, I don't know if you are involved in the PHPExcel team, but anyway thanks for you're support! I have really appreceated.

Jan 9, 2013 at 1:35 PM
Edited Jan 9, 2013 at 1:47 PM

For case 'mm-dd-yy', just ask to PHPExcel :

$imp_data[$ic]=PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(),PHPExcel_Style_NumberFormat::FORMAT_DATE_YYYYMMDD2);// give a string like '2013-01-09', nice to insert in SQL
$imp_data[$ic]=PHPExcel_Style_NumberFormat::toFormattedString($cell->getValue(),'yyyy/mm/dd');//a string like '2013/01/09'
$imp_data[$ic]=PHPExcel_Shared_Date::ExcelToPHP($cell->getValue());// a php date, if you need


You can have problems if you have members born before 1900 but i think you need another case in your switch for this (not the same number format).

A small note :

$Current_Year		= substr(date('Y'), 2, 2);//ok...
//but try :