I tried to create 3 outline levels in Excel file (with PHPSpreadsheet), which is being filled this way:
$worksheet=$spreadsheet->getActiveSheet();
$colCount=$stmt->columnCount();
$rowNo=$worksheet->getHighestRow() + 1;
$object = (object) $arrFinal;
foreach ($object as $row)
{
$colNo = 0;
foreach ($row as $element)
{
if ($colNo < 6)
{
//nothing to do here now
}
else
{
$worksheet->getRowDimension($rowNo)->setOutlineLevel(0)->setVisible(false)->setCollapsed(true);
if (($colNo == 28) && ($row['AcceptDate'] != ''))
{
$value = date('d.m.Y', strtotime(substr($row['AcceptDate'],0,10)));
}
else
$value=$element;
if (is_null($value)) $value=''; $worksheet->setCellValueByColumnAndRow($colNo-5,$rowNo,$value);
if ($row['Level'] == 2)
{
$worksheet->getRowDimension($rowNo)->setOutlineLevel(1)->setVisible(false)->setCollapsed(true);
$worksheet->getStyle('A'.$rowNo)->getNumberFormat()->setFormatCode(\PhpOffice\PhpSpreadsheet\Style\NumberFormat::FORMAT_NUMBER_COMMA_SEPARATED2);
}
if ($row['Level'] == 2 || $row['Level'] == 1)
{
$worksheet->getStyle('A'.$rowNo)->getAlignment()->applyFromArray( [ 'horizontal' => \PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER] );
}
if ($row['Level'] == 3)
{
$worksheet->getRowDimension($rowNo)->setOutlineLevel(2)->setVisible(false)->setCollapsed(true);
$worksheet->mergeCells('A'.$rowNo.':O'.$rowNo);
$worksheet->mergeCells('Q'.$rowNo.':Z'.$rowNo);
}
}
$colNo++;
}
$rowNo++;
}
$spreadsheet->getActiveSheet()->setAutoFilter(sprintf("A4:%s%d",Coordinate::stringFromColumnIndex($colCount-6),$rowNo-1));
Everything is working OK except one thing - these groups of rows are not hidden and collapsed, when I check the file after filling with data. However, if I just do this:
$worksheet->getRowDimension(1)->setOutlineLevel(0)->setVisible(false)->setCollapsed(true);
right after
$spreadsheet=IOFactory::createReader('Xlsx')->load('templates/FB.xlsx');
it works as intented. What am I doing wrong here? Tried to search for similiar questions, but didn't find helpful explanation for such cases. Maybe missed something.
P.S. Added some code to original test case (setAutoFilter).
Well, my bad, didn't realize that I had setAutoFilter
after data filling, which was overriding the results of setVisible(false)->setCollapsed(true)
usage in foreach
, setting all rows visible again. To avoid this behaviour, I just placed filtering
$spreadsheet->getActiveSheet()->setAutoFilter(sprintf("A4:%s%d",Coordinate::stringFromColumnIndex($colCount-6),$rowNo-1));
before foreach
.