Aspose Cells Java: Conditional formatting range is split after Cells.copyColumns

Aspose.Cells Product Family
1

Hi,

There seem to be some problem with splitting/changing conditional formatting cells range only if performing Cells.copyColumns,
I couldn’t attach XSLX file (please let me know how if there is such an option).
But it is easy to reproduce based on the following test code and information I provide below:

public class ConditionalFormattingTestCase {
public static void main(String[] args) throws Exception {
Workbook wb = new Workbook(“cond-formatting.xslx.xlsx”);
Worksheet ws = wb.getWorksheets().get(0);
printConditionalFormattingAreas(ws, “In template”);
ws.getCells().insertRows(5, 1, true);
printConditionalFormattingAreas(ws, “After insert row”);
ws.getCells().copyRows(ws.getCells(), 4, 5, 1);
printConditionalFormattingAreas(ws, “After copy row”);

	ws.getCells().insertColumns(4, 1, true);
	printConditionalFormattingAreas(ws, "After insert column");
	ws.getCells().copyColumns(ws.getCells(), 3, 4, 1);
	printConditionalFormattingAreas(ws, "After copy column");
}

private static void printConditionalFormattingAreas(Worksheet ws, String pStage) {
    ConditionalFormattingCollection fms = ws.getConditionalFormattings();
    System.out.println("--------- " + pStage + " --------------------");
    for (int j = 0; j < fms.getCount();j++) {
    	FormatConditionCollection fm = fms.get(j);
    	System.out.println(fm.getCellArea(0));
    }	
}

}

This will print:

--------- In template --------------------
Aspose.Cells.CellArea(D5:G7)[4,3,6,6]
--------- After insert row --------------------
Aspose.Cells.CellArea(D5:G8)[4,3,7,6]
--------- After copy row --------------------
Aspose.Cells.CellArea(D5:G8)[4,3,7,6]
--------- After insert column --------------------
Aspose.Cells.CellArea(D5:H8)[4,3,7,7]
--------- After copy column --------------------
Aspose.Cells.CellArea(D5:D8)[4,3,7,3]
Aspose.Cells.CellArea(E5:E8)[4,4,7,4]

As you can see there was a single range with conditional formatting.
It has grown as expected after rows and columns were inserted.
Everything was fine also after copyRows.
But copyColumn splits replaces existing single rule with range (D5:H8) with 2 rules with ranges D5:D8 and E5:E8 correspondingly.

I use version 22.10
The same actions in MS Excel do not produce this problem.

Regards,
Andriy

2

@softgenic_gmbh,

Please zip your template file (“cond-formatting.xslx.xlsx”) using some zip tool (e.g., WinRAR) and attach the zipped archive. We will check your issue soon.

3

Hi,

attaching the file
cond-formatting.zip (10.4 KB)

4

@softgenic_gmbh,

After an initial test, I reproduced the issue regarding conditional formatting using your template file. We need to investigate your issue in details. We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSJAVA-45203

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

5

@softgenic_gmbh,

For splitting formulas while some operations such as insert/delete/copy ranges/rows/columns, it is complicated and we cannot guarantee that the result will be same with ms excel always. The first thing we need to guarantee is the result of conditional formatting should not be broken for those operations. Anyways, we will investigate your case and evaluate whether we can make the areas of conditional formatting same with ms excel as you expected.

6

Thank you for prompt reply.

I’d like to clarify additionally regarding this statement. In fact this behaviour breaks the result of conditional formatting. The rule changes the color from very light to very dark depending on how large is the number in specified area.
And while all other operation correctly “grow” the range of single formatting rule that includes matrix with multiple rows and columns, copyColumns breaks consistency for 2 reasons:

  1. It removes conditional formatting from F, G, H columns in this example
  2. Coloring is done within single column sub-area, not in the whole matrix. As results if column D contains only zeros they will be dark-red. And in the next column positive amounts will have lighter color than zeros in column D.

Please see attached screenshots that illustrate the problem.Correct coloring.png (79.0 KB)
Current coloring.png (73.9 KB)

7

@softgenic_gmbh,

Thanks for sharing further details about coloring issue in conditional formatting. We have logged your details with screenshots in your existing ticket “CELLSJAVA-45203” into our database. We will look into it soon. Also, if we need more details and sample files, we will ask you for it.