Excel Intermediate Function Implementation

Question

Excel provides the “Total” option from the menu Data → Outline → Subtotal . It automatically creates subavs and the ability to collapse data. The image below shows how an action converts a sheet.

And this is exactly what I need to do through the POI. I know how to set a subtotal function to a cell so that I can calculate subtotals myself. But how to enable this folding on the left border?

I realized that there is a groupRow() method, but these nested groups do not work as they should. If I use the following code, I get only two groups. One large (1-7) and (1-3). Group (5-7) is absent and changing the order of calls is not affected.

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

Answer 1

I am using a rather old version of POI, but this is how I did it:
I also needed a few nested groups, so I had a model for lines in which the indentation level was also saved (it was a tree, so the indentation was implicit). I crossed the model with a visitor to get the beginning and ending line numbers of the group. Then called HSSFSheet.groupRow subsequently for each group. If I remember correctly, the order of group calls is important.

Answer 2

I think this is exactly what you are looking for:

http://www.mysamplecode.com/2011/10/apache-poi-excel-row-group-collapse.html

if you use subtotal(9,<range>) instead of sum(<range>) , you can execute nested groups since subtotal ignores the cell with the subtotal in its range