Bagaimana menggunakan ADO.NET untuk mengambil dan memodifikasi catatan di buku kerja Excel dengan Visual Basic.NET

Kembali ke atas

Cara menggunakan penyedia DB OLE Jet dengan Microsoft Excel Workbook

Rangkaian sambungan

Tipe data

Konvensi penamaan meja

• Menggunakan nama lembar yang diikuti dengan tanda dolar (misalnya, [Sheet1$] atau [saya lembar kerja$]). Tabel buku kerja yang direkomendasikan ini cara mencakup seluruh digunakan berbagai lembar kerja.
  Pilih * dari [Sheet1$]
• Menggunakan berbagai dengan nama yang ditetapkan (misalnya, [MyNamedRange]):
  Pilih * dari [MyNamedRange]
• Menggunakan berbagai dengan alamat tertentu (misalnya, [Sheet1$ A1: B10]):
  Pilih * dari [Sheet1$ A1: B10]

Kembali ke atas

Cara menggunakan Excel Workbook sebagai ADO.Sumber NET Data

Mengambil catatan

Menambahkan dan memperbarui catatan

• Langsung menjalankan perintah untuk memasukkan atau memperbarui catatan satu di waktu. Untuk melakukan ini, Anda dapat membuat OLEDbCommand objek pada sambungan dan set yang CommandText properti untuk perintah yang berlaku untuk memasukkan catatan
  
  

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

  atau perintah untuk mengupdate catatan
  
  

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

  dan kemudian memanggil ExecuteNonQuery metode.
• Membuat perubahan DataSet bahwa Anda telah mengisi dengan tabel/permintaan dari buku kerja Excel dan kemudian memanggil Pemutakhiran metode DataAdapter untuk menyelesaikan perubahan dari DataSet kembali ke buku kerja. Namun, untuk menggunakan Pemutakhiran metode untuk perubahan resolusi Anda harus mengatur parameterized perintah untuk DataAdapter InsertCommand

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

  dan UpdateCommand:

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

  Masukkan parameterized dan UPDATE perintah diperlukan karena OleDbDataAdapter tidak memberikan informasi kunci/indeks untuk buku kerja Excel; tanpa kunci/indeks bidang, CommandBuilder tidak dapat secara otomatis menghasilkan perintah untuk Anda.
• Ekspor data dari sumber data lain ke buku kerja Excel yang disediakan bahwa sumber data lain dapat digunakan dengan Jet OLE DB penyedia. Data sumber-sumber yang dapat Anda gunakan dengan Jet OLE DB penyedia cara ini termasuk File teks, Microsoft Access database, dan, tentu saja, buku kerja Excel lainnya. Dengan satu masukkan ke perintah, Anda dapat mengekspor data dari tabel/permintaan lain ke buku kerja Anda:

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

  Masukkan ke mensyaratkan bahwa target meja (atau lembar kerja) sudah ada; data ditambahkan ke tabel sasaran.
  
  Anda juga dapat menggunakan PILIH.INTO untuk mengekspor meja/permintaan ke buku kerja:

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

  Ketika Anda menggunakan pilih.INTO, jika target meja atau buku kerja tidak ada, itu akan membuat untuk Anda. Jika tabel sudah ada sebelum PILIH.KE perintah yang dikeluarkan, Anda akan menerima pesan kesalahan.

Menghapus catatan

Buat buku kerja dan tabel

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

Kembali ke atas

Langkah demi langkah

Contoh kode

1. Mulai Visual Basic baru.NET Aplikasi Windows proyek.
   
   Form1 dibuat oleh default.
2. Tambahkan enam RadioButton kontrol dan tombol kontrol untuk Form1.
3. Pilih semua kontrol RadioButton dan mengatur Ukuran properti untuk 200,24.
4. Pada Lihat menu, klik Kode.
5. Tambahkan baris berikut ke awal kode modul:

   Imports System.Data.OleDb

6. Masukkan kode berikut ke Bentuk kelas:

   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. Mengubah jalan untuk akses sampel database, Northwind, untuk anggota m_sNorthwind dalam kode, jika diperlukan.

Mencobanya

1. Pada Lihat menu, titik Windows lainnya, lalu klik Keluaran untuk menampilkan Keluaran jendela.
2. Tekan F5 untuk membangun dan menjalankan program.
3. Klik Create_Workbook kemudian klik Pergi. The Create_Workbook prosedur berjalan CREATE TABLE perintah untuk membuat dua buku kerja baru: C:\ExcelData1.xls dan C:\ExcelData2.xls. ExcelData1.xls berisi satu lembar (tabel) bernama EmployeeData dan ExcelData2.xls berisi satu lembar (tabel) bernama InventoryData. Tabel dipenuhi dengan catatan.
   
   CATATAN: Pada setiap langkah yang tersisa dalam tes ini, buka buku kerja Excel untuk memeriksa hasilnya. Atau, klik Retrieve_Records untuk melihat isi table(s) di Keluaran Jendela Visual Studio.NET.
4. Klik Retrieve_Records kemudian klik Pergi. The Retrieve_Records prosedur ekstrak catatan dari tabel dan menampilkan mereka dalam Keluaran jendela, seperti ini:

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

5. Klik Add_Records kemudian klik Pergi. The Add_Records rutin menambahkan dua catatan untuk setiap tabel:

   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. Klik Update_Records kemudian klik Pergi. The Update_Records rutin update dua catatan di setiap buku kerja:

   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. Klik Update_Individual_Cells kemudian klik Pergi. The Update_Individual_Cells rutin memodifikasi spesifik sel pada lembar kerja EmployeeData di ExcelData1.xls; secara khusus, sel F3, G3 dan I4 diperbarui.
8. Klik Use_External_Source kemudian klik Pergi. Ketika Anda menggunakan INSERT.KE perintah, Use_External_Source rutin menambahkan catatan dari tabel Northwind 'Karyawan' lembar kerja EmployeeData di ExcelData1.xls. Dan, Use_External_Source menggunakan sebuah pilih.MENJADI perintah untuk membuat sebuah tabel baru (atau lembar) di ExcelData2.xls yang berisi semua data dari tabel Northwind 'Produk'.
   
   CATATAN: Jika Anda klik Use_External_Source lebih dari satu kali, daftar karyawan akan ditambahkan beberapa kali karena kunci utama tidak diakui atau diberlakukan.

Kembali ke atas

Sel format

1. Tekan F5 untuk membangun dan menjalankan sampel.
2. Pada Form1, klik Create_Workbook kemudian klik Pergi.
3. Mulai Microsoft Excel dan membuka C:\ExcelData1.xls.
4. Menerapkan gaya font tebal ke sel A2.
5. Menerapkan miring, garis bawah gaya ke sel B2 dan menyelaraskan pusat.
6. Menerapkan format tanggal panjang ke sel C2.
7. Simpan dan tutup C:\ExcelData1.xls.
8. Pada Form1, klik Add_Records kemudian klik Pergi.
9. Buka C:\ExcelData1.xls di Excel dan menyadari bahwa dua baru baris telah mewarisi format dari baris pertama.

Kembali ke atas

Keterbatasan

• Anda tidak dapat menyisipkan rumus dalam sel menggunakan ADO.NET.
• Jet OLE penyedia DB mampu memberikan kunci/indeks informasi untuk tabel di buku kerja Excel. Untuk alasan ini, Anda tidak dapat menggunakan CommandBuilder untuk secara otomatis menghasilkan pembaruan dan insersi Records di buku kerja Excel.

Kembali ke atas