Adding Data Bound Controls to the Worksheet

By default, each of the fields in the Customers table in the Data Sources window displays a NamedRange control, indicating the type of control that will be added to the worksheet when you drag the field to it. You can change the type of control by clicking the arrow next to ContactName to reveal a drop-down menu, as shown in Figure 7.21.

Figure 7.21. Selecting the type of control to data-bind

Now you can drag the selected type of control from the Data Sources window to the worksheet. Note that you don't drag the control that is in the drop-down list. Instead, select the desired control in the drop-down and then drag the field (for example, ContactName) to the worksheet. First you add the ContactName field to the worksheet as a data-bound

NamedRange control, and then you add the entire Customers table as a ListObject control and modify its contents.

1. Expand the Customers node in the Data Sources window, and drag the ContactName field to cell A2 on Sheet1 of the workbook so that a NamedRange control is added.

2. From the Data Sources window, drag the entire Customers table to cell A4 on Sheet1 of the workbook so that a ListObject control is added.

3. Right-click the CustomerID field of the ListObject, point to Delete, and then select Column. This action removes the CustomerID column from the ListObject.

4. Remove the following columns from the ListObject as described in step 3: ContactName and ContactTitle. Sheet1 should resemble Figure 7.22.

Start Page Sheetl.vb* Encel'

Workbook l.xls* ' 10 . B

1 Data Sources » V x

A I "'«I

I U SE ■ ES g $ ./. » üi -A

II ;a in t r»

B9 - fx

E

Ê 1 - -1

orthwindDataSet

A

B

1 o 1

D

E

F

G

H

1

*

5g ContactName j- Jg Address i ^ Ft". [ E Np JVt--".-

1

2

3

<CompanyName>

4

5

CustomerID

Comp iiny Hn il

CoutnctNïiiii

ContactTi

Address

City

Region

Postal Cot

Con

6

7

3

9

I I

^

11

13

-

|i^Data Sources J^^ Solution Explorer

14

I Propertie

15

I Sheet 1 Microsoft,Office.Tools,Excel.V »

16

17

:: il « -/

H

10

El

(DataBindings)

19

(Name)

Sheet 1

2D

ConsolidationFuni

21

DisplayRightToLel

False

22

FilterMode

Figure 7.22. Data-bound controls on a worksheet in Visual Studio

Figure 7.22. Data-bound controls on a worksheet in Visual Studio

5. Press F5 to run the code. The worksheet will display data, as shown in Figure 7.23.

Figure 7.23. Controls bound to data

6. Move your cursor from cell A4 to cell A5, and notice that the contact name listed in cell A1 is automatically updated.

Without writing any code, you have bound data to the worksheet by using ListObject and NamedRange controls from the Data Sources window. You can instead bind data to a control programmatically by calling the SetDataBinding method of the control, but it is much easier to drag and drop data-bound controls to your worksheet's surface and let VSTO generate the necessary code for you.

Notice that when you first added the data-bound NamedRange and ListObject controls to the worksheet by dragging them from the Data Sources window, three objects were created and added to the component tray. These objects include a dataset called NorthwindDataSet, a table adapter object called CustomersTableAdapter, and a binding source object called CustomerBindingSource.

0 0

Post a comment