Merging cells in Excel by rows and columns together using PHPExcel

Question

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

$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).

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.

$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 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.

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

Answer 1

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

$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));              

Answer 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)

Answer 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))