Hi Aspose Team,
it looks like ASPOSE.Cells version 2.0.0.4 for Java destroys formulas that cover a whole row/column if the insertRangeShiftXxxx Method is used:
final Cells cells = sheet.getCells();
cells.getCell(“A1”).setFormula("=SUM(B:B)"); // calc sum of column b
System.out.println("Initial formula: " + cells.getCell(“A1”).getFormula());
// Prints ‘Initial formula: =SUM(B$1:B$65536)’ already modified but result is correct
cells.insertRangeShiftDown(/B1/ 0 , 1, /B6/ 5, 1);
System.out.println("Destroyed formula: " + cells.getCell(“A1”).getFormula());
// Prints ‘Destroyed formula: =SUM(B$7:B$6)’
The Formula should still sum up the b column, but there are 2 issues:
- the start value row is shifted down (increased), this seems to be because of the initial transformation of the range from ‘B:B’ to ‘B$1:B$65536’
- the end value additionaly flows over at 65536 (65536 + 6 & 0xFFFF =
BTW: How is it possible to insert data above the 1st row, cells.insertRangeShiftDown(-1, 1, -1, 1); does not seem to work.
Thanks in advance
Hi,
Thank you for considering Aspose.
Your mentioned issue (regarding insertRangeShift method) has already been fixed in our latest release. Please try the attached latest version of Aspose.Cells.
To insert data above the 1st row, the startRowIndex should be 0. In fact, your code, cells.insertRangeShiftDown(/B1/ 0 , 1, /B6/ 5, 1); is inserting data above the 1st row. After this insertion, the original 1st row will become the 7th row.
Thank You & Best Regards,