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!
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 lineYou probably want to apply this to your shared styles, otherwise you're likely to override your sheet style