使用Spreadsheet_Excel_Reader解析xls日期时多一天的问题咨询

我有一个包含日程的.xls文件，其中一列日期格式为02.10.2018（d.m.Y）。使用Spreadsheet_Excel_Reader解析该文件时，该列日期返回值比实际多1天：原日期为02.10.2018，解析后输出为03.10.2018。但如果在日期值间使用逗号、反斜杠等分隔符，解析得到的日期则为正确值。

附代码：

function exceltohtml($file = NULL) { 
    $data = new Spreadsheet_Excel_Reader(); 
    $data->setOutputEncoding('utf-8'); 
    $data->setUTFEncoder('mb'); 
    if (!file_exists($_SERVER['DOCUMENT_ROOT'] . $file)) { 
        echo 'Необходимо залить файл настроек'; 
        die(); 
    } 
    // 2. Проверка эксель пустой/не пустой 
    $data->read($_SERVER['DOCUMENT_ROOT'] . $file); 
    if (empty($data->sheets[0]['cells']) || !count($data->sheets[0]['cells'])) { 
        echo 'Эксель файл некорректный или пустой'; 
        die(); 
    } else { 
        $rows = $data->sheets[0]['cells']; 
        return $rows; 
    } 
}

截图：

为什么会出现这个问题？ #

这个坑其实是老旧的Spreadsheet_Excel_Reader库+Excel历史bug共同导致的：

• 当日期用.分隔时，Excel会自动把这列识别为日期数值格式——Excel内部把日期存储为从1900年1月0日开始计数的浮点数，但它有个历史bug：错误地将1900年标记为闰年，导致1900年3月1日之后的日期数值比实际多1天。
• 而Spreadsheet_Excel_Reader这个库年代久远，没有正确处理这个Excel的闰年bug，在把Excel的日期数值转换为PHP日期时，直接用了原始偏移值，就导致日期多算了1天。
• 当你用逗号、反斜杠等分隔符时，Excel会把日期当成纯文本存储，库直接读取字符串，自然不会触发这个数值转换的错误。

无需特殊技巧的解决方法 #

方法1：修复Spreadsheet_Excel_Reader的日期转换逻辑（最快） #

找到库的核心文件（一般是reader.php），定位到ExcelToPHP函数——这个函数负责把Excel日期数值转成PHP时间戳。原函数大概是这样：

function ExcelToPHP($dateValue = 0) {
    if ($dateValue > 0) {
        $utcDays = $dateValue - 25569;
        $returnValue = round($utcDays * 86400);
        if (($returnValue <= PHP_INT_MAX) && ($returnValue >= -PHP_INT_MAX)) {
            $returnValue = (integer) $returnValue;
        }
        return $returnValue;
    } else {
        return false;
    }
}

我们需要给它加上Excel闰年bug的修正：如果日期数值大于60（对应1900年3月1日），偏移值从25569改成25568。修改后的函数：

function ExcelToPHP($dateValue = 0) {
    if ($dateValue > 0) {
        // 修正Excel 1900年闰年bug
        $offset = $dateValue > 60 ? 25568 : 25569;
        $utcDays = $dateValue - $offset;
        $returnValue = round($utcDays * 86400);
        if (($returnValue <= PHP_INT_MAX) && ($returnValue >= -PHP_INT_MAX)) {
            $returnValue = (integer) $returnValue;
        }
        return $returnValue;
    } else {
        return false;
    }
}

保存后重新解析文件，日期就会正确显示了。

方法2：换成现代的PhpSpreadsheet库（推荐） #

Spreadsheet_Excel_Reader已经多年没有维护，不仅日期处理有bug，还不支持新版Excel格式。推荐换成PhpSpreadsheet（原PHPExcel的官方继任者），它对日期的处理非常精准，而且功能更全面。

用PhpSpreadsheet重写你的函数：

function exceltohtml($file = NULL) { 
    // 确保已通过Composer安装PhpSpreadsheet，引入自动加载文件
    require 'vendor/autoload.php';

    $filePath = $_SERVER['DOCUMENT_ROOT'] . $file;
    if (!file_exists($filePath)) { 
        echo 'Необходимо залить файл настроек'; 
        die(); 
    } 

    // 加载XLS文件
    $reader = new \PhpOffice\PhpSpreadsheet\Reader\Xls();
    $spreadsheet = $reader->load($filePath);
    $worksheet = $spreadsheet->getActiveSheet();
    
    $rows = [];
    // 遍历所有行
    foreach ($worksheet->getRowIterator() as $row) {
        $cellIterator = $row->getCellIterator();
        $cellIterator->setIterateOnlyExistingCells(false); // 遍历所有单元格（包括空的）
        $rowData = [];
        foreach ($cellIterator as $cell) {
            // 自动识别日期格式并输出正确的格式化值
            if ($cell->getDataType() === \PhpOffice\PhpSpreadsheet\Cell\Cell::TYPE_DATE) {
                $rowData[] = $cell->getFormattedValue();
            } else {
                $rowData[] = $cell->getValue();
            }
        }
        $rows[] = $rowData;
    }

    if (empty($rows)) {
        echo 'Эксель файл некорректный или пустой'; 
        die(); 
    }

    return $rows;
}

这个方法不需要修改旧库代码，一劳永逸解决日期问题，还能兼容更多Excel特性。

方法3：临时手动修正日期（应急方案） #

如果暂时没法改库或换库，可以在解析后手动给日期减1天：

// 假设$rows是你从原函数获取的解析结果
foreach ($rows as &$row) {
    foreach ($row as &$cell) {
        // 匹配d.m.Y格式的日期
        if (preg_match('/^\d{2}\.\d{2}\.\d{4}$/', $cell)) {
            $date = DateTime::createFromFormat('d.m.Y', $cell);
            $date->modify('-1 day');
            $cell = $date->format('d.m.Y');
        }
    }
}

不过这只是临时 workaround，只适用于特定格式的日期，不如前两种方法可靠。

内容的提问来源于stack exchange，提问作者Anton Trofimov