PHPEXCEL获取格式化的日期，如在excel文件中可见 [英] PHPEXCEL get formatted date as is visible in excel file
问题描述
我正在尝试使用PhpExcel lib导入excel文件
对于所有其他字段，getValue()函数起作用，但是当它遇到ms-excel2013中设置的格式为日期的字段时
exel文件中的日期字段采用d-m-Y格式，例如16-11-2014
但是，当我尝试导入它的值时，getValue()返回11-16-14，然后将其传递给strtotime进一步returns false依次导致date('Y-m-d',strtotime($date))返回1970-01-01.
我搜索了整个Web和stackoverflow，但是没有解决方案解决了我的问题.
在excel文件中，我看到日期为16-11-2014，并希望将其原样导入.
这是代码
protected function importExcel($filePath) {
$excelData = array();
if ($filePath) {
$objPHPExcel = PHPExcel_IOFactory::load($filePath);
$objPHPExcel->setReadDataOnly(true);
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
$worksheetTitle = $worksheet->getTitle();
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$nrColumns = ord($highestColumn) - 64;
$data = array();
for ($row = 1; $row <= $highestRow; ++$row) {
$values = array();
for ($col = 0; $col < $highestColumnIndex; ++$col) {
$cell = $worksheet->getCellByColumnAndRow($col, $row);
if (PHPExcel_Shared_Date::isDateTime($cell))
throw new Exception("is date time"); // just a check
$val = $cell->getValue();
if (isset($val) && $val)
$data[$row][$col] = $val;
}
}
$excelData[$worksheetTitle] = $data;
}
return $excelData;
}
return FALSE;
}
我已经找到解决方案:
文件 PHPExcel/Style/NumberFormat.php 中的方法 _formatAsDate
如果日期类似于16/11/2014，则当传递给strtotime时将导致false，因为日期应该被strtotime设置为m/d/Y格式.因此，如果将格式更改为m/d/Y(如果是d/m/Y)，则解决方案将始终是正确的.
更早:
- 2014/11/16 == 1970-01-01(行:1)
- 2014/11/16 == 1970-01-01(行:2)
- 2014年12月23日== 1970-01-01(行:3).
现在:
- 11/16/2014 == 2014-11-16(行:1)
- 11/16/2014 == 2014-11-16(行:2)
- 2014年12月23日== 2014-12-23(行:3)
代码仍然相同，导入文件也很简单:
protected function importExcel($filePath) {
$excelData = array();
if ($filePath) {
$objPHPExcel = PHPExcel_IOFactory::load($filePath);
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
$worksheetTitle = $worksheet->getTitle();
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$nrColumns = ord($highestColumn) - 64;
$data = array();
for ($row = 1; $row <= $highestRow; ++$row) {
$values = array();
for ($col = 0; $col < $highestColumnIndex; ++$col) {
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$val = $cell->getValue();
if (isset($val) && $val)
$data[$row][$col] = $val;
}
}
$excelData[$worksheetTitle] = $data;
}
return $excelData;
}
return FALSE;
}
I am trying to import a excel file using PhpExcel lib
for all other fields the getValue() function works but when it encounters a field with format date as set in ms-excel2013
the date field in exel file is in format d-m-Y like 16-11-2014
but when I try to import it's value the getValue() returns 11-16-14 which when passed to strtotime further returns false in turn causing the date('Y-m-d',strtotime($date)) to return 1970-01-01.
I searched whole of web and stackoverflow but none solution fixed my problem.
In excel file i see the date as 16-11-2014 and want it to be imported as is.
Here's the code
protected function importExcel($filePath) {
$excelData = array();
if ($filePath) {
$objPHPExcel = PHPExcel_IOFactory::load($filePath);
$objPHPExcel->setReadDataOnly(true);
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
$worksheetTitle = $worksheet->getTitle();
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$nrColumns = ord($highestColumn) - 64;
$data = array();
for ($row = 1; $row <= $highestRow; ++$row) {
$values = array();
for ($col = 0; $col < $highestColumnIndex; ++$col) {
$cell = $worksheet->getCellByColumnAndRow($col, $row);
if (PHPExcel_Shared_Date::isDateTime($cell))
throw new Exception("is date time"); // just a check
$val = $cell->getValue();
if (isset($val) && $val)
$data[$row][$col] = $val;
}
}
$excelData[$worksheetTitle] = $data;
}
return $excelData;
}
return FALSE;
}
I have found out the solution:
Method _formatAsDate in the file PHPExcel/Style/NumberFormat.php
if the date is like 16/11/2014, when passed to strtotime will result in false as the date is supposed to be in format m/d/Y by strtotime. So if you change the format to m/d/Y if it's d/m/Y then the solution will always be correct.
Earlier:
- 16/11/2014==1970-01-01 (Row: 1)
- 16/11/2014==1970-01-01 (Row: 2)
- 23/12/2014==1970-01-01 (Row: 3).
Now:
- 11/16/2014==2014-11-16 (Row: 1)
- 11/16/2014==2014-11-16 (Row: 2)
- 12/23/2014==2014-12-23 (Row: 3)
Code is still the same and simple to import the file:
protected function importExcel($filePath) {
$excelData = array();
if ($filePath) {
$objPHPExcel = PHPExcel_IOFactory::load($filePath);
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
$worksheetTitle = $worksheet->getTitle();
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$nrColumns = ord($highestColumn) - 64;
$data = array();
for ($row = 1; $row <= $highestRow; ++$row) {
$values = array();
for ($col = 0; $col < $highestColumnIndex; ++$col) {
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$val = $cell->getValue();
if (isset($val) && $val)
$data[$row][$col] = $val;
}
}
$excelData[$worksheetTitle] = $data;
}
return $excelData;
}
return FALSE;
}
这篇关于PHPEXCEL获取格式化的日期，如在excel文件中可见的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
