PHPExcel cells merge set font smaller to fit into the merge cells with fixed width and height

Topics: Developer Forum
Apr 4, 2014 at 7:04 PM
I'm using PHPExcel to generate an Excel file for an order.

I have some constraints:
  • cells are merged
  • cell are aligned vertical and horizontal to center
  • text is wrapped and on multiple lines
  • text need to fit the merge cells: if too long to fit set font smaller but keep the text wrap
I have done the first 3 but I can't achieve the last requirement.

Example of:

what I have

enter image description here

what I need

enter image description here

The rows and columns are standard width and height and can't be changed due to the other cells width and height. Client design....

I have tried:
    $this->excel->getActiveSheet()->mergeCells('A28:C32');      
    $this->excel->getActiveSheet()->getStyle('A28')->getAlignment()->applyFromArray(
        array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            'vertical'   => PHPExcel_Style_Alignment::VERTICAL_CENTER,
            'rotation'   => 0,
            'wrap'       => true
        )
    )->setShrinkToFit(true);    
and any other combination of these above:
            $this->excel->getActiveSheet()->mergeCells('A28:C32');
            $this->excel->getActiveSheet()->getStyle('A28:C32')->getAlignment()->applyFromArray(
        array(
            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
            'vertical'   => PHPExcel_Style_Alignment::VERTICAL_CENTER,
            'rotation'   => 0,
            'wrap'       => true
        )
     )->setShrinkToFit(true);

             //or this
             $this->excel->getActiveSheet()->mergeCells('A28:C32')->getAlignment()->setWrapText(true)->setShrinkToFit(true);
I'm aware of
             $this->excel->getActiveSheet()->getStyle("A1")->getFont()->setSize(10);
but I need the text to be smaller only if it is not fit the merged cells.

Thanks for any suggestion.
Apr 7, 2014 at 1:58 PM
In Excel, you cannot have simultaneously 'Wrap' and 'ShrinkToFit'. Although PHPExcel allows you to define both, in the end, Excel will not take it into account.
You will therefore have to simulate and to do this, know the width and the height you have, calculate the font size so that the string is contained within the available space. Not obvious, even more so with a proportional font.
Apr 7, 2014 at 3:13 PM
Thanks for your answer, LWol.

The only solution I found is to actually count the number of lines and set the font accordingly.

If the number of lines of string is greater than number of rows of merged cells (5 in my case) then set the font smaller.
  if (substr_count($return_to, "\n") <= 4){ //count 5 lines or less -> font = 10

      $this->excel->getActiveSheet()->getStyle("A28")->getFont()->setSize(10);

  }else{ //count more than 5 lines -> font = 9

      $this->excel->getActiveSheet()->getStyle("A28")->getFont()->setSize(9);

  }