Export PivotCache

Aspose.Cells Product Family
1

Is there a way to export the PivotCache behind a PivotTable?

2

@harley.333,
We are discussing this and will share our feedback after detailed analysis.

3

@harley.333,

Could you please provide us a sample Excel file containing the pivot table and pivot cache. Also let us know how do you access or retrieve pivot cache data using MS-Excel manually. This will help us look into your issue more precisely and assist you accordingly.

4

I cannot provide a file, because they contain sensitive information. However, I suppose any pivot-table would work. Manually, I can access the pivot-cache data by right-clicking a cell within the pivot-table and selecting “Show Details” from the context-menu. The file-format is xlsb, so I cannot directly access the pivot-cache from the XML.
I have attempted to use interop, but the PivotCache.Recordset property throws an exception. Plus, I’d like to avoid interop.
Apparently, the original data was stored in an external Excel (which I do not have access to). I can determine this from looking at PivotCache.DataSource and other properties.

5

@harley.333,

You may share any sample file (containing pivot table) based on dummy data. Please zip the file prior attaching.

I could not find “Show Details” context menu item. I am using MS Excel 2015. Could you give us more details on how to find/access it, you may share some screenshots (if appropriate). We will check it soon.

6

pivot.zip (6.6 KB)
The attached file is in the xlsb format and contains a single sheet with a single pivot table.
The sheet which originally contained the data used to build the pivot table has been removed from the workbook.
To restore the original data (which is stored in the PivotCache), you can right-click the pivot table and select “Show Details” from the context-menu.

7

@harley.333,
We are checking details and will share our feedback soon.

8

@harley.333,

We understand your requirements now. We already logged a ticket for it in our database for investigations. This issue was logged as following:
CELLSNET-42466 - Access PivotCache objects from an Excel workbook

We are very sorry but we could not actively work on the feature. The feature is more complex than it looks. Anyways, we will further evaluate it (if possible) and share new updates on it.