PHPExcel How to apply styles and set cell width and cell height to cell generated dynamically

Question

I have form that I generate its content to Excel through PHPExcel, my problem is that how can I set width and height and also styles to the heading cells.

the excel generated demo is here:

enter image description here

the excel i want is here: enter image description here

here is my code:

for ($col = 'A'; $col != 'J'; $col++) {
       $objPHPExcel->getActiveSheet()->getColumnDimension($col)->setAutoSize(true);
                }

                $objPHPExcel->getProperties()->setCreator("HOO")
                                                        ->setLastModifiedBy("HOO")
                                                        ->setTitle("Jobs History")
                                                        ->setSubject("PHPExcel Test Document")
                                                        ->setDescription("Test document for PHPExcel, generated using PHP classes.")
                                                        ->setKeywords("office PHPExcel php")
                                                        ->setCategory("Test result file");

                $objPHPExcel->setActiveSheetIndex(0)->mergeCells('A1:I1');
                $objPHPExcel->getActiveSheet()->setCellValue('A1', $this->lang->line('history_excel_title'));
                $objPHPExcel->getActiveSheet()->getStyle("A1")->getFont()->setBold(true);

                $objPHPExcel->getActiveSheet()->getStyle('A2:I2')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
                $objPHPExcel->getActiveSheet()->getStyle('A2:I2')->getFill()->getStartColor()->setARGB('29bb04');
                // Add some data
                $objPHPExcel->getActiveSheet()->getStyle("A2:I2")->getFont()->setBold(true);
                $objPHPExcel->getActiveSheet()->getStyle('A2:I2')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);


                //echo date('H:i:s') , " Add some data" , EOL;
                $objPHPExcel->setActiveSheetIndex(0)
                            ->setCellValue('A2', $this->lang->line('global_no'))
                            ->setCellValue('B2', $this->lang->line('history_name'))
                            ->setCellValue('C2', $this->lang->line('history_type_emp'))
                            ->setCellValue('D2', $this->lang->line('history_job_title'))
                            ->setCellValue('E2', $this->lang->line('history_emp_date'))
                            ->setCellValue('F2', $this->lang->line('history_emp_duration'))
                            ->setCellValue('G2', $this->lang->line('history_duty'))
                            ->setCellValue('H2', $this->lang->line('history_contact_duty'))
                            ->setCellValue('I2', $this->lang->line('history_salary'));

                $objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);

                $i =3;
                foreach($details->result() AS $item)
                {
                    $objPHPExcel->getActiveSheet()->getStyle('A'.$i.'')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                    $objPHPExcel->getActiveSheet()->getStyle('B'.$i.'')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                    $objPHPExcel->getActiveSheet()->getStyle('C'.$i.'')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                    $objPHPExcel->getActiveSheet()->getStyle('D'.$i.'')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                    $objPHPExcel->getActiveSheet()->getStyle('E'.$i.'')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                    $objPHPExcel->getActiveSheet()->getStyle('F'.$i.'')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                    $objPHPExcel->getActiveSheet()->getStyle('G'.$i.'')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                    $objPHPExcel->getActiveSheet()->getStyle('H'.$i.'')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                    $objPHPExcel->getActiveSheet()->getStyle('I'.$i.'')->getBorders()->getAllBorders()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);
                    // Miscellaneous glyphs, UTF-8
                    $objPHPExcel->setActiveSheetIndex(0)
                            ->setCellValue('A'.$i, $i-2)
                            ->setCellValue('B'.$i, $item->employer_office)
                            ->setCellValue('C'.$i, $item->job_type)
                            ->setCellValue('D'.$i, $item->job_title)
                            ->setCellValue('E'.$i, $item->job_appointment_date)
                            ->setCellValue('F'.$i, $item->job_duration)
                            ->setCellValue('G'.$i, $item->job_place)
                            ->setCellValue('H'.$i, $item->type_of_relation)
                            ->setCellValue('I'.$i, $item->monthly_salary);
                    $i++;
                }

score 45 · Accepted Answer · answered Aug 08 '14 at 15:45

45

You can use

$objWorksheet->getActiveSheet()->getRowDimension('1')->setRowHeight(40);
$objWorksheet->getActiveSheet()->getColumnDimension('A')->setWidth(100);

or define auto-size:

$objWorksheet->getRowDimension('1')->setRowHeight(-1);

answered Aug 08 '14 at 15:45

Rogerio de Moraes

1,527
18
15

Should set the row height to 'auto' for row 1 (define auto-size). – Rogerio de Moraes Aug 08 '14 at 16:08
1

For columns I used something like this: `$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setAutoSize(true)` Setting width to -1 didn't work for me. – Luxian Sep 25 '14 at 14:45
1

**@Luxian the most recomended version is always the last. Try in version 1.8.0, 2014-03-02. I used this. Some functions only running in PHP 5.3.0 or last.** – Rogerio de Moraes Dec 11 '14 at 10:23
1

**`getColumnDimension()->setWidth` is not avaliable in some old version. 'Case the class, use a new function in PHP.** – Rogerio de Moraes Dec 11 '14 at 10:27

score 8 · Answer 2 · edited Feb 21 '14 at 05:50

8

Try this:

$objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(40);

edited Feb 21 '14 at 05:50

László Papp

51,870
39
111
135

answered Feb 21 '14 at 05:23

uzor

89
1
3

3

Hi @uzor: welcome to StackOverflow. It is usually more helpful if you can write a few words around your code snippet. The questioner will get more from your answer this way, and be more likely to up-vote it. You can edit your answer by clicked `edit`. You can format your code by adding a few spaces in front of it. Give it a try and ask in the comments if questions. – Assad Ebrahim Feb 21 '14 at 05:46

PHPExcel How to apply styles and set cell width and cell height to cell generated dynamically

2 Answers2

Linked