使用PHPExcel将行和列中的单元格合并在一起

I need to merge cells in Excel (xlsx) by rows and again by columns using PHPExcel. I tried the following.

我需要按行合并Excel(xlsx)中的单元格,再使用PHPExcel合并列。我尝试了以下内容。

$sheet->mergeCells("G".($row_count+1).":G".($row_count+4));             
$sheet->mergeCells("H".($row_count+1).":H".($row_count+4));             
$sheet->mergeCells("I".($row_count+1).":I".($row_count+4));     

Where the variable $row_count has some unpredictable dynamic value like 25, 50, 75 and so on (no regular pattern).

变量$ row_count有一些不可预测的动态值,如25,50,75等(没有常规模式)。

It merges the cells as shown in the preceding snap shot as can be seen immediately below the Note cell. After merging these cells by rows, I'm trying to merge them by columns as follows.

它合并单元格,如前面的快照所示,可以在Note单元格的正下方看到。在按行合并这些单元格之后,我正在尝试按列合并它们,如下所示。

$sheet->mergeCells("G".($row_count+1).":I".($row_count+1));             

but it doesn't work. When I try to open the excel file, it asks for a confirmation (with a confirmation box)

但它不起作用。当我尝试打开excel文件时,它会要求确认(带有确认框)

Excel found unreadable content in 'report.xlsx'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.

Excel在“report.xlsx”中找到了不可读的内容。您想恢复此工作簿的内容吗?如果您信任此工作簿的来源,请单击“是”。

How to merge cells by rows and columns together in Excel then?

如何在Excel中按行和列合并单元格呢?

4 个解决方案

#1

Merging simply requires a valid range of cells like A1:B2, so your

合并只需要一个有效的单元格范围,如A1:B2,所以你的

$sheet->mergeCells("G".($row_count+1).":I".($row_count+1));

should work without any problem.

应该没有任何问题。

Can you please experiment with a simple test case to prove that this is causing you a problem, and not something else in your script

您能否尝试一个简单的测试用例来证明这会导致您出现问题,而不是您脚本中的其他问题

EDIT

After rereading your question: Your problem may be that you're trying to merge cells that are already part of a merge range, rather than merging each row, then trying to merge by column, try merging the full rangein one go.

重新阅读您的问题后:您的问题可能是您正在尝试合并已经属于合并范围的单元格,而不是合并每一行,然后尝试按列合并,尝试合并整个范围。

$sheet->mergeCells("G".($row_count+1).":I".($row_count+4));              

#2

There is one more method for cell merging

还有一种用于细胞合并的方法

    /**
 * Set merge on a cell range by using numeric cell coordinates
 *
 * @param   int $pColumn1   Numeric column coordinate of the first cell
 * @param   int $pRow1      Numeric row coordinate of the first cell
 * @param   int $pColumn2   Numeric column coordinate of the last cell
 * @param   int $pRow2      Numeric row coordinate of the last cell
 * @throws  Exception
 * @return PHPExcel_Worksheet
 */
     public function mergeCellsByColumnAndRow($pColumn1 = 0, $pRow1 = 1, $pColumn2 = 0, $pRow2 = 1)

#3

function cellsToMergeByColsRow($start = -1, $end = -1, $row = -1){
    $merge = 'A1:A1';
    if($start>=0 && $end>=0 && $row>=0){
        $start = PHPExcel_Cell::stringFromColumnIndex($start);
        $end = PHPExcel_Cell::stringFromColumnIndex($end);
        $merge = "$start{$row}:$end{$row}";
    }
    return $merge;
}

Addition to the case:

除案件外:

$objPHPExcel->getActiveSheet()->mergeCells(cellsToMergeByColsRow(0,2,3))

#4

I make a simple function to calc cells to merge by cols and row.

我做了一个简单的函数来计算细胞以cols和row合并。

function cellsToMergeByColsRow($start = NULL, $end = NULL, $row = NULL){
    $merge = 'A1:A1';
    if($start && $end && $row){
        $start = PHPExcel_Cell::stringFromColumnIndex($start);
        $end = PHPExcel_Cell::stringFromColumnIndex($end);
        $merge = "$start{$row}:$end{$row}";

    }

    return $merge;
}

And call

$sheet->mergeCells(cellsToMergeByColsRow($col, $col+5, $row));

Thanks @Mark Baker

谢谢@Mark Ba​​ker