How to center the text in PHPExcel merged cell
PhpPhpexcelPhp Problem Overview
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:
Php Solutions
Solution 1 - Php
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);
Solution 2 - Php
<?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");
?>
Solution 3 - Php
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");
Solution 4 - Php
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.
Solution 5 - Php
When using merged columns, I got it centered by using PHPExcel_Style_Alignment::HORIZONTAL_CENTER_CONTINUOUS instead of PHPExcel_Style_Alignment::HORIZONTAL_CENTER
Solution 6 - Php
// 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');