We recently had a defect appear in our product, and we have chased it down to something having changed in aspose-cells for java 21.12. 21.11 works as before, then the change happened in 21.12 and the change persists in every release we’ve tried after that up to 24.2.
In the release notes:
I suspect one of these two may be the culprit (based solely on the title as I know no details of each):
CELLSJAVA-43469
“Possible regression: Performance degradation of FormatConditionCollection.addArea()”
CELLSJAVA-44000
“Cells style is incorrect in HTML when using icon set and other conditional formatting at the same time”
What we found is that having several FormatConditions on a cell, to set the color of a cell based on a text value, no longer sets the correct color of every cell. We were able to figure out a workaround that involves creating one FormatConditionCollection per FormatCondition that we want to set, then doing a addArea() for each of those FormatConditionCollections. This creates unnecessary collections, since the whole point of a collection should be to store more than one element, not just a single element. But, I confirmed adding more elements then doing a addArea() of the entire collection breaks the colors from 21.12 onward.
Here is our standalone test file that demonstrates a case that works, plus several that don’t. Simply put the approriate jar in the classpath, comment out the case you want to try, run it, then open up the output file.
MainCells.java.zip (2.2 KB)
Here’s a code snippet of broken1() so others can see what this is about:
public static void broken1(Worksheet worksheet) {
CellArea area = CellArea.createCellArea(2, 1, 4, 1);
ConditionalFormattingCollection cfs = worksheet.getConditionalFormattings();
// Create one collection; should be able to hold 3 FormatConditions, right?
int index = cfs.add();
FormatConditionCollection fcs = cfs.get(index);
// Create 3 FormatConditions, one per column value that we have
int i1 = fcs.addCondition(FormatConditionType.CELL_VALUE, OperatorType.EQUAL, "first", null);
int i2 = fcs.addCondition(FormatConditionType.CELL_VALUE, OperatorType.EQUAL, "second", null);
int i3 = fcs.addCondition(FormatConditionType.CELL_VALUE, OperatorType.EQUAL, "third", null);
FormatCondition fc1 = fcs.get(i1);
FormatCondition fc2 = fcs.get(i2);
FormatCondition fc3 = fcs.get(i3);
// Get each condition's style
Style ss1 = fc1.getStyle();
Style ss2 = fc2.getStyle();
Style ss3 = fc3.getStyle();
// Create the colors
// NOTE: replace these RGB values with built-in colors and it somehow works
Color color1 = hex2Color("FF0000");
// Color color1 = Color.getRed();
Color color2 = hex2Color("FFFF66");
// Color color2 = Color.getYellow();
Color color3 = hex2Color("9BDFFF");
// Color color3 = Color.getBlue();
// Set those colors as the background of each style when that condition triggers
ss1.setBackgroundColor(color1);
ss2.setBackgroundColor(color2);
ss3.setBackgroundColor(color3);
fc1.setStyle(ss1);
fc2.setStyle(ss2);
fc3.setStyle(ss3);
// Set this whole collection on the area all at once. I would expect this to be able to apply all the conditions in this collection
// to the area.
fcs.addArea(area);
}
Here’s an explanation of the various scenarios in that file:
works(): We got this one to work by creating one collection per condition. And, each condition has to be fully processed to the point of calling addArea() before moving on to the next condition.
broken1(): This attempts to use one collection for all 3 of the conditions, followed by a single addArea() at the end. This used to work. What’s extra strange here is if I create the Color instances using convenience methods like Color.getRed() instead of the RGB values, it gets the colors correct! We have to use RGB values though; convenience methods for colors would reduce functionality of our product.
broken2(): This is same as broken1() but rearranged so that each condition is fully processed before moving on to the next condition.
broken3(): This is a variation of works() in that we’re still making one collection per condition. All that changed is that work on each condition is interspersed so that we’re not completing all the work for one condition all in one block of code. But, this line of code will fail with an index out of bounds:
int i1 = fcs1.addCondition(FormatConditionType.CELL_VALUE, OperatorType.EQUAL, “first”, null);
It does not seem logical to me that there should be an index out of bounds, since the API calls that created the collection then the condition are using the index returned by the Aspose API, not some incorrect number we made up.
Here is what the colors look like when it works:
image.jpg (190.1 KB)
And when it doesn’t work:
image.jpg (180.1 KB)
Any help here much appreciated! And, we’d really like if it could go back to working as it did in 21.11. Thanks!