We're sorry Aspose doesn't work properply without JavaScript enabled.

Free Support Forum - aspose.com

Invalid cell references in conditional formula


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();
range.copy(source, options);

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.

1. Open MS Excel and template file
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 for your posting and using Aspose.Cells.

We were able to replicate this issue with your sample code and source excel file using the latest version: Aspose.Cells for Java 8.7.2 as per your description. Conditional formatting range is incorrect and does not match with Microsoft Excel.

We have logged this issue in our database for investigation. We will look into it and fix this issue. Once the issue is resolved or we have some other update for you, we will let you know asap.

This issue has been logged as

  • CELLSJAVA-41803 - Conditional formatting range is incorrect and does not match with Microsoft Excel.


We are pleased to inform you that your issue logged earlier as “CELLSJAVA-41803” has been fixed. We will soon provide you the fix after performing QA and incorporating other enhancements and fixes.

Once the fix is available for public use, we will share the Download link with you here.

Thank you.

The issues you have found earlier (filed as CELLSJAVA-41803) have been fixed in this update.

This message was posted using Notification2Forum from Downloads module by Aspose Notifier.