PHPExcel style text to center

Question

I'm using PHPExcel with codeigniter to generate .xlx, .xlxs file, Every thing is working perfect except text formatting. Here is the screenshot of html view that I'm passing to PHPExcel library to generate .xlx file.

And Here is the output.

As you can see that text indentation and styles are removed. Here is my code used to generate output.

public function html_to_excel_download($filename, $data=null){
    if ($data != null) {
    // proper encoding of data UTF-8 for unicode characters
    $data = chr(255).chr(254).iconv("UTF-8", "UTF-16LE//IGNORE", $data); 

    // Put the data into a temporary file
    $tmpfile = time().'.html';
    file_put_contents($tmpfile, $data);

    // Read the contents of the file into PHPExcel Reader class
    $reader = new PHPExcel_Reader_HTML; 
    $content = $reader->load($tmpfile); 


    // Excel Writer
    $objWriter = PHPExcel_IOFactory::createWriter($content, 'Excel2007'); 


    // Download File
    header('Content-Type: application/vnd.ms-excel');
    header('Content-Disposition: attachment;filename="'.$filename.'"');
    header('Cache-Control: max-age=0');
    $objWriter->save('php://output');
    unlink($tmpfile);

    exit();
}

}

How can I indent title text to center?

score 4 · Answer 1 · answered Apr 12 '16 at 08:54

you may try another way to do that... Step 1) Define "$this->excel->setActiveSheetIndex(0);" at the top of function

step 2) Define style array and add it as a variable with excel object like,

$styleArray = array(
        'font' => array(
            'bold' => true,
            'color' => array('rgb' => '2F4F4F')
        ),
        'alignment' => array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
        )
    );

Step 3) $this->excel->getActiveSheet()->getStyle('A3:Z3')->applyFromArray($styleArray);

Note: You have to use getStyle() in your code to apply specific styles on column/row

score 1 · Answer 2 · answered Apr 12 '16 at 06:59

1

// single column A, setting e.g. horizontal alignment 
$objWorksheet->getStyle('A')->getAlignment()->setHorizontal(...); 

// range of columns A to K 
$objWorksheet->getStyle('A:K')->getAlignment()->setHorizontal(...);

AND you can see full documentation on this site for formatting SHEET HERE

answered Apr 12 '16 at 06:59

Kunal

604
10
18

Thanks for response, I made few changes on my code and tried styling after creating new phpexcel object, '$excel = new PHPExcel(); $worksheet = $excel->getActiveSheet(); //style $worksheet->getStyle("A")->getFill()->getStartColor()->setRGB("FFFF00"); ', but still not working. Can you please tell me where I'm missing. – Aditya Lepcha Apr 12 '16 at 07:31

score 0 · Answer 3 · edited Aug 12 '17 at 14:39

There are two ways you can get rid of this issue, one is without json array:

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

where $doc = new PHPExcel(); or a PHPExcel object

The second is using the json array as described here

I'm pretty confident that you'll find all instructions about this library in the official documentation

PHPExcel style text to center

3 Answers3