基于模板的Excel高级导出：借助PHPExcel打造简洁、易维护的数据输出方案

一、前言

       在企业级应用开发中，Excel导出功能几乎是每个后台系统必备的能力。但当我们需要导出的Excel包含以下复杂特征时，传统的逐行逐列构建方式就显得力不从心：

• 多级表头合并单元格

• 固定样式（边框、底色、字体）

• 预定义的公式计算

• 多个Sheet页关联

• 特定格式的业务报表

面对这些需求，基于模板的Excel导出方案能让我们以更优雅的方式解决问题。

二、传统方案的痛点

传统方案通常使用PhpSpreadsheet或PHPExcel，通过代码逐单元格构建：

php

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();
$sheet->setCellValue('A1', '姓名');
$sheet->setCellValue('B1', '年龄');
$sheet->mergeCells('A1:B1');
$sheet->getStyle('A1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setARGB('FFCCCCCC');
// ... 几百行样式代码

这种方式的缺点显而易见：

1. 样式代码冗长：边框、颜色、字体等设置占据大量代码

2. 维护困难：调整样式需要修改代码并重新部署

3. 可读性差：无法直观预览最终效果

4. 开发效率低：调试一个单元格样式往往需要多次导出测试

三、模板方案核心思路

核心思想：将样式设计交给Excel软件，PHP只负责数据填充

text

创建Excel模板（包含样式、公式、合并单元格）
        ↓
  模板文件存储（.xlsx）
        ↓
   PHP读取模板文件
        ↓
   定位占位符并填充数据
        ↓
   保存为新的Excel文件

四、技术选型

推荐使用 PhpSpreadsheet（PHPExcel的官方升级版）：

bash

composer require phpoffice/phpspreadsheet

五、实战案例

5.1 创建模板文件

首先在Excel中设计好模板，使用特殊标记作为占位符：

销售报表模板（sales_template.xlsx）设计：

• 合并单元格、背景色、边框均在模板中预先设置

• 使用 {placeholder} 格式标记数据填充位置

• 公式 =SUM(...) 在填充后自动计算

5.2 核心代码实现

php

<?php

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

class ExcelTemplateExport
{
    private $templatePath;
    private $spreadsheet;
    
    public function __construct($templatePath)
    {
        $this->templatePath = $templatePath;
        $this->loadTemplate();
    }
    
    /**
     * 加载模板文件
     */
    private function loadTemplate()
    {
        if (!file_exists($this->templatePath)) {
            throw new Exception("模板文件不存在: {$this->templatePath}");
        }
        $this->spreadsheet = IOFactory::load($this->templatePath);
    }
    
    /**
     * 填充数据到指定Sheet
     * 
     * @param array $data 数据映射，格式：['{placeholder}' => 'value', ...]
     * @param int $sheetIndex Sheet索引（从0开始）
     */
    public function fillData($data, $sheetIndex = 0)
    {
        $sheet = $this->spreadsheet->getSheet($sheetIndex);
        
        // 遍历所有单元格查找占位符
        foreach ($sheet->getRowIterator() as $row) {
            $cellIterator = $row->getCellIterator();
            $cellIterator->setIterateOnlyExistingCells(false);
            
            foreach ($cellIterator as $cell) {
                $cellValue = $cell->getValue();
                if (is_string($cellValue)) {
                    foreach ($data as $placeholder => $value) {
                        if (strpos($cellValue, $placeholder) !== false) {
                            $newValue = str_replace($placeholder, $value, $cellValue);
                            $cell->setValue($newValue);
                        }
                    }
                }
            }
        }
    }
    
    /**
     * 填充表格行数据（动态扩展）
     * 
     * @param array $rows 二维数组，每行数据
     * @param string $startCell 起始单元格，如 'A5'
     * @param array $mapping 列映射，格式：['列坐标' => '字段名', 'A' => 'product_name']
     */
    public function fillTableData($rows, $startCell, $mapping)
    {
        $sheet = $this->spreadsheet->getActiveSheet();
        
        // 解析起始位置
        preg_match('/([A-Z]+)(\d+)/', $startCell, $matches);
        $startColumn = $matches[1];
        $startRow = (int)$matches[2];
        
        $currentRow = $startRow;
        foreach ($rows as $rowData) {
            foreach ($mapping as $columnLetter => $fieldName) {
                $cellCoordinate = $columnLetter . $currentRow;
                $value = $rowData[$fieldName] ?? '';
                $sheet->setCellValue($cellCoordinate, $value);
            }
            $currentRow++;
        }
        
        // 返回最后一行行号，便于后续填充合计公式
        return $currentRow - 1;
    }
    
    /**
     * 克隆模板行（用于动态添加多行数据）
     * 
     * @param string $templateRowCell 模板行的任意单元格，如 'A5'
     * @param int $copyCount 需要复制的行数
     */
    public function cloneRows($templateRowCell, $copyCount)
    {
        $sheet = $this->spreadsheet->getActiveSheet();
        
        // 获取模板行号
        preg_match('/(\d+)/', $templateRowCell, $matches);
        $templateRowNum = (int)$matches[0];
        
        // 获取模板行的样式
        $templateRow = $sheet->getRowIterator($templateRowNum)->current();
        
        // 计算需要插入的位置
        $insertBeforeRow = $templateRowNum + 1;
        
        for ($i = 0; $i < $copyCount; $i++) {
            $sheet->insertNewRowBefore($insertBeforeRow, 1);
            // 复制样式（需要遍历所有列，这里简化实现）
            $this->copyRowStyle($templateRowNum, $insertBeforeRow);
            $insertBeforeRow++;
        }
        
        return $templateRowNum;
    }
    
    /**
     * 复制行样式
     */
    private function copyRowStyle($sourceRow, $targetRow)
    {
        $sheet = $this->spreadsheet->getActiveSheet();
        $highestColumn = $sheet->getHighestColumn();
        $colLetters = range('A', $highestColumn);
        
        foreach ($colLetters as $col) {
            $sourceCell = $sheet->getCell($col . $sourceRow);
            $targetCell = $sheet->getCell($col . $targetRow);
            
            // 复制样式
            $targetCell->setStyle($sourceCell->getStyle());
            // 复制公式
            if ($sourceCell->isFormula()) {
                $targetCell->setValue($sourceCell->getValue());
            }
        }
    }
    
    /**
     * 输出文件到浏览器
     */
    public function output($filename)
    {
        // 重算所有公式
        $this->spreadsheet->getActiveSheet()->setAutoFilter(false);
        
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $filename . '.xlsx"');
        header('Cache-Control: max-age=0');
        
        $writer = IOFactory::createWriter($this->spreadsheet, 'Xlsx');
        $writer->save('php://output');
    }
    
    /**
     * 保存到服务器文件
     */
    public function save($path)
    {
        $writer = IOFactory::createWriter($this->spreadsheet, 'Xlsx');
        $writer->save($path);
    }
}

5.3 业务调用示例

php

<?php

// 1. 加载模板
$exporter = new ExcelTemplateExport('templates/sales_report.xlsx');

// 2. 填充普通占位符
$exporter->fillData([
    '{report_date}' => date('Y-m-d'),
    '{company_name}' => 'XX科技有限公司',
    '{total_amount}' => '¥ 125,800.00'
]);

// 3. 准备业务数据
$salesData = [
    ['product_name' => '产品A', 'jan' => 12000, 'feb' => 13500, 'mar' => 14200],
    ['product_name' => '产品B', 'jan' => 8900,  'feb' => 9200,  'mar' => 10100],
    ['product_name' => '产品C', 'jan' => 15600, 'feb' => 16200, 'mar' => 17800],
];

// 4. 克隆模板行（模板中预先设计好一行样式）
// 假设第5行是模板行，包含样式和公式 =SUM(B5:D5)
$exporter->cloneRows('A5', count($salesData) - 1);

// 5. 填充表格数据
$exporter->fillTableData($salesData, 'A5', [
    'A' => 'product_name',
    'B' => 'jan', 
    'C' => 'feb',
    'D' => 'mar'
    // E列公式会自动计算，无需填充
]);

// 6. 输出文件
$exporter->output('销售报表_' . date('YmdHis'));

六、方案优势总结

七、注意事项

1. 占位符唯一性：避免普通文本与占位符重复

2. 模板备份：修改模板前务必备份，PhpSpreadsheet会直接修改原对象

3. 内存管理：大文件导出注意内存限制，可用 setMemoryLimit('512M')

4. 公式刷新：下载前调用 $sheet->setAutoFilter(false) 强制重算

5. 兼容性：模板使用 .xlsx 格式，不推荐 .xls（老格式功能受限）

八、结语

      基于模板的Excel导出方案，将复杂的样式设计从代码中剥离出来，让PHP专注于数据处理这一核心职责。这个思路不仅适用于Excel导出，也适用于Word、PDF等文档生成场景。

合理运用这种设计模式，能够让代码更简洁、更易维护，同时也为运营、产品等非技术同事参与报表模板制作提供了可能。