PHPExcel toArray从XLSX格式更改日期和时间列 [英] PHPExcel toArray is changing date and time columns from XLSX format

问题描述

我正在使用此处找到的excelToArray函数: https://gist.github.com/calvinchoy/5821235

I am using the excelToArray function found here: https://gist.github.com/calvinchoy/5821235

function excelToArray($filePath, $header = true) {
    require_once("./PHPExcel/Classes/PHPExcel.php"));
    //Create excel reader after determining the file type
    $inputFileName = $filePath;
    /**  Identify the type of $inputFileName  **/
    $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
    /**  Create a new Reader of the type that has been identified  **/
    $objReader = PHPExcel_IOFactory::createReader($inputFileType);
    /** Set read type to read cell data onl **/
    $objReader->setReadDataOnly(true);
    /**  Load $inputFileName to a PHPExcel Object  **/
    $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);
    }
    return $namedDataArray;
}

我有两个版本的电子表格，一个为CSV版本，一个为Excel版本.他们在这里:

I have two versions of a spreadsheet, one in CSV and one in Excel. Here they are:

CSV: https://drive.google.com/open?id=0B2GilRTNrTzKd3V3aEVET1NqSW8

XLSX: https://drive.google.com/open?id=0B2GilRTNrTzKdzJNZnh0cmhhpa1E

当我使用此功能上传 CSV 并获得var_dump结果时

When I upload the CSV using this function and var_dump the results I get

array (size=58)
  0 => 
    array (size=4)
      'PD' => string '11/10/2016' (length=10)
      'Pt' => string '9:12' (length=4)
      'fd' => string '11/10/2017' (length=10)
      'ft' => string '9:12' (length=4)
  1 => 
    array (size=4)...

但是当我上传 XLSX 时，我得到了:

But when I upload the XLSX I get:

array (size=58)
  0 => 
    array (size=5)
      'PD' => float 42684
      'Pt' => float 0.38333333333333
      'fd' => float 43049
      'ft' => float 0.38333333333333
      '' => null
  1 => 
    array (size=5)

请注意，PD从2016年11月10日变为42684，铂从9:12变为0.38333 ...

Notice that the PD goes from 11/10/2016 to 42684, and Pt from 9:12 to 0.38333...

是什么原因导致XLSX文件在显示时无法读取?

我已经阅读了其他堆栈问题，但是

I have already read other stack questions, but I appear to be passing toArray the correct values. Not sure what I' missing...

推荐答案

MS Excel将日期存储为序列化的时间戳，即自1900年1月1日(或1904年1月1日)以来的天数，具体取决于它是使用Windows还是Windows. Mac日历). PHPExcel也会这样做，因此所有日期/时间都将按照MS Excel与之完全相同的方式加载并存储在电子表格对象中.

MS Excel stores dates as a serialized timestamp, the number of days since 1t January 1900 (or 1st January 1904, depending on whether it is using the Windows or the Mac calendar). PHPExcel does likewise, so all dates/times are loaded to store in the spreadsheet object in exactly the way that MS Excel works with them.

因此，当您加载带有人类格式日期的文件时，它会将其读取为MS Excel序列化的时间戳.通常，它还会存储数字格式掩码，告诉PHPExcel该单元格包含一个应设置为日期格式的时间戳记值，但是您是在告诉PHPExcel的加载程序不要通过使用$objReader->setReadDataOnly(true);采取此附加操作，这意味着仅存储数据，而不是格式信息. 因为PHPExcel没有附加的格式信息，所以它不知道该单元格包含应作为日期支付的内容，因此它只能支付序列化的tiemstamp，它实际上只是一个浮点数.

So when you load a file with a human format date, it reads that as an MS Excel serialized timestamp. Ordinarily, it would also store the number format mask telling PHPExcel that this cell contains a timestamp value that shoud be formatted as a date, but you're telling PHPExcel's loader not to take this additional action by using $objReader->setReadDataOnly(true); which means store only the data, and not the formatting information. Because PHPExcel desn't hve this additional formatting information, it cannot know that the cell contains something that should be dispayed as a date, so it can only dispay the serialized tiemstamp, which is really just a float.

换句话说，如果您希望将日期视为日期，或者除非您想自己处理所有日期，否则不要执行$objReader->setReadDataOnly(true);

In other words, don't do $objReader->setReadDataOnly(true); if you want dates to be treated as dates, or unless you want to do all the date handling yourself

这篇关于PHPExcel toArray从XLSX格式更改日期和时间列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！