How do I create multiple groups in a spreadsheet and have them collapsed when the spreadsheet is opened?

Question

I am attempting to create a spreadsheet with multiple summary rows, all of which are collapsed when the workbook is opened. I've tried using SummaryRows(), but it collapses the entire spreadsheet into a single group controlled by a single button. My code below will set all of the groups correctly, but they're all expanded by default.

// initialize a row indexer
var currentRow = 1;

// iterate through a collection containing several groups
for (var x in arr_x)
{
    // skip a row for the group header
    currentRow += 1;

    // set start row for current group
    var startRow = currentRow;

    // iterate through the current group
    foreach (var y in x.arr_y)
    {
        // do spreadsheet things
        currentRow++;
    }

    // get last row of current group
    var endRow = currentRow - 1;

    // group the rows
    sheet.Cells[$"{startRow}:{endRow}"].Rows.Group();
}

// put expando at the top of the groups
sheet.Outline.SummaryRow = SummaryRow.Above;

Ultimately, I'd like to see a structure like below:

+ | Group 1
+ | Group 2
+ | Group 3

And when expanded:

- | Group 1
  | Item 1
  | Item 2
  | Item 3
+ | Group 2
+ | Group 3

score 1 · Accepted Answer · answered Nov 02 '22 at 23:24

It looks like you might be trying to group rows that are immediately adjacent to each other? If this is the case, then it would be the expected behavior that each of these groups with the same outline level will get combined / coalesced into one single group. Microsoft Excel behaves in the same way. For instance, try using Excel to group Rows 1:5, and then group Rows 6:10. You will find that you now have a single group consisting of Rows 1:10.

This is just the way that the grouping feature works. If you need separated groups, you will need to ensure that, for a given outline level, there is at least 1 row of separation between the groups. For instance, in my above example you could group Rows 1:5, leave Row 6 alone, and group Rows 7:11.

Yikes, my pseudocode has a bug in it. I'm already doing what you suggest, I'm not attempting to group immediately adjacent rows. I'll fix it to reflect how my code is actually behaving. — S. Davenport, Nov 03 '22 at 14:16
And after reviewing my actual code, I discovered it had the same bug. I was defeated by a missing post-increment. — S. Davenport, Nov 03 '22 at 15:36

score 1 · Answer 2 · answered Nov 03 '22 at 06:02

As per Tim's answer groups require a "header" or a "total" row to be distinct from each other. That can be achieved with a small change to your code:

// set start row for current group
var startRow = currentRow + 1;

This will create a header row for each iteration of x

You should also set the SummaryRow switch to above so that expand / collapse link is aligned with the header row.

Outline.ShowLevels determines whether the groups are open or closed when the file opens

// Collapse all region detail levels.
sheet.Outline.SummaryRow = SummaryRow.Above;

dataSheet.Outline.ShowLevels(1, 0);

How do I create multiple groups in a spreadsheet and have them collapsed when the spreadsheet is opened?

2 Answers2