PHPExcel not setting row height based on content

1.3k views Asked by At

I've been trying to set the height of excel rows to auto, based on the content of some long, wrapped cells without carriage returns.

I tried the approachs mentioned in the answers to several older questions, but none of the approachs do the job. I couldn't figure out why.

The following is the code I have. I kept all of it in order to find any height reset that I could be inadvertently doing.

$objReader = new PHPExcel_Reader_CSV();
$objPHPExcel = $objReader->load("data.csv");
$sheet = $objPHPExcel->getActiveSheet();
//$sheet->getDefaultRowDimension()->setRowHeight(-1);
$sheet->getDefaultStyle()->getAlignment()->setWrapText(true);
$sheet->getColumnDimension('A')->setWidth(14);
$sheet->getColumnDimension('B')->setWidth(50);
$sheet->getColumnDimension('C')->setWidth(14);
$sheet->getColumnDimension('D')->setWidth(14);
$sheet->getColumnDimension('E')->setWidth(30);
$sheet->getColumnDimension('F')->setWidth(30);
$sheet->getColumnDimension('G')->setWidth(5);
$sheet->getColumnDimension('H')->setWidth(5);
$sheet->getColumnDimension('I')->setWidth(5);
$sheet->getColumnDimension('J')->setWidth(5);
$sheet->getColumnDimension('K')->setWidth(5);
$sheet->getColumnDimension('L')->setWidth(5);
$sheet->getColumnDimension('M')->setWidth(30);
$sheet->getColumnDimension('N')->setWidth(14);
$sheet->getColumnDimension('O')->setWidth(14);
$sheet->getColumnDimension('P')->setWidth(14);
$sheet->getColumnDimension('Q')->setWidth(14);
$sheet->getColumnDimension('R')->setWidth(14);
$sheet->getColumnDimension('S')->setWidth(14);
$sheet->getColumnDimension('T')->setWidth(14);
$sheet->getColumnDimension('U')->setWidth(14);
$sheet->getColumnDimension('V')->setWidth(50);
$sheet->getColumnDimension('W')->setWidth(30);
$sheet->getColumnDimension('X')->setWidth(30);
$sheet->getColumnDimension('Y')->setWidth(30);
$sheet->getColumnDimension('Z')->setWidth(30);
$sheet->getColumnDimension('AA')->setWidth(30);
$sheetstyle = $sheet->getStyle('a1:aa'.$sheet->getHighestRow());
$sheetstyle->getAlignment()->setWrapText(true);
$sheetstyle->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
$sheetstyle->getFont()->setSize(10);
//$sheet->getRowDimension()->setRowHeight(-1);

$sharedStyle1 = new PHPExcel_Style();
$sharedStyle2 = new PHPExcel_Style();
$sharedStyle3 = new PHPExcel_Style();

$sharedStyle1->applyFromArray(
  array('fill'  => array(
    'type'      => PHPExcel_Style_Fill::FILL_SOLID,
    'color'     => array('argb' => 'FFCCFFCC')
  ),
  'borders' => array(
    'bottom'    => array('style' => PHPExcel_Style_Border::BORDER_NONE),
    'right'     => array('style' => PHPExcel_Style_Border::BORDER_NONE),
    'top'     => array('style' => PHPExcel_Style_Border::BORDER_NONE),
    'left'    => array('style' => PHPExcel_Style_Border::BORDER_NONE)
  )
));

$sharedStyle2->applyFromArray(
  array('fill'  => array(
    'type'      => PHPExcel_Style_Fill::FILL_SOLID,
    'color'     => array('argb' => 'FFFFFF00')
  ),
  'borders' => array(
    'bottom'    => array('style' => PHPExcel_Style_Border::BORDER_NONE),
    'right'     => array('style' => PHPExcel_Style_Border::BORDER_NONE),
    'top'     => array('style' => PHPExcel_Style_Border::BORDER_NONE),
    'left'    => array('style' => PHPExcel_Style_Border::BORDER_NONE)
  )
));
$sharedStyle3->applyFromArray(
  array('fill'  => array(
    'type'      => PHPExcel_Style_Fill::FILL_SOLID,
    'color'     => array('argb' => 'EEEEEEEE')
  ),
  'borders' => array(
    'bottom'    => array('style' => PHPExcel_Style_Border::BORDER_NONE),
    'right'     => array('style' => PHPExcel_Style_Border::BORDER_NONE),
    'top'     => array('style' => PHPExcel_Style_Border::BORDER_NONE),
    'left'    => array('style' => PHPExcel_Style_Border::BORDER_NONE)
  )
));
$objPHPExcel->getActiveSheet()->setSharedStyle($sharedStyle1, "A1:AA1");

for($xxx = 1; $xxx <= $sheet->getHighestRow(); $xxx++){
  $sheet->getRowDimension($xxx)->setRowHeight(-1);
}

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="activity_export-'.date('Y-m-d').'.xlsx"');
        header('Cache-Control: max-age=0');
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
        $objWriter->save('php://output');
          throw new sfStopException();

If someone can spot the problem I would be grateful!

1

There are 1 answers

0
Mark Baker On

You need to set cells to wrap text - or they must contain a newline ("\n") character - otherwise MS Excel will display the content on a single line

$objPHPExcel->getActiveSheet()
    ->getStyle('A3:A6')
    ->getAlignment()
    ->setWrapText(true);

You probably want to apply this to your shared styles, otherwise you're likely to override your sheet style