InsertCutCells - Cutting rows and then inserting them somewhere else does not maintain formula references

Aspose.Cells Product Family
1

Hi there,

I am using Aspose.Cells version 23.5.0.
I want to create a method which cut whole rows and insert them above a specific row index. For that I am using the InsertCutCells method.

 private void MoveRows(Worksheet worksheet, int startRow, int endRow, int rowIndexDestination)
    {
        var usedRangeLastColumnIndex = worksheet.Cells.MaxColumn;

        var rowsRangeToCut = worksheet.Cells.CreateRange(
            CellArea.CreateCellArea(
                startRow,
                0,
                endRow,
                usedRangeLastColumnIndex));

        // Cut the rows and insert them above the destination row
        worksheet.Cells.InsertCutCells(rowsRangeToCut, rowIndexDestination, 0, ShiftType.Down);
    }

The issue is that cells with formulas lose their references, which does not happen when I use Interop’s c# methods:

var destinationRange = worksheet
.Range[worksheet.Cells[rowIndexDestination, 1], worksheet.Cells[rowIndexDestination, usedRangeLastColumnIndex]];
rowsRangeToCut.Cut();
destinationRange.Insert(XlInsertShiftDirection.xlShiftDown);

In the attached zip file I have the input.xlsx, output.xlsx and expected.xlsx.

move-rows-example.zip (31.7 KB)

Where startRow = 7, endRow = 11, rowIndexDestination = 17.

Please advise,

Thanks,
Shlomi

2

@shlomi.z,

Thanks for the template file.

I tested your scenario/case using our latest version/fix: Aspose.Cells for .NET v23.5 (Releases | NuGet), it works fine and as expected. Please try latest version/fix and let us know if you still find any issue.

3

Hi @amjad_sahi,

In the examples I attached, I also used Aspose.Cells v23.5.
If you noticed in the attached zip, there’s a file called output.xlsx.
The issue there is that some formula cells show ‘#REF!’, as depicted in the following screenshot. It is an unwanted result, and different from interop c#, or when user cut rows and inserts them.

image.png (27.8 KB)

Thanks.

4

@shlomi.z,

Thank you for pointing out the issue and now we find those #REF! values in those copied cells. We will look into the issue soon.

5

@shlomi.z
We have opened the following new ticket(s) in our internal issue tracking system and will deliver their fixes according to the terms mentioned in Free Support Policies.

Issue ID(s): CELLSNET-53406

You can obtain Paid Support Services if you need support on a priority basis, along with the direct access to our Paid Support management team.

6

Hi @amjad.sahi,

Thank you for the quick response.
As we are obligated to supply a quick solution to our clients, is there a workaround for the InsertCutCells issue, until it will be solved?

Thanks,
Shlomi

8

@shlomi.z,

As workaround, we think you may simulate the cut-paste progress step by step to get the expected result. Please try below code block:

            int usedRangeLastColumnIndex = cells.MaxColumn;
            var rowsRangeToCut = cells.CreateRange(7, 0, 5, usedRangeLastColumnIndex);

            //copy rangeToCut to end of data range as cache
            Range tmp = cells.CreateRange(cells.MaxDataRow + 1, 0, 5, usedRangeLastColumnIndex);
            tmp.Copy(rowsRangeToCut);
            //delete rangeToCut
            cells.DeleteRange(rowsRangeToCut.FirstRow, rowsRangeToCut.FirstColumn,
                rowsRangeToCut.FirstRow + rowsRangeToCut.RowCount - 1,
                rowsRangeToCut.FirstColumn + rowsRangeToCut.ColumnCount - 1, ShiftType.Up);
            //insert range for copying later
            cells.InsertRange(CellArea.CreateCellArea(17 - rowsRangeToCut.RowCount,
                rowsRangeToCut.FirstColumn, 16,
                rowsRangeToCut.FirstColumn + rowsRangeToCut.ColumnCount - 1), ShiftType.Down);
            //copy the cached data to destination
            cells.CreateRange(17 - rowsRangeToCut.RowCount, rowsRangeToCut.FirstColumn,
                rowsRangeToCut.RowCount, rowsRangeToCut.ColumnCount).Copy(tmp);
            //delete the cached data
            cells.DeleteRange(tmp.FirstRow, tmp.FirstColumn,
                tmp.FirstRow + tmp.RowCount - 1,
                tmp.FirstColumn + tmp.ColumnCount - 1, ShiftType.Up);
9

@shlomi.z,

We are pleased to inform you that your issue (logged earlier as “CELLSNET-53406”) has been resolved. The fix will be included in our upcoming release (Aspose. Cells v23.6) that we plan to release in the first half of June 2023. You will be notified when the next version is released.

10

The issues you have found earlier (filed as CELLSNET-53406) have been fixed in this update. This message was posted using Bugs notification tool by johnson.shi

11

Hi @amjad.sahi,

I’ve upgraded to Aspose.Cells for .NET 23.6.
Issue still persists:
Still showing #REF!
image.png (31.0 KB)

Please advise,

Thanks,

Shlomi

12

@shlomi.z,

Thanks for the screenshot. We will be looking into it soon.

We are sorry for any inconvenience caused!

13

@shlomi.z,

We have tested the code with your specified parameters with our latest version but cannot find the issue. Formulas in the generated file are all fine. Would you please confirm you are using the correct version(you may output GetVersion() to confirm the version number). If the version is no problem, please confirm you are using the same code and parameters as those in the original post. If not, please provide the console application you are using, we will check it soon.