Centering text in merged cells

Oct 12, 2009 at 2:22 PM

Hello,

I'm using the following example:

$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Header A');
$objPHPExcel->getActiveSheet()->setCellValue('A2', 'A1');
$objPHPExcel->getActiveSheet()->setCellValue('B2', 'A2');
$objPHPExcel->getActiveSheet()->setCellValue('C2', 'A3');
$objPHPExcel->getActiveSheet()->setCellValue('D2', 'A4');
$objPHPExcel->getActiveSheet()->mergeCells('A1:D1');

 

How can I center the text in the merged cells as now appears left centered? (Now, "Header A" appears in A1, but I want it in the middle (B1+C1)/2.).
I get desired result if I'm centering the text from Microsoft Excel but I wanted to do it programmatically.

Thanks

Developer
Oct 13, 2009 at 2:41 AM

Sounds like you want to merge and center:

$objPHPExcel->getActiveSheet()->mergeCells('A1:D1');
$objPHPExcel->getActiveSheet()->getStyle('A1:D1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);

Oct 13, 2009 at 7:56 AM

Hi,

Thanks but I get the following error when I do this:

PHP Fatal error:  Uncaught exception 'Exception' with message 'Cell coordinate string can not be a range of cells.' in /htdocs/PHPExcel/Worksheet.php:1085\nStack trace:\n#0 /export_situatie_costuri.php(149)

Oct 13, 2009 at 10:49 AM

I figure it out....you have to use only first cell when centering and not the whole merged cells, like:

$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
$objPHPExcel->getActiveSheet()->mergeCells('A1:D1');

Thanks koyama :)

Developer
Oct 13, 2009 at 12:01 PM
anjelika wrote:

Hi,

Thanks but I get the following error when I do this:

PHP Fatal error:  Uncaught exception 'Exception' with message 'Cell coordinate string can not be a range of cells.' in /htdocs/PHPExcel/Worksheet.php:1085\nStack trace:\n#0 /export_situatie_costuri.php(149)

Sounds like you are using an old version of PHPExcel. You need at least PHPExcel 1.7.0 to use cell range like in getStyle('A1:D1')

 

anjelika wrote:

I figure it out....you have to use only first cell when centering and not the whole merged cells, like:

$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);


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

Thanks koyama :)

While it may seem to work ok, you should apply the style to A1:D1 instead of just A1. There would be problems if you were styling borders.

 

Apr 8, 2010 at 5:52 PM

It probably goes without saying, but in Excel 2003 SP3, if the mergecells descriptions are reversed, it causes an unreadable error.  Example, mergeCells('D2:A1') creates the error, while mergeCells('A1:D2') resolves the problem. 

The reversed mergeCells('D2:A1') format works fine in Excel 2003 SP1 and Excel 2007.