Update the formula references after using Cells.DeleteRow and Cells.DeleteColumn using Aspose.Cells for .NET in C#

Aspose.Cells Product Family
1

Hi,

I recently found a bug in these methods where calling them correctly deletes the row/column on the workbook, but does not update the formula references based on this deletion. I am attaching a snippet of the code I ran:

using NUnit.Framework;
using System;

namespace Aspose_bug_in_Delete_Row
{
    class Program
    {
        static void Main(string[] args)
        {
            using (Workbook workbook = new Workbook())
            {
                Worksheet sheet2 = workbook.Worksheets[workbook.Worksheets.Add()];
                Worksheet sheet3 = workbook.Worksheets[workbook.Worksheets.Add()];
                string formula = $"{sheet2.Name}!B2";
                sheet3.Cells["A1"].Formula = formula;

                sheet2.Cells.DeleteRows(
                    rowIndex: 0,
                    totalRows: 1);

                workbook.CalculateFormula();

                Assert.AreEqual(
                    expected: $"={sheet2.Name}!B1",
                    actual: sheet3.Cells["A1"].Formula);
            }
        }
    }
}

The result that we get is this:

test failure.PNG (23.6 KB)

We are aware that there are methods (like the ones below) that can be used to update references, but updating references and recalculating with each deletion seems computationally excessive. Without this bug we would be able to aggregate delete operations and ideally have Workbook.CalculateFormula() update the references for us.

        public void DeleteColumn(int columnIndex);

        public void DeleteColumn(int columnIndex, bool updateReference);
  
        public void DeleteColumns(int columnIndex, int totalColumns, bool updateReference);

        public void DeleteRow(int rowIndex);

        public bool DeleteRows(int rowIndex, int totalRows);
        
        public bool DeleteRows(int rowIndex, int totalRows, bool updateReference);

This bug leads to some undesired compromise of the integrity of workbook formulas and it would be great if you could fix this on your end. Thanks.

Best,
YJ

2

@yaash45,
We have analysed your sample code and program output. We cannot state it as a bug as it is expected behaviour as per the documentation and provided overloaded functions. As per your requirement, the delete operations should be aggregated and CalculateFormula() shall update references at the end. This mechanism can be implemented for smaller changes like in your sample code but it seems to be inappropriate for large programs where lot of deletions and formulas are used. Now if we keep on aggregating the deletion operations then what will be the scenario for the in-between chained-calculations after deleting rows or columns. It is not possible to defer processing data till the end of the program where CalculateFormula() is called. If you look into Excel, and set the calculation mode to manual, even then it updates references after each delete operation.

Thus it does not seem to be a bug in the product and you have to use the relevant overloaded function for deleting rows or columns.

3

Thank you very much for the clarification. We will keep this in mind while constructing our logic.

Best,
YJ

4

@yaash45,
You are welcome.