使用phpExcel获取日期时间(Get datetime with phpExcel)

我的列的格式为cell：dd / mm / yyyy hh：mm，在阅读这些工作表时，我需要将此值仅获取日期并保存在mysql中。

默认情况下，我正在阅读日期，并提供漏洞保存，但日期格式很奇怪。

单元格中的列日期： 13/11/2017 00:01

我的代码：

$inputFileType = PHPExcel_IOFactory::identify($inputFileName); $objReader = PHPExcel_IOFactory::createReader($inputFileType); $objReader->setReadDataOnly(true); $objPHPExcel = $objReader->load($inputFileName); //Get worksheet and built array with first row as header $objWorksheet = $objPHPExcel->getActiveSheet(); //excel with first row header, use header as key if($header){ $highestRow = $objWorksheet->getHighestRow(); $highestColumn = $objWorksheet->getHighestColumn(); $headingsArray = $objWorksheet->rangeToArray('A1:'.$highestColumn.'1',null, true, true, true); $headingsArray = $headingsArray[1]; $r = -1; $namedDataArray = array(); for ($row = 2; $row <= $highestRow; ++$row) { $dataRow = $objWorksheet->rangeToArray('A'.$row.':'.$highestColumn.$row,null, true, true, true); if ((isset($dataRow[$row]['A'])) && ($dataRow[$row]['A'] > '')) { ++$r; foreach($headingsArray as $columnKey => $columnHeading) { $namedDataArray[$r][$columnHeading] = $dataRow[$row][$columnKey]; } } } } else{ //excel sheet with no header $namedDataArray = $objWorksheet->toArray(null,true,true,true); }

当直接读取值是给回声： 43052.000717593

我的代码只是以我需要的格式获取日期并保存

$data_mailing_file = explode(" ", $value['field_date']); $dataP = explode('/', $data_mailing_file[0]); $data_save = $dataP[2].'-'.$dataP[1].'-'.$dataP[0];

My column has the format of cell: dd / mm / yyyy hh: mm, when reading these worksheets, I need to get this value only the date and save in mysql.

By default, I'm reading the date, and giving exploits to save it, but the date comes in a weird format.

Column date in cell: 13/11/2017 00:01

My code:

When reading the value directly is to give an echo: 43052.000717593

My code to just get the date in the format I need and save

$data_mailing_file = explode(" ", $value['field_date']); $dataP = explode('/', $data_mailing_file[0]); $data_save = $dataP[2].'-'.$dataP[1].'-'.$dataP[0];

最满意答案

43052.000717593是MS Excel序列化的时间戳值，自1900年1月1日以来的天数（或1904年1月1日（如果电子表格使用Mac日历））的天数。

MS Excel使用格式掩码格式化该值，您说这是dd/mm/yyyy hh:mm并将该值显示为13/11/2017 00:01 ，这正是我对该格式掩码的期望。

如果你想以不同的方式格式化日期，那么你可以在调用rangeToArray()之前更改格式掩码。 yyyy-mm-dd的Excel格式掩码只会以适当的格式给出日期。

或者获取序列化的时间戳值，然后使用ExcelToPHP()或ExcelToPHPObject()方法PHPExcel_Shared_Date给出unix时间戳或PHP DateTime对象，然后可以使用PHP的date()函数或DateTime格式化对象的format()方法。

The 43052.000717593 is an MS Excel serialized timestamp value, a count of the number of days since 1st January 1900 (or 1st January 1904 if the spreadsheet is using the Mac calendar).

MS Excel formats that value using the format mask, which you say is dd/mm/yyyy hh:mm and display that value as 13/11/2017 00:01, which is exactly what I'd expect from that format mask.

If you want to format the date differently; then you could change the format mask before calling rangeToArray(). An Excel format mask of yyyy-mm-dd will give you the date only in an appropriate format.

Alternatively get that serialized timestamp value, and then use the ExcelToPHP() or ExcelToPHPObject() methods of PHPExcel_Shared_Date to give a unix timestamp or a PHP DateTime object respectively, that you can then format however you want using PHP's date() function or the DateTime object's format() method.

更多推荐