使用openxml透视表信息 [英] pivot table information using openxml

问题描述

我需要使用open xml获取每个excel表单元格引用的数据透视表信息..

i require to get pivot table information with cell reference per excel sheet using open xml..

任何一个可以指导吗？

谢谢

推荐答案

Hi Viral84，

Hi Viral84,

您想获得哪些信息？您是否需要数据透视表的地址或源数据范围的地址？

What information do you want to get? Do you need the address of the pivot table or address of the source data range?

要获得数据透视表地址，您可以参考

For getting pivot table address, you could refer to

  ;         

          

  SpreadsheetDocument document = SpreadsheetDocument.Open(@"C:\Users\Desktop\PivotTablesAndCharts.xlsm", true);
            using (document)
            {
                WorkbookPart wbPart = document.WorkbookPart;
                foreach (WorksheetPart worksheetpart in wbPart.WorksheetParts)
                {
                    if (worksheetpart.PivotTableParts.Count() > 0)
                    {
                        foreach (PivotTablePart pt in worksheetpart.PivotTableParts)
                        {
                            PivotTableDefinition ptDefinition = pt.PivotTableDefinition;
                            MessageBox.Show(ptDefinition.Location.Reference.ToString());
                        }
                    }
                }
            }

要获取数据透视表的源数据范围，可以参考              

For getting source data range of the pivot table, you could refer to              

 foreach (PivotTableCacheDefinitionPart ptCache in wbPart.PivotTableCacheDefinitionParts) {
                   MessageBox.Show(
                      ptCache.PivotCacheDefinition.CacheSource.WorksheetSource.Sheet+":"+
                      ptCache.PivotCacheDefinition.CacheSource.WorksheetSource.Reference.ToString());
                }

最好的问候，

Terry

这篇关于使用openxml透视表信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！