ClosedXML and C#: How to collapse rows by Default?

Question

I am trying to write code which produces excel report with pivot table. For accomplishing this task I am using ClosedXML library. The output looks like this:

The problem is that I have to get all groups of data collapsed by default, i.e. in the output I should see the following:

In other words, my output should contain collapsed rows and only summary should be displayed. How can I achieve this in code? Which method should I use?

        pt.ShowRowStripes = true;
        secondWorksheet.FirstRow().Hide();
        secondWorksheet.TabActive = true;
        secondWorksheet.CollapseRows(1);
        secondWorksheet.Rows().Collapse();
        pt.EnableShowDetails = false;
        pt.ShowValuesRow = false;
        secondWorksheet.PageSetup.ShowGridlines = true;
        secondWorksheet.ShowGridLines = true;
        workbook.PageOptions.ShowGridlines = true;
        secondWorksheet.PivotTables.First().EnableShowDetails = false;

The command `secondWorksheet.CollapseRows(1);` should work and collapse all rows in the groups. Not sure if there are conflicts when working with pivot tables. See [this answer](http://stackoverflow.com/a/25783647/2610249) for another example. — Raidri, Jan 26 '17 at 15:43

score 1 · Accepted Answer · answered Jan 27 '17 at 08:17

1

This is not currently supported by ClosedXML. Pivot tables are still very much work in progress.

answered Jan 27 '17 at 08:17

Francois Botha

4,520
1
34
46

score 0 · Answer 2 · answered Feb 28 '19 at 18:49

Using ClosedXML.Signed version 0.94.2, this worked for me:

IXLPivotTable pivotTable = workbook.Worksheet("SheetContainingPivotTable").PivotTables.First();
pivotTable.ColumnLabels.ToList().ForEach(x => x.SetCollapsed(true));
pivotTable.RowLabels.ToList().ForEach(x => x.SetCollapsed(true));

ClosedXML and C#: How to collapse rows by Default?

2 Answers2