Adds PivotItem.IsHideDetail property which gets and sets whether the pivot item hides detail.
In Excel, if you format only once, the format will disappear when you expand the line. So you need to format again after expanding the line. Because of internal data, when expanding and shrinking, they correspond to two different states and internal data. Therefore, if you need to expand and shrink the data format unchanged, you need to set the format twice. Please refer to the video named "Video_sample.zip"Video_sample.zip (1.4 MB)
for details.
The sample code for .NET:
Workbook wb = new Workbook("State Reconciliation.xlsx");
Worksheet worksheet = wb.Worksheets[0];
PivotTable pivotTable = worksheet.PivotTables[0];
pivotTable.RefreshData();
pivotTable.CalculateData();
//Find the cell containing the row field text/label
Cell cell = worksheet.Cells.Find("State Apport. Factor", null);
//Create the style with your desired formatting
Style style = wb.CreateStyle();
style.Custom = "0.00%";
style.Font.Name = "Calibri";
style.Font.Size = 11;
//Get the row index
int row = cell.Row;
//Get the cell area based on pivot table range
CellArea area = pivotTable.TableRange1;
//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);
}
//Change the state of PivotItem
pivotTable.RowFields[0].PivotItems["State Apport. Factor"].IsHideDetail = false;
pivotTable.RefreshData();
pivotTable.CalculateData();
//After expanding the data, format it again
for (int i = start; i <= area.EndColumn; i++)
{
pivotTable.Format(row, i, style);
}
//Restore previous state
pivotTable.RowFields[0].PivotItems["State Apport. Factor"].IsHideDetail = true;
wb.Save("out.xlsx");
Please check the result video named “Video_result.zip” Video_result.zip (661.5 KB)
Can you add these lines (In the Below image) in the correct format accordingly to the 1st code you have sent to me (On January 11th) as it is throwing error after adding these lines. (I cannot find those keywords in your site as well, that’s why I’m asking you)
. image.png (13.2 KB)
Please note, the sample code @John.He shared in previous post is in .NET, so you have to convert it to Java. I have converted it for your reference and for your needs:
e.g. Sample code:
Workbook wb = new Workbook("f:\\files\\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);
//Get the row index
int row = cell.getRow();
//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);
}
//Change the state of PivotItem
pivotTable.getRowFields().get(0).getPivotItems().get("State Apport. Factor").setHideDetail(false);//this line will work in upcoming v22.3
pivotTable.refreshData();
pivotTable.calculateData();
//After expanding the data, format it again
for (int i = start; i <= area.EndColumn; i++)
{
pivotTable.format(row, i, style);
}
//Restore previous state
pivotTable.getRowFields().get(0).getPivotItems().get("State Apport. Factor").setHideDetail(true);
wb.save("f:\\files\\out.xlsx");
Please note, the new API PivotItem.setHideDetail/getHideDetail will be included in our upcoming release Aspose.Cells for Java v22.3. The release is expected either before the end of this week or next week early.
The issues you have found earlier (filed as CELLSJAVA-44408) have been fixed in this update. This message was posted using Bugs notification tool by Peyton.Xu
Yes, we did notify you in this thread when we released the supported/fixed version (see the below quote). The issue was fixed in Aspose.Cells for Java v22.3 but you may also try our latest version Aspose.Cells for Java v22.4.
Hi Amjad, A small correction which is going to end our story, That is the rows are working fine but when we are expanding jurisdictions, The state apport Factor and Marginal Tax Rate rows are reverting back to dollar can you send me the sample code for that too like the above code i.e., It should be in percentage even while expanding Jurisdiction column. I’m sending you the sample image in highlighting and a sample excel file.
Probably this is your current output Excel file by Aspose.Cells APIs. I tried to play upon your desired task for the pivot table in MS Excel manually but could not do that. I guess this is due to pivot table behavior (in MS Excel) as once you expand data under “Jurisdiction” pivot column/field, it tends to change the the data and formatting based on “ACME-HQ” original data.
Could you please share your expected Excel file, so once we expand the Jurisdiction pivot column, it still retain the percentage formatting for State Apport. Factor and Marginal Tax Rate field rows. You may accomplish the task manually in MS Excel (if you can do it) and save the Excel file to provide us here. We will check it on how to do it via Aspose.Cells API.
Yes, when we expand jurisdiction field and the respective states in that jurisdictions they have to be in percent format for State apport factor and Marginal tax rate. here is the expected result file and imagesState Reconciliation (2).zip (51.2 KB) SR.png (16.5 KB)
Thanks for the sample Excel file containing the pivot table to demonstrate your expected behavior.
I confirmed your expected behavior. We have logged a ticket with an id “CELLSJAVA-44628” for your task. We will check and may devise some code segment or other way around to retain percentage format of certain pivot rows when expanding node data of some pivot column/field. Once we have an update on it, we will let you know here.
@SakethDodda,
After research, it will be 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, it will be difficult to maintain format consistency. So we decide to support formatting the entire row of data in the PivotTable. We have created a ticket “CELLSJAVA-44632” to trace this new feature.
Your issue has been resolved now. The fix will be included in the next release (Aspose.Cells for Java v22.6) which is scheduled in the second week of June 2022. You will also be notified when the next version is published.
Aspose.Cells for Java v22.6 which is scheduled before the end of second week of June 2022. The date is not finalized as we publish releases once ready.