Issue with InsertCutCells Affecting Cross-Worksheet Formula References

Aspose.Cells Product Family
1

Hello,

Using Aspose.Cells 21.2.0, I would expect the following Nunit test case to pass:

  public void CutRowsWithShiftCrossWorksheetReferenceAsposeLevel()
        {
            var w = new Aspose.Cells.Workbook();
            var worksheet1 = w.Worksheets[0];
            var worksheet2 = w.Worksheets[w.Worksheets.Add()];

            var expectedFormula = $"={worksheet1.Name}!A3";

            worksheet2.Cells.CreateRange("A1")[0, 0].Formula = expectedFormula;
            var source = worksheet1.Cells.CreateRange("2:2");
            worksheet1.Cells.InsertCutCells(source, 0, 0, Aspose.Cells.ShiftType.Down);

            Assert.AreEqual(expectedFormula, worksheet2.Cells.GetCell(0, 0).Formula);
        }

Replicating the steps** in the Excel GUI (or Interop) results in the behavior expected in the test, but it seems that Aspose shifts the worksheet2 formula to refer to “A4” instead of “A3” on worksheet1.

**Steps and expected behaviour:

  1. Starting with a fresh workbook, add a 2nd worksheet and have any 2nd worksheet cell refer to a cell in the 1st worksheet
  2. Select an entire row above the referenced cell and cut-then-insert it above the referenced cell
  3. The formula should not have changed

Thanks for your time,
Alex

2 Likes
2

@ag.canalyst,

As you are shifting the cells down using ShiftType.Down, so this is due to your line of code:
worksheet1.Cells.InsertCutCells(source, 0, 0, Aspose.Cells.ShiftType.Down);

Please change the line of code to:
worksheet1.Cells.InsertCutCells(source, 0, 0, Aspose.Cells.ShiftType.Right);
it will work fine as I tested.

Hope, this helps a bit.

3

Hi @Amjad_Sahi,

Thanks for your response. How about the following case?workbooks.zip (21.6 KB)

        var w = new Aspose.Cells.Workbook(@"example-before.xlsx");
        var sheet1 = w.Worksheets[0];

        var source = sheet1.Cells.CreateRange("4:4");

        sheet1.Cells.InsertCutCells(source, 0, 0, Aspose.Cells.ShiftType.Down);

        w.Save(@"example-after-aspose-shift-down.xlsx");

I have attached a few workbooks

  • The “before” workbook (example-before)
  • The result of manually cutting row 4 and pasting into cell A1 in the Excel GUI (example-after-manual)
  • The result of the above code performing the equivalent operation in Aspose (example-after-aspose-shift-down)

I also tried to generate a 4th workbook with a rightwards shift (it would have been example-after-aspose-shift-right), but replacing “ShiftType.Down” with “ShiftType.Right” in the code causes the following exception: “Aspose.Cells.CellsException : Aspose.Cells cannot shift nonblank cell off the worksheet.”

Note that example-after-manual’s “Sheet2!A1” has a value of 4 whereas example-after-aspose-shift-down’s “Sheet2!A1” has a value of 0.

Based on these results, I would gather the following:

  1. Shift down is the correct operation I should be using. I should not be using shift right.
  2. Aspose’s shift down causes issues with cross-worksheet formula references.

Thank you,
Alex

4

@ag.canalyst,
Thank you for providing more information. We will analyze it and provide our feedback accordingly.

5

@ag.canalyst,

Please notice, we were able to reproduce the issue as you mentioned using you samples/test cases. We tried to workaround it but to no avail, so we need to investigate and figure it out in the APIs. We have logged a ticket with an id “CELLSNET-47906” for your issue. We will look into your issue soon.

Once we have an update on it, we will let you know.

6

Thank you @ahsaniqbalsidiqui and @Amjad_Sahi for investigating this issue. I look forward to your response.

Best Regards,
Alex

7

@ag.canalyst,

Sure thing, once we have any new information available, we will share it with you.
Hopefully, your issue will be resolved soon.

8

@ag.canalyst,
This is to inform you that we have fixed your issue now. We will soon provide you the fixed version after performing QA and incorporating other enhancements and fixes.

9

@ag.canalyst
Please try the latest fix 21.2.9.
Aspose.Cells21.2.9 For .Net2_AuthenticodeSigned.Zip (5.5 MB)
Aspose.Cells21.2.9 For .Net4.0.Zip (5.5 MB)
Aspose.Cells21.2.9 For .NetStandard20.Zip (5.5 MB)

10

Hi @simon.zhao and @ahsaniqbalsidiqui,

Thank you very much for your quick response to this issue. I can confirm that the attached dlls no longer demonstrate the issue and our code which uses Aspose now functions as expected.

I look forward to seeing this fix in the next Aspose release on Nuget.

Best,
Alex

11

@ag.canalyst,
You are welcome. This fix will be available through NuGet manager till the end of this week.

1 Like
12

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

1 Like