Amjad, A small request that can you send me code snippet to make all the rows(Fed Tax Inc on State Basis to Income Tax) expanded by default when they are not null. Here is the excel pivot sheet and attached image of desired output.State Reconciliation.zip (17.8 KB)
ss2.PNG (18.9 KB)
Your pivot table in the workbook refers to external file reference or your data source of the pivot table is missing or removed. Aspose.Cells does not support to refresh or calculate pivot data with data model or does not support external data source. Please provide a sample file containing the pivot table that should refer to data source existed in the same workbook. We will evaluate your issue soon.
This email is from a contractor that provides managed services to 3rd parties, including Thomson Reuters.
Should i send the workbook with the source data in it?
Thanks for the file.
Please see the following sample code to accomplish your task for your reference:
e.g.
Sample code:
Workbook wb = new Workbook("f:\\files\\State Reconciliation - 2022-05-27T204150.857.xlsx");
Worksheet worksheet = wb.getWorksheets().get(0);
PivotTable pivotTable = worksheet.getPivotTables().get(0);
pivotTable.getRowFields().get(0).getPivotItems().get("State Apport. Factor").setHideDetail(true);
int pageItemCount = pivotTable.getRowFields().get(0).getPivotItems().getCount();
for (int i = 0; i < pageItemCount; i++)
{
PivotItem item = pivotTable.getRowFields().get(0).getPivotItems().get(i);
item.setHideDetail(false);
}
pivotTable.refreshData();
pivotTable.calculateData();
wb.save("f:\\files\\out1.xlsx");
Hope, this helps a bit.
Im getting PivotItem cannot be resoolved as a type error. How can this be fixed?
Which version of Aspose.Cells for Java you are using? I am using Aspose.Cells for Java v22.5 (please try it if you are not already using it) and it works absolutely fine using the sample code (I pasted in my previous reply) with your file (you attached previously).
Im using 22.4. kay we will install directly 22.6 in our system as it has other fix as you said. Thanks for the quick reply Amjad
Alright. But I do not think 22.4 does not include PivotItem class, this class should be there in com.aspose.cells package of older versions as well.
This email is from a contractor that provides managed services to 3rd parties, including Thomson Reuters.
But it is not working in here. Is there a way to add that class externally into our package?
This email is from a contractor that provides managed services to 3rd parties, including Thomson Reuters.
Hi Amjad. Ignore the previous message. I externally added the class and its working now. One more thing is the new version released (22.6)?
Good to know that you have sorted it out now.
Aspose.Cells for Java v22.6 will be published before the end of this week or in the next week early.
Okay thank you Amjad. Hope this closes my issue after the new release.
@SakethDodda,
There are some tips for you:
- Adds PivotTable.FormatRow(int row, Style style) method, which formats the row data in the pivottable area.
- The sample code as follows:
Workbook wb = new Workbook(“State Reconciliation.xlsx”);
Worksheet worksheet = wb.getWorksheets().get(0);
PivotTable pivotTable = worksheet.getPivotTables().get(0);
pivotTable.refreshData();
pivotTable.calculateData();
//Find the cell containing the row field text/label
Cell cell = worksheet.getCells().find(“State Apport. Factor”, null);
//Create the style with your desired formatting
Style style = wb.createStyle();
style.setCustom(“0.00%”);
style.getFont().setName(“Calibri”);
style.getFont().setSize(11);
//format the row data
pivotTable.formatRow(cell.getRow(), style);
cell = worksheet.getCells().find(“Marginal Tax Rate”, null);
//format the row data
pivotTable.formatRow(cell.getRow(), style);
wb.save(“out.xlsx”);
Please try Aspose.Cells for Java v22.6.
The issues you have found earlier (filed as CELLSJAVA-44632,CELLSJAVA-44628) have been fixed in this update. This message was posted using Bugs notification tool by Peyton.Xu
Hi ,
as per your message what does this mean? So we decide to support formatting the entire row of data in the PivotTable. Does this work same as expected, i.e., when we expand jurisdiction column the 2 rows(state apport. factor and marginal tax rate) should appear in percentage?
Can you please confirm and send the code again
It was difficult to control the PivotItem.IsHideDetail attribute to format the whole row of data especially when the PivotItems in the row fields and column fields are expanded and collapsed. We could not retain the formatting when expanded and collapsed fields. So we decide to support formatting the whole row’s data in the PivotTable report.
Please try the following sample code with newer API (PivotTable.formatRow) using latest version/fix: Aspose.Cells for Java v22.6. It will give your desired behavior/task for your requirements:
e.g.
Sample code:
Workbook wb = new Workbook("f:\\files\\State Reconciliation (1).xlsx");
Worksheet worksheet = wb.getWorksheets().get(0);
PivotTable pivotTable = worksheet.getPivotTables().get(0);
pivotTable.refreshData();
pivotTable.calculateData();
//Find the cell containing the row field text/label
Cell cell = worksheet.getCells().find("State Apport. Factor", null);
//Create the style with your desired formatting
Style style = wb.createStyle();
style.setCustom("0.00%");
style.getFont().setName("Calibri");
style.getFont().setSize(11);
//format the row data
pivotTable.formatRow(cell.getRow(), style);
cell = worksheet.getCells().find("Marginal Tax Rate", null);
//format the row data
pivotTable.formatRow(cell.getRow(), style);
wb.save("f:\\files\\out1.xlsx");
Yes, it will give your expected results.
Please try the above code and let us know your results.
So, do I need to comment the code that we have used by using .isHideDetail().
Yes, please do not use isHideDetail and use PivotTable.formatRow() method instead. For reference, please see and try my sample code.