ListObject Control
Applies to |
|---|
The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office. Document-level projects
Application-level projects
For more information, see Features Available by Application and Project Type. |
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 Tools for Office creates a ListObject control that you can program against directly without having to traverse the Microsoft Office Excel object model.
Creating the Control
You can add ListObject controls to a Microsoft Office Excel worksheet at design time or at run time in document-level customizations. Starting in Visual Studio 2008 Service Pack 1 (SP1), you can add ListObject controls in an application-level add-in 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 Tools for Office 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. Starting in SP1, 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.
Formatting
Formatting that can be applied to a 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 Tools for Office 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, or the ErrorAddDataBoundRow to retry after an error has been fixed.
For more information, see How to: Validate Data When a New Row is Added to a ListObject Control and How to: Handle Errors When a New Row is Added to a ListObject Control.
Renaming ListObject Controls in Excel 2007
Excel 2007 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.
Note
Excel lists are called Excel tables in Excel 2007.
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: Handle Errors When a New Row is Added to a ListObject Control
How to: Map ListObject Columns to Data
How to: Fill ListObject Controls with Data
Excel Add-In Dynamic Controls Sample
How to: Populate Worksheets with Data from a Database
Concepts
Understanding ListObject Column and Row Order Persistence
Binding Data to Controls in Office Solutions
Extending Word Documents and Excel Workbooks in Application-Level Add-ins at Run Time
Excel Application-Level Add-in Development
Adding Controls to Office Documents at Run Time
Programmatic Limitations of Host Items and Host Controls
Other Resources
Change History
Date |
History |
Reason |
|---|---|---|
July 2008 |
Added information about using list object controls in an application-level add-in. |
SP1 feature change. |