ClosedXML Outline

Question

I am trying to create a Group in the exported Excel Workbook using OpenXML.

My source data table looks like this:

Row     State   Product Sales
1       NY      A       100
2       NY      A       200
3       NY      B       300
4       CA      A       100
5       CA      A       200
6       CA      B       300

I would like to create an outline by State and then Product with a subtotal on each group

I tried

 ws.Outline.SummaryVLocation = XLOutlineSummaryVLocation.Top;
 ws.Rows(1, 3).Group(); // Create an outline (level 2) for rows 1-4
 ws.Rows(4, 6).Group();

But it's not giving me what I want, and I don't see an option to add the subtotals.

How can I achieve this?

Raidri Raidri · Accepted Answer · 2014-09-11T09:20:30

The code example in the documentation which you use is either outdated or just wrong.
If you want to group rows 2 to 4 you need to use the code ws.Rows(3, 4).Group(); (see picture). This is consistent with Excel itself, there you have to select only rows 3 and 4 before clicking the group button to get the same result.

enter image description here

When you try to group rows 1 to 3 like in your code you group them all under row 0 which leads to errors since there is no row 0. You can control this behaviour to some extend with the XLOutlineSummaryVLocation property. If you use Bottom instead of top you use the top two rows to group rows 2 to 4: ws.Rows(2, 3).Group();

With all this said two more points:

You need to use Excel row numbers not the numbers in your column "Row".
All this grouping and collapsing is only for display purposes. To sum up the sales numbers you have to use the subtotal functions in Excel (which I find rather confusing and unhelpful in this case) or add columns and results directly in C#.

Using this code should lead to your desired result (see picture below):

ws.Outline.SummaryVLocation = XLOutlineSummaryVLocation.Top;
ws.Cell(1, 5).SetValue("Product subtotals");
ws.Cell(1, 6).SetValue("State subtotals");

ws.Rows(3, 4).Group();                      // group rows 2 to 4 (state NY), outline level 1
ws.Cell(2, 6).SetFormulaA1("=SUM(D2:D4)");  // subtotal for all NY sales
ws.Row(3).Group();                          // group rows 2 and 3 (product A), outline level 2
ws.Cell(2, 5).SetFormulaA1("=SUM(D2:D3)");  // subtotal for all NY, product A sales
ws.Cell(4, 5).SetFormulaA1("=SUM(D4)");     // subtotal for all NY, product B sales

ws.Rows(6, 7).Group();                      // group rows 5 to 7 (state CA), outline level 1
ws.Row(6).Group();                          // group rows 5 and 6 (product A), outline level 2
ws.CollapseRows(2);                         // collapse group level 2 (products)

enter image description here

ClosedXML Outline

1 Answers