ListObject Control
The ListObject control is a list that exposes events and can be bound to data. When you add a list to a worksheet, Visual Studio creates a ListObject control that you can program against directly without having to traverse the Microsoft Office Excel object model.
Applies to: The information in this topic applies to document-level projects and application-level projects for Excel 2007 and Excel 2010. For more information, see Features Available by Office Application and Project Type.
Creating the Control
In document-level projects, you can add ListObject controls to a worksheet at design time or at run time. In application-level projects, you can add ListObject controls to worksheets only at run time. For more information, see How to: Add ListObject Controls to Worksheets.
Note
By default, dynamically created list objects are not persisted in the worksheet as host controls when the worksheet is closed. For more information, see Adding Controls to Office Documents at Run Time.
Binding Data to the Control
A ListObject control supports simple and complex data binding. The ListObject control can be bound to a data source using the DataSource and DataMember properties at design time or the SetDataBinding method at run time.
Note
The ListObject is updated automatically when it is bound to a data source, such as a DataTable, that raises events when the data changes. If you bind the ListObject to a data source that does not raise events when the data changes, you must call the RefreshDataRow or RefreshDataRows method to update the ListObject.
When you add a ListObject to a worksheet cell by mapping a repeating schema element to that cell, Visual Studio automatically maps the ListObject to the generated dataset. However, the ListObject is not automatically bound to the data. You can take steps to bind the ListObject to the dataset at design time or at run time in a document-level project. You can programmatically bind the ListObject to the dataset at run time in an application-level add-in.
Because the data is separate from the ListObject, you should add and remove data through the bound dataset, and not directly through the ListObject. If the data in the bound dataset is updated through any mechanism, the ListObject control automatically reflects the changes. For more information, see Binding Data to Controls in Office Solutions.
You can quickly fill a ListObject control by binding the ListObject to a data source. If you edit the data in a data-bound ListObject, the changes are automatically made in the data source as well. If you want to fill a ListObject and then enable the user to change the data in the ListObject without modifying the data source, you can use the Disconnect method to detach the ListObject from the data source. For more information, see How to: Fill ListObject Controls with Data.
Note
Data binding is not supported on overlapping ListObject controls.
Improving Performance in ListObject Controls
Reading an XML file into a data-bound ListObject control tends to be slower if you bind the control first, and then call ReadXml to fill the dataset. To improve performance, call ReadXml before you bind the control.
Disconnecting ListObject Controls from the Data Source
After you fill a ListObject control with data by binding it to a data source, you can disconnect it so that modifications made to the data in the list object do not affect the data source. For more information, see How to: Fill ListObject Controls with Data.
Restoring Column and Row Order
When you bind data to a ListObject control that was added to a document at design time, Visual Studio keeps track of the column and row order whenever the workbook is saved. If a user moves the ListObject columns or rows during run time, the new order is preserved the next time the workbook is opened and the ListObject control binds to the data source again.
If you want to restore the ListObject to its original column and row order, you can call the ResetPersistedBindingInformation method. This method removes the custom document properties related to the column and row order of specified ListObject. Call this method from the Shutdown event of the Workbook if you do not want to preserve the column and row order of the ListObject.
Formatting
Formatting that can be applied to a Microsoft.Office.Interop.Excel.ListObject can be applied to a Microsoft.Office.Tools.Excel.ListObject control. This includes borders, fonts, number format, and styles. End-users can rearrange columns in a data-bound ListObject, and these changes will be persisted with the document, provided the ListObject was added to the document at design time. The next time the document is opened, the list object will be bound to the same data source, but the column order will reflect the users' changes.
Adding and Removing Columns at Run Time
You cannot manually add or remove columns in a data-bound ListObject control at run time. If an end-user tries to delete a column, it will immediately be restored and any columns added will be removed. Therefore, it is important to write code to explain to users why they cannot perform these actions on a ListObject that is bound to data. Visual Studio provides several events on a ListObject related to data binding. For example, you can use the OriginalDataRestored event to warn users that the data they have attempted to delete cannot be deleted and has been restored.
Adding and Removing Rows at Run Time
You can manually add and remove rows in a data-bound ListObject control, provided the data source allows the addition of new rows and is not read-only. You can write code against events such as the BeforeAddDataBoundRow to validate the data. For more information, see How to: Validate Data When a New Row is Added to a ListObject Control.
Sometimes the relationship of the list object to the data source causes routine errors. For example, you can map which columns you want to appear in the ListObject, so if you omit columns that have restrictions, such as a field that cannot accept null values, errors are raised every time a row is created. You can write code to add the missing values in an event handler for the ErrorAddDataBoundRow event.
Renaming ListObject Controls in Excel
Excel enables users to change the name of Excel tables at run time by using the Design tab. However, the ListObject control does not support this feature. If a user tries to rename an Excel table that corresponds to a ListObject, the name of the Excel table will automatically revert to the original name when the workbook is saved.
Events
The following events are available for the ListObject control:
See Also
Tasks
How to: Add ListObject Controls to Worksheets
How to: Resize ListObject Controls
How to: Validate Data When a New Row is Added to a ListObject Control
How to: Map ListObject Columns to Data
How to: Fill ListObject Controls with Data
How to: Populate Worksheets with Data from a Database
Concepts
Automating Excel by Using Extended Objects
Extending Word Documents and Excel Workbooks in Application-Level Add-ins at Run Time
Adding Controls to Office Documents at Run Time
Programmatic Limitations of Host Items and Host Controls
Other Resources
Office Development Samples and Walkthroughs