How to select record on report filter in pivot table

Aspose.Cells Product Family
1

Hi,

Please see attached excel file, in “BillingPivot” tab, a report filter on B3, the values are from G cell of “BillingData” tab.

I want to make the report filter select the max value(in this case the max value is 2006/4/21) after some new records add to BillingData tab by using aspose, and the pivot table should be really filtered, please give me some clue, thanks in advance.

2

Hi,


Apologies for late reply.
What I understand from your post, you have two queries.

1) You want to update the Pivot Table data source. Please see the below reference article. You need create a name range and you may assign it to your Pivot Table as data source.
http://www.aspose.com/documentation/.net-components/aspose.cells-for-.net/pivot-table.html

2) You want to programmatically select the MAX value from a Report Filter of Pivot Table. I am afraid that the current API of Aspose.Cells JAVA does not support this feature. Although, we have logged this as “Requested Feature” in our database under Ticket ID CELLSJAVA-28115. First we will analyse this requirement and will update you on it’s implementation time lines.

Please feel free to write back.

3

Hi,


Regarding the second point in above reply, you can use the setCurrentPageItem method of PivotField using our latest fix version of Aspose.Cells JAVA v2.5.4.6 [attached].

Sample Code JAVA
workbook.open(“D:\Billing.xls”);
PivotTable table = workbook.getWorksheets().getSheet(0).getPivotTables().get(0);
PivotField field = table.getPageFields().get(1);
field.setCurrentPageItem((short)(field.getItemCount()-1));
table.setRefreshDataOnOpeningFile(true);
//if need
table.calculateData();

Please feed us back with your results. Thank you
4

Could you provide instructions on how to do this in the .NET version? I don’t seem to have access to most of these methods; starting with the get methods (I can get the fields another way, but I don’t even have a setCurrentPageItem or anything similar on the field.


Thanks in advance,
Mirek

5

Hi,

Thanks for your posting and using Aspose.Cells for .NET.

Please download and use the latest version: Aspose.Cells
for .NET v7.3.5.1.

Please use PivotTField.CurrentPageItem the property. Below is the translated code in .NET

C#


Workbook workbook = new Workbook(“D:\Billing.xls”);

PivotTable table = workbook.Worksheets[0].PivotTables[0];

PivotField field = table.PageFields[1];

field.CurrentPageItem = (short)(field.ItemCount - 1);

table.RefreshDataOnOpeningFile = true;

table.CalculateData();


6

Is there any updates on Ticket Id CELLSJAVA-28115 .
I have a similar requirement.

7

Hi Nilima,

Thanks for your posting and using Aspose.Cells.

This is actually an old ticket which is non-existent in the current database. Could you please elaborate your requirements in detail in a New Post. We will look into it and create a New Ticket in the current database to support your feature request.

Thanks for your cooperation.