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).
