How to format Pivot Table data into percentage and Currency (Certain rows in percentage and certain rows in Currency-$).
Please provide suggestions for this issue
Here in the image rows marked red should be in % format and rest all rows should be in dollar ($) format
and here is the code snippet for this issue:
index= pivotTable.addFieldToArea(PivotFieldType.DATA,pivotElements.get(key));
pivotTable.getDataFields().get(index).setFunction(ConsolidationFunction.SUM);
//Setting DataField to text Format
pivotTable.getDataFields().get(index).setNumberFormat("$#,##0_);Red");
Could you please zip and share your current Excel file (containing the pivot table as per the image) by Aspose.Cells API and a sample Excel file containing your desired pivot table formatted as per your custom needs. You can create/update your desired pivot table in MS Excel manually to share here. We will check and help you through.
Attached zip file is the same pivot table as shown in the image that was attached in the forum. And also I attached a sample excel sheet with our desired outputs. Our need is to make only two columns as percentage format as shown in the desired output zip file. Please help us in this issue.
The pivot table is based on its source data in the sheet, so how come you could just apply different formatting for selected rows in data fields? You provided the output having static data which you have formatted using Format Cells dialog. Could you please share a sample Excel file having your desired formatted data in pivot table report (not in static data/table). We will check it soon.
Here is the data you asked for in pivot table format with our desired outputs (Whole State apport factor and Marginal Tax rate rows should be in % format).
And can we connect through call in teams if its feasible for you, so that we can discuss in detailed without any discrepancies.
Thanks for providing the file containing your desired pivot table with percentage formatting for your selected rows.
See the following sample code segment with comments on how to format selected row (you first need to find the row based on your desired pivot row field label/text) for your reference. You may refer to the code segment and write your own code or add your code based on your custom needs:
e.g. Sample code:
.......
Worksheet worksheet = workbook.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 = workbook.createStyle();
style.setCustom("0.00%");
style.getFont().setName("Calibri");
style.getFont().setSize(11);
//Get the row index
int row = cell.getRow();
System.out.println(row);
//Get the cell area based on pivot table range
CellArea area = pivotTable.getTableRange1();
//Get the starting column index
int start = area.StartColumn;
//browse the relevant row upto last column in the pivot table report
//format each cell in the row to set percentage numbers formatting
for (int i = start; i <= area.EndColumn; i++)
{
pivotTable.format(row, i, style);
}
workbook.save("f:\\files\\out1.xlsx");
Hope, this helps a bit.
We are sorry but generally, we don’t provide technical support via phone or net meetings. The best way to get help is via forums and we can assist you better via forums threads.
Hi,
Thank you for the above sample code, this worked well but we are now stuck with other small issue i.e., the cells with value $0 are not appearing in the table after the code change. You can see the difference of above mentioned issue in the attached two sheets (Before and after the code change). Can you please give some solution for us regarding this. Thank you in advance. State recon after code change.zip (142.1 KB) State recon before code change.zip (140.7 KB)
Which version of the APIs you are using? Please try our latest version/fix: Aspose.Cells for Java v22.1. I have tested using my sample code with your (previous) original file and it works ok, those $0 are retained in the output Excel file.
Sorry for the previous issue I mentioned. Its not required for us. But when we are expanding those 2 rows that we have changed, they are again changing to $ . Can you provide the solution for it. And also can you tell me where the code (call) goes when we click on expand.
You can see in the below images while i expand that particular column it is again changed to $. Capture.PNG (9.0 KB) Capture1.PNG (8.7 KB) Copy of State recon after code change.zip (40.3 KB)
Could you please share your expected file, you may accomplish the task manually in MS Excel and save the Excel file to provide us. We will check it further.
@SakethDodda,
We have logged a ticket with an id “CELLSJAVA-44408” for your issue. We will look into it soon.
Once we have an update on it, we will let you know.
Hi,
we cant change the values in our report file. That is the reason i have attached the images in the previous dialogue box. The issue is, after expanding the State Apport. Factor row and Marginal Tax rows, they are again changing to $ from % format. I hope you got this.
Yes, we understand your requirements. We have already logged a ticket with an id “CELLSNET-50356”. Either we will devise a code snippet or figure out the issue with enhanced APIs to support your needs.
