Walkthrough: Complex data binding in VSTO Add-in project
You can bind data to host controls and Windows Forms controls in VSTO Add-in projects. This walkthrough demonstrates how to add controls to a Microsoft Office Excel worksheet and bind the controls to data at run time.
Applies to: The information in this topic applies to VSTO Add-in projects for Excel. For more information, see Features available by Office application and project type.
This walkthrough illustrates the following tasks:
Adding a ListObject control to a worksheet at run time.
Creating a BindingSource that connects the control to an instance of a dataset.
Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Personalize the IDE.
You need the following components to complete this walkthrough:
An edition of Visual Studio that includes the Microsoft Office developer tools. For more information, see Configure a computer to develop Office solutions.
Excel 2013 or Excel 2010.
Access to a running instance of SQL Server 2005 or SQL Server 2005 Express that has the
AdventureWorksLTsample database attached to it. You can download the
AdventureWorksLTdatabase from the SQL Server Samples GitHub repo. For more information about attaching a database, see the following topics:
To attach a database by using SQL Server Management Studio or SQL Server Management Studio Express, see How to: Attach a database (SQL Server Management Studio).
To attach a database by using the command line, see How to: Attach a database file to SQL Server Express.
Create a new project
The first step is to create an Excel VSTO Add-in project.
To create a new project
Create an Excel VSTO Add-in project with the name Populating Worksheets from a Database, using either Visual Basic or C#.
For more information, see How to: Create Office Projects in Visual Studio.
Visual Studio opens the
ThisAddIn.csfile and adds the Populating Worksheets from a Database project to Solution Explorer.
Create a data source
Use the Data Sources window to add a typed dataset to your project.
To add a typed dataset to the project
If the Data Sources window is not visible, display it by, on the menu bar, choosing View > Other Windows > Data Sources.
Choose Add New Data Source to start the Data Source Configuration Wizard.
Click Database, and then click Next.
If you have an existing connection to the
AdventureWorksLTdatabase, choose this connection and click Next.
Otherwise, click New Connection, and use the Add Connection dialog box to create the new connection. For more information, see Add new connections.
In the Save the Connection String to the Application Configuration File page, click Next.
In the Choose Your Database Objects page, expand Tables and select Address (SalesLT).
The AdventureWorksLTDataSet.xsd file is added to Solution Explorer. This file defines the following items:
A typed dataset named
AdventureWorksLTDataSet. This dataset represents the contents of the Address (SalesLT) table in the AdventureWorksLT database.
A TableAdapter named
AddressTableAdapter. This TableAdapter can be used to read and write data in the
AdventureWorksLTDataSet. For more information, see TableAdapter overview.
You will use both of these objects later in this walkthrough.
Create controls and bind controls to data
For this walkthrough, the ListObject control displays all the data in the table you selected as soon as the user opens the workbook. The list object uses a BindingSource to connect the control to the database.
For more information about binding controls to data, see Bind data to controls in Office solutions.
To add the list object, dataset, and table adapter
ThisAddInclass, declare the following controls to display the
Addresstable of the
private Microsoft.Office.Tools.Excel.ListObject addressListObject; private AdventureWorksLTDataSet adventureWorksDataSet; private AdventureWorksLTDataSetTableAdapters.AddressTableAdapter addressTableAdapter; private System.Windows.Forms.BindingSource addressBindingSource;
Private addressListObject As Microsoft.Office.Tools.Excel.ListObject Private adventureWorksDataSet As AdventureWorksLTDataSet Private addressTableAdapter As AdventureWorksLTDataSetTableAdapters.AddressTableAdapter Private addressBindingSource As System.Windows.Forms.BindingSource
ThisAddIn_Startupmethod, add the following code to initialize the dataset and fill the dataset with information from the
this.addressTableAdapter = new AdventureWorksLTDataSetTableAdapters.AddressTableAdapter(); this.adventureWorksDataSet = new AdventureWorksLTDataSet(); this.addressTableAdapter.Fill(this.adventureWorksDataSet.Address); this.addressBindingSource = new System.Windows.Forms.BindingSource();
Me.addressTableAdapter = New AdventureWorksLTDataSetTableAdapters.AddressTableAdapter() Me.adventureWorksDataSet = New AdventureWorksLTDataSet() Me.addressTableAdapter.Fill(Me.adventureWorksDataSet.Address) Me.addressBindingSource = New System.Windows.Forms.BindingSource()
Add the following code to the
ThisAddIn_Startupmethod. This generates a host item that extends the worksheet. For more information, see Extend Word documents and Excel workbooks in VSTO Add-ins at run time.
Excel.Worksheet worksheet = (Excel.Worksheet)this.Application.ActiveWorkbook.Worksheets; // Create a workhseet host item. Worksheet extendedWorksheet = Globals.Factory.GetVstoObject(worksheet);
Dim worksheet As Excel.Worksheet = DirectCast(Me.Application.ActiveWorkbook.Worksheets(1), Excel.Worksheet) ' Create a workhseet host item. Dim extendedWorksheet As Worksheet = Globals.Factory.GetVstoObject(worksheet)
Create a range and add the ListObject control.
Excel.Range cell = extendedWorksheet.Range["$A$1:$G$5"]; this.addressListObject = extendedWorksheet.Controls.AddListObject(cell, "list1");
Dim cell As Excel.Range = extendedWorksheet.Range("$A$1:$G$5", System.Type.Missing) Me.addressListObject = extendedWorksheet.Controls.AddListObject(cell, "list1")
Bind the list object to
AdventureWorksLTDataSetby using the BindingSource. Pass in the names of the columns you want to bind to the list object.
this.addressBindingSource.DataSource = this.adventureWorksDataSet.Address; this.addressListObject.AutoSetDataBoundColumnHeaders = true; this.addressListObject.SetDataBinding( this.addressBindingSource, "", "AddressID", "AddressLine1", "AddressLine2", "City", "StateProvince", "CountryRegion", "PostalCode");
Me.addressBindingSource.DataSource = Me.adventureWorksDataSet.Address Me.addressListObject.AutoSetDataBoundColumnHeaders = True Me.addressListObject.SetDataBinding( _ Me.addressBindingSource, "", "AddressID", "AddressLine1", _ "AddressLine2", "City", "StateProvince", "CountryRegion", "PostalCode")
Test the Add-in
When you open Excel, the ListObject control displays the data from the
Address table of the
To test the VSTO Add-in
A ListObject control named
addressListObjectis created in the worksheet. At the same time, a dataset object named
adventureWorksLTDataSetand a BindingSource named
addressBindingSourceare added to the project. The ListObject is bound to the BindingSource, which in turn is bound to the dataset object.
- Data in Office solutions
- Bind data to controls in Office solutions
- How to: Populate worksheets with data from a database
- How to: Populate documents with data from a database
- How to: Populate documents with data from services
- How to: Populate documents with data from objects
- How to: Scroll through database records in a worksheet
- How to: Update a data source with data from a host control
- Walkthrough: Simple data binding in a document-level project
- Walkthrough: Complex data binding in a document-level project
- Use local database files in Office solutions overview
- Add new data sources
- Bind Windows Forms controls to data in Visual Studio
- Use local database files in Office solutions overview
- BindingSource component overview