ListObject control

  • Article

Applies to: yesVisual Studio noVisual Studio for Mac

Note

This article applies to Visual Studio 2017. If you're looking for the latest Visual Studio documentation, see Visual Studio documentation. We recommend upgrading to the latest version of Visual Studio. Download it here

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 VSTO Add-in projects for Excel. For more information, see Features available by Office application and project type.

Create the control

In document-level projects, you can add ListObject controls to a worksheet at design time or at run time. In VSTO Add-in 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 Add controls to Office documents at run time.

Bind 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 a VSTO 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 Bind 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.

Improve 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.

Disconnect 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.

Restore 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.

Format

Formatting that can be applied to a ListObject can be applied to a 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.

Add and remove 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.

Add and remove 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.

Rename 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