如何使用 OpenXML 查找数据透视表的数据源

发布于 2024-12-17 17:30:45 字数 591 浏览 2 评论 0原文

我正在使用 EPP 打开并编辑现有的 Excel 文档。

该文档包含 2 张纸 - 一张带有数据透视表（名为 Pivot），另一张带有数据 (Data!$A$1:$L$9899)。

我使用下面的代码引用了 ExcelPivotTable，但找不到与数据源相关的任何属性。

ExcelPackage package = new ExcelPackage(pivotSpreadsheet);

        foreach (ExcelWorksheet worksheet in package.Workbook.Worksheets)
        {
            if (worksheet.PivotTables.Count > 0)
            {
                pivotWorkSheetName = worksheet.Name;
                pivotTable = worksheet.PivotTables[0];
            }
        }

如何获取源数据的名称和范围？是否有我遗漏的明显属性，或者我是否必须去寻找一些 xml？

原文

I am using EPP to open and edit an existing excel document.

The document contains 2 sheets - one with a pivot table (named Pivot) and one with the data (Data!$A$1:$L$9899).

I have a reference to the ExcelPivotTable with the code below, but can't find any properties that relate to the data source.

ExcelPackage package = new ExcelPackage(pivotSpreadsheet);

        foreach (ExcelWorksheet worksheet in package.Workbook.Worksheets)
        {
            if (worksheet.PivotTables.Count > 0)
            {
                pivotWorkSheetName = worksheet.Name;
                pivotTable = worksheet.PivotTables[0];
            }
        }

How do I get the name and range of the source data? Is there an obvious property that I'm missing or do I have to go hunting through some xml?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

百合的盛世恋 2024-12-24 17:30:45

数据透视表使用数据缓存来存储数据，以提高性能和数据安全性。抽象原因。请记住，您可以有一个指向 Web 服务调用的枢轴。缓存本身就是存储该引用的。对于引用工作簿中其他位置的数据的数据透视表，您可以在 EPPlus 中访问它，如下所示：

worksheet.PivotTables[0].CacheDefinition.SourceRange.FullAddress;

PivotTables use a data cache for the data store for performance & abstraction reasons. Remember, you can have a pivot that points to a web service call. The cache itself is what stores that reference. For pivots that refer to data elsewhere in a workbook, you can access it in EPPlus like this:

worksheet.PivotTables[0].CacheDefinition.SourceRange.FullAddress;

回复收藏 0 原文

贩梦商人 2024-12-24 17:30:45

如果有人有兴趣使用 OpenXML SDK 2.5 更新数据源，那么这里是我使用的代码。

    using (var spreadsheet = SpreadsheetDocument.Open(filepath, true))
    {
            PivotTableCacheDefinitionPart ptp = spreadsheet.WorkbookPart.PivotTableCacheDefinitionParts.First();
            ptp.PivotCacheDefinition.RefreshOnLoad = true;//refresh the pivot table on document load
            ptp.PivotCacheDefinition.RecordCount = Convert.ToUInt32(ds.Tables[0].Rows.Count);
            ptp.PivotCacheDefinition.CacheSource.WorksheetSource.Reference = "A1:" + IntToLetters(ds.Tables[0].Columns.Count) + (ds.Tables[0].Rows.Count + 1);//Cell Range as data source
            ptp.PivotTableCacheRecordsPart.PivotCacheRecords.RemoveAllChildren();//it is rebuilt when pivot table is refreshed
            ptp.PivotTableCacheRecordsPart.PivotCacheRecords.Count = 0;//it is rebuilt when pivot table is refreshed
    }
    public string IntToLetters(int value)//copied from another stackoverflow post
    {
            string result = string.Empty;
            while (--value >= 0)
            {
                result = (char)('A' + value % 26) + result;
                value /= 26;
            }
            return result;
    }

If anyone is interested to update the data source with OpenXML SDK 2.5 then here is the code I used.

    using (var spreadsheet = SpreadsheetDocument.Open(filepath, true))
    {
            PivotTableCacheDefinitionPart ptp = spreadsheet.WorkbookPart.PivotTableCacheDefinitionParts.First();
            ptp.PivotCacheDefinition.RefreshOnLoad = true;//refresh the pivot table on document load
            ptp.PivotCacheDefinition.RecordCount = Convert.ToUInt32(ds.Tables[0].Rows.Count);
            ptp.PivotCacheDefinition.CacheSource.WorksheetSource.Reference = "A1:" + IntToLetters(ds.Tables[0].Columns.Count) + (ds.Tables[0].Rows.Count + 1);//Cell Range as data source
            ptp.PivotTableCacheRecordsPart.PivotCacheRecords.RemoveAllChildren();//it is rebuilt when pivot table is refreshed
            ptp.PivotTableCacheRecordsPart.PivotCacheRecords.Count = 0;//it is rebuilt when pivot table is refreshed
    }
    public string IntToLetters(int value)//copied from another stackoverflow post
    {
            string result = string.Empty;
            while (--value >= 0)
            {
                result = (char)('A' + value % 26) + result;
                value /= 26;
            }
            return result;
    }

回复收藏 0 原文

~没有更多了~