none
pivot table information using openxml RRS feed

  • Question

  • hi,

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

    any one can guide for this ?

    Thanks

    Tuesday, July 18, 2017 10:27 AM

All replies

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

    Best Regards,

    Terry


    Thursday, July 20, 2017 1:57 AM