PHPExcel - dynamic row height for merged cells

Question

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.

Answer 1

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.

Answer 2

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);

Answer 3

To set the Auto Height for any row use:

$_row_number = 10;

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

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