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

1
votes

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: ClosedXML produced excel worksheet with pivot table

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: Desired output - every row is collapsed.

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;
2
c#excelclosedxml
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 for another example. - Raidri

2 Answers

1
votes

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

0
votes

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));