php 如何使用 PHPExcel 库从 excel 中获取日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19674633/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to get date from excel using PHPExcel library
提问by user2934950
I am trying to get Date from excel using PHPExcel. But I am not getting date, I am getting string value which is not seconds from 1970 .
我正在尝试使用 PHPExcel 从 excel 获取日期。但我没有得到日期，我得到的字符串值不是 1970 的秒数。
Code I have tried is
我试过的代码是
$InvDate=trim($excel->getActiveSheet()->getCell('B' . $i)->getValue());
回答by Sergey
Try use
尝试使用
$cell = $excel->getActiveSheet()->getCell('B' . $i);
$InvDate= $cell->getValue();
if(PHPExcel_Shared_Date::isDateTime($cell)) {
$InvDate = date($format, PHPExcel_Shared_Date::ExcelToPHP($InvDate));
}
P.S.
聚苯乙烯
@DiegoDD: Should mention that $format is the desired format for the date. e.g.:
@DiegoDD：应该提到 $format 是日期所需的格式。例如：
$InvDate = date($format = "Y-m-d", PHPExcel_Shared_Date::ExcelToPHP($InvDate));
P.P.S. 2019 Look at answer @gabriel-lupu, with new version of PhpOffice https://stackoverflow.com/a/45070205/426533
PPS 2019 看答案@gabriel-lupu，新版本的PhpOffice https://stackoverflow.com/a/45070205/426533
回答by Mark Baker
For a date, getValue() should return a float, which is the Excel serialized timestamp value for that date/time... I suspect it's your trim() that's casting it to string. The actual value is the number of days since 1/1/1900 (or 1/1/1904 depending on the calendar that the spreadsheet is using).
对于日期，getValue() 应该返回一个浮点数，这是该日期/时间的 Excel 序列化时间戳值...我怀疑是您的 trim() 将其转换为字符串。实际值是自 1/1/1900（或 1/1/1904，具体取决于电子表格使用的日历）以来的天数。
Calling getFormattedValue() or getCalculatedValue() instead of getValue() should return the date formatted as a human-readable string according to the numberformatmask of the cell.
调用 getFormattedValue() 或 getCalculatedValue() 而不是 getValue() 应根据单元格的 numberformatmask 返回格式化为人类可读字符串的日期。
Alternatively, Sergey's solution tests if the cell has a date/time numberformatmask and calls the appropriate helper method to convert that Excel serialized timestamp to a unix timestamp, then uses the normal PHP date function to format it as human readable according to the value of $format. There's a similar helper method PHPExcel_Shared_Date::ExcelToPHPObject() that will convert an Excel serialized timestamp to a PHP DateTime object
或者，Sergey 的解决方案测试单元格是否具有日期/时间数字格式掩码并调用适当的辅助方法将该 Excel 序列化时间戳转换为 unix 时间戳，然后使用普通的 PHP 日期函数根据 $ 的值将其格式化为人类可读的格式。有一个类似的辅助方法 PHPExcel_Shared_Date::ExcelToPHPObject() 可以将 Excel 序列化时间戳转换为 PHP DateTime 对象
回答by Gabriel Lupu
In the new version of the library, PhpOffice, the function that handles this is excelToDateTimeObjectso the new code format should be:
在新版本的库PhpOffice 中，处理这个的函数是excelToDateTimeObject所以新的代码格式应该是：
$cell = $excel->getActiveSheet()->getCell('B' . $i);
$InvDate= $cell->getValue();
if (PhpOffice\PhpSpreadsheet\Shared\Date::isDateTime($cell)) {
$InvDate = PhpOffice\PhpSpreadsheet\Shared\Date::excelToDateTimeObject($InvDate);
}
回答by shasi kanth
You can get the cell values as string (also the date values) this way:
您可以通过这种方式将单元格值作为字符串（也是日期值）获取：
$sheet = $objPHPExcel->getActiveSheet();
$lastRow = $sheet->getHighestRow();
$lastColumn = $sheet->getHighestColumn();
$rows = $sheet->rangetoArray('A2:'.$lastColumn . $lastRow, NULL, True, True, False);
foreach ($rows as $row => $cols) {
foreach($cols as $col => $cell) {
echo trim($cell).'<br>'; // Gives the value as string
}
}
回答by Omar
$cell = $excel->getActiveSheet()->getCell('B' . $i);
$InvDate= $cell->getValue();
$InvDate= PHPExcel_Shared_Date::ExcelToPHPObject($InvDate)->format('Y-m-d H:i:s');
Try this
尝试这个
