Free Support Forum - aspose.com

insertRows issue

Hi.


I have founded one issue, when insertRows works incorrectly. I have prepared independent test for you, but it works correctly.

1) i have a several rows with a function, that links to cell in row above.
2) i have inserted a several rows after first row
3) i assume, that all functions will affect inserting and move cell’s link, but in my case it fails.

@Test
public void insertRowsTest() throws Exception {
//Works
Workbook workbook = new Workbook(“D://in.xlsx”);
Cells cells = workbook.getWorksheets().get(0).getCells();

System.out.println(cells.get(“B3”).getFormula());
System.out.println(cells.get(“B4”).getFormula());

cells.insertRows(2, 4, true);

System.out.println(cells.get(“B7”).getFormula()); //Not affected because row 8 is above on inserted range
System.out.println(cells.get(“B8”).getFormula()); //Affected

workbook.save(“D://out.xlsx”);

assertEquals("=IF(C8<=$C$1,DATE(YEAR($B$1),C7+1,1),"")", cells.get(“B8”).getFormula());

System.out.println("");

//Fail
workbook = new Workbook(“D://in1.xls”);
cells = workbook.getWorksheets().get(1).getCells();

System.out.println(cells.get(“B9”).getFormula());
System.out.println(cells.get(“B10”).getFormula());

cells.insertRows(8, 4, true);

System.out.println(cells.get(“B13”).getFormula()); //Not affected because row 8 is above on inserted range
System.out.println(cells.get(“B14”).getFormula()); //Not affected

workbook.save(“D://out1.xls”);

assertEquals("=IF(C14<=$C$1,DATE(YEAR($B$1),C13+1,1),"")", cells.get(“B14”).getFormula());
}

As you can see in out.xlsx all is fine. All formulas was affected.

But in in.xls (probably format is important) Formulas for second sheet (“Скважины”), column B (starts with row 14) are not affected inserting rows.

Best regards. Alexey


Also you can see, that part of formula was affected by inserting (C10<=$C$1) ->(C14<=$C$1), but another part of formula does not.

Hi Alexey,

Thanks for your posting and using Aspose.Cells.

We were able to observe this issue and found that in case of xls file, formulas are not updated correctly. However in case of xlsx file, it works correctly. We tested this issue with your excel files and sample code using the latest version: Aspose.Cells
for Java v8.4.1.5
.

The formula in cell B14 in output xls file is

=IF(C14<=$C$1,DATE(YEAR($B$1),C9+1,1),"")

but the correct formula should be

=IF(C14<=$C$1,DATE(YEAR($B$1),C13+1,1),"")

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-41331 - Formulas are not updated correctly after insert rows

Hi,

Thanks for using Aspose.Cells.

Please download and try the latest fix: Aspose.Cells
for Java v8.4.2.1
and let us know your feedback.

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


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