for Loop for Grouping in PHPSpreadsheet

[mongoose00318]

I'm trying to dynamically group a range of rows using PHPSpreadsheet and having a lot of trouble thinking through the logic. My code so far looks like this:

$worksheet = $spreadsheet->getActiveSheet();
// Get the highest row number and column letter referenced in the worksheet
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
// Increment the highest column letter
$highestColumn++;

$job_num_chk = '';
$grp_start_row = '';

//format the inserted records
for ($row = 2; $row <= $highestRow; ++$row) {
	//$spreadsheet->getActiveSheet()->getRowDimension($row)->setRowHeight(53);
	for ($col = 'A'; $col != $highestColumn; ++$col) {
		
		$spreadsheet->getActiveSheet()->getStyle($col . $row)->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
		$spreadsheet->getActiveSheet()->getStyle($col . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
		
		if ($col == 'B') {
			$cur_job_num = $spreadsheet->getActiveSheet()->getCell($col . $row)->getValue(); //get current job number
			
			//check if job number is different
			if ( $job_num_chk !== $cur_job_num ) {
				$spreadsheet->getActiveSheet()->insertNewRowBefore($row, 1); //insert dividing row
				
				//set dividing row to black
				$spreadsheet->getActiveSheet()->getStyle('A'.$row.':N'.$row)->getFill()
					->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
					->getStartColor()->setARGB('000000');
				
				$job_num_chk = $cur_job_num; //update job number
			}
			
		}

	}
	
}

This line: 

$grp_start_row = '';

Is where I'm hitting a mental wall...here's what the spreadsheet looks like so far:

I want to be able to group the rows 101-105 in that example like below:

I need to be able to determine where the start row is and the end row is in order to use the grouping feature (https://phpspreadsheet.readthedocs.io/en/latest/topics/recipes/#groupoutline-a-row)

Thanks everyone.

[mongoose00318]

Well..I've tried this...but it isn't quite working...

$worksheet = $spreadsheet->getActiveSheet();
// Get the highest row number and column letter referenced in the worksheet
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
// Increment the highest column letter
$highestColumn++;

$job_num_chk = '';
$groupings = [];

//format the inserted records
for ($row = 2; $row <= $highestRow; ++$row) {
	//$spreadsheet->getActiveSheet()->getRowDimension($row)->setRowHeight(53);
	for ($col = 'A'; $col != $highestColumn; ++$col) {
		
		$spreadsheet->getActiveSheet()->getStyle($col . $row)->getAlignment()->setVertical(\PhpOffice\PhpSpreadsheet\Style\Alignment::VERTICAL_TOP);
		$spreadsheet->getActiveSheet()->getStyle($col . $row)->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);
		
		if ($col == 'B') {
			$cur_job_num = $spreadsheet->getActiveSheet()->getCell($col . $row)->getValue(); //get current job number
			
			//check if job number is different
			if ( $job_num_chk !== $cur_job_num ) {

				/*
				$spreadsheet->getActiveSheet()->insertNewRowBefore($row, 1); //insert dividing row
				
				//set dividing row to black
				$spreadsheet->getActiveSheet()->getStyle('A'.$row.':N'.$row)->getFill()
					->setFillType(\PhpOffice\PhpSpreadsheet\Style\Fill::FILL_SOLID)
					->getStartColor()->setARGB('000000');
				*/
				
				$job_num_chk = $cur_job_num; //update job number
			}
			
			$row_m = $row-1;
			$row_a = $row+1;
			
			$above = $spreadsheet->getActiveSheet()->getCell($col . $row_m)->getValue();
			$below = $spreadsheet->getActiveSheet()->getCell($col . $row_a)->getValue();

			if ( $cur_job_num !== $above ) {
				$groupings[$cur_job_num]['start'] = $row;
			}
			elseif ( $cur_job_num !== $below ) {
				$groupings[$cur_job_num]['end'] = $row;
			}
			
		}

	}
	
}

//setup row groupings
foreach ( $groupings as $g ) {
	$end = isset($g['end'] ) ? $g['end'] : $g['start'];
	for ( $row = $g['start']; $row <= $end; ++$row ) {
		$spreadsheet->getActiveSheet()
			->getRowDimension($row)
				->setOutlineLevel(1)
				->setVisible(true)
				->setCollapsed(false);
	}
}

It seems to have made two large groups:

But I've looked at my array and it seems to be correct:

Array
(
    [22005572] => Array
        (
            [start] => 2
            [end] => 3
        )

    [22039882] => Array
        (
            [start] => 4
        )

    [23521431] => Array
        (
            [start] => 5
            [end] => 6
        )

    [23616901] => Array
        (
            [start] => 7
        )

    [23617551] => Array
        (
            [start] => 8
        )

    [23617561] => Array
        (
            [start] => 9
        )

    [23617851] => Array
        (
            [start] => 10
            [end] => 14
        )

    [23618511] => Array
        (
            [start] => 15
        )

    [23618761] => Array
        (
            [start] => 16
        )

    [22016952] => Array
        (
            [start] => 17
            [end] => 18
        )

    [22017812] => Array
        (
            [start] => 19
            [end] => 20
        )

    [22023852] => Array
        (
            [start] => 21
            [end] => 25
        )

    [22023892] => Array
        (
            [start] => 26
            [end] => 28
        )

    [22027752] => Array
        (
            [start] => 29
            [end] => 30
        )

    [22031712] => Array
        (
            [start] => 31
            [end] => 32
        )

    [22032642] => Array
        (
            [start] => 33
            [end] => 34
        )

    [22036842] => Array
        (
            [start] => 35
        )

    [22040842] => Array
        (
            [start] => 36
        )

    [22961571] => Array
        (
            [start] => 37
        )

    [22978751] => Array
        (
            [start] => 38
        )

    [22978831] => Array
        (
            [start] => 39
            [end] => 40
        )

    [22992301] => Array
        (
            [start] => 41
        )

    [22992321] => Array
        (
            [start] => 42
        )

    [22992331] => Array
        (
            [start] => 43
        )

    [23585641] => Array
        (
            [start] => 44
        )

    [22010852] => Array
        (
            [start] => 45
            [end] => 46
        )

    [22007412] => Array
        (
            [start] => 47
            [end] => 48
        )

    [22031192] => Array
        (
            [start] => 49
            [end] => 54
        )

    [22039462] => Array
        (
            [start] => 55
        )

    [22042092] => Array
        (
            [start] => 56
        )

    [23584291] => Array
        (
            [start] => 57
            [end] => 58
        )

    [23615841] => Array
        (
            [start] => 59
        )

    [23615921] => Array
        (
            [start] => 60
        )

    [23616201] => Array
        (
            [start] => 61
        )

    [23616221] => Array
        (
            [start] => 62
        )

    [60266410] => Array
        (
            [start] => 63
        )

    [29100510] => Array
        (
            [start] => 139
            [end] => 68
        )

    [25294050] => Array
        (
            [start] => 69
            [end] => 74
        )

    [25294060] => Array
        (
            [start] => 75
            [end] => 77
        )

    [25294070] => Array
        (
            [start] => 78
            [end] => 83
        )

    [25294080] => Array
        (
            [start] => 84
            [end] => 89
        )

    [25294680] => Array
        (
            [start] => 90
            [end] => 93
        )

    [25294690] => Array
        (
            [start] => 94
            [end] => 99
        )

    [23616371] => Array
        (
            [start] => 100
        )

    [23616381] => Array
        (
            [start] => 101
        )

    [23616391] => Array
        (
            [start] => 102
        )

    [23616491] => Array
        (
            [start] => 103
            [end] => 104
        )

    [23616731] => Array
        (
            [start] => 105
            [end] => 106
        )

    [23616741] => Array
        (
            [start] => 107
        )

    [23551851] => Array
        (
            [start] => 108
        )

    [22003322] => Array
        (
            [start] => 109
        )

    [22004302] => Array
        (
            [start] => 110
            [end] => 111
        )

    [22011072] => Array
        (
            [start] => 112
        )

    [22015832] => Array
        (
            [start] => 113
            [end] => 117
        )

    [22024612] => Array
        (
            [start] => 118
        )

    [22026012] => Array
        (
            [start] => 119
        )

    [22027702] => Array
        (
            [start] => 120
            [end] => 121
        )

    [22028302] => Array
        (
            [start] => 122
        )

    [22028382] => Array
        (
            [start] => 123
        )

    [22031532] => Array
        (
            [start] => 124
            [end] => 126
        )

    [22973641] => Array
        (
            [start] => 127
        )

    [22979331] => Array
        (
            [start] => 128
        )

    [29089920] => Array
        (
            [start] => 129
            [end] => 133
        )

    [23587361] => Array
        (
            [start] => 134
            [end] => 136
        )

    [23607111] => Array
        (
            [start] => 137
        )

    [23615581] => Array
        (
            [start] => 138
        )

    [23616961] => Array
        (
            [start] => 140
            [end] => 141
        )

    [22012662] => Array
        (
            [start] => 142
        )

    [22018892] => Array
        (
            [start] => 143
            [end] => 146
        )

    [22022802] => Array
        (
            [start] => 147
        )

    [22027922] => Array
        (
            [start] => 148
        )

    [22033592] => Array
        (
            [start] => 149
        )

)

 

[mongoose00318]

I've tried adjusting the setOutlineLevel but it is just grouping all of the rows together as one group right now. From my understanding the setOutlineLevel is for nesting groups anyhow...not sure what is going wrong because this code works fine:

for ($row = 2; $row <= 10; ++$row) {
    $spreadsheet->getActiveSheet()
        ->getRowDimension($row)
            ->setOutlineLevel(1)
            ->setVisible(true)
            ->setCollapsed(false);
}

for ($row = 12; $row <= 20; ++$row) {
    $spreadsheet->getActiveSheet()
        ->getRowDimension($row)
            ->setOutlineLevel(1)
            ->setVisible(true)
            ->setCollapsed(false);
}

I can't spot the difference between what I'm doing with:

//setup row groupings
foreach ( $groupings as $g ) {
	$end = isset($g['end'] ) ? $g['end'] : $g['start'];
	for ( $row = $g['start']; $row <= $end; ++$row ) {
		$spreadsheet->getActiveSheet()
			->getRowDimension($row)
				->setOutlineLevel(1)
				->setVisible(true)
				->setCollapsed(false);

	}
}

and the code above it...

[mongoose00318]

Well..I figured it out...here is the solution for others that may have this issue...

You need to add blank rows between the groups you want..otherwise it just sees it as one continuous group...