Date & Time as a string

Is it possible to retrive a date (formated as date 2012-01-15) as string, meaning to get '2012-01-15' value parsed? Also for time?

First of all:
Thank you very much for this nice class to the author :)

On the issue of dates, I suggest an update in the following direction:

Info:
If your date looks like "40305" and should be like "2010-05-07", the problem is that the date fields have integers that are the amount of days from 1900-00-00...well...ok :)


simple php-convertion:
$value = date('Y-m-d', mktime(0,0,0,1,$value-1,1900));


However..how do you know it is a date field? :)

You need to update the function "value( $cell )" with the following functionality:

First, you need to also load the 'styles.xml' from the .xlsx file (it is actually a zip file, try renaming .xlsx to .zip).

In the styles.xml you find Tags like this:
<xf numFmtId="14" fontId="0" fillId="0" borderId="0" xfId="0" applyNumberFormat="1"/>

Now..look in all "xf" tags if the attribute "applyNumberFormat" is equal to the (optional) attribute "s" in the "$cell" variable.

If you found a match, then the attribute "numFmtId" in the "xf" tag is what you want to know.

The "numFmtId" defines the format of that cell.

If it is a custom format, then you should find it in the styles.xml in a tag like this:
<numFmt numFmtId="166" formatCode="#,##0.000" />

If the "numFmtId" is lower than 164, it is probably a default definition and not in the styles.xml. Then you have to look up the official "Ecma Office Open XML File Formats Standard documents" (http://www.ecma-international.org/news/TC45_current_work/TC45_available_docs.htm) to find out what format is defined for that ID.


An incomplete list of these formats:

0 = 'General';
1 = '0';
2 = '0.00';
3 = '#,##0';
4 = '#,##0.00';

9 = '0%';
10 = '0.00%';
11 = '0.00E+00';
12 = '# ?/?';
13 = '# ??/??';
14 = 'mm-dd-yy';
15 = 'd-mmm-yy';
16 = 'd-mmm';
17 = 'mmm-yy';
18 = 'h:mm AM/PM';
19 = 'h:mm:ss AM/PM';
20 = 'h:mm';
21 = 'h:mm:ss';
22 = 'm/d/yy h:mm';

37 = '#,##0 ;(#,##0)';
38 = '#,##0 ;[Red](#,##0)';
39 = '#,##0.00;(#,##0.00)';
40 = '#,##0.00;[Red](#,##0.00)';

44 = '_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)';
45 = 'mm:ss';
46 = '[h]:mm:ss';
47 = 'mmss.0';
48 = '##0.0E+0';
49 = '@';

27 = '[$-404]e/m/d';
30 = 'm/d/yy';
36 = '[$-404]e/m/d';
50 = '[$-404]e/m/d';
57 = '[$-404]e/m/d';

59 = 't0';
60 = 't0.00';
61 = 't#,##0';
62 = 't#,##0.00';
67 = 't0%';
68 = 't0.00%';
69 = 't# ?/?';
70 = 't# ??/??';


So..if you finally know the format of the cell, you can send it to some function like the following to determine/guess if it is a date field and - if yes, then use the 'simple php conversion' from above on the value :)


function isDateFormat($pFormatCode){
$possibleDateFormatCharacters = 'ymdHis';
if ((substr($pFormatCode,0,1) == '_') || (substr($pFormatCode,0,2) == '0 ')) {
return false; // usually a number
}
if (preg_match('/(^|\])[^\[]*['.$possibleDateFormatCharacters.']/i',$pFormatCode)) {
return true;
}
return false; // no date..
}


Sorry - it's a bit complicated, but I hope this info helps someone who is looking to solve this issue - seems to be working for me :)

And maybe the author is interested in updating his class - if you (mr. author) have questions, just let me know :)

I also appreciate this class. Thank you very much!


There is not only a problem with date cells, but also with Excel time values.
For examle "13:37:00" turns into "0.56736111111111".
There might be an option for handling such formats.

Another idea:
it would be great if it's possible to set (incoming and) outgoing encoding (e.g. utf-8).

Additonal to this solution of omar marod for date fields:

$value = date('Y-m-d', mktime(0,0,0,1,$value-1,1900));

this is how you can convert time fields:

$value = date('H:i:s', round($value*86400)-3600);

I'm not really sure why I have to subtract 1 hour (3600 seconds), but it works fine for me.

Example:
Excel time "13:37:00" by default turns into float "0.56736111111111".

$value=0.56736111111111;
echo date('H:i:s', round($value*86400)-3600); //output="13:37:00"

@class author:

I added some (small and easy) functionalities. If you are interested in getting my code, just let me know.

Additonal attributes:
protected $file;
protected $use_headlines=TRUE;
protected $active_worksheet=1;

protected $headlines=array();
protected $data=array();


Now I can setup file and active worksheet once, and then work with it without these parameters

File-Setup:
public function setFile($filename){
$filename=(string) $filename;
if(strlen($filename)>0 && file_exists($filename)){
$this->file=$filename;
$this->_unzip();
$this->_parse();
return TRUE;
}
return FALSE;
}


Set active worksheet:
public function setWorksheet($worksheet_id=1, $use_headlines=TRUE){
$worksheet_id=intval($worksheet_id);
if(isset($this->sheets[$worksheet_id])){
$this->active_worksheet=$worksheet_id;
$this->use_headlines=$use_headlines;

$this->setData();
return TRUE;
}
return FALSE;
}

The idea of "use_headlines" is, that I can now split headlines and data.

I renamed your "getData" method into "setData", which doesn't return anything, but is setting attributes $this->headlines and $this->data.
(If $use_headlines==TRUE it just puts the first row to the headlines-array).

Because of my problems with dates and times, I added the following method:

public function getPreparedData(array $date_columns=array(), array $time_columns=array(), $trim_vals=TRUE){
$prepared=array();
foreach($this->data AS $row=>$v){
foreach($v AS $column=>$val){
if($trim_vals)$val=trim($val);

if(in_array($column, $date_columns)){
$val = date('Y-m-d', mktime(0,0,0,1,$val-1,1900));
}
elseif(in_array($column, $time_columns)){
$val = date('H:i:s', round($val*86400)-3600);
}
$prepared[$row][$column]=$val;
}
}
return $prepared;
}

I'm still not really happy with that - it's not flexible - but at the moment it works fine for me.

As seen in another post here, I also adopted the idea of getting the worksheet names as follows:

public function getWorksheetNames(){
$worksheets = array();
$xmlWorkBook = simplexml_load_string( $this->getEntryData("xl/workbook.xml") );
$i=1;
foreach ($xmlWorkBook->sheets->sheet as $sheetName) {
$worksheets[$i] = (string)$sheetName['name'][0];
$i++;
}
return $worksheets;
}

It returns something like array(1=>'Table1'), so I can use the array keys to set the active worksheet.

It's a issue related to time-zone,see for no time available(null value,for that it must be 00:00) ,which value display,for me it was 5:30 ,i HAVE SUBSTRACTED seconds accordingly,in that way ,u can solved ur probem.

heyy Omar, if you have the working code for the same problem, can you send it to [email protected]. Thanks in advance

Abhishek Shah, try gmdate

-- AND TO ALL -- see 0.6.3

Thank you for working on the auto date conversion.


I tried the new version and the examples now no longer work.

For the first sample I get output:

:$xlsx->rows()

$xlsx->rowsEx()

The remainder also result in similar output but more extensive.

Yes it't working use following formula
gmdate('H:i', round($value*86400))