How to center the text in PHPExcel merged cell

Question

How to center text "test"?

This is my code:

<?php
    /** Error reporting */
    error_reporting(E_ALL);
    ini_set('display_errors', TRUE);
    ini_set('display_startup_errors', TRUE);
    date_default_timezone_set('Europe/London');

    /** Include PHPExcel */
    require_once '../Classes/PHPExcel.php';

    $objPHPExcel = new PHPExcel();
    $sheet = $objPHPExcel->getActiveSheet();
    $sheet->setCellValueByColumnAndRow(0, 1, "test");
    $sheet->mergeCells('A1:B1');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save("test.xlsx");

Output Excel document:

enter image description here

score 114 · Accepted Answer · answered Jan 22 '14 at 15:00

114

if you want to align only this cells, you can do something like this:

    $style = array(
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        )
    );

    $sheet->getStyle("A1:B1")->applyFromArray($style);

But, if you want to apply this style to all cells, try this:

    $style = array(
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        )
    );

    $sheet->getDefaultStyle()->applyFromArray($style);

answered Jan 22 '14 at 15:00

dap.tci

2,455
1
20
18

3

in latest PhpExcel it works like this: $style = array( 'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER, ); that is WITHOUT the 'alignment' array key – Andrei Diaconescu Nov 26 '15 at 16:37
To center allign column values use: $objPHPExcelSheet->getDefaultStyle()->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); – Staafsak Jun 20 '16 at 22:54
for laravel 5.x use `\PHPExcel_Style_Alignment::HORIZONTAL_CENTER` instead `PHPExcel_Style_Alignment::HORIZONTAL_CENTER` – Marosdee Uma Feb 11 '19 at 12:34

score 18 · Answer 2 · answered Jul 18 '14 at 15:10

<?php
    /** Error reporting */
    error_reporting(E_ALL);
    ini_set('display_errors', TRUE);
    ini_set('display_startup_errors', TRUE);
    date_default_timezone_set('Europe/London');

    /** Include PHPExcel */
    require_once '../Classes/PHPExcel.php';

    $objPHPExcel = new PHPExcel();
    $sheet = $objPHPExcel->getActiveSheet();
    $sheet->setCellValueByColumnAndRow(0, 1, "test");
    $sheet->mergeCells('A1:B1');
    $sheet->getActiveSheet()->getStyle('A1:B1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save("test.xlsx");
?>

**You can set VERTICAL Alignment too** `setVERTICAL(PHPExcel_Style_Alignment::PHPExcel_Style_Alignment::VERTICAL_CENTER);` — Rogerio de Moraes, Dec 11 '14 at 14:12

score 11 · Answer 3 · answered Jan 22 '14 at 14:59

The solution is to set the cell style through this function:

$sheet->getStyle('A1')->getAlignment()->applyFromArray(
    array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,)
);

Full code

<?php
/** Error reporting */
error_reporting(E_ALL);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);
date_default_timezone_set('Europe/London');

/** Include PHPExcel */
require_once '../Classes/PHPExcel.php';

$objPHPExcel = new PHPExcel();
$sheet = $objPHPExcel->getActiveSheet();
$sheet->setCellValueByColumnAndRow(0, 1, "test");
$sheet->mergeCells('A1:B1');
$sheet->getStyle('A1')->getAlignment()->applyFromArray(
    array('horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,)
);
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save("test.xlsx");

enter image description here

score 7 · Answer 4 · answered Aug 07 '18 at 07:02

We can also set the vertical alignment with using this way

$style_cell = array(
   'alignment' => array(
       'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
       'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
   ) 
);

with this cell set the vertically aligned into the middle.

score 2 · Answer 5 · answered Jan 23 '18 at 13:32

2

When using merged columns, I got it centered by using PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS instead of PHPExcel_Style_Alignment::HORIZONTAL_CENTER

answered Jan 23 '18 at 13:32

technetium

110
2
9

score 0 · Answer 6 · answered Dec 28 '21 at 06:30

// this will work in  Excel2007
$object->getActiveSheet()->setCellValueByColumnAndRow(0, 1, "UTI - AMC  LTD.  - DOFA ");
$style = array(
'alignment' => array(
'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
)
);

$object->getDefaultStyle()->applyFromArray($style);
$object->getActiveSheet()->mergeCells('A1:H1');

score 0 · Answer 7 · answered Jul 12 '23 at 16:06

You can using applyFromArray or setHorizontal and setVertical to setting alignment, like codes below:

$objPHPExcel->getActiveSheet()->getStyle("A1")->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::VERTICAL_CENTER);
$objPHPExcel->getActiveSheet()->getStyle("A1")->getAlignment()->setVertical(\PHPExcel_Style_Alignment::VERTICAL_CENTER);

OR

$objPHPExcel->getActiveSheet()->getStyle("A1")->getAlignment()->applyFromArray(
    array(
        "horizontal" => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER, 
        "vertical" => \PHPExcel_Style_Alignment::VERTICAL_CENTER
    )
);

How to center the text in PHPExcel merged cell

7 Answers7

Full code

Linked