Conditional formatting not being extended while shifting rows

Aspose.Cells Product Family
1

Start from a simple Excel file like the one attached.

test.zip (6.6 KB)

Manually via Excel:

Given an initial cell of =ROW(G10) (which references the row number) and a conditional formatting rule set to that cell.

image.png (25.6 KB)

Insert rows

Copy the row

image.png (14.5 KB)

Insert copied cells

image.png (15.4 KB)

Conditional formatting expanded across inserted rows

image.png (32.2 KB)

Shift range

Copy the cell

image.png (7.5 KB)

Insert copied cells

image.png (10.3 KB)

image.png (11.1 KB)

Conditional formatting expanded across cell range

image.png (15.2 KB)

This does not work in aspose 23.6.0. Note that it did work in 19.9.1, which we had to revert to:

// create space beneath the LK call to be filled by copied content
worksheet.Cells.InsertRange(ca, ShiftType.Down);
// copy styling and formulas from LK call row
toCopy = worksheet.Cells.CreateRange(row, col, 1, expandBy);
// define the range the copied row will be applied to
toCreate = worksheet.Cells.CreateRange(row, col, length, expandBy);
// make the copy
toCreate.Copy(toCopy);
2

@bblinn,

Thank you for providing the Excel file template and screenshots. However, I was unable to accurately evaluate your issue using the code snippet you provided, as I am unsure about some of the variables and objects and their associated data used in the segment. It would be greatly appreciated if you could provide a complete, runnable sample code or, preferably, a standalone console application (please zip the project prior to attaching) that we can use to evaluate and reproduce the issue on our end. This way, we can trace the issue and resolve it promptly.

P.S. Please exclude the latest version of Aspose.Cells.Dll to minimize the size of the zipped archive.

3

Hello, please find sample code below. The example file is the test.zip in the original post.

Shift range example

var ca = new CellArea();
ca.StartRow = 10;
ca.EndRow = 202; 
ca.StartColumn = 5;
ca.EndColumn = 5;


worksheet.Cells.InsertRange(ca, ShiftType.Down);
toCopy = worksheet.Cells.CreateRange(9, 5, 1, 1);
toCreate = worksheet.Cells.CreateRange(9, 5, 194, 1);
toCreate.Copy(toCopy);

Insert rows example

var ca = new CellArea();
ca.StartRow = 10;
ca.EndRow = 202; 
ca.StartColumn = 5;
ca.EndColumn = 5;

// insert rows example
worksheet.Cells.InsertRows(10, 193, false);
for (var r = 0; r < 194; r++) {
    if (r > 0) {
        // copy styling and formulas
        worksheet.Cells.CopyRow(worksheet.Cells, 9, 9 + r);
    }
}
5

@bblinn
Through testing, we can reproduce the issue and found that the conditional format range did not change after shifting the cells.
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): CELLSNET-53737
6

@bblinn,

This is to inform you that your issue has been resolved now. The fix will be included in an upcoming release (Aspose.Cells v23.8) that we plan to release in the first half of August 2023. You will be notified when the next version is released.

7

The issues you have found earlier (filed as CELLSNET-53737) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi

8

Hello, this latest version of 23.8.0 works for the specific case mentioned above. But when the start column is not the same as the end column, the behavior reverts to creating an individual style per cell instead of a single style for the whole range

        var ca = new CellArea();
        ca.StartRow = 10;
        ca.EndRow = 202;
        ca.StartColumn = 5;
        ca.EndColumn = 6;


        worksheet.Cells.InsertRange(ca, ShiftType.Down);
        var toCopy = worksheet.Cells.CreateRange(9, 5, 1, 2);
        var toCreate = worksheet.Cells.CreateRange(9, 5, 194, 2);
        toCreate.Copy(toCopy);
9

@bblinn,

Thank you for providing the details. I was able to reproduce the issue as you mentioned using your updated code segment. I found that while the conditional formatting is applied to the whole range, it creates individual styles per cell instead of a single style for the entire range.

We need to evaluate 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): CELLSNET-53978

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.

10

@bblinn,

We are pleased to inform you that your issue “CELLSNET-53978” has been resolved. The fix will be included in our upcoming release Aspose.Cells v23.9 which is due in the first half of September next month. You will be notified once the new version is published.

11

The issues you have found earlier (filed as CELLSNET-53978) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi