@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
@SakethDodda,
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().
@SakethDodda,
Yes, please do not use isHideDetail and use PivotTable.formatRow() method instead. For reference, please see and try my sample code.
This email is from a contractor that provides managed services to 3rd parties, including Thomson Reuters.
Thanks a lot Amjad. It worked Thanks for your continuous efforts.
Regards,
Saketh
@SakethDodda,
Thanks for your feedback.
Good to know that it works for your needs. In the event of further queries or issue, feel free to write us back.
Hi,
I got the same formatting issue with different scenarios. Our report have 2 conditions
- with no group everywhere column
Present condition- Apport column is wrongly shown in $ format
Required condition- Apport column should be in percentage(0.0000%) -refer Entity Calculations (2) file
- with group everywhere column - Group everywhere column should be in $ and apport column should be in %(0.0000%).It is coming perfectly with our present code, But we need the 1st condition to be working fine. refer Entity Calculations (3) file
Entity Calculations (2).zip (11.9 KB)
Entity Calculations (3).zip (11.9 KB)
@SakethDodda,
Thanks for the files.
Are you using pivot tables? But the contents in your provided files are simple data as I checked these files, probably you only copied/pasted the data to show wrong results. Could you please provide your expected Excel files (containing pivot tables(if you are using it) or sample data/table) to show what you are trying to accomplish, you may create/update data/pivot tables accordingly in MS Excel manually to save the files. Moreover, we need your exact runnable code segments that you are currently using which give incorrect results in the output files. Also, share your input files (if applicable) and current output Excel files by Aspose.Cells for Java API. We will check your issue(s) and assist you accordingly.
PS. please zip the Excel files or other files prior attaching.
Hi Amjad,
No , this is not pivot table. This data comes directly from our application. Here is the expected excel file for condition 1-Entity Calculations (expected Cond 1).zip (12.0 KB)
and we need the code that supports the both conditions. Attached above is the first condition and the 2nd file in the previous one was already appearing fine and we want that too.
Here is the code segment corresponds to this formatting.Code segment.zip (910 Bytes)
And feel free to ask me again if you didn’t understand the exact expected results.
Thank you.
@SakethDodda,
I checked your expected file and sample code. You sample code is not clean and you are mixing the things. Also, we cannot evaluate your sample code precisely which is not runnable as these are segments. You should evaluate your code by yourselves. I simply used your previous file, i.e., “Entity Calculations (2).xlsx” you attach in the first reply as an input/template file and used the following sample code to accomplish your task. The output Excel file has your desired formatting intact for the underlying data (in “Apport %” column) in the sheet.
e.g.
Sample code:
Workbook workbook = new Workbook("f:\\files\\Entity Calculations (2).xlsx");
Style style = workbook.createStyle();
StyleFlag flag = new StyleFlag();
Range range = workbook.getWorksheets().get(0).getCells().createRange(10, 4, 6,1);
style.setCustom("0.00%");
flag.setNumberFormat(true);
range.applyStyle(style, flag);
workbook.save("f:\\files\\out1.xlsx");
Please find attached both input and output file for your reference. Please try the above sample code and you will get the expected results. That’s means the issue is with your code so you should fix it by yourselves.
files1.zip (24.4 KB)
Regarding second issue, I am not sure about it, so kindly do provide your expected Excel file to show what you are trying to accomplish, you may create/update data/pivot tables accordingly in MS Excel manually to save the file. Moreover, share your code segment that you are currently using which gives incorrect results in the output file. Also, share your input file (if applicable) and current output Excel file by Aspose.Cells for Java API. We will check it soon.
Thank you for the suggestion amjad. Here is the other file which requires formatting and it is difficult for me to apply different formats to different rows. In this file when you expand the jurisdictions . APP & TaxRate columns should be in % and weight app should set to setNumber(1). Rest all columns should be in $ as shown. Can you help us to apply these styles.
Entity Reconciliation - Summary (4).zip (323.6 KB)
REQUIRED.zip (323.5 KB)
This is how i applied $ to all columns-
style.setNumber(8);
pivotTable.formatAll(style);
and not able to apply the above mentioned styles.
Please do the needful . Hope i provided the required data.
Thank you.
@SakethDodda,
Thanks for providing the file containing your desired pivot table with percentage and other formatting for your selected cols and cells.
See the following sample code segment with comments on how to format cells in your desired columns accordingly (you first need to find the row/col based on your desired field label/text) for your reference. You may refer to the code segment and write/update your own code or add your code based on your custom needs. I used your undesired file as an input file and directly update formattings accordingly.
e.g.
Sample code:
Workbook wb = new Workbook("f:\\files\\Entity Reconciliation - Summary (4).xlsx");
Worksheet worksheet = wb.getWorksheets().get(0);
PivotTable pivotTable = worksheet.getPivotTables().get(0);
pivotTable.refreshData();
pivotTable.calculateData();
//Find the cell containing the field text/label "APP"
//update format the cells in that column
Cell cell = worksheet.getCells().find("APP", null);
//Create the style with your desired formatting
Style style = wb.createStyle();
style.setCustom("0.00%");
//Get the row index
int row = cell.getRow();
int col = cell.getColumn();
System.out.println(row);
System.out.println(col);
//Get the cell area based on pivot table range
CellArea area = pivotTable.getTableRange1();
//Get the starting column index
int start = area.StartRow;
//browse the relevant row upto last row in the pivot table report
//format each cell in the row to set percentage numbers formatting
for (int i = start; i <= area.EndRow; i++)
{
pivotTable.format(i, col, style);
}
//Find and update formatting for TAX RATE field
cell = worksheet.getCells().find("TAX RATE", null);
//Get the row index
row = cell.getRow();
col = cell.getColumn();
//browse the relevant row upto last row in the pivot table report
//format each cell in the row to set percentage numbers formatting
for (int i = start; i <= area.EndRow; i++)
{
pivotTable.format(i, col, style);
}
//Create another style for WEIGHT column
Style style1 = wb.createStyle();
style1.setNumber(1);
//Find and update formatting for WEIGHT field
cell = worksheet.getCells().find("WEIGHT", null);
//Get the row index
row = cell.getRow();
col = cell.getColumn();
//browse the relevant row upto last row in the pivot table report
//format each cell in the row to set general formatting
for (int i = start; i <= area.EndRow; i++)
{
pivotTable.format(i, col, style1);
}
//Change the state of PivotItem
pivotTable.getRowFields().get(0).getPivotItems().get("SALES").setHideDetail(false);
pivotTable.refreshData();
pivotTable.calculateData();
//After expanding the data, format it again
pivotTable.format(33, col, style1);
pivotTable.format(33, col-1, style);
pivotTable.format(33, col+1, style);
//Restore previous state
pivotTable.getRowFields().get(0).getPivotItems().get("SALES").setHideDetail(true);
wb.save("f:\\files\\out1.xlsx");
Hope, this helps a bit.
Then do we need to format seperately for all the expanded states?
@SakethDodda,
Yes, since you need different formats for different cells for a single row.
Not for the single row, every row in table w.r.t APP & TaxRate -0.00% and Weight- setNumber(1) for all rows… Is the above given code for all rows ?
@SakethDodda,
The code segment picks your desired columns (APP, TEXT RATE, WEIGHT, etc.) only and format each cell in the columns for your custom needs. See and refer to the code segment for your understanding. The last part implements the change of state (expand/collapse) of pivot item (“SALES”) where we have to apply formatting again, so when you expand it your desired/updated formatting should retain.