How to distinguish date format from integer

Topics: Developer Forum
Jan 28, 2009 at 8:34 PM
When I try and get the format of an excel 2007 cell, I can't tell the difference between a date and just another number.  Same with time and a float.  For example, the cell contains 36526, which if it is in date format, that is equal to 01/01/2000, but it also could just be the integer 36526.  I am writing a generic reader that doesn't know if the cell should be a date or integer or string or whatever.  Is there some method I can call on the cell to get what type it is?

When I call $cell->getDataType(), I get 'n' back, but that just says it is a number, it doesn't tell me if I need to convert the 36526 to a date or if it should just be a number.

Is this possible or do I *have* to know that the cell is suppose to be a date, which means I can't write a completely generic program.

Thanks,
Shawn
Developer
Jan 28, 2009 at 9:05 PM
You are correct. There is no dedicated date or time type in Excel. They are just numbers in Excel.

What you need to look at is the number format.

$formatCode = $worksheet->getStyle('A1')->getNumberFormat()->getFormatCode();

For example, it may give you this:

"[$-F400]h:mm:ss\ AM/PM"

Then you have a date. There is a function which may be useful:

$bool = PHPExcel_Shared_Date::isDateTimeFormatCode($formatCode); // true if $formatCode is date/time-like

I'm not sure whether this method is bullet proof for filtering dates and times, but you may give it a try. If Mark drop by this thread, he may have something to add.

Note: Please use latest source code, as date/time reading behavior of PHPExcel has changed since 1.6.5.
http://www.codeplex.com/PHPExcel/SourceControl/ListDownloadableCommits.aspx



Jan 28, 2009 at 9:12 PM
I tried looking at the getFormatCode() before and it always returns 'General', so that didn't help.  Which is odd, cause I definitely was expecting a different format, like you said.  Hopefully Mark will have a solution.  Thanks!
Developer
Jan 28, 2009 at 9:19 PM
Hmm... This is strange. Just checking, were you using $reader->setReadDataOnly(true) ? That would not reveal formatting.

Jan 28, 2009 at 9:29 PM
Yes, I am. I commented that line out and I still don't get any
formatting. Here is the code (if there is a better way to do the
columnLetterMap please let me know). I was hoping there would be a
function like $worksheet->getHighestColumnByNumber().

$columnLetterMap = array(
'A' => 0,
'B' => 1,
'C' => 2,
'D' => 3,
'E' => 4,
'F' => 5,
'G' => 6,
'H' => 7,
'I' => 8,
'J' => 9,
'K' => 10,
'L' => 11,
'M' => 12
);

$reader = new PHPExcel_Reader_Excel2007();
//$reader->setReadDataOnly(true);
$excel = $reader->load( $file );

$worksheet = $excel->getSheet(0);

$numRows = $worksheet->getHighestRow();
$numCols = $columnLetterMap[$worksheet->getHighestColumn()];

for( $row = 1; $row <= $numRows; $row++ ) {
for( $col = 0; $col <= $numCols; $col++ ) {
$cell = $worksheet->getCellByColumnAndRow( $col, $row );
$style = $worksheet->getStyleByColumnAndRow( $col, $row );

$numberFormat = $style->getNumberFormat()->getFormatCode();
print "format: $numberFormat
";
}
}



koyama wrote:
>
>
> From: koyama
>
> Hmm... This is strange. Just checking, were you using
> |$reader->setReadDataOnly(true)| ? That would not reveal formatting.
>
> Read the full discussion online
> <http://www.codeplex.com/PHPExcel/Thread/View.aspx?ThreadId=45529&ANCHOR#Post151933>.
>
> To add a post to this discussion, reply to this email
> ([email removed]
> <mailto:[email removed]?subject=[PHPExcel:45529]>)
>
> To start a new discussion for this project, email
> [email removed] <mailto:[email removed]>
>
> You are receiving this email because you subscribed to this discussion
> on CodePlex. You can unsubscribe
> <http://www.codeplex.com/site/discussions/thread/unsubscribe/45529> on
> codePlex.com.
>
> Please note: Images and attachments will be removed from emails. Any
> posts to this discussion will also be available online at codeplex.com
>
Developer
Jan 28, 2009 at 10:46 PM
You are asking for alternative to $columnLetterMap. Try like this:

PHPExcel_Cell::columnIndexFromString('A'); // = 1

I don't notice any errors. If you don't find the problem, you can try to send me the sheet (erik at phpexcel dot net) ?

Coordinator
Jan 28, 2009 at 10:52 PM
Edited Jan 28, 2009 at 11:25 PM
If you open the workbook in Excel, does the cell definitely show a formatted date value ("01/01/2000" or similar) rather than a numeric (36525)?

Are you running the 1.6.5 production release, or the latest SVN code? We made some changes around 14th/15th January to ensure that dates were correctly identified in the Excel readers


$numCols = PHPExcel_Cell::columnIndexFromString($worksheet->getHighestColumn());
will return the number of columns in the worksheet as a number rather than a column reference: 1 rather than A, 2 rather than B, etc

(Looks like Erik beat me to it)
Developer
Jan 29, 2009 at 12:09 AM
It look like there indeed is something to the problem. It does say 'General' when one asks for number format.

Mark, I have forwarded you the Excel file as received by sgrobins. Could this have something to do with built-in number formats?

Coordinator
Jan 29, 2009 at 9:12 AM
Edited Jan 29, 2009 at 9:16 AM
Looking at sheet1 in the xml, cells F2 and G2 are both being treated as $cellDataType of 's' with c['s'] unset, which means it's being treated as a string when read into PHPExcel, rather than as a numeric. No formatting test gets done to see if it is a date
Developer
Jan 30, 2009 at 2:27 AM
Yeah, it looks like the problem is that the style is not written explicitly in the workbook, but is a built-in style.

We have discussed this before, and built-in styles were implemented for Excel 2007 reader, except for dates.
http://www.codeplex.com/PHPExcel/WorkItem/View.aspx?WorkItemId=7313

I have posted a new work item to discuss what we can do about dates:
http://www.codeplex.com/PHPExcel/WorkItem/View.aspx?WorkItemId=9102