Free Support Forum - aspose.com

Formula references not updating

Using Aspose.Cells 4.4.0 for .Net.

I have multiple sheets with multiple inserts and deletes on each one to output the data. Some of the formulas that refer to other sheets are not being updated correctly to take account of all of these inserts and deletes and end up pointing at the wrong rows. I have attached a simple example that shows that the SUM formula is incorrect at the end (although formulas using CELL1+CELL2+CELL3+CELL4 are updated correctly).

Source code:

Dim license As Aspose.Cells.License = New Aspose.Cells.License

license.SetLicense("Aspose.Cells.lic")

Dim excel As Aspose.Cells.Workbook = New Aspose.Cells.Workbook

excel.Open("c:\test.xls")

excel.Worksheets(0).Cells.InsertRows(0, 2, True)

excel.Worksheets(0).Cells.DeleteRows(0, 1, True)

excel.Worksheets(1).Cells.InsertRows(0, 2, True)

excel.Worksheets(1).Cells.DeleteRows(0, 1, True)

excel.Save("c:\testoutput.xls")

Thanks,

David

Hi David,

Please try the attached version. I tested it works fine using your template file.

Thank you.

Amjad,

Thanks for the update. The formulas are now being updated correctly. However, with 4.4.0.0 it took 36 seconds to generate the file in my project. With 4.4.0.30 it takes 200 seconds to generate with the CPU running between 50 and 60% for most of that. I have tried it 3 times with each version to ensure that the results are accurate. Do you know of any reason why it would be so much slower?

Thanks,

David

Hi David,

We will check it soon. However, we appreciate if you could create a sample test application and post it here to show the delay.

Thank you.

Hi David,

Please try this fix.

I think it is caused by that we update the formulas when we insert/delete rows

Amjad, Warren,

Sorry for not replying sooner - I was out of the office.

The latest version is back to the speed of 4.0.0.0.

Thanks,

David