Group/Outline with SUBTOTAL 2 levels

Topics: Developer Forum
Aug 2, 2012 at 9:41 AM

I'm working on grouping/outlining rows with phpexcel. Below is a representation of my excel sheet. For each unique value in the first column, there has to be a total line. And each block has to be collapsable. All the rows together have to be collapsable to a grand total.

A 10
A 50
A 60
Total 120
B 10
B 30
Total 40
Grand Total 160

I added a Total line for each block like so:

$sheet->setCellValue("A$row", "Total " . $previous);
$sheet->setCellValue("B$row", "=SUBTOTAL(9,B$startrow:B$endrow)"); //9 means sum

Where $startrow en $endrow are the start respectively the end of a block. I then created the outline/group:

foreach(range($startrow,$endrow) as $rrow) {

This works well. I now have the possibility to collapse each individual block to just the total rows.

Inserting a Grand Total line isn't all that hard either. I simply add:


Now, to add the outline on all the rows in total I tried:

foreach(range(1,$lastrow) as $rrow) {
But this doesn't work. My first outline disappears. I am only able to collapse rows 1 to the last one.

If I change the outlinelevel to 2 I simply get a second line that collapses rows 1 tot the last one.

Am I doing something wrong? Or is this simply not possible for now? The documentation is a bit blurry about this.

Thanks for the help


Dec 19, 2013 at 9:14 PM
Set the outer level FIRST to 1. Then your second set of groupings to 2.

You first loop could look like this:
foreach(range($startrow,$endrow) as $rrow) {