Làm th? nào ð? chuy?n d? li?u vào m?t b?ng tính Excel b?ng cách s? d?ng Visual Basic.NET

T?ng quan

• Chuy?n d? li?u di ð?ng c?a t? bào.
• Chuy?n d? li?u trong m?t m?ng ð?n m?t lo?t các t? bào.
• Chuy?n d? li?u trong m?t recordset ADO ð?n m?t lo?t các t? bào b?i b?ng cách s? d?ng các CopyFromRecordset phýõng pháp.
• T?o ra m?t QueryTable ð?i tý?ng vào m?t b?ng tính Excel có ch?a các k?t qu? c?a m?t truy v?n trên m?t ODBC ho?c OLEDB ngu?n d? li?u.
• Chuy?n d? li?u vào b?ng t?m, và sau ðó dán các n?i dung b?ng t?m vào m?t b?ng tính Excel.

• Chuy?n d? li?u c?a b?n vào m?t tab ho?c d?u ph?y phân cách t?p tin vãn b?n r?ng Excel có th? sau ðó phân tích vào các t? bào trên m?t b?ng tính.
• Chuy?n d? li?u vào m?t b?ng tính b?ng cách s? d?ng ADO.NET.
• Chuy?n d? li?u XML ð? Excel (Phiên b?n 2002 ch?) ð? cung c?p d? li?u ðó là ð?nh d?ng và s?p x?p thành các hàng và c?t.

K? thu?t

S? d?ng t? ð?ng hóa ð? truy?n d? li?u di ð?ng c?a t? bào

        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()
				

S? d?ng t? ð?ng hóa ð? chuy?n m?t m?ng c?a d? li?u ð?n m?t ph?m vi ngày m?t b?ng

        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
				

S? d?ng t? ð?ng hóa ð? chuy?n m?t recordset ADO ð?n m?t lo?t các b?ng tính

        '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()
				

S? d?ng t? ð?ng hóa ð? t?o ra m?t ð?i tý?ng QueryTable trên m?t b?ng

• Excel x? l? vi?c t?o ra recordset và c?a nó v? trí vào b?ng tính.
• Truy v?n có th? ðý?c lýu v?i các QueryTable ð?i tý?ng nhý v?y là nó có th? ðý?c làm m?i l?n sau ðó ð? có ðý?c m?t C?p Nh?t RecordSet.
• Khi tin nhaén QueryTable s? ðý?c thêm vào b?ng tính c?a b?n, b?n có th? ch? ra r?ng d? li?u ð? hi?n có trong các t? bào trên b?ng tính ðý?c d?ch chuy?n ð? phù h?p v?i các d? li?u m?i (xem các RefreshStyle b?t ð?ng s?n ð? bi?t chi ti?t).

        '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
				

S? d?ng b?ng t?m

        '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()
				

T?o m?t t?p tin vãn b?n delimited Excel có th? phân tích cú pháp thành các hàng và c?t

        '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()
				

Chuy?n d? li?u vào m?t b?ng tính b?ng cách s? d?ng 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()
				

Chuy?n d? li?u XML (Excel 2002 ch?)

T?o hoàn ch?nh m?u Visual Basic.LÝ?I d? án

1. T?o m?t thý m?c m?i ð? gi? Excel Workbook mà các m?u s? t?o cho b?n, và sau ðó tên thý m?c C:\Exceldata\.
2. Làm theo các bý?c sau ð? t?o ra m?t b?ng tính m?i cho m?u ð? vi?t thý cho:
   1. B?t ð?u m?t b?ng tính m?i trong Excel.
   2. Ngày Sheet1 c?a b?ng tính m?i, h?y g? FirstName trong cell A1 và Th?trong t? bào A2.
   3. Lýu b?ng tính nhý C:\Exceldata\Book7.xls.
3. B?t ð?u Visual Studio.NET. Trên các T?p tin tr?nh ðõn, nh?p vào M?i sau ðó b?m D? án. Dý?i Visual Basic d? án, ch?n ?ng d?ng Windows. Theo m?c ð?nh, Form1 ðý?c t?o ra.
4. Thêm m?t tham chi?u ð?n các thý vi?n ð?i tý?ng Excel. Ð? làm ði?u này, th?c hi?n theo các bý?c sau:
   1. Trên các D? án tr?nh ðõn, nh?p vào Thêm tài li?u tham kh?o.
   2. Trên các COM tab, xác ð?nh v? trí Microsoft Excel 10,0 ð?i tý?ng thý vi?n, sau ðó b?m Ch?n.
      
      Lýu ? N?u b?n không làm nhý v?y, Microsoft khuy?n cáo r?ng b?n t?i v? và sau ðó cài ð?t Microsoft Office XP chính Interop h?i ð?ng (PIAs). Ð? bi?t thêm chi ti?t v? Office XP PIAs, nh?p vào s? bài vi?t sau ðây ð? xem bài vi?t trong cõ s? ki?n th?c Microsoft:
      328912

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

      Microsoft Office XP chính interop h?i ð?ng (PIAs) có s?n ð? t?i v?
   3. Trên các COM tab, xác ð?nh v? trí Microsoft ActiveX Data Objects 2,7 Library, sau ðó b?m Ch?n.
   4. Nh?p vào Ok trong các Thêm tài li?u tham kh?o h?p tho?i ð? ch?p nh?n c?a b?n l?a ch?n. N?u b?n nh?n ðý?c m?t nh?c ð?n t?o ra hàm bao cho các thý vi?n mà b?n ð? ch?n, b?m vào Có.
5. Thêm m?t H?p combo ki?m soát và m?t Nút ki?m soát ð? Form1.
6. Thêm m? sau ðây ð? 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
   					

   Lýu ? N?u b?n không cài ð?t vãn ph?ng thý m?c m?c ð?nh (c: Program Files Microsoft Office), thay ð?i các sNorthwind h?ng s? trong m?u m? ð? phù h?p v?i ðý?ng d?n cài ð?t c?a b?n cho Northwind.mdb.
   
   
7. Thêm m? sau vào phía trên c?a Form1.vb.

   Imports Microsoft.Office.Interop
   					

8. Nh?n F5 ð? xây d?ng và sau ðó ch?y các m?u.

Quay l?i ð?u trang | Cung câìp PhaÒn hôÌi