The issue is caused by a ClosedXML implementation bug.
It can easily be reproduced by using the following snippet (a modified version of their Pivot Tables example) and opening the resulting file in Excel:
static void CreateTestPivotTables(string filePath)
{
var wb = new XLWorkbook();
var wsData = wb.Worksheets.Add("Data");
wsData.Cell("A1").Value = "Category";
wsData.Cell("A2").Value = "A";
wsData.Cell("A3").Value = "B";
wsData.Cell("A4").Value = "B";
wsData.Cell("B1").Value = "Number";
wsData.Cell("B2").Value = 100;
wsData.Cell("B3").Value = 150;
wsData.Cell("B4").Value = 75;
var source = wsData.Range("A1:B4");
for (int i = 1; i <= 2; i++)
{
var name = "PT" + i;
var wsPT = wb.Worksheets.Add(name);
var pt = wsPT.PivotTables.AddNew(name, wsPT.Cell("A1"), source);
pt.RowLabels.Add("Category");
pt.Values.Add("Number")
.ShowAsPctFrom("Category").And("A")
.NumberFormat.Format = "0%";
}
wb.SaveAs(filePath);
}
The bug is located in XLWorkbook_Save.cs - GeneratePivotTables method:
private static void GeneratePivotTables(WorkbookPart workbookPart, WorksheetPart worksheetPart,
XLWorksheet xlWorksheet,
SaveContext context)
{
foreach (var pt in xlWorksheet.PivotTables)
{
var ptCdp = context.RelIdGenerator.GetNext(RelType.Workbook);
var pivotTableCacheDefinitionPart = workbookPart.AddNewPart<PivotTableCacheDefinitionPart>(ptCdp);
GeneratePivotTableCacheDefinitionPartContent(pivotTableCacheDefinitionPart, pt);
var pivotCaches = new PivotCaches();
var pivotCache = new PivotCache {CacheId = 0U, Id = ptCdp};
pivotCaches.AppendChild(pivotCache);
workbookPart.Workbook.AppendChild(pivotCaches);
var pivotTablePart =
worksheetPart.AddNewPart<PivotTablePart>(context.RelIdGenerator.GetNext(RelType.Workbook));
GeneratePivotTablePartContent(pivotTablePart, pt);
pivotTablePart.AddPart(pivotTableCacheDefinitionPart, context.RelIdGenerator.GetNext(RelType.Workbook));
}
}
by the line workbookPart.Workbook.AppendChild(pivotCaches); which adds multiple PivotCaches to workbookPart.Workbook while it's allowed to contain 0 or 1.
With that being said, the only way to fix it is inside the source code by modifying the above method as follows:
private static void GeneratePivotTables(WorkbookPart workbookPart, WorksheetPart worksheetPart,
XLWorksheet xlWorksheet,
SaveContext context)
{
var pivotCaches = workbookPart.Workbook.GetFirstChild<PivotCaches>();
foreach (var pt in xlWorksheet.PivotTables)
{
var ptCdp = context.RelIdGenerator.GetNext(RelType.Workbook);
var pivotTableCacheDefinitionPart = workbookPart.AddNewPart<PivotTableCacheDefinitionPart>(ptCdp);
GeneratePivotTableCacheDefinitionPartContent(pivotTableCacheDefinitionPart, pt);
if (pivotCaches == null)
workbookPart.Workbook.AppendChild(pivotCaches = new PivotCaches());
var pivotCache = new PivotCache { CacheId = (uint)pivotCaches.Count(), Id = ptCdp };
pivotCaches.AppendChild(pivotCache);
var pivotTablePart =
worksheetPart.AddNewPart<PivotTablePart>(context.RelIdGenerator.GetNext(RelType.Workbook));
GeneratePivotTablePartContent(pivotTablePart, pt);
pivotTablePart.PivotTableDefinition.CacheId = pivotCache.CacheId;
pivotTablePart.AddPart(pivotTableCacheDefinitionPart, context.RelIdGenerator.GetNext(RelType.Workbook));
}
}
Update: The good news are that my post triggered a ClosedXML source repository fix by Francois Botha (also credits to petelids who brought it up there), so you can take the code from there until their next release which hopefully will include it.
