Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

PHPExcel - dynamic row height for merged cells

Tags:

php

phpexcel

After a lot of trial and error, I still can't seem to figure out a workaround to get merged cells to have an AutoFit height.

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

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

$totalWidth = 67.44; //width of columns A-H

$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 go through these same steps manually in excel, I get the result I want, but the output of the code above is always the default 12.75 row height.

Anyone have any ideas? I don't really mind having to hardcode the column widths, i just want the height to be responsive to the text.

Thanks in advance.

like image 269
Law Hop Avatar asked Nov 09 '12 17:11

Law Hop


2 Answers

Autoheight doesn't work on merged cells. I think this is a problem with Excel not PHPExcel. If you want to do this you must use a work around. This is mine...

I have a function that takes text, splits into lines on newlines ('\n') and calculates the number of rows needed to 'fit' the text based on the number of characters per line (fiddle factor).

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 fiddle-factor, arrived at by trial and error, is 55. I then 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 just to give a little separation between this cell and the next.

like image 194
ken clark Avatar answered Sep 26 '22 02:09

ken clark


I think, that I found better solution. When I inserting data to merged columns in foreach, I pick the longest string in row by comparing with strlen(). After that insert the longest string to the last+1 column and set it to hidden column. There is example for row 1 with 4 columns (A-D)

$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);
like image 43
Martin Suchan Avatar answered Sep 23 '22 02:09

Martin Suchan