Cells.InsertRows() and Cells.DeleteRows() don't update formula and chart references

Aspose.Cells Product Family
1

Please see the attached template file. I’ve discovered the following behavior with the above methods:

1) Cells.InsertRows() dynamically updates formula references but not chart ranges.
2) Cells.DeleteRows() does not dynamically update formula references. It may also affect chart ranges, though there’s no way to test that until InsertRows is fixed.

To confirm that Excel should behave this way, insert a row between rows 3 and 4 in the template and add some data. You’ll see the sum and chart update itself. Do the same using InsertRows() and the formula will update but the chart will not.

Now reload the template, insert a row between 3 and 4, then delete row 5. Both the chart and formula update themselves. Do the same with the DeleteRows() method and you should get a circular reference error because the formula did not update itself.

Thanks, Natan.

2

Quick follow-up:

I tested the InsertRow() method and it dynamically updates the chart. I would rather use InsertRows() though.

3

Also, please make sure that pivot tables are dynamically updated as well.

Thanks.

4

Please try this attached fix.

5

This hotfix did not resolve the problem. Charts are not dynamically updated using the InsertRows() method. I also get a circular reference error because DeleteRows() is not updating formulas.

6

Please download and try v3.4.5. Bug in DeleteRows method is also solved.

7

The InsertRows() method seems to be fixed. I’m still having trouble with DeleteRows() method. Charts and formulas are not dynamically updated when rows are deleted from the spreadsheet. To see Excel do this, do the following:

1) Put values in A1 and A2.
2) A3 gets the formula SUM(A1:A2).
3) Insert a row between rows 1 and 2 and put a value in A2.

The sum is now SUM(A1:A3).

4) Delete row 3 and the sum is now SUM(A1:A2).

With 3.4.5 I’m still getting a circular reference error because the cell references are not updated. Charts are also affected in a similar way.

8

Please try this fix.

9

Third time’s a charm. Thanks!

10

@cohenn,
Aspose.Cells has replaced Aspose.Excel that is discontinued and no more available. It contains all the latest features provided by different versions of MS Excel. You can insert and delete rows/columns without any error similar to MS Excel where formulas are updated after deleting or inserting rows and columns. Here is an example that demonstrates this feature where a formula is calculated before and after deleting a row and it contains results as per the expectation.

Workbook workbook = new Workbook();
workbook.Worksheets[0].Cells["A1"].Value = 5;
workbook.Worksheets[0].Cells["A2"].Value = 10;
workbook.Worksheets[0].Cells["A3"].Value = 15;
workbook.Worksheets[0].Cells["A4"].Formula = "=SUM(A1:A3)";

workbook.CalculateFormula();
Console.WriteLine($"Before deleting row: {workbook.Worksheets[0].Cells["A4"].Value}");

workbook.Worksheets[0].Cells.DeleteRow(1);
workbook.CalculateFormula();
Console.WriteLine($"After deleting row: {workbook.Worksheets[0].Cells["A3"].Value}");

For more information about inserting and deleting rows and columns, refer to the following article:
Insert or Delete Rows or Columns

Download the latest trial version here:
Aspose.Cells for .NET (Latest Version)

Download a complete runnable solution here to test a variety of features of this product.