DeleteRow() method not updating formulas properly

Aspose.Cells Product Family
1

Laurence,

Looks like the DeleteRow() method is off by one row after a series of delete operations. For example, if I have:

8 GarbageRow
9 Data
10 Data
11 Data
12 GarbageRow
13 SummaryRow =SUM(A8:A12)

I remove row 12 and then row 8. The formula should now be SUM(A8:A10) but instead it’s SUM(A7:A10).

I’ve attached my project – just run it.

Thanks,
Natan



2

Please try this fix attached at <A href="

3

Laurence,

I applied the hotfix and I’m now getting circular reference errors. Most of the subtotals are now broken. Also, I noticed that charts are also off by one row, so please take a look at that while you look at formulas.

Thanks,
Natan

4

For subtotals, I don't find the problem. Maybe your code works fine with old dll but doesn't work with the new dll. Please check it.

For chart issue, I find a problem but I don't know it's the same issue as yours.

Following is my test code and attached is my sample file:

Workbook workbook = new Workbook();
workbook.Open("d:\\test\\book1.xls");

Cells cells = workbook.Worksheets[0].Cells;
cells.DeleteRow(2);

workbook.Save("d:\\test\\abc.xls");

Could you please create a simple project to show your problem?

5

Laurence,

So you're saying that my code was relying on a bug in order to work properly? I'll test my code again, though I did simulate it in Excel first and everything worked as expected. Essentially, my project is doing nothing more than copying a few designated template rows and inserting new rows as needed (determined by the number of XML data rows). I then mark the first cell of any rows I want to delete with the value "rdDeleteRow". The last step of the program is simply to run through those rows and delete them, starting from the bottom and going up. If I started at the top, the numbering would get thrown off and I wouldn't be deleting the right rows.

So after simulating this behavior in Excel, I wrote code to do the same and everything worked as expected except that the formulas were all off by one row. The only thing I can think to do is to comment out the call to subRemoveDisposableRows() and make sure I'm deleting the right rows. Can you tell me what you changed or what the bug was? Maybe I was relying on faulty logic.

Thanks,
Natan

6

Hi Natan,

After re-checking your template file, I found a problem and fix it. Maybe it causes your problem. Please try this attached v3.9.1.6.

If the problem still occurs, could you post your sample project here? Thank you very much.

7

Laurence,

No change with the hotfix. For now, I need to move on to more pressing matters so we’ll ship with 3.9.1.2 at the moment. Hopefully next week I’ll have some time to create a simple project and do some more testing.

Thanks,
Natan

8

Laurence,

I found an easy way for you to recreate this issue. Comment out line 381 from rdExcelTemplate.vb:

mexlTemplate.Worksheets(sWorksheet).Cells.DeleteRow(alRowList(nRowIndex))

You’ll see that the spreadsheet now has rows that begin with “rdDeleteRow” in the first column. Manually delete those rows one at a time starting from the bottom and going up. You’ll see that the subtotal formulas are properly updated each time. Now uncomment that line and see that I’m deleting those exact same rows in the same order. The workbook will load with circular reference errors and the subtotals are wrong.

Let me know if you need any more information.

Thanks,
Natan

9

Laurence,

I would like to double this. 3.9.1.5 does not work for us either (no matter whether we insert or delete). The release notes for 4.0.0.0 do not state that anything was fixed in this area so I assume the problem still exists.

Natan, if you say that 3.9.1.2 still works for you, do you know where I can download it? I do not want to revert back to a pre 3.9.0.0 release because of all the class name changes we went through just recently.

I would like to stress that this feature is of high importance to us as the business unit ran reports today that showed the issue.

BTW: Our last working release was somthing around 3.8.0.2

Regards

Kai

10

Sorry for this bug. With your help, I found and fixed this problem. Please try this attached version.

11

Laurence,

thanks for this. To me, at first glance, this looks fine.

Kai