The Microsoft.Office.Tools.Excel library has some quite handy extensions for Excel. However, one of them is far more trouble than it is worth. The VSTO ListObject promises to provide extended support for lists and named ranges, including events and additional properties and methods. However, there are a number of issues that make this class flawed:
- Deleting rows has always been concerning and quite problematic. Such as deleting the last row, deleting the first row, deleting one of three selected rows, and deleting the last row when there are only two rows. This will either break the list or give you unexpected results, such as not notifying you of a delete or being unable to determine the changes made.
- Copy/Paste has some serious problems. One issue (https://support.microsoft.com/en-us/kb/3081715), was fixed. However, you will still have similar issues as related to deleting, especially if data is pasted over existing rows.
- Finally, filtering a ListObject LO and then trying to determine changes, especially if the user deletes or pastes data into a range. It will break your solution.
The VSTO ListObject seemingly helps a lot, especially when you are looking for a little extra to extend yor solution. However, it is best ignored. This advice also comes directly from the Visual Studio Tools for Officeproduct team. In one Program Managers words, “there be dragons there.” You have been warned. 😐
The advice is to use the native ListObject in Microsoft.Office.Interop.Excel and work within the limitations there, for being limited is far better than being broken. 😷
do you have any examples populating an excel.listobject
Unfortunately, offhand, no. I have worked with it years ago with a customer solution, but it has been years and I no longer have access to that code.