How to: Map ListObject Columns to Data

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 Excel 2007 Excel 2003 Application-level projects Excel 2007 For more information, see Features Available by Application and Project Type.

When you bind a ListObject control to a DataTable, you might not want to display all the columns in a list, or you might have certain columns that are not bound to data. You can map which columns you want to appear in the ListObject when you call the SetDataBinding method.

For a related video demonstration, see How Do I: Create a List in Excel that is Connected to a SharePoint List?.

Mapping Columns

To map a data table to columns in a list

1. Create the DataTable at the class level.

   Dim table As System.Data.DataTable = New System.Data.DataTable("Employees")
   

   System.Data.DataTable table = new System.Data.DataTable("Employees");
   

2. Add sample columns and data in the Startup event handler of the Sheet1 class (in a document-level project) or ThisAddIn class (in an application-level project).

   table.Columns.Add("Id", GetType(Int32))
   table.Columns.Add("FirstName", GetType(String))
   table.Columns.Add("LastName", GetType(String))
   table.Columns.Add("Title", GetType(String))
   
   table.Rows.Add(1, "Nancy", "Anderson", "Sales Representative")
   table.Rows.Add(2, "Robert", "Brown", "Sales Representative")
   

   table.Columns.Add("Id", typeof(int));
   table.Columns.Add("FirstName", typeof(string));
   table.Columns.Add("LastName", typeof(string));
   table.Columns.Add("Title", typeof(string));
   
   table.Rows.Add(1, "Nancy", "Anderson", "Sales Representative");
   table.Rows.Add(2, "Robert", "Brown", "Sales Representative");
   

3. Call the SetDataBinding method and pass in the column names in the order they should appear. The list object will be bound to the newly-created DataTable, but the order of the columns in the list object will differ from the order they appear in the DataTable.

   Me.List1.AutoSetDataBoundColumnHeaders = True 
   Me.List1.SetDataBinding(table, "", "Title", "LastName", "FirstName")
   

   this.list1.AutoSetDataBoundColumnHeaders = true;
   this.list1.SetDataBinding(table, "", "Title", "LastName", "FirstName");
   

Specifying Unmapped Columns

When you map columns to a DataTable, you can also specify that certain columns should not be bound to data by passing in an empty string. A new column that is not bound to data is then added to the ListObject control.

To specify an unmapped column when mapping ListObject columns

• Call the SetDataBinding method and pass in the column names in the order they should appear. Use an empty string to indicate where an unmapped column is added; in this case, between the title column and the last name column.

  Me.List1.SetDataBinding(table, "", "Title", "", "LastName", "FirstName")
  

  this.list1.SetDataBinding(table, "", "Title", "", "LastName", "FirstName");
  

Compiling the Code

This code example assumes you have an existing ListObject named list1 on the worksheet in which this code appears.

See Also

Tasks

How to: Fill ListObject Controls with Data

Concepts

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

ListObject Control

Understanding ListObject Column and Row Order Persistence

Other Resources

Controls on Office Documents

Excel Host Controls