How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET

How to Use the Jet OLE DB Provider With Microsoft Excel Workbooks

Connection String

Data Types

Table Naming Conventions

• Use the sheet name followed by a dollar sign (for example, [Sheet1$] or [My Worksheet$]). A workbook table that is referenced in this manner includes the whole used range of the worksheet.
  Select * from [Sheet1$]
• Use a range with a defined name (for example, [MyNamedRange]):
  Select * from [MyNamedRange]
• Use a range with a specific address (for example, [Sheet1$A1:B10]):
  Select * from [Sheet1$A1:B10]

How to Use Excel Workbooks as ADO.NET Data Sources

Retrieve Records

Add and Update Records

• Directly run a command to insert or update records one at a time. To do this, you can create an OLEDbCommand object on your connection and set its CommandText property to a valid command to insert records
  
  

  INSERT INTO [Sheet1$] (F1, F2) values ('111', 'ABC')

  or a command to update records
  
  

  UPDATE [Sheet1$] SET F2 = 'XYZ' WHERE F1 = '111'

  and then call the ExecuteNonQuery method.
• Make changes to a DataSet that you have filled with a table/query from an Excel workbook and then call the Update method of the DataAdapter to resolve changes from the DataSet back to the workbook. However, to use the Update method for change resolution you must set parameterized commands for the DataAdapter's InsertCommand

  INSERT INTO [Sheet1$] (F1, F2) values (?, ?)

  and UpdateCommand:

  UPDATE [Sheet1$] SET F2 = ? WHERE F1 = ?

  Parameterized INSERT and UPDATE commands are required because the OleDbDataAdapter does not supply key/index information for Excel workbooks; without key/index fields, the CommandBuilder cannot automatically generate the commands for you.
• Export data from another data source into an Excel workbook provided that the other data source can be used with the Jet OLE DB Provider. Data sources that you can use with the Jet OLE DB Provider in this manner include Text files, Microsoft Access databases, and, of course, other Excel workbooks. With a single INSERT INTO command, you can export data from another table/query into your workbook:

  INSERT INTO [Sheet1$] IN 'C:\Book1.xls' 'Excel 8.0;' SELECT * FROM MyTable"

  INSERT INTO requires that the target table (or worksheet) already exist; data is appended to the target table.
  
  You may also use SELECT..INTO to export your table/query to a workbook:

  SELECT * INTO [Excel 8.0;Database=C:\Book1.xls].[Sheet1] FROM [MyTable]

  When you use SELECT..INTO, if the target table or workbook does not exist, it will be created for you. If the table already exists before the SELECT..INTO command is issued, you will receive an error.

Delete Records

Create Workbooks and Tables

CREATE TABLE Sheet1 (F1 char(255), F2 char(255))

Step-by-Step

Sample Code

1. Start a new Visual Basic .NET Windows Application project.
   
   Form1 is created by default.
2. Add six RadioButton controls and a Button control to Form1.
3. Select all of the RadioButton controls and set the Size property to 200,24.
4. On the View menu, click Code.
5. Add the following line to the very beginning of the code module:

   Imports System.Data.OleDb

6. Insert the following code into the Form class:

   Private m_sConn1 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                  "Data Source=C:\ExcelData1.xls;" & _
                  "Extended Properties=""Excel 8.0;HDR=YES"""
   
   Private m_sConn2 As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                  "Data Source=C:\ExcelData2.xls;" & _
                  "Extended Properties=""Excel 8.0;HDR=YES"""
   
   Private m_sNorthwind = _
         "C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb"
   
   Private m_sAction As String
   
   Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
         RadioButton1.Text = "Create_Workbook"
         RadioButton2.Text = "Retrieve_Records"
         RadioButton3.Text = "Add_Records"
         RadioButton4.Text = "Update_Records"
         RadioButton5.Text = "Update_Individual_Cells"
         RadioButton6.Text = "Use_External_Source"
         Button1.Text = "Go!"
   End Sub
   
   Private Sub RadioButtons_Click(ByVal sender As Object, ByVal e As System.EventArgs) _
         Handles RadioButton1.Click, RadioButton2.Click, RadioButton3.Click, _
         RadioButton4.Click, RadioButton5.Click, RadioButton6.Click
         m_sAction = sender.Text'Store the text for the selected radio button
   End Sub
   
   Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
         Try
            ' Call the associated routine to add/update/modify the workbook.
            Select Case m_sAction
               Case "Create_Workbook" : Create_Workbook()
               Case "Retrieve_Records" : Retrieve_Records()
               Case "Add_Records" : Add_Records()
               Case "Update_Records" : Update_Records()
               Case "Update_Individual_Cells" : Update_Individual_Cells()
               Case "Use_External_Source" : Use_External_Source()
            End Select
   
         Catch ex As OleDbException
            Dim er As OleDbError
            For Each er In ex.Errors
               MsgBox(er.Message)
            Next
         Catch ex2 As System.InvalidOperationException
            MsgBox(ex2.Message)
         End Try
   
   
   End Sub
   
   Public Sub Create_Workbook()
   
         ' If the workbooks already exist, prompt to delete.
         Dim answer As MsgBoxResult
         If Dir("C:\ExcelData1.xls") <> "" Or Dir("C:\ExcelData2.xls") <> "" Then
             answer = MsgBox("Delete existing workbooks (C:\ExcelData1.xls and " & _
                      "C:\ExcelData2.xls)?", MsgBoxStyle.YesNo)
             If answer = MsgBoxResult.Yes Then
                 If Dir("C:\ExcelData1.xls") <> "" Then Kill("C:\ExcelData1.xls")
                 If Dir("C:\ExcelData2.xls") <> "" Then Kill("C:\ExcelData2.xls")
             Else
                 Exit Sub
             End If
         End If
   
   '==========================================================================
         ' Create a workbook with a table named EmployeeData. The table has 3 
         ' fields: ID (char 255), Name (char 255) and Birthdate (date).  
   '==========================================================================
         Dim conn As New OleDbConnection()
         conn.ConnectionString = m_sConn1
         conn.Open()
         Dim cmd1 As New OleDbCommand()
         cmd1.Connection = conn
         cmd1.CommandText = "CREATE TABLE EmployeeData (Id char(255), Name char(255), BirthDate date)"
         cmd1.ExecuteNonQuery()
         cmd1.CommandText = "INSERT INTO EmployeeData (Id, Name, BirthDate) values ('AAA', 'Andrew', '12/4/1955')"
         cmd1.ExecuteNonQuery()
         conn.Close()
   
   '==========================================================================
         ' Create a workbook with a table named InventoryData. The table has 3 
         ' fields: Product (char 255), Qty (float) and Price (currency). 
   '==========================================================================
   
         conn.ConnectionString = m_sConn2
         conn.Open()
         Dim cmd2 As New OleDbCommand()
         cmd2.Connection = conn
         cmd2.CommandText = "CREATE TABLE InventoryData (Product char(255), Qty float, Price currency)"
         cmd2.ExecuteNonQuery()
         cmd2.CommandText = "INSERT INTO InventoryData (Product, Qty, Price) values ('Cola', 200, 1.35)"
         cmd2.ExecuteNonQuery()
         cmd2.CommandText = "INSERT INTO InventoryData (Product, Qty, Price) values ('Chips', 550, 0.89)"
         cmd2.ExecuteNonQuery()
         conn.Close()
   
         ' NOTE: You can ALTER and DROP tables in a similar fashion.
   
   End Sub
   
   Public Sub Retrieve_Records()
   
         '==========================================================
         'Use a DataReader to read data from the EmployeeData table.
         '==========================================================
   
         Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1)
         conn1.Open()
         Dim cmd1 As New System.Data.OleDb.OleDbCommand("Select * From [EmployeeData$]", conn1)
         Dim rdr As OleDbDataReader = cmd1.ExecuteReader
   
         Debug.WriteLine(vbCrLf & "EmployeeData:" & vbCrLf & "=============")
         Do While rdr.Read()
            Debug.WriteLine(System.String.Format("{0,-10}{1, -15}{2}", _
               rdr.GetString(0), rdr.GetString(1), _
               rdr.GetDateTime(2).ToString("d")))
         Loop
         rdr.Close()
         conn1.Close()
   
         '========================================================
         'Use a DataSet to read data from the InventoryData table.
         '========================================================
         Dim conn2 As New OleDbConnection(m_sConn2)
         Dim da As New OleDbDataAdapter("Select * From [InventoryData$]", conn2)
         Dim ds As DataSet = New DataSet()
         da.Fill(ds)
         Debug.WriteLine(vbCrLf & "InventoryData:" & vbCrLf & "==============")
         Dim dr As DataRow
         For Each dr In ds.Tables(0).Rows'Show results in output window
            Debug.WriteLine(System.String.Format("{0,-15}{1, -6}{2}", _
               dr("Product"), dr("Qty"), dr("Price")))
         Next
         conn2.Close()
   
   End Sub
   
   Public Sub Add_Records()
   
   '==========================================================================
         ' Run an INSERT INTO command to add new records to the workbook. 
   '==========================================================================
         Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1)
         conn1.Open()
         Dim cmd As New System.Data.OleDb.OleDbCommand()
         cmd.Connection = conn1
         cmd.CommandText = "INSERT INTO [EmployeeData$] (ID, Name, BirthDate) values ('CCC', 'Charlie', '10/14/48')"
         cmd.ExecuteNonQuery()
         cmd.CommandText = "INSERT INTO [EmployeeData$] (ID, Name, BirthDate) values ('DDD', 'Deloris', '7/19/98')"
         cmd.ExecuteNonQuery()
         conn1.Close()
   
         '====================================================================
         'Use the InsertCommand object to add new records to the InventoryData
         'table.
         '====================================================================
         Dim conn2 As New OleDbConnection(m_sConn2)
         Dim da As New OleDbDataAdapter("Select * From [InventoryData$]", conn2)
         Dim ds As DataSet = New DataSet()
         da.Fill(ds, "MyExcelTable")
   
         ' Generate the InsertCommand and add the parameters for the command.
         da.InsertCommand = New OleDbCommand( _
            "INSERT INTO [InventoryData$] (Product, Qty, Price) VALUES (?, ?, ?)", conn2)
         da.InsertCommand.Parameters.Add("@Product", OleDbType.VarChar, 255, "Product")
         da.InsertCommand.Parameters.Add("@Qty", OleDbType.Double).SourceColumn = "Qty"
         da.InsertCommand.Parameters.Add("@Price", OleDbType.Currency).SourceColumn = "Price"
   
         ' Add two new records to the dataset.
         Dim dr As DataRow
         dr = ds.Tables(0).NewRow
         dr("Product") = "Bread" : dr("Qty") = 390 : dr("Price") = 1.89 : ds.Tables(0).Rows.Add(dr)
         dr = ds.Tables(0).NewRow
         dr("Product") = "Milk" : dr("Qty") = 99 : dr("Price") = 2.59 : ds.Tables(0).Rows.Add(dr)
   
         ' Apply the dataset changes to the actual data source (the workbook).
         da.Update(ds, "MyExcelTable")
         conn2.Close()
   
   End Sub
   
   Public Sub Update_Records()
   
   '==========================================================================
         ' Run an UPDATE command to change a record in the EmployeeData
         ' table.
   '==========================================================================
         Dim conn1 As New System.Data.OleDb.OleDbConnection(m_sConn1)
         conn1.Open()
         Dim cmd As New System.Data.OleDb.OleDbCommand()
         cmd.Connection = conn1
         cmd.CommandText = "UPDATE [EmployeeData$] " & _
                       "SET NAME = 'Aaron', BirthDate = '5/4/1975' WHERE ID = 'AAA'"
         cmd.ExecuteNonQuery()
         conn1.Close()
   
         '====================================================================
         ' Use the UpdateCommand object to modify records in the InventoryData
         ' table.
         '====================================================================
         Dim conn2 As New OleDbConnection(m_sConn2)
         Dim da As New OleDbDataAdapter("Select * From [InventoryData$]", conn2)
         Dim ds As DataSet = New DataSet()
         da.Fill(ds, "MyInventoryTable")
   
         ' Generate the UpdateCommand and add the parameters for the command.
         da.UpdateCommand = New OleDbCommand( _
            "UPDATE [InventoryData$] SET Qty = ?, Price=? WHERE Product = ?", conn2)
         da.UpdateCommand.Parameters.Add("@Qty", OleDbType.Numeric).SourceColumn = "Qty"
         da.UpdateCommand.Parameters.Add("@Price", OleDbType.Currency).SourceColumn = "Price"
         da.UpdateCommand.Parameters.Add("@Product", OleDbType.VarChar, 255, "Product")
   
         ' Update the first two records.
         ds.Tables(0).Rows(0)("Qty") = 1000
         ds.Tables(0).Rows(0)("Price") = 10.1
         ds.Tables(0).Rows(1)("Qty") = 2000
         ds.Tables(0).Rows(1)("Price") = 20.2
   
         ' Apply the dataset changes to the actual data source (the workbook).
         da.Update(ds, "MyInventoryTable")
         conn2.Close()
   
   End Sub
   
   Public Sub Update_Individual_Cells()
   
   '==========================================================================
         ' Update individual cells on the EmployeeData worksheet; 
         ' specifically, cells F3, G3, and I4 are modified.
   '==========================================================================
   
         ' NOTE: The connection string indicates that the table does *NOT* 
         ' have a header row.
         Dim conn As New System.Data.OleDb.OleDbConnection(m_sConn1.Replace("HDR=YES", "HDR=NO"))
         conn.Open()
         Dim cmd As New System.Data.OleDb.OleDbCommand()
         cmd.Connection = conn
         cmd.CommandText = "UPDATE [EmployeeData$F3:G3] SET F1 = 'Cell F3', F2 = 'Cell G3'"
         cmd.ExecuteNonQuery()
         cmd.CommandText = "UPDATE [EmployeeData$I4:I4] SET F1 = 'Cell I4'"
         cmd.ExecuteNonQuery()
         conn.Close()
   
   End Sub
   
   Public Sub Use_External_Source()
   
         ' Open a connection to the sample Northwind Access database.
         Dim conn As New System.Data.OleDb.OleDbConnection( _
               "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & m_sNorthwind & ";")
         conn.Open()
         Dim cmd As New System.Data.OleDb.OleDbCommand()
         cmd.Connection = conn
   
   '=======================================================================
         ' Run an INSERT..INTO command on the Northwind database to append 
         ' the records from a table/query to an existing table in the Excel 
         ' workbook.
   '=======================================================================
         cmd.CommandText = "INSERT INTO [EmployeeData$] IN 'C:\ExcelData1.xls' 'Excel 8.0;'" & _
            "SELECT EmployeeID AS ID, FirstName AS Name, BirthDate FROM Employees"
         cmd.ExecuteNonQuery()
   
   '==========================================================================
         ' Run a SELECT..INTO command on the Northwind database to insert 
         ' all the records from a table/query into a new sheet in the Excel 
         ' workbook.
   '==========================================================================
         cmd.CommandText = "SELECT * INTO [Excel 8.0;Database=C:\ExcelData2.xls].[ProductSales]" & _
                         "FROM [Product Sales for 1997]"
         cmd.ExecuteNonQuery()
   
         conn.Close()
   
   End Sub

7. Modify the path to the sample Access database, Northwind, for the m_sNorthwind member in the code, if necessary.

Try It Out

1. On the View menu, point to Other Windows, and then click Output to display the Output window.
2. Press F5 to build and run the program.
3. Click Create_Workbook and then click Go. The Create_Workbook procedure runs CREATE TABLE commands to create two new workbooks: C:\ExcelData1.xls and C:\ExcelData2.xls. ExcelData1.xls contains one sheet (table) named EmployeeData and ExcelData2.xls contains one sheet (table) named InventoryData. The tables are filled with records.
   
   NOTE: At each remaining step in this test, open the workbooks in Excel to examine the results. Or, click Retrieve_Records to view the contents of the table(s) in the Output Window of Visual Studio .NET.
4. Click Retrieve_Records and then click Go. The Retrieve_Records procedure extracts the records from the tables and displays them in the Output window, similar to this:

   EmployeeData:
   =============
   AAA       Andrew         12/4/1955
   
   InventoryData:
   ==============
   Cola           200   1.35
   Chips          550   0.89

5. Click Add_Records and then click Go. The Add_Records routine adds two records to each table:

   EmployeeData:
   =============
   AAA       Andrew         12/4/1955
   CCC       Charlie        10/14/1948
   DDD       Deloris        7/19/1998
   
   InventoryData:
   ==============
   Cola           200   1.35
   Chips          550   0.89
   Bread          390   1.89
   Milk           99    2.59

6. Click Update_Records and then click Go. The Update_Records routine updates two records in each workbook:

   EmployeeData:
   =============
   AAA       Aaron          5/4/1975
   CCC       Charlie        10/14/1948
   DDD       Deloris        7/19/1998
   
   InventoryData:
   ==============
   Cola           1000  10.1
   Chips          2000  20.2
   Bread          390   1.89
   Milk           99    2.59

7. Click Update_Individual_Cells and then click Go. The Update_Individual_Cells routine modifies specific cells on the EmployeeData worksheet in ExcelData1.xls; specifically, cells F3, G3, and I4 are updated.
8. Click Use_External_Source and then click Go. When you use an INSERT..INTO command, the Use_External_Source routine appends records from the Northwind table 'Employees' to the EmployeeData worksheet in ExcelData1.xls. And, Use_External_Source uses a SELECT..INTO command to create a new table (or sheet) in ExcelData2.xls that contains all of the records from the Northwind table 'Products'.
   
   NOTE: If you click Use_External_Source more than one time, the Employees list will be appended multiple times because the primary key is not recognized or enforced.

Cell Formatting

1. Press F5 to build and run the sample.
2. On Form1, click Create_Workbook and then click Go.
3. Start Microsoft Excel and open C:\ExcelData1.xls.
4. Apply a bold font style to cell A2.
5. Apply an italic, underline style to cell B2 and align center.
6. Apply a long date format to cell C2.
7. Save and close C:\ExcelData1.xls.
8. On Form1, click Add_Records and then click Go.
9. Open C:\ExcelData1.xls in Excel and notice that the two new rows have inherited the formatting from the first row.

Limitations

• You cannot insert formulas in cells using ADO.NET.
• The Jet OLE DB Provider is unable to provide key/index information for tables in an Excel workbook. For this reason, you cannot use the CommandBuilder to automatically generate updates and insertions for records in an Excel workbook.