How to: Map ListObject Columns to Data

How to: Map ListObject Columns to D...

Switch on low bandwidth view

Collapse All

Language Filter

This page is specific to

Microsoft Visual Studio 2008/.NET Framework 3.5

Other versions are also available for the following:

Microsoft Visual Studio Tools for the Microsoft Office system (version 3.0)

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.

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.

link to video 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

Create the DataTable at the class level.

Visual Basic

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

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

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

Visual Basic

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");

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.
Visual Basic
```
Me.List1.AutoSetDataBoundColumnHeaders = True
Me.List1.SetDataBinding(table, "", "Title", "LastName", "FirstName")
```
C#
```
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.
Visual Basic
```
Me.List1.SetDataBinding(table, "", "Title", "", "LastName", "FirstName")
```
C#
```
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.

To map a data table to columns in a list

To specify an unmapped column when mapping ListObject columns

Tasks

Concepts

Other Resources