Access existing pivot table?

Aspose.Cells Product Family
1

I'm evaluating Aspose.Cells for .NET (version 4.8.1) and have some questions about pivot table support. All the examples I can find deal with creating a new pivot table. I can't seem to find any reference to accessing an existing PT other than to set "update on open" and then set the data and data range.

This seems to work fine, but if I try to access the existing PT programmatically, I can't. For example, I have an XLSX workbook (attached) that has simple data on Sheet2 and a pivot table on Sheet1. If I run the code below, pts is 0. Is this the way it's supposed to work? I see the same thing opening an XLS file that has PTs, too.

Workbook workbook = new Workbook();

workbook.Open(@"..\..\Data\Data.xlsx");

int pts = workbook.Worksheets[0].PivotTables.Count;

Thanks,

Chuck

2

Hi Chuck,

Thank you for considering Aspose.

Well, I am afraid your requested feature is not supported at the moment. This feature is already registered in our internal issue tracking system with issue id CELLSNET-6593. We will implement this feature in our future version. We will update you in this thread when the feature is supported.

Thank You & Best Regards,

3

Thanks for raising this issue Chuck,

This is a feature I would really like to see sooner rather than later. Is there a time frame when this will be implemented?

In the mean time I would be interested in a workaround to retrieve an existing pivot table. I guess a range reference covering the cells holding the table would work? But how do you make this dynamic in case the pivot tables changes (rows/columns added as you alter the data source)?

Just wondering if Aspose has an example of such a workaround?

Thanks
Bert

4

Hi,

“This is a feature I would really like to see sooner rather than later. Is there a time frame when this will be implemented?”

The feature is under process. We will try to complete the feature before the end of Q1 2010.

“In the mean time I would be interested in a workaround to retrieve an
existing pivot table. I guess a range reference covering the cells
holding the table would work? But how do you make this dynamic in case
the pivot tables changes (rows/columns added as you alter the data
source)?”

Well, it works only when you opens the generated excel file (containing your updated pivot table) into MS Excel as MS Excel would refresh it then. You cannot get the updated values at run time.
See the document: http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/pivot-table.html


Thank you.



5

Hi Amjad,

Just wondering what the progress is on pivot table support? It’s now nearly end of Q1 2010.

Maybe there’s a beta/ pre-release version available for testing.

Thanks
Bert

6

Hi Bert.

We will update you soon.

Thank you.

7

Hi,

Sorry that we could not complete the feature yet. As a matter of fact, we could not find enough time to complete the task for we have to work on some other modules (in parallel with it) which are also equally important for the users. Hopefully we can finish this task before the end of May 2010.

Again, sorry for any inconvenience and thanks for your understanding!

8

Hi,

is there any progress in completing the task?

Thanks

Horst

9

Hi,

is there any progress in completing the task or is this thread dead?

Thanks

Horst

10

Well Horst,

I’m waiting for an update too, so I think the thread is still alive.

Regards
Bert

11

Hi Bert,

good to see that the thread is not dead. It would be nice to get a reliable "estimated time of implementation" from Aspose that I can pass to my customer.

Regards

Horst

12

Hi,

As a matter of fact, we have supported reading pivot tables in XLSX files but we are still working on this feature. If you want to change existing pivot table in the template file, please set PivotTable.RefreshDataOnOpeningFile to “true”, bacause we do not support to calculate pivot table data to the cells yet. We are hoping we can support calculating pivot table data in Q4 2010.

Thanks for your understanding!

13

@kwokho,

We supported the feature (create, manipulate and refresh Pivot Tables in XLS/XLSX (MS Excel 2007 - 2019)) with enhancements in newer versions. We recommend you to kindly upgrade to and give it a try to latest version of Aspose.Cells. Also, see the document for your reference:
Create Pivot Tables and Pivot Charts