ClosedXML - Creating multiple pivot tables

Question

I am trying to export some data to an excel sheet S1 whose data would be shown as Pivoted views in the next two sheets S2 and S3. I am able to create a single pivot and it works perfect. But when I create two pivots, the consequent Excel file renders as corrupt.

By corrupt I mean,

On clicking yes, I get this -

Corrupt

Here is the code I am using to create the pivots -

using XL = ClosedXML.Excel;
...
XL.XLWorkbook wb = new XL.XLWorkbook();
dsData = Session["ExportData"] as DataSet;

var sheet1 = wb.Worksheets.Add("output table");
sheet1.Cell(1, 1).InsertTable(dsData.Tables[0], "output table", true);

// sheet1 is the reference sheet S1
var dataRange = sheet1.RangeUsed();

// First Pivot
XL.IXLWorksheet ptSheet1 = wb.Worksheets.Add("S2");

var pt1 = ptSheet1.PivotTables.AddNew("PivotTable1", ptSheet.Cell(3, 1), dataRange);


pt1.ReportFilters.Add("CX");

pt1.RowLabels.Add("C1");
pt1.RowLabels.Add("C2");
pt1.RowLabels.Add("C3");
pt1.RowLabels.Add("C4");

pt1.ColumnLabels.Add("CL1");
pt1.ColumnLabels.Add("CL2");
pt1.ColumnLabels.Add("CL3");

pt1.Values.Add("V").SummaryFormula = XL.XLPivotSummary.Sum;


// Second Pivot
XL.IXLWorksheet ptSheet2 = wb.Worksheets.Add("S3");

var pt2 = ptSheet2.PivotTables.AddNew("PivotTable2", ptSheet1.Cell(3, 1), dataRange);

pt2.ReportFilters.Add("QQ");

pt2.RowLabels.Add("C1");
pt2.RowLabels.Add("C2");

pt2.ColumnLabels.Add("CL1");
pt2.ColumnLabels.Add("CL2");
pt2.ColumnLabels.Add("CL3");

pt2.Values.Add("V").SummaryFormula = XL.XLPivotSummary.Sum;

C1, C2, C3. C4 and V are the column names in my reference sheet S1.

what do you mean by renders as corrupt? any error(s) occur? please show us the whole code for exporting — jomsk1e, Jul 17 '15 at 07:23
I am experiencing the exact same issue . . did you ever figure out a solution or a workaround? — leora, Oct 29 '16 at 12:32
@leora Sorry, but we did not find a solution. We ended up building the Excel with OpenXML instead. That worked. — Nikhil Girraj, Nov 03 '16 at 06:01
@leora I am no longer working on the requirement / project. I will be happy to accept an answer if anyone can confirm that any of the answers indeed work. — Nikhil Girraj, Nov 03 '16 at 06:05

score 12 · Accepted Answer · edited May 23 '17 at 11:46

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.

As a maintainer of the package, I'd love it if people could submit patches as pull requests. http://github.com/ClosedXML/ClosedXML — Francois Botha, Nov 01 '16 at 09:21
@FrancoisBotha Sure, I've just worked yesterday on the questions and haven't time for contributing (it has some additional requirements I guess, not like just throwing a code snippet), and then the user petelids seems to already did that. — Ivan Stoev, Nov 01 '16 at 09:28
@IvanStoev - I did but I rushed it and made a bit of a hash of it. I've deleted my answer as it wasn't adding any value (and upvoted yours instead). FrancoisBotha has created a better patch. — petelids, Nov 01 '16 at 13:27
@IvanStoev No problem. Nice to see some kind of involvement from the community in any case. — Francois Botha, Nov 01 '16 at 14:16

Brian · Answer 2 · 2016-10-29T14:57:41.877

Try this modification. I made a note where I added an additional line. Also, I think the AddNew() method may have had the wrong worksheet reference? You may have been trying to add a pivot table on top of another one. That may have been the real issue rather than the additional line I added.

using XL = ClosedXML.Excel;
...
XL.XLWorkbook wb = new XL.XLWorkbook();
dsData = Session["ExportData"] as DataSet;
var sheet1 = wb.Worksheets.Add("output table");
sheet1.Cell(1, 1).InsertTable(dsData.Tables[0], "output table", true);

// sheet1 is the reference sheet S1
var dataRange = sheet1.RangeUsed();
PivotCache cache = wb.PivotCaches.Add(dataRange); //---THIS LINE HAS BEEN ADDED---

// First Pivot
XL.IXLWorksheet ptSheet1 = wb.Worksheets.Add("S2");
var pt1 = ptSheet1.PivotTables.AddNew("PivotTable1", ptSheet1.Cell(3, 1), cache);  
//Changed ptSheet.Cell... to ptSheet1.Cell...
pt1.ReportFilters.Add("CX");
pt1.RowLabels.Add("C1");
pt1.RowLabels.Add("C2");
pt1.RowLabels.Add("C3");
pt1.RowLabels.Add("C4");
pt1.ColumnLabels.Add("CL1");
pt1.ColumnLabels.Add("CL2");
pt1.ColumnLabels.Add("CL3");
pt1.Values.Add("V").SummaryFormula = XL.XLPivotSummary.Sum;

// Second Pivot
XL.IXLWorksheet ptSheet2 = wb.Worksheets.Add("S3");
var pt2 = ptSheet2.PivotTables.AddNew("PivotTable2", ptSheet2.Cell(3, 1), cache);  
//Changed ptSheet1.Cell... to ptSheet2.Cell...
pt2.ReportFilters.Add("QQ");
pt2.RowLabels.Add("C1");
pt2.RowLabels.Add("C2");
pt2.ColumnLabels.Add("CL1");
pt2.ColumnLabels.Add("CL2");
pt2.ColumnLabels.Add("CL3");
pt2.Values.Add("V").SummaryFormula = XL.XLPivotSummary.Sum;

ClosedXML - Creating multiple pivot tables

2 Answers2

Linked