Pivot Table - Set value of a Page Field

Aspose.Cells Product Family
1

Hi.

I have a pivot table with a filter on a field.
I know where is the cell which contains the value of this filter but I don’t find how to change this value.
If I use cell.setValue(filterValue), it changes the value that I read in the cell, but it doesn’t change the filter itself.

http://img4.hostingpics.net/pics/502298filter.png

Here is a picture of what I have.
I want to change B2 cell.
The filter is set to R201410 and I want to set it to R201407 but cell.setValue(“R201407”) change only the value of the cell, not the filter.

If you know what I need to do, it will help me.

Thanks in advance.

2

Hi,


Please see the sample code on how to specify your desired Pivot Table’s page items to filter data:
e.g
Sample code:

[C#]
string filePath = @“e:\test2\Book1.xlsx”;

Workbook workbook = new Workbook(filePath);

PivotTable table = workbook.Worksheets[“Pivot”].PivotTables[0];

PivotFieldCollection pivotFieldCollection = table.PageFields;
//Select the Item
pivotFieldCollection[0].CurrentPageItem = 1;
//Or hide the items if you do not want to specify.
// pivotFieldCollection[0].HideItem(2, true);

table.RefreshData();
table.CalculateData();
workbook.Save(“e:\test2\out1.xlsx”)


[JAVA]
Workbook workbook = new Workbook(filePath);

PivotTable table = workbook.getWorksheets().get(“Pivot”).getPivotTables().get(0);

PivotFieldCollection pivotFieldCollection = table.getPageFields();
//Select the Item
short s = 1;
pivotFieldCollection.get(0).setCurrentPageItem(s);
//Or hide the items if you do not want to specify.
// pivotFieldCollection.get(0).hideItem(2, true);

table.refreshData();
table.calculateData();
workbook.save(“e:\test2\out1asdfasfasdf1.xlsx”);

Hope, this helps a bit.

Thank you.
3

Thanks.

I try this but table.refreshData() doesn’t seem to work.
I have this error :
java.lang.IllegalArgumentException: Unkown Area
at com.aspose.cells.alj.a(Unknown Source)
at com.aspose.cells.alj.b(Unknown Source)
at com.aspose.cells.alj.d(Unknown Source)
at com.aspose.cells.PivotTable.refreshData(Unknown Source)
at ExcelAspose.getDataFromPipe2(ExcelAspose.java:144)

line 144 is the line with refreshData().


Here is my code :
Workbook workbook = new Workbook(nomFichier);
Worksheet worksheet = workbook.getWorksheets().get(nomOnglet);
PivotTable pivotTable = worksheet.getPivotTables().get(0);
PivotFieldCollection pivotFieldCollection = pivotTable.getPageFields();
pivotFieldCollection.get(0).setCurrentPageItem((short)1);

pivotTable.refreshData();
pivotTable.calculateData();
workbook.save(nomFichier+“2.xlsm”);

4

Hi,


Please make sure you are using latest version of the product i.e. v8.1.0.

If you still find the issue, kindly post your template Excel file here, we will check your issue soon.

PS. You may download and try our latest version/fix here: Aspose.Cells for Java v8.1.0.1

Thank you.

5

Hi.
I use the last version of Aspoce Cells 8.1.0.

I try this code with 2 differents files.

String path = “D:\Doc stage\”;
String fileName = “essai.xlsm”;
try {
Workbook workbook = new Workbook(path+fileName);
PivotTable table = workbook.getWorksheets().get(“Pivot”).getPivotTables().get(0);
PivotFieldCollection pivotFieldCollection = table.getPageFields();
//Select the Item
short s = 1;
pivotFieldCollection.get(0).setCurrentPageItem(s);
//Or hide the items if you do not want to specify.
// pivotFieldCollection.get(0).hideItem(2, true);
table.refreshData();
table.calculateData();
workbook.save(path+“out1asdfasfasdf1.xlsm”);
} catch (Exception e) {
e.printStackTrace();
}

With the first file (essai.xlsm), the new file that is created has unreadable content.

I also try with a file where the pivot table use data from another file. And this time, I have the error :
java.lang.IllegalArgumentException: Unkown Area
at com.aspose.cells.alj.a(Unknown Source)
at com.aspose.cells.alj.b(Unknown Source)
at com.aspose.cells.alj.d(Unknown Source)
at com.aspose.cells.PivotTable.refreshData(Unknown Source)
at ExcelAspose.getDataFromPipe3(ExcelAspose.java:146)
at ExcelAspose.main(ExcelAspose.java:19)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:483)
at com.intellij.rt.execution.application.AppMain.main(AppMain.java:134)

Thanks.

6

Hi,


Please change your code segment as following:
e.g
Sample code:

String path = “D:\Doc stage\”;
String fileName = “essai.xlsm”;
try {
Workbook workbook = new Workbook(path+fileName);
PivotTable table = workbook.getWorksheets().get(“Feuil4”).getPivotTables().get(0);
PivotFieldCollection pivotFieldCollection = table.getPageFields();
pivotFieldCollection.get(0).setMultipleItemSelectionAllowed(false);
//Select the Item
short s = 1;
pivotFieldCollection.get(0).setCurrentPageItem(s); //Display Red item’s filtered data
//Or hide the items if you do not want to specify.
// pivotFieldCollection.get(0).hideItem(2, true);
table.refreshData();
table.calculateData();
table.setRefreshDataOnOpeningFile(true);
workbook.save(path+“out1.xlsm”);
} catch (Exception e) {
e.printStackTrace();
}


Thank you.
7

Thanks for your quick response. It works fine with this file.

Is it possible to do the same thing when the source of data is another file ?

EDIT : table.refreshData() doesn’t work if the data source is another file.
But if I remove this line, it works.

8

Hi,


Well, I am afraid, we do not support refreshing pivot table based on other source file or workbooks.

Thank you.
9

Ok.
Thanks for all this quick answers.

10

Hi,


You are welcome, feel free to contact us any time if you need further help or have some other issue or queries, we will be happy to assist you soon.

Thank you.