如何用Open XML SDK创建带筛选器的透视表及关联图表

针对你从Microsoft.Office.Interop.Excel迁移到C# Open XML SDK后，需要实现的带筛选器的透视表、关联图表更新及图表标签添加需求，以下是可直接复用的代码实现方案：

1. 创建/更新带透视筛选器的透视表 #

核心是先创建透视缓存关联数据源，再定义透视表的行/列/值字段，最后添加透视筛选器（页字段）：

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

public static void CreateOrUpdatePivotTableWithFilter(SpreadsheetDocument doc, string sourceSheetName, string pivotSheetName, string pivotTableName)
{
    // 获取工作簿及数据源工作表
    WorkbookPart workbookPart = doc.WorkbookPart;
    WorksheetPart sourceSheetPart = workbookPart.WorksheetParts.First(w => w.Worksheet.Descendants<SheetName>().First().Text == sourceSheetName);

    // 检查透视表工作表是否存在，不存在则创建
    WorksheetPart pivotSheetPart = workbookPart.WorksheetParts.FirstOrDefault(w => w.Worksheet.Descendants<SheetName>().First().Text == pivotSheetName);
    if (pivotSheetPart == null)
    {
        pivotSheetPart = workbookPart.AddNewPart<WorksheetPart>();
        pivotSheetPart.Worksheet = new Worksheet(new SheetData());
        Sheet pivotSheet = new Sheet() 
        { 
            Id = workbookPart.GetIdOfPart(pivotSheetPart), 
            SheetId = (uint)workbookPart.WorksheetParts.Count, 
            Name = pivotSheetName 
        };
        workbookPart.Workbook.AppendChild(pivotSheet);
    }

    // 创建或获取透视缓存
    PivotCacheDefinitionPart pivotCacheDefPart = workbookPart.PivotCacheDefinitionParts.FirstOrDefault();
    if (pivotCacheDefPart == null)
    {
        pivotCacheDefPart = workbookPart.AddNewPart<PivotCacheDefinitionPart>();
        PivotCacheDefinition pivotCacheDef = new PivotCacheDefinition() { CacheId = 1, RefreshOnLoad = true };
        pivotCacheDef.Append(new WorksheetSource() { Name = sourceSheetName, SheetId = sourceSheetPart.Worksheet.Descendants<Sheet>().First().SheetId.Value });
        pivotCacheDefPart.PivotCacheDefinition = pivotCacheDef;
    }

    // 创建或更新透视表
    PivotTablePart pivotTablePart = pivotSheetPart.PivotTableParts.FirstOrDefault();
    if (pivotTablePart == null)
    {
        pivotTablePart = pivotSheetPart.AddNewPart<PivotTablePart>();
        pivotTablePart.PivotTableDefinition = new PivotTableDefinition()
        {
            Name = pivotTableName,
            CacheId = 1,
            Location = new Location() { Reference = "A1" }
        };
    }

    // 配置透视字段（示例：数据源含"类别"、"区域"、"销售额"三列）
    PivotFields pivotFields = pivotTablePart.PivotTableDefinition.Descendants<PivotFields>().FirstOrDefault() ?? new PivotFields();
    pivotFields.RemoveAllChildren();
    pivotFields.Append(new PivotField() { Index = 0, Name = "类别", Outline = true });
    pivotFields.Append(new PivotField() { Index = 1, Name = "区域", Outline = true });
    pivotFields.Append(new PivotField() { Index = 2, Name = "销售额", DataField = true });
    if (pivotTablePart.PivotTableDefinition.Descendants<PivotFields>().FirstOrDefault() == null)
    {
        pivotTablePart.PivotTableDefinition.Append(pivotFields);
    }

    // 配置行字段（类别）
    RowFields rowFields = pivotTablePart.PivotTableDefinition.Descendants<RowFields>().FirstOrDefault() ?? new RowFields();
    rowFields.RemoveAllChildren();
    rowFields.Append(new Field() { X = 0 });
    if (pivotTablePart.PivotTableDefinition.Descendants<RowFields>().FirstOrDefault() == null)
    {
        pivotTablePart.PivotTableDefinition.Append(rowFields);
    }

    // 配置值字段（销售额求和）
    DataFields dataFields = pivotTablePart.PivotTableDefinition.Descendants<DataFields>().FirstOrDefault() ?? new DataFields();
    dataFields.RemoveAllChildren();
    dataFields.Append(new DataField() { Name = "销售额总和", FieldName = "销售额", FunctionValues = FunctionValuesValues.Sum });
    if (pivotTablePart.PivotTableDefinition.Descendants<DataFields>().FirstOrDefault() == null)
    {
        pivotTablePart.PivotTableDefinition.Append(dataFields);
    }

    // 添加透视筛选器：筛选"区域"列为"华东"（假设华东是区域列的第一个选项）
    PageFields pageFields = pivotTablePart.PivotTableDefinition.Descendants<PageFields>().FirstOrDefault() ?? new PageFields();
    pageFields.RemoveAllChildren();
    pageFields.Append(new PageField() { FieldIndex = 1, Item = new Item() { X = 0 } });
    if (pivotTablePart.PivotTableDefinition.Descendants<PageFields>().FirstOrDefault() == null)
    {
        pivotTablePart.PivotTableDefinition.Append(pageFields);
    }
}

2. 基于透视表创建/更新带筛选的条形图/饼图 #

图表需通过PivotSource绑定透视表，筛选器会自动继承透视表的筛选规则，也可单独设置图表筛选：

2.1 条形图实现 #

using DocumentFormat.OpenXml.Drawing.Charts;

public static void CreateOrUpdateBarChartFromPivot(SpreadsheetDocument doc, string pivotSheetName, string chartName)
{
    WorksheetPart pivotSheetPart = doc.WorkbookPart.WorksheetParts.First(w => w.Worksheet.Descendants<SheetName>().First().Text == pivotSheetName);
    
    // 创建或获取绘图部件
    DrawingsPart drawingsPart = pivotSheetPart.DrawingsPart ?? pivotSheetPart.AddNewPart<DrawingsPart>();
    
    // 创建或获取图表部件
    ChartPart chartPart = drawingsPart.ChartParts.FirstOrDefault() ?? drawingsPart.AddNewPart<ChartPart>();
    
    // 构建条形图
    Chart chart = new Chart();
    PlotArea plotArea = new PlotArea();
    BarChart barChart = new BarChart() { BarDirection = BarDirectionValues.Column };

    // 绑定透视表数据源
    barChart.Append(new PivotSource() { Name = "SalesPivot", FormatId = 0 });

    // 配置条形图系列
    BarChartSeries series = new BarChartSeries();
    series.Append(new Index() { Val = 0 });
    series.Append(new Order() { Val = 0 });
    series.Append(new SeriesText() { NumericValue = new StringValue("销售额") });
    
    // 添加数据标签（外部显示值）
    DataLabels barLabels = new DataLabels() { ShowValue = true };
    barLabels.Append(new Position() { Val = DataLabelPositionValues.OutsideEnd });
    series.Append(barLabels);

    barChart.Append(series);
    plotArea.Append(barChart);
    chart.Append(plotArea);
    chartPart.Chart = chart;

    // 将图表定位到工作表的D1:J20区域
    TwoCellAnchor anchor = new TwoCellAnchor();
    anchor.Append(new From() { Column = 4, ColumnOffset = 0, Row = 1, RowOffset = 0 });
    anchor.Append(new To() { Column = 10, ColumnOffset = 0, Row = 20, RowOffset = 0 });
    anchor.Append(new GraphicFrame() { Macro = "", Name = chartName });
    
    drawingsPart.WorksheetDrawing = drawingsPart.WorksheetDrawing ?? new WorksheetDrawing();
    drawingsPart.WorksheetDrawing.Append(anchor);
}

2.2 饼图实现 #

public static void CreateOrUpdatePieChartFromPivot(SpreadsheetDocument doc, string pivotSheetName, string chartName)
{
    WorksheetPart pivotSheetPart = doc.WorkbookPart.WorksheetParts.First(w => w.Worksheet.Descendants<SheetName>().First().Text == pivotSheetName);
    
    DrawingsPart drawingsPart = pivotSheetPart.DrawingsPart ?? pivotSheetPart.AddNewPart<DrawingsPart>();
    ChartPart chartPart = drawingsPart.ChartParts.FirstOrDefault() ?? drawingsPart.AddNewPart<ChartPart>();
    
    Chart chart = new Chart();
    PlotArea plotArea = new PlotArea();
    PieChart pieChart = new PieChart();

    // 绑定透视表数据源
    pieChart.Append(new PivotSource() { Name = "SalesPivot", FormatId = 0 });

    // 配置饼图系列
    PieChartSeries series = new PieChartSeries();
    series.Append(new Index() { Val = 0 });
    series.Append(new Order() { Val = 0 });
    series.Append(new SeriesText() { NumericValue = new StringValue("销售额占比") });
    
    // 添加数据标签（显示值+百分比，自动最优位置）
    DataLabels pieLabels = new DataLabels() { ShowValue = true, ShowPercentage = true };
    pieLabels.Append(new Position() { Val = DataLabelPositionValues.BestFit });
    series.Append(pieLabels);

    pieChart.Append(series);
    plotArea.Append(pieChart);
    chart.Append(plotArea);
    chartPart.Chart = chart;

    // 将图表定位到工作表的K1:Q20区域
    TwoCellAnchor anchor = new TwoCellAnchor();
    anchor.Append(new From() { Column = 11, ColumnOffset = 0, Row = 1, RowOffset = 0 });
    anchor.Append(new To() { Column = 17, ColumnOffset = 0, Row = 20, RowOffset = 0 });
    anchor.Append(new GraphicFrame() { Macro = "", Name = chartName });
    
    drawingsPart.WorksheetDrawing = drawingsPart.WorksheetDrawing ?? new WorksheetDrawing();
    drawingsPart.WorksheetDrawing.Append(anchor);
}

3. 关键说明 #

• 透视表的筛选器通过PageFields实现，可根据实际需求修改FieldIndex和Item.X的值（对应数据源列索引和筛选项的索引）
• 图表绑定透视表后，会自动同步透视表的筛选结果；若需单独设置图表筛选，可在BarChart/PieChart下添加Filter元素
• 数据标签的显示内容（值、百分比、类别名）可通过DataLabels的属性（ShowValue/ShowPercentage/ShowLegendKey等）调整

内容的提问来源于stack exchange，提问作者Pradeep