Geek Answers Handbook

PHPExcel - dynamic row height for merged cells

After a lot of trial and error, I still can’t find a workaround to merge cells to have AutoFit height.

I tried an approach based on a bit of VBA code that I found on this site: https://groups.google.com/forum/?fromgroups=#!topic/microsoft.public.excel.programming/pcvg7o5sKhA

The following code inserts text, wraps it, and changes the width of the cell (A1) to the total width of the merged cells that I want. Then it merges the cells and sets column A back down to the original width. $ note - any long line of text. $ vAlignTop is an array that sets the alignment of text at the top of the cell.

$totalWidth = 67.44; //width of columns AH $objPHPExcel->getActiveSheet()->setCellValue('A1', $note); $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth($totalWidth); $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true); $objPHPExcel->getActiveSheet()->getStyle('A1:H1')->applyFromArray($vAlignTop); $objPHPExcel->getActiveSheet()->mergeCells('A1:H1'); $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(8.43); //original width of column A

When I follow these steps manually in excel, I get the result I want, but the output of the above code is always the default line height of 12.75.

Does anyone have any ideas? I don't mind hard coding the column width, I just want the height to respond to the text.

Thanks in advance.

+1
source share
3 answers

Autoheight does not work on merged cells. I think this is a problem with Excel, not with PHPExcel. If you want to do this, you must use work. It is mine...

I have a function that accepts text, splits into lines on the lines of a new line ('\ n'), and calculates the number of lines needed to “fit” the text, depending on the number of characters in the line (script coefficient).

 function getRowcount($text, $width=55) { $rc = 0; $line = explode("\n", $text); foreach($line as $source) { $rc += intval((strlen($source) / $width) +1); } return $rc; }

For my report, the script factor obtained by trial and error is 55. Then I use the above function in my code ...

 $purpose = $survey["purpose"]; $numrows = getRowcount($purpose); $objPHPExcel->getActiveSheet()->setCellValue('B'.$xlrow, 'Report Purpose'); $objPHPExcel->getActiveSheet()->getStyle('B'.$xlrow)->applyFromArray($fmt_cover_bold); $objPHPExcel->getActiveSheet()->setCellValue('C'.$xlrow, $purpose); $objPHPExcel->getActiveSheet()->getRowDimension($xlrow)->setRowHeight($numrows * 12.75 + 2.25); $objPHPExcel->getActiveSheet()->mergeCells('C'.$xlrow.':E'.$xlrow); $objPHPExcel->getActiveSheet()->getStyle('C'.$xlrow.':E'.$xlrow)->applyFromArray($fmt_normal_wrap); $xlrow++;

I add 2.25 to give a little separation between this cell and the next.

+4
source

To set the automatic height for using any string:

 $_row_number = 10; $excel->getActiveSheet()->getRowDimension($_row_number)->setRowHeight(-1);

i.e. set the parameter to -1 instead of any number.

+1
source

I think I have found a better solution. When I insert data into joined columns in foreach, I select the longest row in the row compared to strlen (). After that, insert the longest row in the last column + 1 and set it to a hidden column. Example for row 1 with 4 columns (AD)

 $longestContent = "the longest content in row 1"; $mergerColumnsWidth = 24; $objPHPExcel->getActiveSheet()->setCellValue("E1", $longestContent); $objPHPExcel->getActiveSheet()->getStyle("E1")->getAlignment()->setWrapText(true); $objPHPExcel->getActiveSheet()->getColumnDimension("E")->setWidth(mergerColumnsWidth ); $objPHPExcel->getActiveSheet()->getColumnDimension("E")->setVisible(false);
0
source

More articles:


All Articles