Php_excel07- How to make cell growth based on cell data (xls)

Question

In my application, I need to export the xls file to a predefined format.

so i just integrated php_excel2007 . I use one template with a predefined format.

The problem here is that these cells can change dynamically. if the data is much larger than the height of the cell, then the data is compressed.

So, anyway, to increase cell height based on cell content (in xlx not xlsx)?

Answer 1

 <?php error_reporting(E_ALL); require_once ROOT.'/PHPExcel.php'; function down($details) { // Create new PHPExcel object $objPHPExcel = new PHPExcel(); $objReader = PHPExcel_IOFactory::createReader('Excel5'); $objPHPExcel = $objReader->load(ROOT."/templates/project_report1.xls"); // Set properties $p_i=5;$alpa="B";$row_no=5;$mil="";$d_cel="B"; $objPHPExcel->setActiveSheetIndex() ->setCellValue('K1', $details['report_details']['cur_time']); $objPHPExcel->setActiveSheetIndex() ->setCellValue('C2', 'REPORT START DATE:'.$details['report_details']['start_date']) ->setCellValue('H2', $details['report_details']['details']) ->setCellValue('C3', 'SHOWING:'.$details['report_details']['showing']); foreach($details as $p_name=>$date){ //thisis to display date at the top foreach($date as $p1=>$m_name1){ $objPHPExcel->setActiveSheetIndex(0)->setCellValue($d_cel.'4', $p1);$d_cel++;//to display date in the top } break; } foreach($details as $p_name=>$date){ if($p_name=="report_details")break; $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A'.$p_i, $p_name); $objPHPExcel->getActiveSheet(0)->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('A5'), 'A'.$p_i ); $objPHPExcel->setActiveSheetIndex(0)->getStyle('A'.$p_i)->getAlignment()->setWrapText(true); foreach($date as $p=>$m_name){ $mil=$tic=$st=" "; foreach($m_name as $name=>$val){ if($name=="milestone") foreach($val as $in_det=>$det){ if($det && isset($det['start_date'])) $mil.=$det['name']."\n".$det['start_date']."\n"; else $mil.=$det['name']; } if($name=="ticket") foreach($val as $in_det=>$det){ if($det) $tic.=$det['name']." ".$det['start_date']."\n"; } if($name=="task") foreach($val as $in_det=>$det){ if($det) $st.=$det['name']." ".$det['start_date']."\n"; } } $summary=$mil.$tic.$st; $objPHPExcel->getActiveSheet(0)->duplicateStyle( $objPHPExcel->getActiveSheet()->getStyle('B5'), $alpa.$p_i ); $objPHPExcel->getActiveSheet(0)->getRowDimension($p_i)->setRowHeight(); $objPHPExcel->getActiveSheet(0)->getStyle('A'.$p_i.':'.'M'.$p_i)->getAlignment()->setWrapText(true); $objPHPExcel->setActiveSheetIndex(0)->getStyle($alpa.$p_i)->getFont()->setSize(5); $objPHPExcel->setActiveSheetIndex(0)->setCellValue($alpa.$p_i, $summary); $alpa++; } $alpa="B"; $p_i++; } $objPHPExcel->setActiveSheetIndex(0); $objPHPExcel->getActiveSheet()->setTitle('Report'); // Set active sheet index to the first sheet, so Excel opens this as the first sheet // Redirect output to a client's web browser (Excel2007) header('Content-Type: application/vnd.ms-excel'); header('Content-Disposition: attachment;filename="project_report.xls"'); header('Cache-Control: max-age=0'); $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5'); $objWriter->save('php://output'); exit; } ?> 

Answer 2

The only way described in this answer to your previous question on this question is: setting the line height for auto-tuning and aligning the cell for wrapping. This should work the same way, whether you use Excel5 Writer or 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 on the sample code to explain that each method being called actually does

Answer 3

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

 // auto-size on row 1 $objWorksheet->getRowDimension('1')->setRowHeight(-1);