Search code examples
c#spreadsheetgear

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


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

Solution

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