Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

php_excel07- How to make grow the height of cell based on cell data(xls)

Tags:

php

phpexcel

In my application i need to export to xls file in a predefined format.

so I just integrated php_excel2007. I am using one template with a predefined format.

The problem here is the cell data may change dynamically. if data is much bigger than cell height then data is collapsing.

So is ther anyway to increase the height of cell based on content of the cell(in XLX not xlsx)?

like image 368
sandeep Avatar asked Oct 12 '10 11:10

sandeep


People also ask

How do you automatically adjust cell height in Excel?

On the Home tab, in the Cells group, click Format. Under Cell Size, click AutoFit Row Height. Tip: To quickly autofit all rows on the worksheet, click the Select All button, and then double-click the boundary below one of the row headings.

Why does wrap text not adjust row height in Excel?

Now, Excel should also automatically adjust the row height to make all of the wrapped cell content visible. If it does not — it's because you have manually adjusted the row height (or the column width) yourself at some point. That sort of disengages the auto Wrap Text.


2 Answers

The only way is as described in this answer to your previous question on this topic: setting the row height to autofit, and the cell alignment to wrap. This should work the same way, whether you use the Excel5 Writer or the Excel2007 Writer.

$objPHPExcel = new PHPExcel();

// Set some long string values in some cells
$objPHPExcel->getActiveSheet()->getCell('A1')->setValue("This is a large block of text,\ncomprising several lines,\nthat will be set to autofit.");
$objPHPExcel->getActiveSheet()->getCell('A2')->setValue("This is a large block of text that will NOT be set to autofit.");
$objPHPExcel->getActiveSheet()->getCell('B1')->setValue("This is another large block of text without any line breaks, that will be set to autofit.");
$objPHPExcel->getActiveSheet()->getCell('A3')->setValue("This is another large block of text,\ncomprising several lines,\nthat will be set to autofit.");
$objPHPExcel->getActiveSheet()->getCell('A4')->setValue("This is another large block of text without any line breaks, that will be set to autofit but not wrap.");
// Fix the column width to a reasonable size
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(30);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(30);
//  Set text wrap for cells A1 and B1. This forces the text to wrap, but doesn't adjust the height of the row
$objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setWrapText(true);
$objPHPExcel->getActiveSheet()->getStyle('B1')->getAlignment()->setWrapText(true);
// Set rows 1, 3 and 4 to autofit the height to the size of text
$objPHPExcel->getActiveSheet()->getRowDimension(1)->setRowHeight(-1);
$objPHPExcel->getActiveSheet()->getRowDimension(3)->setRowHeight(-1);
$objPHPExcel->getActiveSheet()->getRowDimension(4)->setRowHeight(-1);

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('testAutoHeight.xls');

EDIT

Added comments to the code sample to explain what each called method actually does

like image 155
Mark Baker Avatar answered Sep 27 '22 20:09

Mark Baker


setRowHeight to -1 for make auto height (based on cell data)

// auto-size on row 1

$objWorksheet->getRowDimension('1')->setRowHeight(-1);
like image 27
bungdito Avatar answered Sep 27 '22 20:09

bungdito