使用CodeIgniter的PHPExcel生成Excel在MS Office打开报合并单元格错误

问题现象 #

用CodeIgniter+PHPExcel生成的Excel文件，在LibreOffice/OpenOffice中正常打开，但Microsoft Office打开时弹出错误提示：

removed records : merge cells from /xl/worksheets/sheet1.xml part

问题原因 #

代码中合并单元格的逻辑存在两处问题，导致MS Office解析XML结构时出错：

1. 表头的A1:J1合并操作被放在字段循环里，重复执行10次，生成冗余的合并节点。
2. 数据行合并时，每次循环都重复执行相同范围的合并，导致XML中出现重复的合并记录，MS Office无法识别。

修复方案 #

1. 修复表头合并逻辑 #

把表头合并操作移到字段循环外，只执行一次，同时避免重复设置A1单元格的值：

// 先设置表头合并和部门名称（仅执行一次）
if (isset($data[0]->department)) {
    $this->excel->getActiveSheet()->setCellValue('A1', "Name of Department: " . $data[0]->department);
}
$this->excel->getActiveSheet()->mergeCells('A1:J1');

// 循环处理列标题和列宽
$col = 0;
foreach ($fields as $key => $field) {
    $column = PHPExcel_Cell::stringFromColumnIndex($col);
    $this->excel->getActiveSheet()->setCellValueByColumnAndRow($col, 2, ucwords($field['Title']));
    $this->excel->getActiveSheet()->getColumnDimension($column)->setWidth($field['Width']);
    $col++;
}

2. 修复数据行合并逻辑 #

避免重复合并同一范围，每次扩展合并时先取消旧的合并，再合并到新行：

if (!empty($data)) {
    $counter = 1;
    $SerialNo = 1;
    $scheme = "";
    $schemeyear = "";
    $row = 3;
    foreach ($data as $key => $value) {
        // 原有设置单元格值的逻辑保持不变
        $col = 0;
        $value->componentvaluetype = ($value->componentvaluetype != "Text") ? $value->componentvaluetype : (($value->componettvalueunit != '') ? $value->componettvalueunit : $value->componentvaluetype);
        $value->component = html_entity_decode($value->component);
        $value->schemetitle = html_entity_decode($value->schemetitle);
        foreach ($fields as $field_key => $field_value) {
            $val = @$value->$field_key;
            if ($field_key == "Rno") {
                $val = $SerialNo;
            } else if (@$field_key == "isapproved") {
                $val = ($value->isapproved == -1) ? "Pending" : (($value->isapproved == 1) ? "Approved" : "Rejected");
            }
            $val = str_replace('&', '&', str_replace(array('\r', "\\"), "", str_replace(array('\r\n', '\n'), "\n",  html_entity_decode($val, ENT_QUOTES, 'UTF-8'))));
            $this->excel->getActiveSheet()->setCellValueByColumnAndRow($col, $row, $val);
            $col++;
        }

        // 修复后的合并逻辑
        if ($scheme == @$value->schemetitle && $schemeyear == @$value->schemebudgetyear) {
            $first_row = $row - $counter;
            // 取消之前的合并，准备扩展范围
            if ($counter > 1) {
                $this->excel->getActiveSheet()->unmergeCells('A' . $first_row . ':A' . ($row-1));
                $this->excel->getActiveSheet()->unmergeCells('B' . $first_row . ':B' . ($row-1));
                $this->excel->getActiveSheet()->unmergeCells('C' . $first_row . ':C' . ($row-1));
                $this->excel->getActiveSheet()->unmergeCells('D' . $first_row . ':D' . ($row-1));
                $this->excel->getActiveSheet()->unmergeCells('E' . $first_row . ':E' . ($row-1));
                $this->excel->getActiveSheet()->unmergeCells('F' . $first_row . ':F' . ($row-1));
                $this->excel->getActiveSheet()->unmergeCells('G' . $first_row . ':G' . ($row-1));
            }
            // 合并到当前行
            $this->excel->getActiveSheet()->mergeCells('A' . $first_row . ':A' . $row);
            $this->excel->getActiveSheet()->mergeCells('B' . $first_row . ':B' . $row);
            $this->excel->getActiveSheet()->mergeCells('C' . $first_row . ':C' . $row);
            $this->excel->getActiveSheet()->mergeCells('D' . $first_row . ':D' . $row);
            $this->excel->getActiveSheet()->mergeCells('E' . $first_row . ':E' . $row);
            $this->excel->getActiveSheet()->mergeCells('F' . $first_row . ':F' . $row);
            $this->excel->getActiveSheet()->mergeCells('G' . $first_row . ':G' . $row);
            $counter++;
        } else {
            $SerialNo++;
            $counter = 1;
        }
        $scheme = $value->schemetitle;
        $schemeyear = $value->schemebudgetyear;
        $row++;
    }
}

额外注意事项 #

• 合并单元格时，确保只有左上角单元格设置了数据，其他合并单元格不要赋值，MS Office只识别合并区域左上角的内容。
• 可使用$this->excel->getActiveSheet()->getMergeCells()检查已存在的合并范围，避免添加无效节点。

内容的提问来源于stack exchange，提问作者Vishal Patel