Search code examples
javaexcelapache-poifoldingsubtotal

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);
Solution

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