This project has moved. For the latest updates, please go here.

Issue with Pivot Tables

Jun 24, 2015 at 8:41 PM
I am unable to create a Pivot table with ReportFilters. Once I add a report filter, I get an error trying to open the excel file. To reproduce, I used the Pivot example from the site. The only change I made was changing "Month" from a column to a report filter. The code is below:
Can you tell me if I am doing anything wrong?
      var pastries = new List<Pastry>
        {
           new Pastry("Croissant", 150, "Apr"),
            new Pastry("Croissant", 250, "May"),
            new Pastry("Croissant", 134, "June"),
            new Pastry("Doughnut", 250, "Apr"),
            new Pastry("Doughnut", 225, "May"),
            new Pastry("Doughnut", 210, "June"),
            new Pastry("Bearclaw", 134, "Apr"),
            new Pastry("Bearclaw", 184, "May"),
            new Pastry("Bearclaw", 124, "June"),
            new Pastry("Danish", 394, "Apr"),
            new Pastry("Danish", 190, "May"),
            new Pastry("Danish", 221, "June"),
            new Pastry("Scone", 135, "Apr"),
            new Pastry("Scone", 122, "May"),
            new Pastry("Scone", 243, "June")
        };


        var workbook = new XLWorkbook();
        var sheet = workbook.Worksheets.Add("PastrySalesData");

        // Insert our list of pastry data into the "PastrySalesData" sheet at cell 1,1
        var source = sheet.Cell(1, 1).InsertTable(pastries, "PastrySalesData", true);

        // Create a range that includes our table, including the header row
        var range = source.DataRange;
        var header = sheet.Range(1, 1, 1, 3);
        var dataRange = sheet.Range(header.FirstCell(), range.LastCell());

        // Add a new sheet for our pivot table
        var ptSheet = workbook.Worksheets.Add("PivotTable");

        // Create the pivot table, using the data from the "PastrySalesData" table
        var pt = ptSheet.PivotTables.AddNew("PivotTable", ptSheet.Cell(1, 1), dataRange);

        // The rows in our pivot table will be the names of the pastries
        pt.RowLabels.Add("Name");

        // The columns will be the months
        pt.ReportFilters.Add("Month");

        // The values in our table will come from the "NumberOfOrders" field
        // The default calculation setting is a total of each row/column
        pt.Values.Add("NumberOfOrders");


        workbook.SaveAs("c:\\temp\\PastrySample.xlsx");
Editor
Jul 18, 2015 at 12:17 AM
I finally had a chance to dive into this (I wrote the example this is derived from); it looks like the problem is that ClosedXML doesn't update the location of the pivot table when you add a report filter. The filter gets added at cell A1 on the PivotTable sheet, but the pt object is still tracking its location as starting at A1.

If you change the line
var pt = ptSheet.PivotTables.AddNew("PivotTable", ptSheet.Cell(1, 1), dataRange);

to

var pt = ptSheet.PivotTables.AddNew("PivotTable", ptSheet.Cell(3, 1), dataRange);

your example will work correctly. Unfortunately, I don't see an easy way to modify the ClosedXML code to adjust the pivot table's location automatically when the report filter is added. Perhaps someone more involved with the project would know how to address this. You might want to open an issue.