PHPExcel 1.7.8导出大数据时合计行公式及样式不显示问题

问题回顾 #

你在PHP 5.6环境下用PHPExcel 1.7.8导出数据库数据到Excel时遇到了一个奇怪的问题：当结果集行数少于20000行时，带SUM公式的总计行（包括公式计算、数字格式、灰色背景）都能正常显示；但行数超过20000行后，合计单元格的公式和背景色直接消失了。如果把公式里的=去掉改成纯文本，或者把结果集砍到15000行左右，合计行又能正常工作。

你的总计行核心代码是这样的：

// 设置总计文本
$objPHPExcel->getActiveSheet()->setCellValue('B' . $i, Yii::t('asientos_excel_diario', 'TOTALES'));

// M列求和公式
$formula = '=SUM(' . "M{$pos_ini_suma_totales}:M" . $ultima_fila . ')';
$objPHPExcel->getActiveSheet()->setCellValue('M' . $i, $formula);
$objPHPExcel->getActiveSheet()->getStyle('M' . $i)->getNumberFormat()->setFormatCode('#,##0.00');

// N列求和公式
$formula = '=SUM(' . "N{$pos_ini_suma_totales}:N" . $ultima_fila . ')';
$objPHPExcel->getActiveSheet()->setCellValue('N' . $i, $formula);
$objPHPExcel->getActiveSheet()->getStyle('N' . $i)->getNumberFormat()->setFormatCode('#,##0.00');

// 设置背景色
$objPHPExcel->getActiveSheet()->getStyle('A5:N5')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFA0A0A0');
$objPHPExcel->getActiveSheet()->getStyle('A' . $i . ':' . 'N' . $i)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFA0A0A0');

文件写入用的是Excel5格式：

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');

可能的原因 #

1. Excel 5（.xls）格式的PHPExcel兼容性Bug：虽然Excel 5本身支持最多65536行，但PHPExcel 1.7.8这个老版本（2013年左右发布）对大数据量的公式和样式处理存在缺陷，当行数达到20000这个阈值时，公式解析或样式渲染流程出现异常，导致后续的设置没被正确写入文件。
2. 内存不足导致代码中断：处理30000行数据时，PHP内存占用过高，可能导致合计行的公式和样式代码没有完全执行，但你提到移除公式后能正常显示，所以这个可能性相对低一些。

解决方案 #

1. 切换到Excel 2007（.xlsx）格式（最推荐） #

Excel 2007格式支持1048576行，且PHPExcel对其的大数据量处理更稳定。只需修改写入代码：

// 创建Excel2007格式的Writer
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

// 设置浏览器响应头（如果是直接输出到浏览器的话）
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="tu_archivo.xlsx"');
header('Cache-Control: max-age=0');

$objWriter->save('php://output');

这个方法应该能直接解决问题，因为.xlsx格式的处理逻辑在PHPExcel中更完善，很少出现大数据量下的公式/样式丢失问题。

2. 升级PHPExcel或更换为PhpSpreadsheet #

PHPExcel已经停止维护，官方推荐用PhpSpreadsheet（它是PHPExcel的继任者），修复了大量旧版本的Bug，对大数据量的支持更好。如果暂时无法切换到PhpSpreadsheet，也可以尝试升级到PHPExcel的最后一个稳定版本（1.8.1），看看是否修复了这个行数阈值的问题。

3. 手动计算合计值，绕过Excel公式 #

如果暂时无法更换格式或升级库，可以在PHP代码里先计算好合计值，直接写入单元格，不用依赖Excel的SUM公式：

// 假设$resultSet是你的数据库结果集，先计算M列总和
$sumM = 0;
foreach ($resultSet as $row) {
    $sumM += (float)$row['nombre_columna_m']; // 根据实际字段名修改
}

// 直接设置合计值，而非公式
$objPHPExcel->getActiveSheet()->setCellValue('M' . $i, $sumM);

// 同样处理N列
$sumN = 0;
foreach ($resultSet as $row) {
    $sumN += (float)$row['nombre_columna_n'];
}
$objPHPExcel->getActiveSheet()->setCellValue('N' . $i, $sumN);

// 保留数字格式和背景色设置，和之前一样
$objPHPExcel->getActiveSheet()->getStyle('M' . $i)->getNumberFormat()->setFormatCode('#,##0.00');
$objPHPExcel->getActiveSheet()->getStyle('N' . $i)->getNumberFormat()->setFormatCode('#,##0.00');
$objPHPExcel->getActiveSheet()->getStyle('A' . $i . ':N' . $i)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID)->getStartColor()->setARGB('FFA0A0A0');

这样绕开了PHPExcel对大数据量公式的处理问题，同时保证合计值和样式正常显示。

4. 调整PHP内存限制（辅助排查） #

虽然可能性低，但可以尝试提高PHP内存限制，确保脚本不会因为内存不足中断：

// 在脚本最开头设置
ini_set('memory_limit', '512M'); // 根据实际情况调整，比如1G

调试建议 #

• 写一个最小化的测试脚本：生成30000行随机数据+合计行，复现问题，确认是格式或版本的问题；
• 导出后打开Excel时，注意是否有文件损坏的提示，这能帮助定位是格式解析层面的问题。

内容的提问来源于stack exchange，提问作者Alexandru Trandafir Catalin