Accessing Excel Databases using ADO.NET in VB.NET

In this article I will explain about Accessing Excel Databases using ADO.NET.
  • 2843
The next example will show you how to connect with Excel databases. To test this sample, if you don't have an Excel database, you can export data from your Northwind database. As you can see from figure 11-33, You can export the Employees table from Microsoft Access by right-clicking on the table and selecting the Export option or by selecting File > Export.

 Figure-11_33.gif
Figure 11-33: Exporting the Employees table as an Excel spreadsheet
 
When you export, make sure you have selected the Microsoft Excel 97-2000 (*.xls) option in the Save as type drop-down list (see Figure 11-34).
 
Figure-11_34.gif
 
Figure 11-34: Saving the Employees table as an Excel spreadsheet
 
Now if you open Employees.xls, it looks like figure 11-35.
 
Figure-11_35.gif
 
Figure 11-35: Employees.xls data view
 
Again you can access the Excel database either using an ODBC DSN or by passing the database name directly in the connection string. In this sample, you're passing the database name directly:

Dim ConnnectionString 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 data adapter, selecting some field of the table, filling a dataset from data adapter, and binding data with the data-bound controls. Listing 11-4 shows the full source code.
 
Listing 11-4: Accessing Employees.xls using the ODBC data provider

   Private Sub Form1_Load(ByVal senderAs Object,ByVal e As System.EventArgs)

       ' Connection string for ODBC Excel Driver

       Dim ConnectionStringAs String ="Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\Employees.xls"

       Dim conn As New OdbcConnection(ConnectionString)

       ' Table in Excel can be thought of as sheets and are queried as shown

       Dim sql As String = "Select EmployeeID, FirstName, LastName FROM Employees"

        conn.Open()

       Dim da As New OdbcDataAdapter(sql, conn)

       Dim ds As New DataSet()

        da.Fill(ds,"Employees")

        dataGrid1.DataSource = ds.DefaultViewManager

        listBox1.DataSource = ds.DefaultViewManager

        listBox1.DisplayMember = "Employees.FirstName"

   End Sub

The output of listing 11-4 looks like figure 11-36.


Figure-11_36.gif
 
Figure 11-36: Output of listing 11-4
 
Conclusion
 
Hope this article would have helped you in understanding Accessing Excel Databases using ADO.NET

Categories

More Articles

© 2013 dotNetheaven. All rights reserved.