I am using Cells.Net and using the following code to move rows up or down:
workSheet.Cells.InsertCutCells(workSheet.Cells.CreateRange(srcPos, count, false), dstPos, 0, ShiftType.Down);
This code works perfectly, except when the moved rows contain formulas. It seems that the cell references in these formulas are not being preserved, but rather, they are being offset.
For example, a sheet like this
The above code is called with srcPos=1, count=1, dstPos=3, result is:
The formula references another cell after moving down.
My expected result is ( same with performing equivalent operation in Microsoft Excel):
Is this a bug, or are there other more suitable APIs to achieve my goal?
Test by creating sample file and using the latest version Aspose.Cells for. NET v23.11. We can obtain the correct results. Please refer to the attachment (12.4 KB).
The sample code as follows:
Workbook workbook = new Workbook(filePath + "a.xlsx");
Worksheet workSheet = workbook.Worksheets;
workSheet.Cells.InsertCutCells(workSheet.Cells.CreateRange(1, 1, false), 3, 0, ShiftType.Down);
workbook.Save(filePath + "out_net.xlsx");
If you still have questions or confusion. Please provide sample files and executable sample code, and we will check them soon.
I upgraded to 23.11, and the results completely met my expectations. Additionally, a similar bug that I hadn’t had a chance to describe was also fixed. Thank you very much for your response.
It seems like I accidentally deleted my previous reply, so I’m repeating it now.
You are welcome. I’m glad your issue has been resolved. If you have any questions, please feel free to contact us.