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

enter image description here

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?

Mark Baker · Accepted Answer · 2012-06-06T08:30:34.530

26

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

edited Jun 06 '12 at 08:30

answered Jun 06 '12 at 07:54

Mark Baker

209,507
32
346
385

@markBaker, I already used PHPExcel and I have only cumpliments to tell about this library. Now I'm working with Aspose for a java project and there's a limitation for this library that it seems PHPExcel also has.. We cannot merge already merged cells with a new one. Do you know why? – periback2 Aug 29 '13 at 16:30
Mainly because it's painful to handle in the code, and adds an overhead for every merge, testing if it's part of any existing merge and amalgamating the two if it is... and which can't provide any feedback when it does so – Mark Baker Aug 29 '13 at 16:53
This didn't work on me, im using maatwebsite v2.1.0. – Angel Apr 02 '22 at 04:03

score 11 · Answer 2 · answered Feb 11 '16 at 09:38

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)

score 4 · Answer 3 · answered Oct 19 '13 at 15:52

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

jashk · Answer 4 · 2013-09-06T17:27:56.910

3

I make a simple function to calc cells to merge by cols and 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

edited Sep 06 '13 at 17:27

answered Jun 03 '13 at 20:55

jashk

56
5

You might want to use the built-in helper functions PHPExcel_Cell::columnIndexFromString() and PHPExcel_Cell::stringFromColumnIndex() rather than your own chr/floor/etc because they're already built-in – Mark Baker Aug 30 '13 at 16:28
Thanks to mention these helpers – jashk Sep 06 '13 at 17:32

score 1 · Answer 5 · answered Jan 18 '19 at 16:58

I was also looking solution for this question. where i want to merge cell and put content (value) on that. After few search i got some solution on this. but did not checked because i am using Maatawebsite for get Excel file.

But any one can try thing.. Solution is based on PHPExcel nit sure ,it will work on Maatawebsite.

Source Link

Merge from column A row 1 to column E row 1

$objPHPExcel->getActiveSheet()->mergeCells('A1:E1');

// add some text
$objPHPExcel->getActiveSheet()->setCellValue('A1','The quick brown fox.');

Merge from column A row 1 to column E row 3

$objPHPExcel->getActiveSheet()->mergeCells('A1:E3');

// add some text
$objPHPExcel->getActiveSheet()->setCellValue('A1','The quick brown fox.');

I checked maatawebsite document and they have same method mergeCells. so i think i would be work.

This Solution from Maatawebste.

$sheet->cells('A1:C1', function($cells) {
    $cells->setBorder('thin', 'thin', 'thin', 'thin');
});
$sheet->mergeCells('A1:C1');

Solution 2nd

$sheet->setMergeColumn(array(
'columns' => array('A','B','C','D'),
'rows' => array(
array(2,3),
array(5,11),
)
));

score 1 · Answer 6 · edited Jan 27 '20 at 19:12

1

$sheet -> mergeCellsByColumnAndRow($col1, $row1, col2, row2);

is the function.

edited Jan 27 '20 at 19:12

סטנלי גרונן

2,917
23
46
68

answered Jan 27 '20 at 18:50

jmrobin92

31
3

Merging cells in Excel by rows and columns together using PHPExcel

6 Answers6

Linked