Kai Kai - 20 days ago 5
Java Question

Implement Subtotal feature of Excel

Excel provides the Subtotal option from the Menu

Data
->
Outline
->
Subtotal
. It creates automatically the sub-sums and the possibility to fold the data. The image below demonstrates how the action transforms the sheet.

enter image description here

And this is exactly what I need to do via POI. I know how to set a subtotal function into a cell so I could calculate the intermediate sums by myself. But how do I enable this folding on the left border?

I realised there is the
groupRow()
method but those nested groups doesn't work like they should. If I use the following code I only get two groups. One large (1-7) and (1-3). The group (5-7) is missing and changing the order of the calls has no effect.

sheet.groupRow(1, 7);
sheet.groupRow(1, 3);
sheet.groupRow(5, 7);

Answer

I use a quite old verion of POI but this is how I did it:
I also needed multiple nested groups so I had a model for the rows where the indent level was stored as well (it was a tree so the indent was implicit). I traversed the model with a visitor to get the group start and end row numbers. Then called HSSFSheet.groupRow subsequently for each group. If I remember correctly, the order of the group calls is important.