Excel ListObject Last Remaining Row Does Not Get Deleted

Question

Hi

I have the same problem as the poster of this thread:

http://social.msdn.microsoft.com/Forums/en-US/vsto/thread/46eeaf51-4f93-4817-8bcf-b6b47df68856

The discussion in the thread talks about work-arounds - which is not what I am looking for.

When there is only one row in a databound ListObject, and the user deletes this row (by any of the possiblities the Excel user interface offers for this purpose), the row is deleted from the ListObject, but not from the underlying DataTable. In particular:

• ListObject.Change does not get called
• BindingSource.ListChanged does not get called
• DataTable.RowDeleted does not get called

IMHO this is a bug in VSTO's implementation of the databound list object. And a pretty bad one at that because it may take the user quite a while before he realizes that what he sees is *not* what he gets. 

I should like to know whether Microsoft is aware of this bug and whether there are any plans to fix it.

Georg 

Answer 1

Hi Georg,

Thanks for posting in the MSDN Forum.

I'll involve some experts who are familiar with your issue, this may take some time. Much appreciate for your patience and understanding.

Best Regards,

---

Leo_Gao [MSFT]
MSDN Community Support | Feedback to us

Answer 2

Hi Georg,

This is a step in understanding the issue from standpoint of accurately reporting a bug.  So I'm data-gathering.

Although the

• BindingSource.ListChanged does not get called
• DataTable.RowDeleted does not get called

Does the actual row in the datatable get deleted? Is it a question of sinking events, or an actual failure of the applicable method in Excel to change the data?

---

Please remember to mark the replies as answer if they help and unmark them if they provide no help. and click "Vote as Helpful" this and other helpful posts, so other users will see your thread as useful. Best Regards, Chris Jensen

Answer 3

Hi

The row in the System.Data.DataTable does not get deleted.

The problem only manifests itself if the ListObject contains exactly one last row. If you delete this last row (for example through the Delete Table Rows menu command), the ListObject row goes blank, but the row in the underlying DataTable is not deleted. When I save the document at this point and then re-open it re-binding the ListObject to the DataTable), the 'deleted' row is shown again in the ListObject.

(That BindingSource.ListChanged and DataTable.RowDeleted do not get called is not surprising under the circumstance. More surprising is the fact that ListObject.Change is not called either)

There is no problem if you have n rows, n >= 2, in the ListObject and delete n or fewer rows. In these cases the underlying DataTable is updated correctly.

Additional Information: my application is an Excel 2010 document level customization (Template) built using Visual Studio 2010, Version 10.0.40219.1 SP1 Rel (as shown in Help / About).  

Georg

Answer 4

Hi Georg,

Thank you for the information.

---

Please remember to mark the replies as answer if they help and unmark them if they provide no help. and click "Vote as Helpful" this and other helpful posts, so other users will see your thread as useful. Best Regards, Chris Jensen

Answer 5

Hi Georg,

Thank you for meticulously defining the issue and responding to the questions in the earlier post.

The issue has been reported as a bug and fixing it would cascade recursively possibly breaking other code to the point the development team has decided to not fix it.

A possible workaroundwould be to capture the Sheet::SelectionChange event to check if you are running into this condition (listObject has no rows, but the DataTable still has a row) and mark the sheet dirty so that SheetChange fires which in turn would trigger the correct listobject chain of events. Attached is a sample that demonstrates this. The specific code of importance is :-

        void sheet_SelectionChange(Excel.Range Target)
        {
            //If we are getting into the condition where list object has 0 rows, but the data table has a row, trigger sheetchange.
            if ((vstoListObject.ListRows.Count == 0) && (table.Rows.Count == 1))
            {
                //Need to trigger Worksheet change.
                Target.Cells[0.0].Value2 = Target.Cells[0.0].Value2;
               
            }
        }

So, with this workaround the ListObject and underlying DataTable would be synch'ed the moment user selects some other cell after deleting the last row.

---

Please remember to mark the replies as answer if they help and unmark them if they provide no help. and click "Vote as Helpful" this and other helpful posts, so other users will see your thread as useful. Best Regards, Chris Jensen

Answer 6

Hello Chris

Thank you for your effort in clarifying this issue.

Regards - Georg