Accessing Excel Databases

You'll now learn how to connect to Excel databases. If you don't have an Excel database, you can export data from your Northwind database to test this example. As you did in the previous section, export the Employees table from Microsoft Access by right-clicking the table and selecting the Export option. (You can also choose File Export).

When you export, make sure you select the Microsoft Excel 97–2000 (*.xls) option in the Save as Type drop-down list. For this example, save the file as Employees.xls.

Now, if you open Employees.xls, it looks like Figure 10-10.

Figure 10-10: Employees.xls data view

Again, you can access the Excel database either by using an ODBC DSN or by passing the database name directly in the connection string. In this example, you're passing the database name directly:

 Dim ConnectionString as string = "Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\\Employees.xls" 

After that, the code should be familiar to you. It's the same steps as creating a DataAdapter, selecting some fields of the table, filling a DataSet from the DataAdapter, and binding data with the data-bound controls. To test this code, create a Windows application, add a DataGrid control and a ListBox control to the form, and import the Microsoft.Data.Odbc namespace. Listing 10-4 shows the full source code.

Listing 10-4: Accessing Employees.xls Using the ODBC Data Provider

 private void Form1_Load(object sender, System.EventArgs e) {  // Connection string for ODBC Excel Driver string ConnectionString = @"Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\Employees.xls"; OdbcConnection conn = new OdbcConnection(ConnectionString); // Tables in Excel can be thought of as sheets and are queried as shown string sql = "Select EmployeeID, FirstName, LastName FROM Employees"; conn.Open(); OdbcDataAdapter da = new OdbcDataAdapter(sql, conn); DataSet ds = new DataSet(); da.Fill(ds, "Employees"); dataGrid1.DataSource = ds.DefaultViewManager; listBox1.DataSource = ds.DefaultViewManager; listBox1.DisplayMember = "Employees.FirstName"; } 

The output of Listing 10-4 looks like Figure 10-11.

Figure 10-11: Output of Listing 10-4