Bagaimana untuk mentransfer data ke buku kerja Excel dengan menggunakan Visual Basic.NET

Sekilas pandang

• Mentransfer data sel oleh sel.
• Mentransfer data dalam array untuk kisaran sel.
• Transfer data dalam recordset ADO ke kisaran sel oleh menggunakan CopyFromRecordset metode.
• Membuat QueryTable objek pada lembar kerja Excel yang berisi hasil query pada ODBC atau OLEDB sumber data.
• Mentransfer data ke clipboard, dan kemudian paste isi Clipboard ke lembar kerja Excel.

• Mentransfer data ke tab atau koma-delimited file teks Excel yang kemudian dapat mengurai menjadi sel-sel pada lembar kerja.
• Mentransfer data ke lembar kerja menggunakan ADO.NET.
• Transfer XML data ke Excel (2002 hanya versi) untuk menyediakan data yang diformat dan diatur ke dalam baris dan kolom.

Teknik

Menggunakan otomatisasi untuk mentransfer data sel oleh sel

        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet As Object

        'Start a new workbook in Excel.
        oExcel = CreateObject("Excel.Application")
        oBook = oExcel.Workbooks.Add

        'Add data to cells of the first worksheet in the new workbook.
        oSheet = oBook.Worksheets(1)
        oSheet.Range("A1").Value = "Last Name"
        oSheet.Range("B1").Value = "First Name"
        oSheet.Range("A1:B1").Font.Bold = True
        oSheet.Range("A2").Value = "Doe"
        oSheet.Range("B2").Value = "John"

        'Save the Workbook and quit Excel.
        oBook.SaveAs(sSampleFolder & "Book1.xls")
        oSheet = Nothing
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing
        GC.Collect()
				

Menggunakan otomatisasi untuk mentransfer sebuah array data ke berbagai pada lembar kerja

        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet As Object

        'Start a new workbook in Excel.
        oExcel = CreateObject("Excel.Application")
        oBook = oExcel.Workbooks.Add

        'Create an array with 3 columns and 100 rows.
        Dim DataArray(99, 2) As Object
        Dim r As Integer
        For r = 0 To 99
            DataArray(r, 0) = "ORD" & Format(r + 1, "0000")
            DataArray(r, 1) = Rnd() * 1000
            DataArray(r, 2) = DataArray(r, 1) * 0.07
        Next

        'Add headers to the worksheet on row 1.
        oSheet = oBook.Worksheets(1)
        oSheet.Range("A1").Value = "Order ID"
        oSheet.Range("B1").Value = "Amount"
        oSheet.Range("C1").Value = "Tax"

        'Transfer the array to the worksheet starting at cell A2.
        oSheet.Range("A2").Resize(100, 3).Value = DataArray

        'Save the Workbook and quit Excel.
        oBook.SaveAs(sSampleFolder & "Book2.xls")
        oSheet = Nothing
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing
        GC.Collect()
				

        oSheet.Range("A2").Resize(100, 3).Value = DataArray
				

Menggunakan otomatisasi untuk mentransfer ADO recordset ke berbagai lembar kerja

        'Create a Recordset from all the records in the Orders table.
        Dim sNWind As String
        Dim conn As New ADODB.Connection()
        Dim rs As ADODB.Recordset
        conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
            sNorthwind & ";")
        conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
        rs = conn.Execute("Orders", , ADODB.CommandTypeEnum.adCmdTable)

        'Create a new workbook in Excel.
        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet As Object
        oExcel = CreateObject("Excel.Application")
        oBook = oExcel.Workbooks.Add
        oSheet = oBook.Worksheets(1)

        'Transfer the field names to Row 1 of the worksheet:
        'Note: CopyFromRecordset copies only the data and not the field
        '      names, so you can transfer the fieldnames by traversing the
        '      fields collection.
        Dim n As Int32
        For n = 1 To rs.Fields.Count
            oSheet.Cells(1, n).Value = rs.Fields(n - 1).Name
        Next

        'Transfer the data to Excel.
        oSheet.Range("A2").CopyFromRecordset(rs)

        'Save the workbook and quit Excel.
        oBook.SaveAs(sSampleFolder & "Book3.xls")
        oSheet = Nothing
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing
        GC.Collect()

        'Close the connection
        rs.Close()
        conn.Close()
				

Menggunakan otomatisasi untuk membuat QueryTable objek pada lembar kerja

• Excel menangani penciptaan recordset dan Penempatan ke lembar kerja.
• Pertanyaan dapat disimpan dengan QueryTable objek jadi itu dapat menyegarkan kemudian memperoleh diperbarui Recordset.
• Ketika baru QueryTable ditambahkan ke lembar kerja Anda, Anda dapat menentukan data yang sudah ada dalam sel pada lembar kerja bergeser sesuai data baru (lihat RefreshStyle properti untuk rincian).

        'Create a new workbook in Excel.
        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet As Object
        oExcel = CreateObject("Excel.Application")
        oBook = oExcel.Workbooks.Add
        oSheet = oBook.Worksheets(1)

        'Create the QueryTable object.
        Dim oQryTable As Object
        oQryTable = oSheet.QueryTables.Add( _
        "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
            sNorthwind & ";", oSheet.Range("A1"), _
            "Select * from Orders")
        oQryTable.RefreshStyle = 2 ' xlInsertEntireRows = 2
        oQryTable.Refresh(False)

        'Save the workbook and quit Excel.
        oBook.SaveAs(sSampleFolder & "Book4.xls")
        oQryTable = Nothing
        oSheet = Nothing
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing
				

Menggunakan Clipboard

        'Copy a string to the Clipboard.
        Dim sData As String
        sData = "FirstName" & vbTab & "LastName" & vbTab & "Birthdate" & vbCr _
            & "Bill" & vbTab & "Brown" & vbTab & "2/5/85" & vbCr _
            & "Joe" & vbTab & "Thomas" & vbTab & "1/1/91"
        System.Windows.Forms.Clipboard.SetDataObject(sData)

        'Create a workbook in Excel.
        Dim oExcel As Object
        Dim oBook As Object
        oExcel = CreateObject("Excel.Application")
        oBook = oExcel.Workbooks.Add

        'Paste the data.
        oBook.Worksheets(1).Range("A1").Select()
        oBook.Worksheets(1).Paste()

        'Save the workbook and quit Excel.
        oBook.SaveAs(sSampleFolder & "Book5.xls")
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing
        GC.Collect()
				

Membuat berkas berpembatas teks yang Excel dapat mengurai menjadi baris dan kolom

        'Connect to the data source.
        Dim objConn As New System.Data.OleDb.OleDbConnection( _
            "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sNorthwind & ";")
        objConn.Open()

        'Execute a command to retrieve all records from the Employees table.
        Dim objCmd As New System.Data.OleDb.OleDbCommand( _
            "Select * From Employees", objConn)
        Dim objReader As System.Data.OleDb.OleDbDataReader
        objReader = objCmd.ExecuteReader()

        'Read the records in the dataset and write select fields to the 
        'output file.
        FileOpen(1, sSampleFolder & "Book6.txt", OpenMode.Output)
        Dim i As Integer, s As String
        While objReader.Read()
            'Loop through first 6 fields and concatenate
            'each field, separated by a tab, into s variable.
            s = ""
            For i = 0 To 5
                If Not objReader.IsDBNull(i) Then
                    If i = 0 Then 'field 1 is EmployeeId
                        s = s & objReader.GetInt32(i).ToString
                    ElseIf i = 5 Then 'field 6 is BirthDate
                        s = s & objReader.GetDateTime(i)
                    Else 'field is a text field
                        s = s & objReader.GetString(i)
                    End If
                End If
                s = s & Microsoft.VisualBasic.ControlChars.Tab
            Next
            PrintLine(1, s)
        End While
        FileClose(1)

        'Close the reader and the connection.
        objReader.Close()
        objConn.Close()
				

        'Create a new instance of Excel.
        Dim oExcel As Object
        oExcel = CreateObject("Excel.Application")

        'Open the text file and save it in the Excel workbook format.
        oExcel.Workbooks.OpenText(sSampleFolder & "Book6.txt", _
            , , , -4142, , True) 'xlTextQualifierNone=-4142

        oExcel.ActiveWorkbook.SaveAs(sSampleFolder & "Book6.xls", _
            -4143) 'xlWorkbookNormal = -4143

        'Quit Excel.
        oExcel.Quit()
        oExcel = Nothing
        GC.Collect()
				

Mentransfer data ke lembar kerja dengan menggunakan ADO.NET

       'Establish a connection to the data source.
        Dim sConnectionString As String
        sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & sSampleFolder & _
            "Book7.xls;Extended Properties=Excel 8.0;"
        Dim objConn As New System.Data.OleDb.OleDbConnection(sConnectionString)
        objConn.Open()

        'Add two records to the table.
        Dim objCmd As New System.Data.OleDb.OleDbCommand()
        objCmd.Connection = objConn
        objCmd.CommandText = "Insert into [Sheet1$] (FirstName, LastName)" & _
            " values ('Bill', 'Brown')"
        objCmd.ExecuteNonQuery()
        objCmd.CommandText = "Insert into [Sheet1$] (FirstName, LastName)" & _
            " values ('Joe', 'Thomas')"
        objCmd.ExecuteNonQuery()

        'Close the connection.
        objConn.Close()
				

Mentransfer data XML (Excel 2002)

Membuat sampel lengkap Visual Basic.Proyek NET

1. Buat folder baru untuk menahan buku kerja Excel yang contoh akan membuat untuk Anda, dan kemudian nama folder C:\Exceldata\.
2. Ikuti langkah berikut untuk membuat buku kerja baru untuk sampel untuk menulis ke:
   1. Mulai buku kerja baru di Excel.
   2. Pada Sheet1 dari buku kerja baru, ketik Nama depan dalam sel A1 dan Nama belakangdalam sel A2.
   3. Simpan buku kerja sebagai C:\Exceldata\Book7.xls.
3. Mulai Visual Studio.NET. Pada Berkas menu, klik Baru kemudian klik Project. Di bawah Proyek-proyek Visual Basic, pilih Aplikasi Windows. Secara default, Form1 dibuat.
4. Menambahkan referensi ke perpustakaan objek Excel. Untuk melakukan hal ini, Ikuti langkah berikut:
   1. Pada Project menu, klik Menambahkan referensi.
   2. Pada COM tab, Cari Perpustakaan objek Microsoft Excel 10,0, lalu klik Pilih.
      
      Catatan Jika Anda tidak melakukannya, Microsoft menyarankan Anda Download dan kemudian instal Microsoft Office XP utama Interop Majelis (PIAs). Untuk informasi lebih lanjut tentang Office XP PIAs, klik nomor artikel di bawah ini untuk melihat artikel di dalam Basis Pengetahuan Microsoft:
      328912

      (http://support.microsoft.com/kb/328912/ )

      Microsoft Office XP utama interop Majelis (PIAs) tersedia untuk di-download
   3. Pada COM tab, Cari Objek Microsoft ActiveX Data 2.7 perpustakaan, lalu klik Pilih.
   4. Klik Oke dalam Menambahkan referensi kotak dialog untuk menerima pilihan Anda. Jika Anda menerima prompt untuk menghasilkan pembungkus untuk perpustakaan yang Anda pilih, klik Ya.
5. Tambahkan Combo Box kontrol dan Tombol mengontrol Form1.
6. Tambahkan kode berikut untuk Form1.

       Const sSampleFolder = "C:\ExcelData\"
       Const sNorthwind = "C:\Program Files\Microsoft Office\Office10\Samples\Northwind.mdb"
   
       Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) _
         Handles MyBase.Load
           ComboBox1.DropDownStyle = ComboBoxStyle.DropDownList
           Dim aList As String() = _
               {"Use Automation to Transfer Data Cell by Cell ", _
                "Use Automation to Transfer an Array of Data to a Range on a Worksheet ", _
                "Use Automation to Transfer an ADO Recordset to a Worksheet Range ", _
                "Use Automation to Create a QueryTable on a Worksheet", _
                "Use the Clipboard", _
                "Create a Delimited Text File that Excel Can Parse into Rows and Columns", _
                "Transfer Data to a Worksheet Using ADO.NET "}
           ComboBox1.Items.AddRange(aList)
           ComboBox1.SelectedIndex = 0
           Button1.Text = "Go!"
       End Sub
   
       Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
         Handles Button1.Click
           Select Case ComboBox1.SelectedIndex
               Case 0 : Automation_CellByCell()
               Case 1 : Automation_UseArray()
               Case 2 : Automation_ADORecordset()
               Case 3 : Automation_QueryTable()
               Case 4 : Use_Clipboard()
               Case 5 : Create_TextFile()
               Case 6 : Use_ADONET()
           End Select
           GC.Collect()
       End Sub
   
       Private Function Automation_CellByCell()
           Dim oExcel As Object
           Dim oBook As Object
           Dim oSheet As Object
   
           'Start a new workbook in Excel.
           oExcel = CreateObject("Excel.Application")
           oBook = oExcel.Workbooks.Add
   
           'Add data to cells of the first worksheet in the new workbook.
           oSheet = oBook.Worksheets(1)
           oSheet.Range("A1").Value = "Last Name"
           oSheet.Range("B1").Value = "First Name"
           oSheet.Range("A1:B1").Font.Bold = True
           oSheet.Range("A2").Value = "Doe"
           oSheet.Range("B2").Value = "John"
   
           'Save the workbook and quit Excel.
           oBook.SaveAs(sSampleFolder & "Book1.xls")
           oSheet = Nothing
           oBook = Nothing
           oExcel.Quit()
           oExcel = Nothing
           GC.Collect()
       End Function
   
       Private Function Automation_UseArray()
           Dim oExcel As Object
           Dim oBook As Object
           Dim oSheet As Object
   
           'Start a new workbook in Excel.
           oExcel = CreateObject("Excel.Application")
           oBook = oExcel.Workbooks.Add
   
           'Create an array with 3 columns and 100 rows.
           Dim DataArray(99, 2) As Object
           Dim r As Integer
           For r = 0 To 99
               DataArray(r, 0) = "ORD" & Format(r + 1, "0000")
               DataArray(r, 1) = Rnd() * 1000
               DataArray(r, 2) = DataArray(r, 1) * 0.07
           Next
   
           'Add headers to the worksheet on row 1.
           oSheet = oBook.Worksheets(1)
           oSheet.Range("A1").Value = "Order ID"
           oSheet.Range("B1").Value = "Amount"
           oSheet.Range("C1").Value = "Tax"
   
           'Transfer the array to the worksheet starting at cell A2.
           oSheet.Range("A2").Resize(100, 3).Value = DataArray
   
           'Save the workbook and quit Excel.
           oBook.SaveAs(sSampleFolder & "Book2.xls")
           oSheet = Nothing
           oBook = Nothing
           oExcel.Quit()
           oExcel = Nothing
           GC.Collect()
       End Function
   
       Private Function Automation_ADORecordset()
           'Create a Recordset from all the records in the Orders table.
           Dim sNWind As String
           Dim conn As New ADODB.Connection()
           Dim rs As ADODB.Recordset
           conn.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
               sNorthwind & ";")
           conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
           rs = conn.Execute("Orders", , ADODB.CommandTypeEnum.adCmdTable)
   
           'Create a new workbook in Excel.
           Dim oExcel As Object
           Dim oBook As Object
           Dim oSheet As Object
           oExcel = CreateObject("Excel.Application")
           oBook = oExcel.Workbooks.Add
           oSheet = oBook.Worksheets(1)
   
           'Transfer the field names to Row 1 of the worksheet:
           'Note: CopyFromRecordset copies only the data and not the field
           '      names, so you can transfer the fieldnames by traversing the
           '      fields collection.
           Dim n As Int32
           For n = 1 To rs.Fields.Count
               oSheet.Cells(1, n).Value = rs.Fields(n - 1).Name
           Next
   
           'Transfer the data to Excel.
           oSheet.Range("A2").CopyFromRecordset(rs)
   
           'Save the workbook and quit Excel.
           oBook.SaveAs(sSampleFolder & "Book3.xls")
           oSheet = Nothing
           oBook = Nothing
           oExcel.Quit()
           oExcel = Nothing
           GC.Collect()
   
           'Close the connection.
           rs.Close()
           conn.Close()
       End Function
   
       Private Function Automation_QueryTable()
           'Create a new workbook in Excel.
           Dim oExcel As Object
           Dim oBook As Object
           Dim oSheet As Object
           oExcel = CreateObject("Excel.Application")
           oBook = oExcel.Workbooks.Add
           oSheet = oBook.Worksheets(1)
   
           'Create the QueryTable object.
           Dim oQryTable As Object
           oQryTable = oSheet.QueryTables.Add( _
           "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
               sNorthwind & ";", oSheet.Range("A1"), _
               "Select * from Orders")
           oQryTable.RefreshStyle = 2 ' xlInsertEntireRows = 2
           oQryTable.Refresh(False)
   
           'Save the workbook and quit Excel.
           oBook.SaveAs(sSampleFolder & "Book4.xls")
           oQryTable = Nothing
           oSheet = Nothing
           oBook = Nothing
           oExcel.Quit()
   
   
           oExcel = Nothing
   
       End Function
   
       Private Function Use_Clipboard()
           'Copy a string to the clipboard.
           Dim sData As String
           sData = "FirstName" & vbTab & "LastName" & vbTab & "Birthdate" & vbCr _
               & "Bill" & vbTab & "Brown" & vbTab & "2/5/85" & vbCr _
               & "Joe" & vbTab & "Thomas" & vbTab & "1/1/91"
           System.Windows.Forms.Clipboard.SetDataObject(sData)
   
           'Create a new workbook in Excel.
           Dim oExcel As Object
           Dim oBook As Object
           oExcel = CreateObject("Excel.Application")
           oBook = oExcel.Workbooks.Add
   
           'Paste the data.
           oBook.Worksheets(1).Range("A1").Select()
           oBook.Worksheets(1).Paste()
   
           'Save the workbook and quit Excel.
           oBook.SaveAs(sSampleFolder & "Book5.xls")
           oBook = Nothing
           oExcel.Quit()
           oExcel = Nothing
           GC.Collect()
       End Function
   
       Private Function Create_TextFile()
           'Connect to the data source.
           Dim objConn As New System.Data.OleDb.OleDbConnection( _
               "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sNorthwind & ";")
           objConn.Open()
   
           'Run a command to retrieve all records from the Employees table.
           Dim objCmd As New System.Data.OleDb.OleDbCommand( _
               "Select * From Employees", objConn)
           Dim objReader As System.Data.OleDb.OleDbDataReader
           objReader = objCmd.ExecuteReader()
   
           'Read the records in the dataset and write select fields to the 
           'output file.
           FileOpen(1, sSampleFolder & "Book6.txt", OpenMode.Output)
           Dim i As Integer, s As String
           While objReader.Read()
               'Loop through first 6 fields and concatenate
               'each field, separated by a tab, into s variable.
               s = ""
               For i = 0 To 5
                   If Not objReader.IsDBNull(i) Then
                       If i = 0 Then 'field 1 is EmployeeId
                           s = s & objReader.GetInt32(i).ToString
                       ElseIf i = 5 Then 'field 6 is BirthDate
                           s = s & objReader.GetDateTime(i)
                       Else 'field is a text field
                           s = s & objReader.GetString(i)
                       End If
                   End If
                   s = s & Microsoft.VisualBasic.ControlChars.Tab
               Next
               PrintLine(1, s)
           End While
           FileClose(1)
   
           'Close the reader and the connection.
           objReader.Close()
           objConn.Close()
   
           'Create a new instance of Excel.
           Dim oExcel As Object
           oExcel = CreateObject("Excel.Application")
   
           'Open the text file and save it in the Excel workbook format.
           oExcel.Workbooks.OpenText(sSampleFolder & "Book6.txt", _
               , , , -4142, , True) 'xlTextQualifierNone=-4142
   
           oExcel.ActiveWorkbook.SaveAs(sSampleFolder & "Book6.xls", _
               -4143) 'xlWorkbookNormal = -4143
   
           'Quit Excel.
           oExcel.Quit()
           oExcel = Nothing
           GC.Collect()
       End Function
   
       Private Function Use_ADONET()
   
           'Verify that the workbook to write to does exist.
           Dim sFile As String = sSampleFolder & "Book7.xls"
           If Dir(sFile) = "" Then
               MsgBox("Please create the workbook Book7.xls and try again.")
               Exit Function
           End If
   
           'Establish a connection to the data source.
           Dim sConnectionString As String
           sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & sSampleFolder & _
               "Book7.xls;Extended Properties=Excel 8.0;"
           Dim objConn As New System.Data.OleDb.OleDbConnection(sConnectionString)
           objConn.Open()
   
           'Add two records to the table named 'MyTable'.
           Dim objCmd As New System.Data.OleDb.OleDbCommand()
           objCmd.Connection = objConn
           objCmd.CommandText = "Insert into [Sheet1$] (FirstName, LastName)" & _
               " values ('Bill', 'Brown')"
           objCmd.ExecuteNonQuery()
           objCmd.CommandText = "Insert into [Sheet1$] (FirstName, LastName)" & _
               " values ('Joe', 'Thomas')"
           objCmd.ExecuteNonQuery()
   
           'Close the connection.
           objConn.Close()
       End Function
   					

   Catatan Jika Anda tidak menginstal Office folder default (c: Program files Microsoft Office), mengubah sNorthwind konstan dalam sampel kode untuk mencocokkan jalan instalasi Anda untuk Northwind.mdb.
   
   
7. Menambahkan kode berikut ke atas Form1.vb.

   Imports Microsoft.Office.Interop
   					

8. Tekan F5 untuk membangun dan kemudian menjalankan sampel.

Kembali ke atas | Berikan Masukan