Pivot Table/Chart

Aspose.Cells Product Family
1

Where is the API to references to manipulate the properties of a Pivote Table in Aspose.Excel (v1.8.7.1)? I can’t find any Property/Method on the Worksheet object. I did a search on the Object Browser but nothing found.

Can we do Pivot Table/Chart through Aspose.Excel?

Thanks,
Prodip

2

Hi Prodip,

Currently Aspose.Excel supports the pivot table/chart in designer. You can create pivot table/chart in your designer file and use Aspose.Excel to import it. And you can change the data source of pivot table/chart at run time.

We are thinking of provide pivot table APIs. But it may take for about several months.

3

Laurence,
Thanks for the reply.

All we have is the dll. What do you mean by the designer? And, how do I change the data source to the Pivot table at run time?

As an alternate method, I am thinking to create an excel template file with a Pivot table and chart. I rewrite the data through aspose excel but I need the API to change Pivot table configurations. Is it possible to change the data source of the Pivot table though aspose API?

Thanks,
Prodip

4

Hi Prodip,

We call the template file as a designer spreadsheet because you can design the content and style of your report manually.

Your understanding is correct and your method is feasible. You can use Aspose.Excel to rewrite the data but not the configuration. Please have a try.

5

Hi Laurence,
I have created a designer(xls template) file with a Pivot Table, Pivote Chart and Pivot Data Source with some static data. The problem is–I could not find how to refresh data (i.e. change the reference range) for the Pivot Table.

Could you please shed some lights?

Thanks,
Prodip

6

Hi Prodip,

Could you send your designer file to me?

Generally you can set the data source of the static data on a range of cell. Please remember to set “Refresh on open” in “Table Options”.

Then you can use Aspose.Excel to change the data on these cells during run time. The data will be refreshed when you open the result file.

7

@Prodip.Kumar,

Aspose.Excel was renamed to become Aspose.Cells now. The new API is more robust and feature rich.

You do not need to use designer spreadsheet for Pivot tables or pivot charts. You can create Pivot tables and pivot charts from the scratch. Aspose.Cells supports to create, manipulate, refresh and render Pivot Tables and Pivot Charts in Excel files (MS Excel 2007 - 2019).

See the article with example code for your reference:
How to create Pivot Tables and Pivot Charts in Excel files