使用ADO.NET操作Excel

1. http://support.microsoft.com/kb/316756/zh-cn 
   
2. 將下列程式碼加入至程式碼模組的開頭：

   Imports System.Data.OleDb 					

3. 在 Excel 2000 和 Excel 2003 中，加入下列程式碼範例，到Form1類別。

       Private Sub Button1_Click(ByVal sender As System.Object, _        ByVal e As System.EventArgs) Handles Button1.Click         ModifyXLData(0) 'Add record.     End Sub      Private Sub Button2_Click(ByVal sender As Object, ByVal e As _        System.EventArgs) Handles Button2.Click         ModifyXLData(1) 'Update record.     End Sub      Private Sub ModifyXLData(ByVal nAction As Int16)          Dim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _                               "Data Source=C:\Test.xls;" & _                               "Extended Properties=""Excel 8.0;HDR=YES"""          ' Create an instance of a DataAdapter.         Dim da As New OleDbDataAdapter("Select * From [Sheet1$]", sConn)         Dim cb As New OleDbCommandBuilder(da)          ' Create an instance of a DataSet from Sheet1 data.         Dim ds As New DataSet("Data")         da.FillSchema(ds, SchemaType.Source, "[Sheet1$]")         da.Fill(ds, "[Sheet1$]")          'Add, modify, or delete a row.         Dim dr As DataRow         Select Case nAction             Case 0   'Add a new row                 Dim rowVals(1) As Object                 rowVals(0) = "hello"                 rowVals(1) = "world"                 ds.Tables(0).Rows.Add(rowVals)             Case 1   'Modify an existing row                 dr = ds.Tables(0).Rows(0)                 dr.BeginEdit()                 dr(0) = "hi"                 dr.EndEdit()         End Select          'Attempt the update.         Try             da.Update(ds, "[Sheet1$]")         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     					

   在 Excel 2007 中，加入下列程式碼範例，到Form1類別。

       Private Sub Button1_Click(ByVal sender As System.Object, _        ByVal e As System.EventArgs) Handles Button1.Click         ModifyXLData(0) 'Add record.     End Sub      Private Sub Button2_Click(ByVal sender As Object, ByVal e As _        System.EventArgs) Handles Button2.Click         ModifyXLData(1) 'Update record.     End Sub      Private Sub ModifyXLData(ByVal nAction As Int16)          Dim sConn As String = "Provider=Microsoft.ACE.OLEDB.12.0;" & _                               '"Data Source=C:\Test.xls;" & _                               '"Extended Properties=""Excel 12.0;HDR=YES"""          ' Create an instance of a DataAdapter.         Dim da As New OleDbDataAdapter("Select * From [Sheet1$]", sConn)         Dim cb As New OleDbCommandBuilder(da)          ' Create an instance of a DataSet from Sheet1 data.         Dim ds As New DataSet("Data")         da.FillSchema(ds, SchemaType.Source, "[Sheet1$]")         da.Fill(ds, "[Sheet1$]")          'Add, modify, or delete a row.         Dim dr As DataRow         Select Case nAction             Case 0   'Add a new row.                 Dim rowVals(1) As Object                 rowVals(0) = "hello"                 rowVals(1) = "world"                 ds.Tables(0).Rows.Add(rowVals)             Case 1   'Modify an existing row                 dr = ds.Tables(0).Rows(0)                 dr.BeginEdit()                 dr(0) = "hi"                 dr.EndEdit()         End Select          'Attempt the update.         Try             da.Update(ds, "[Sheet1$]")         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