Hi,
I have attached the template file. We have found that sheet changes make invalid cell references in conditional formula. We use the following code:
public void checkConditionalFormattingCopying() throws Exception {
Workbook wb = new Workbook(“c:/tmp.xlsx”);
Worksheet sheet = wb.getWorksheets().get(0);
System.out.println("Initial: " + sheet.getConditionalFormattings().get(0).getCellArea(0).toString());
Cells cells = sheet.getCells();
cells.insertRange(CellArea.createCellArea(0, 1, 4, 1), ShiftType.RIGHT);
Range range = cells.createRange(0, 1, 5, 1);
Range source = cells.createRange(0, 0, 5, 1);
PasteOptions options = new PasteOptions();
options.setPasteType(PasteType.ALL);
range.copy(source, options);
wb.save(“c:/out.xlsx”);
System.out.println("Final: " + sheet.getConditionalFormattings().get(0).getCellArea(0).toString());
}
Output looks as follows:
Initial: Aspose.Cells.CellArea(A1:E5)[0,0,4,4]Final: Aspose.Cells.CellArea(A1:B5)[0,0,4,1]
If you do the same things in MS Excel, conditional formula will be $A$1:$F$5.
2. Select B1:B5 range and insert cells with shift cells right
3. Select A1:A5 range and copy data, then paste copied data to B1:B5 range
4. Conditional formula will be $A$1:$F$5
Aspose.Cells makes range of conditional formula $A$1:$B$5 instead of correct A$1:$F$5.
Could you scrutinize this issue and fix the problem ASAP?
Thanks.