ClosedXML / ClosedXML/ClosedXML

Pivot table options have no effect

Open
#614 8 comments 0 reactions 0 assignees View on GitHub
enhancement
Dominant language
C#
Stars
5.7k
Forks
933
Avg merge
12h 14m
Merged PRs (30d)
1

Description

**Do you want to request a *feature* or report a *bug*?**
- [x] Bug
- [ ] Feature

**Version of ClosedXML**

0.90, 0.91 beta

**What is the current behavior?**

Pivot table options have no effect.

MergeAndCenterWithLabels
ShowPropertiesInTooltips
ShowValuesRow
ShowEmptyItemsOnColumns
ShowEmptyItemsOnRows
AutoFitColumns (should this control the option "Autofit column widths on update"? It currently does not seem to do anything)
FilteredItemsInSubtotals (should this control the option "Subtotal filtered page items"? It currently does not seem to do anything)
SortFieldsAtoZ (should this toggle "Display -> Field list -> Sort A to Z" option? It currently does not seem to do anything)
PrintExpandCollapsedButtons
PrintTitles
RepeatRowLabels
RefreshDataOnOpen
Title
Description
EnableCellEditing (this breaks the spreadsheet completely when setting to true)

**What is the expected behavior or new feature?**

Pivot table options should set the options correctly

**Did this work in previous versions of our tool? Which versions?**

No

**Code to reproduce problem:**
```c#

using System;

using ClosedXML.Excel;

class Program
{
static void Main(string[] args)
{
ClosedXMLPivotExample ex = new ClosedXMLPivotExample();

ex.Create(@"C:\ClosedXML\PastryPivot.xlsx");

}
}

```

```c#

using System;
using System.Collections.Generic;
using ClosedXML.Excel;

class ClosedXMLPivotExample
{
private class Pastry
{
public Pastry(string name, int numberOfOrders, double quality, string month, DateTime bakeDate)
{
Name = name;
NumberOfOrders = numberOfOrders;
Quality = quality;
Month = month;
BakeDate = bakeDate;
}

public string Name { get; set; }
public int NumberOfOrders { get; set; }
public double Quality { get; set; }
public string Month { get; set; }
public DateTime BakeDate { get; set; }
}

public void Create(String filePath)
{
var pastries = new List
{
new Pastry("Croissant", 150, 60.2, "Apr", new DateTime(2016, 04, 21)),
new Pastry("Croissant", 250, 50.42, "May", new DateTime(2016, 05, 03)),
new Pastry("Croissant", 134, 22.12, "Jun", new DateTime(2016, 06, 24)),
new Pastry("Doughnut", 250, 89.99, "Apr", new DateTime(2017, 04, 23)),
new Pastry("Doughnut", 225, 70, "May", new DateTime(2016, 05, 24)),
new Pastry("Doughnut", 210, 75.33, "Jun", new DateTime(2016, 06, 02)),
new Pastry("Bearclaw", 134, 10.24, "Apr", new DateTime(2016, 04, 27)),
new Pastry("Bearclaw", 184, 33.33, "May", new DateTime(2016, 05, 20)),
new Pastry("Bearclaw", 124, 25, "Jun", new DateTime(2017, 06, 05)),
new Pastry("Danish", 394, -20.24, "Apr", new DateTime(2017, 04, 24)),
new Pastry("Danish", 190, 60, "May", new DateTime(2017, 05, 08)),
new Pastry("Danish", 221, 24.76, "Jun", new DateTime(2016, 06, 21)),

// Deliberately add different casings of same string to ensure pivot table doesn't duplicate it.
new Pastry("Scone", 135, 0, "Apr", new DateTime(2017, 04, 22)),
new Pastry("SconE", 122, 5.19, "May", new DateTime(2017, 05, 03)),
new Pastry("SCONE", 243, 44.2, "Jun", new DateTime(2017, 06, 14)),
};

using (var wb = new XLWorkbook())
{
var sheet = wb.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);
sheet.Columns().AdjustToContents();

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

IXLWorksheet ptSheet;
IXLPivotTable pt;

ptSheet = wb.Worksheets.Add("pvtFilter");

pt = ptSheet.PivotTables.AddNew("pvtFilter", ptSheet.Cell(1, 1), dataRange);
pt.SetMergeAndCenterWithLabels(true);
pt.ShowPropertiesInTooltips(true);
pt.AutoFitColumns(true);
pt.FilteredItemsInSubtotals(true);
pt.ShowValuesRow(false);
pt.ShowEmptyItemsOnColumns(true);
pt.ShowEmptyItemsOnRows(true);
pt.SortFieldsAtoZ(true);
pt.PrintExpandCollapsedButtons(true);
pt.PrintTitles(true);
pt.RepeatRowLabels(true);
pt.RefreshDataOnOpen(false);
pt.Title("This is a title.");
pt.Description("This is a description.");
// below breaks spreadsheet completely
//pt.EnableCellEditing(true);

pt.RowLabels.Add("Month");

pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum);

wb.SaveAs(filePath);
}
}
}

```

Above code results in a spreadsheet with the settings unchanged from the default.

Contributor guide

Open the contributing guide

Research direction

Start with the supplied C# reproduction, focusing on IXLPivotTable, PivotTables.AddNew, and each option setter listed in the example. Compare the generated workbook's pivot settings with the requested values; done means the options persist correctly and enabling EnableCellEditing no longer corrupts the spreadsheet.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp
Domain
tooling
Issue type
Bug
Difficulty
5/5
Estimated time
Over a week
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.