PHPExcel_Shared_Date::isDateTime problem

Topics: Developer Forum
Oct 27, 2013 at 10:20 AM
I need to import an excel file into database. Issue is with the date. The date can be in any format in excel file. I need to convert it to timestamp to store in database. I am using PHPExcel_Shared_Date::isDateTime to check whether cell is having a date value or not. If yes then I use PHPExcel_Shared_Date::ExcelToPHP to convert the date value to time. My problem is that PHPExcel_Shared_Date::isDateTime is recognising non date cells as date as well. Below is my code.

$objReader = PHPExcel_IOFactory::createReaderForFile($this->getTempFileName());
$objReader->setReadDataOnly(false);
$objPHPExcel = $objReader->load($this->getTempFileName());
$objPHPExcel->setActiveSheetIndex(0);
$worksheet = $objPHPExcel->getActiveSheet();

$highestRow = $worksheet->getHighestRow();
$highestColumn = $worksheet->getHighestColumn();
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);

for ($row = 1; $row <= $highestRow; ++$row) {
$cellRow = array();
$formatedCellRow = array();
for ($col = 0; $col < $highestColumnIndex; $col++) {
    $cell = $worksheet->getCellByColumnAndRow($col, $row);

    $val = $cell->getValue();

    if (PHPExcel_Shared_Date::isDateTime($cell))
        $time = PHPExcel_Shared_Date::ExcelToPHP($val);
    else
        $normal_value = $val;
}
$cellData[] = $cellRow;
}

Help much appreciated.
Coordinator
Oct 27, 2013 at 9:51 PM
Edited Oct 27, 2013 at 9:51 PM
Can you identify the formats that are being incorrectly identified as dates?

The functions tests for the existence of any of the following characters - e, y, m, d, H or s (all case insensitive) - in the numberformat mask, unless they're enclosed in square braces (to prevent values like [red] from being identified as dates, or enclosed in double quotes.
Oct 27, 2013 at 10:38 PM
Thanks for reply.

When I see the formats of the cell in the excel file using $cell->getParent()->getStyle($cell->getCoordinate())->getNumberFormat()->getFormatCode()) I see that there are cells having format as GENERAL and numeric cells having format like #,##0.00. But even they are also considered as dates by PHPExcel_Shared_Date::isDateTime($cell) function.
Coordinator
Oct 28, 2013 at 8:26 PM
The test for General was case-sensitive, so GENERAL wasn't being rejected as a date format, and I've added a trap for scientific format masks that would have been raised as false positives.... changes made to the develop branch on github.

However, I can't replicate a scenario where #,##0.00 will return a false positive as a date
Oct 29, 2013 at 1:36 AM
So should I wait for the changes to be published in next stable branch or is the development branch stable as well?
Coordinator
Oct 29, 2013 at 9:04 AM
On 29/10/2013 00:36, samirhshaikh wrote:

From: samirhshaikh

So should I wait for the changes to be published in next stable branch or is the development branch stable as well?
The develop branch is stable, so you can use that; although you could always replace just the PHPExcel_Shared_Date.php file from github.

-- 
Mark Baker