CODEWITHSUNDEEP
c#excelpivot-tableclosedxml
George Chelariu
George Chelariu

Reputation: 41

ClosedXML PivotTable ReportFilter multiple values

I am working on a piece of code to generate a pivot table in Excel.

This is the code:

using (XL.XLWorkbook workbook = new XL.XLWorkbook(sourceFile))
        {
            var outSheet = workbook.Worksheets.Add("output table");
            outSheet.Cell(1, 1).InsertTable(dt, "out table", true);

            var datarange = outSheet.RangeUsed();

            var pivotSheet = workbook.Worksheets.Add("PivotTable");
            var pivotTable = pivotSheet.PivotTables.AddNew("Pivot Table", pivotSheet.Cell(3, 1), datarange);

            pivotTable.ReportFilters.Add("Filter1");
            pivotTable.ReportFilters.Add("Filter2");
            pivotTable.RowLabels.Add("RLabel");
            pivotTable.ColumnLabels.Add("CLabel");
            pivotTable.Values.Add("Value").SummaryFormula = XL.XLPivotSummary.Sum;

            workbook.SaveAs(@"C:\Temp\Test.xlsx");
        }

How would I go about to filter the values in "Filter1"?

For example, selecting only the values for "Unknown" and "Gcom".

In Excel the Pivot filter looks like this:

Excel Pivot Table Report Filter

I have checked all the ClosedXML documentation for pivots, the ReportFilters functionality is not mentioned.

Source code wiki example

Please advise, is this functionality even available?

Any advice/help is much appreciated.

Upvotes: 0

Views: 1498

Answers (1)

DesertRat
DesertRat

Reputation: 11

Not sure when the functionality was added, but I got it to work with the following additions to your code:

using (XL.XLWorkbook workbook = new XL.XLWorkbook(sourceFile))
    {
        var outSheet = workbook.Worksheets.Add("output table");
        outSheet.Cell(1, 1).InsertTable(dt, "out table", true);

        var datarange = outSheet.RangeUsed();

        var pivotSheet = workbook.Worksheets.Add("PivotTable");
        var pivotTable = pivotSheet.PivotTables.AddNew("Pivot Table", pivotSheet.Cell(3, 1), datarange);

        // I was not sure how to retrieve the filter after adding, but found Add() returns it for you.
        var filter1 = pivotTable.ReportFilters.Add("Filter1");

        // Now add your filter selection.
        filter1.AddSelectedValue("Unknown");
        filter1.AddSelectedValue("GCom");

        pivotTable.ReportFilters.Add("Filter2");
        pivotTable.RowLabels.Add("RLabel");
        pivotTable.ColumnLabels.Add("CLabel");
        pivotTable.Values.Add("Value").SummaryFormula = XL.XLPivotSummary.Sum;

        workbook.SaveAs(@"C:\Temp\Test.xlsx");
    }

Upvotes: 1

Related Questions