phpexcelphpspreadsheet

PHPSpreadsheet collapsing for grouped rows


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


Solution

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