2015年4月17日 星期五
How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET
Sample Code
Start a new Visual Basic .NET Windows Application project.
Form1 is created by default.
Add six RadioButton controls and a Button control to Form1.
Select all of the RadioButton controls and set the Size property to 200,24.
On the View menu, click Code.
Add the following line to the very beginning of the code module:
Imports System.Data.OleDb
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
訂閱：
文章 (Atom)