ClosedXML - Создание нескольких сводных таблиц

Ответ 1

Проблема вызвана ошибкой реализации ClosedXML.

Его можно легко воспроизвести, используя следующий фрагмент (модифицированная версия примера Pivot Tables) и открытие результирующего файла в 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 Франсуа Бота (также запишет petelids, который привел его туда), поэтому вы можете взять код оттуда, пока следующий релиз, который, надеюсь, будет включать его.

Ответ 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;