Hi Team,
I try to replace value in pivot through conditional format, i wrote some code to replace value and background color of cell based on cell value.
My issue is while open file its showing background color and after open its showing normal , background color missing.
This is My code ::
ConditionalFormattingCollection cfs = sheet2.getConditionalFormattings();
int index123 = cfs.add();
FormatConditionCollection fcs = cfs.get(index123);
CellArea ca = new CellArea();
ca = new CellArea();
fcs.addArea(dataBodyRange);//geting all body of pivot values here
int idx = fcs.addCondition(FormatConditionType.CELL_VALUE);
FormatCondition fc = fcs.get(idx);
fc.setFormula1(“224”);
fc.setOperator(OperatorType.EQUAL);
fc.setText(“NA”);
fc.getStyle().setBackgroundColor(Color.getRed());
Please provide solution to replace values in pivot through conditional format.
Please find attached pivot sheet for reference.
Thank you.
Hi,
Thanks for the template file, sample code and details.
We did evaluate your issue a bit. Please try the following sample code as a workaround, it would work fine a bit:
e.g
Sample code:
Workbook book = new Workbook(“Coalition_Advance_Cross_Tab_05042017_1102.xlsx”);
Worksheet sheet2 = book.getWorksheets().get(“Advanced CrossTab”);
PivotTable table = book.getWorksheets().get(“Advanced CrossTab”).getPivotTables().get(0);
PivotFormatConditionCollection pfcc = table.getPivotFormatConditions();
int pIndex = pfcc.add();
PivotFormatCondition pfc = pfcc.get(pIndex);
FormatConditionCollection fcc = pfc.getFormatConditions();
CellArea dataBodyRange = table.getDataBodyRange();
fcc.addArea(dataBodyRange);
int idx = fcc.addCondition(FormatConditionType.CELL_VALUE);
FormatCondition fc = fcc.get(idx);
fc.setFormula1(“224”);
fc.setOperator(OperatorType.EQUAL);
fc.setText(“NA”);
fc.getStyle().setBackgroundColor(Color.getRed());
table.setRefreshDataOnOpeningFile(false);
book.save(“out1.xlsx”);
However, we still think there is an issue when MS-Excel refreshes the PivotTable as the conditional formats will be lost. We got to solve this issue when MS-Excel refreshes automatically, so we will soon add a ticket for it and let you know the ticket id here.
Thank you.
Hi,
As we told you, there is an issue when MS-Excel refreshes the Pivot Table, the conditional formattings will be lost. I used the following sample code with your template file to reproduce the issue:
e.g
Sample code:
Workbook book = new Workbook(“Coalition_Advance_Cross_Tab_05042017_1102.xlsx”);
Worksheet sheet2 = book.getWorksheets().get(“Advanced CrossTab”);
PivotTable table = book.getWorksheets().get(“Advanced CrossTab”).getPivotTables().get(0);
table.refreshData();
table.calculateData();
//We can also use Pivot format condition.
/*
PivotFormatConditionCollection pfcc = table.getPivotFormatConditions();
int pIndex = pfcc.add();
PivotFormatCondition pfc = pfcc.get(pIndex);
FormatConditionCollection fcc = pfc.getFormatConditions();
CellArea dataBodyRange = table.getDataBodyRange();
fcc.addArea(dataBodyRange);
int idx = fcc.addCondition(FormatConditionType.CELL_VALUE);
FormatCondition fc = fcc.get(idx);
fc.setFormula1(“224”);
fc.setOperator(OperatorType.EQUAL);
fc.setText(“NA”);
fc.getStyle().setBackgroundColor(Color.getRed());
*/
ConditionalFormattingCollection cfs = sheet2.getConditionalFormattings();
int index123 = cfs.add();
FormatConditionCollection fcs = cfs.get(index123);
CellArea dataBodyRange = table.getDataBodyRange();
//ca = new CellArea();
fcs.addArea(dataBodyRange);//geting all body of pivot values here
int idx = fcs.addCondition(FormatConditionType.CELL_VALUE);
FormatCondition fc = fcs.get(idx);
fc.setFormula1(“224”);
fc.setOperator(OperatorType.EQUAL);
fc.setText(“NA”);
fc.getStyle().setForegroundColor(Color.getRed());
book.save(“out1.xlsx”);
I have logged a ticket with an id “CELLSJAVA-42247” for your issue. We will look into it soon.
Once we have an update on it, we will let you know here.
Thank you.
HI Team,
any update on this issue.
regard conditional format
Thank you.
Hi,
I am afraid, your issue is not resolved yet. However, I have asked the concerned developer from product team to update on it or provide an ETA (if possible).
Once we receive new news, we will share with you immediately here.
Sorry for the inconvenience caused!
Hi,
We got a response from product team. We are glad to inform you that our product team is working over your issue and hopefully we will provide you the fixed version before the end of this month.
Keep in touch.
Thank you.
Hi,
Please try our latest version/fix: Aspose.Cells for Java v17.4.2
Your issue should be fixed in it.
Let us know your feedback.
Also, there are some tips for you to follow:
- Please use the following sample code to test it"
e.g
Sample code:
Workbook book = new Workbook(filePath + "Coalition_Advance_Cross_Tab_05042017_1102.xlsx");
Worksheet sheet2 = book.getWorksheets().get("Advanced CrossTab");
PivotTable table = book.getWorksheets().get("Advanced CrossTab").getPivotTables().get(0);
table.refreshData();
table.calculateData();
//We can also use Pivot format condition.
PivotFormatConditionCollection pfcc = table.getPivotFormatConditions();
int pIndex = pfcc.add();
PivotFormatCondition pfc = pfcc.get(pIndex);
FormatConditionCollection fcc = pfc.getFormatConditions();
CellArea dataBodyRange = table.getDataBodyRange();
fcc.addArea(dataBodyRange);
int idx = fcc.addCondition(FormatConditionType.CELL_VALUE);
FormatCondition fc = fcc.get(idx);
fc.setFormula1("224");
fc.setOperator(OperatorType.EQUAL);
fc.setText("NA");
fc.getStyle().setBackgroundColor(Color.getRed());
//please don't use the following commented code to test it, the conditional formats will be lost when MS Excel refreshes the PivotTable automatically.
//Because the conditional formats in the worksheet will be covered by PivotTable's formats.
/*
ConditionalFormattingCollection cfs = sheet2.getConditionalFormattings();
int index123 = cfs.add();
FormatConditionCollection fcs = cfs.get(index123);
CellArea dataBodyRange = table.getDataBodyRange();
//ca = new CellArea();
fcs.addArea(dataBodyRange);//geting all body of pivot values here
int idx = fcs.addCondition(FormatConditionType.CELL_VALUE);
FormatCondition fc = fcs.get(idx);
fc.setFormula1("224");
fc.setOperator(OperatorType.EQUAL);
fc.setText("NA");
fc.getStyle().setForegroundColor(Color.getRed());
*/
book.save(filePath + "out_java.xlsx");
Let us know your feedback.
Thank you.
The issues you have found earlier (filed as CELLSJAVA-42247) have been fixed in Aspose.Cells for Java 17.5.
This message was posted using Notification2Forum from Downloads module by Aspose Notifier.