We recommend using Visual Studio 2017

How to: Map ListObject Columns to Data


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.

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.

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

To map a data table to columns in a list

  1. Create the DataTable at the class level.

            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 VSTO Add-in project).

                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.

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

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

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

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

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

Extending Word Documents and Excel Workbooks in VSTO Add-ins at Run Time
Controls on Office Documents
Adding Controls to Office Documents at Run Time
How to: Fill ListObject Controls with Data
Automating Excel by Using Extended Objects
ListObject Control