ClosedXML - 创建多个数据透视表。

Question

我正在尝试将一些数据导出到一个名为S1的Excel工作表中，这些数据将在接下来的两个工作表S2和S3中显示为数据透视图。我能够创建单个透视表并且它完美地工作。但是当我创建两个透视表时，导出的Excel文件会呈现为损坏。

所谓损坏，我的意思是，

点击“是”后，我得到了这个 -

这里是我用来创建透视表的代码 -

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 和 V 是我参考表格 S1 中的列名。

- Nikhil Girraj

---

你所说的“渲染为损坏”是什么意思？有出现任何错误吗？请展示导出的整个代码。 - jomsk1e

@jomsk1e，我已经更新了问题，请查看。 - Nikhil Girraj

我遇到了完全相同的问题...你最终找到了解决方案或解决方法吗？ - leora

已在https://github.com/ClosedXML/ClosedXML/pull/87中修复。 - Francois Botha

@leora 抱歉，我们没有找到解决方案。最终我们使用OpenXML构建了Excel。这个方法有效。 - Nikhil Girraj

@leora 我已经不再参与这个需求/项目的工作了。如果有人能够确认任何一个答案确实有效，我将很乐意接受答案。 - Nikhil Girraj

Answer 1

问题是由于 ClosedXML 的实现漏洞引起的。

可以轻松地通过使用以下代码片段（修改版的数据透视表示例）并在 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);
}

这个漏洞位于XLWorkbook_Save.cs文件中的GeneratePivotTables方法：

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));
    }
}

通过行 workbookPart.Workbook.AppendChild(pivotCaches);，可以将多个 PivotCaches 添加到 workbookPart.Workbook 中，但它允许包含0个或1个。

话虽如此，唯一解决问题的方法是在源代码中修改上述方法，如下所示：

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));
    }
}

更新：好消息是，我的帖子触发了ClosedXML源代码库的修复，由Francois Botha完成（同时要感谢petelids在那里提出了这个问题），所以你可以从那里获取代码，直到他们的下一个发布版本中包含它为止。

- Ivan Stoev

---

作为这个软件包的维护者，如果有人能够提交拉取请求并进行补丁，我会非常感激。http://github.com/ClosedXML/ClosedXML - Francois Botha

@FrancoisBotha 当然，我昨天刚刚处理完这些问题，还没有时间做出贡献（我想它有一些额外的要求，不像只是抛出一个代码片段），而且用户petelids似乎已经完成了。 - Ivan Stoev

@IvanStoev - 我确实尝试过，但是我匆忙操作并且有点搞砸了。我已经删除了我的回答，因为它没有增加任何价值（而是投了你的回答）。FrancoisBotha创建了一个更好的补丁。 - petelids

没问题。在任何情况下看到社区的参与都很高兴。 - Francois Botha

Answer 2

尝试这个修改。我在添加另一行的地方做了一个注释。此外，我认为 AddNew() 方法可能有错误的工作表引用？你可能一直在尝试在另一个透视表上面添加一个透视表。这可能是真正的问题，而不是我添加的额外行。

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;

- Brian