Trying to resolve an issue regarding Pivot tables using aspose cells for Java (V 7.7.0)

Aspose.Cells Product Family
1

my customer has purchased a Total Product Family Developer OEM license in June

2013.

We are currently trying to resolve an issue regarding Pivot tables using

aspose cells for Java (V 7.7.0)

The issue is described in the attached files and in the code described below



Background: Please refer to the attached files



a) File: Image1.png shows Sheet1 in an excel sheet named “Pivot_trial1.xlsx”



b) File Image2.png shows Sheet2 which has a pivot table as shown.

Cell A12 has a filter named : Row Labels. On clicking this cell

we get a popup as shown in this Image2.png file

The value set in the filter popup is “chocolate cake” and

the computed Sum of quantity = 2



c) File Image3.png is what we want to achieve using aspose cells.

ie we want to change the Filter to “cookies” and have the

the computed Sum of quantity set to 3



d) We would then use this value further in our application.



Please let us know how we can achieve this.



We have tried many code options using .a jsp file , some of which are

shown in the

code appended below







Coded in Java and served over: JSP files by a Tomcat web server.

------------Code tried---------------------

Workbook workbook=null;

try

{

workbook = new Workbook(“F:\Pivot_trial1.xlsx”);// Load this file from

disk

}

catch (Exception e1)

{

response.getWriter().print(“Exception reading xlfile
”);return ;

}



Worksheet worksheet = workbook.getWorksheets().get(1);// Get the

worksheet index 1

PivotTable pt=worksheet.getPivotTables().get(0);// Get the only pivot

table here

PivotFilterCollection pfc1 = pt.getPivotFilters();// get all filters

response.getWriter().print(“No of filters=” + pfc1.getCount() + “
”);

//—Get the pivot fields for this pivot table-----

PivotFieldCollection pfc = pt.getBaseFields();

response.getWriter().print(“No of basefields=” + pfc.getCount() +

“
”);

for(int j=0; j < pfc.getCount(); j++)

{

PivotField pf=pfc.get(j);

response.getWriter().print(“Pivotfield index=” + j + “, name=” +

pf.getName() + “
”);

if(pf.getName() !=null && pf.getName().trim().equalsIgnoreCase(“Product”))

{

pf.setCurrentPageItem((short)(1));

}

pt.refreshData();pt.calculateRange();pt.calculateData();

}

//Read relevant cell. Does not update



response.getWriter().print(worksheet.getCells().get(12,1).getStringValue()

+ “
”);

// Error: Pivot table values do not get updated

%>





Please respond asap.



Thanks,
M.Irfan.

2

Hi,


Thanks for the sample code and sharing the template file with screen shots.

I have tested your scenario/ case a bit. I tried to accomplish the task using the following sample code with your template file but it does not work well:
e.g
Sample code:


Workbook workbook = new Workbook(“Pivot_trial1.xlsx”);

//Get Worksheet having PivotTable
Worksheet worksheet = workbook.getWorksheets().get(1);
//Get PivotTable
PivotTable table = worksheet.getPivotTables().get(0);
//Add first PivotFilter
int index = table.getPivotFilters().add(0, PivotFilterType.VALUE_EQUAL);
//Get PivotFilter by Index
PivotFilter filter = table.getPivotFilters().get(index);
//Set various properties
filter.setValue1(“Cookies”);
// filter.setMeasureFldIndex(0);


workbook.save(“out1.xlsx”);

We need to evaluate your issue in details. I have logged a ticket with an id “CELLSJAVA-40698” for your issue. We will look into your issue soon.

Once we have any update on it, we will let you know here.

Thank you.
3

Hi,

Please download and try this fix: Aspose.Cells for Java v7.7.0.2.

There are some points to be noted here:

  1. The method setCurrentPageItem of PivotField is only valid for the PivotField of PageFields, and only sets single item by calling setCurrentPageItem.

  2. If you want to filter the PivotField of RowFields, ColumnFields and so on, you must use two ways as follows to get the expected results:

i)

First, you may hide the PivotItem of PivotField accordingly. The codes should be as follows:

Workbook workbook = new Workbook("D:\\Pivot_trial1.xlsx");

Worksheet worksheet = workbook.getWorksheets().get(1);

PivotTable pt = worksheet.getPivotTables().get(0);

//---Get the pivot fields for this pivot table-----

PivotFieldCollection pfc = pt.getBaseFields();

PivotItemCollection items = null;

PivotItem item = null;

for (int j = 0; j < pfc.getCount(); j++)

{

PivotField pf = pfc.get(j);

if (pf.getName() != null && pf.getName().trim().equals("Product"))

{

items = pf.getPivotItems();

for (int i = 0; i < items.getCount(); i++)

{

item = items.get(i);

if (((String)item.getValue()).equals(“Cookies”))

{

item.setHidden(false);

}

else

{

item.setHidden(true);

}

}

}

}

pt.refreshData();

pt.calculateData();

//Read relevant cell. Does not update

System.out.println(worksheet.getCells().get(12,1).getStringValue());

workbook.save("D:\\Pivot_trial1_out.xlsx");

ii)

Second, According to label filter or value filter. See the following codes:

Workbook workbook = new Workbook("D:\\Pivot_trial1.xlsx");

Worksheet worksheet = workbook.getWorksheets().get(1);

PivotTable pt = worksheet.getPivotTables().get(0);

PivotFilterCollection pfc1 = pt.getPivotFilters();

//Add first PivotFilter

int index = pfc1.add(2, PivotFilterType.CAPTION_EQUAL);

//Get PivotFilter by Index

PivotFilter filter = pfc1.get(index);

//Set various properties

filter.setValue1("Cookies");

filter.getAutoFilter().custom(0, FilterOperatorType.EQUAL, "Cookies");

pt.refreshData();

pt.calculateData();

//Read relevant cell. Does not update

System.out.println(worksheet.getCells().get(12,1).getStringValue());

workbook.save("D:\\Pivot_trial1_out.xlsx");

Hope, it helps you and you could figure out your issue now.

Thank you.

4

The issues you have found earlier (filed as CELLSJAVA-40698) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.