Nasýl yapýlýr: Visual Basic. NET'i kullanarak, verileri bir Excel çalýþma kitabýna aktarma

GENEL BAKIÞ

• <a1>Veri</a1> tarafýndan hücrenin hücre aktarýn.
• Dizideki veri, bir hücre aralýðýna aktarýn.
• ADO kayýt kümesindeki verileri CopyFromRecordset yöntemini kullanarak, bir hücre aralýðýna aktarýn.
• OLEDB ya da ODBC veri kaynaðý üzerinde sorgu sonuçlarýný içeren bir Excel çalýþma sayfasýnda bir sorgu tablosu oluþturun.
• Panoya veri aktarýmý ve Pano içeriðini bir Excel çalýþma sayfasýna yapýþtýrýn.

• Verilerinizi Excel daha sonra ayrýþtýrabilir sekme veya virgülle sýnýrlandýrýlmýþ metin dosyasý için çalýþma sayfasýndaki hücrelere aktarabilirsiniz.
• Verilerinizi ADO.NET kullanarak bir çalýþma sayfasýna aktarýn.
• XML veri (yalnýzca Sürüm 2002) Excel'e aktarma biçimlendirilmiþ ve satýrlar ve sütunlar düzenlendiði bir veri saðlamak için.

Teknikler

Tarafýndan hücrenin hücre veri aktarmak için Otomasyon kullanma

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

Veri dizisi için bir aralýk çalýþma sayfasýna aktarmak için Otomasyon kullanma

        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
				

Bir ADO kayýt kümesi için bir çalýþma sayfasý aralýðý aktarmak için Otomasyon kullanma

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

Otomasyon çalýþma sayfasýnda bir sorgu tablosu nesnesi oluþturmak için kullanýn

• Excel, recordset ve çalýþma kitabýna, bir yerleþim oluþturulmasýný yönetir.
• Böylece, daha sonra güncelleþtirilen bir recordset'i edinmek için yenilenmesi bu sorguyu sorgu tablosu nesnesi ile kaydedilir.
• Çalýþma kitabýnýza yeni bir sorgu tablosu eklendiðinde, önceden varolan çalýþma sayfasýndaki hücrelere veri (Ayrýntýlar için RefreshStyle özelliði bakýn) yeni verileri sýðdýrmak için kaydýrýlacaðý uzaklýk belirtebilirsiniz.

        '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
				

Pano kullanýn.

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

Excel, satýrlar ve sütunlar ayrýþtýrabilir sýnýrlandýrýlmýþ metin dosyasý oluþturun.

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

ADO.NET kullanarak, bir çalýþma sayfasýna veri aktarýmý

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

XML veri (yalnýzca Excel 2002) aktarýmý

Tam bir örnek Visual Basic .NET projesini oluþturma

1. Örnek oluþturmasýný ve sonra C:\Exceldata\ klasör adý, Excel çalýþma kitaplarýný saklamak için yeni bir klasör oluþturun.
2. Yazmak örnek için yeni bir çalýþma kitabý oluþturmak için þu adýmlarý izleyin:
   1. Yeni bir çalýþma kitabýný, Excel'deki baþlatýn.
   2. Yeni bir çalýþma kitabýnýn Sayfa1 üzerinde <a1>hücre</a1> A1 ve A2 hücresindeki Soyadýadý yazýn.
   3. Çalýþma kitabýný C:\Exceldata\Book7.xls kaydedin.
3. Visual Studio .NET'i baþlatýn. Dosya menüsünde Yeni'yi týklatýn ve Project ' ý týklatýn. Visual Basic Projects altýnda Windows Application ' ý seçin. Varsayýlan olarak, Form1 oluþturulur.
4. Excel nesne kitaplýðýna bir baþvuru ekleyin. Bunu yapmak için þu adýmlarý izleyin:
   1. Proje) menüsünde Add Reference ' ý týklatýn.
   2. COM sekmesinde, Microsoft Excel 10.0 Nesne Kitaplýðý ' ný bulun ve sonra da <a2>Seç</a2>'i týklatýn.
      
      Not Önceden yapmadýysanýz, Microsoft karþýdan yükleyin ve sonra Microsoft Office XP Primary ýnterop Assemblies yükleyin önerir (Pýa). Office XP Pýa hakkýnda'daha fazla bilgi için Microsoft Knowledge Base'deki makaleyi görüntülemek üzere aþaðýdaki makale numarasýný týklatýn:
      328912

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

      Microsoft Office XP ana birlikte çalýþma derlemeleri (PIA) karþýdan yüklenebilir
   3. COM sekmesinde, ActiveX Data Objects 2.7 kitaplýðý ' ný bulun ve sonra da <a2>Seç</a2>'i týklatýn.
   4. Seçimlerinizi kabul etmek için Tamam <a0>Baþvuru Ekle</a0> iletiþim kutusunda týklatýn. Seçtiðiniz kitaplýklar için sar?c?lar? oluþturmak isteyen bir ileti alýrsanýz, Evet ' i týklatýn.
5. Form1'e, bir Açýlan kutu denetimi ve bir Düðme denetimi ekleyin.
6. Form1'e aþaðýdaki kodu ekleyin.

       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
   					

   <a1>Not</a1> varsayýlan klasöre (C:\Program Files\Microsoft Office), Office'i yüklemek, sNorthwind sabit kod örneðinde, yükleme yolu için Northwind.mdb eþleþecek þekilde deðiþtirin.
   
   
7. Aþaðýdaki kodu Form1.vb.

   Imports Microsoft.Office.Interop
   					

   en üstüne ekleyin...
8. Oluþturmak ve örnek'ý çalýþtýrmak için F5 tuþuna basýn.

Üste | Geri Bildirim Ver