????? ??? ???????? ??? ???? Excel ???????? Visual Basic .NET

?????? ????????? ?????? ?????????
???? ???????: 306022 - ??? ???????? ???? ????? ????? ??? ???????.
?????? ??? ????? Microsoft Visual C# .NET ?? ??? ???????? ???? 306023.
????? ???? | ?? ????

?? ??? ??????

??????

???? ??? ??????? ???? ????? ?????? ?? ????? ???? ???????? ??? Excel 2002 ?? ?????? Visual Basic .NET. ???? ??? ??????? ????? ????? ????? ?? ????? ???? ????? ????? ???? ???? ?????? ???? ???? ?? ?????? ????.

???? ???? ???

?? ??????? ????????? ????? ???? ???????? ??? ???? Excel ???????. ?? ??????? ????????? ????? ??????? ?????? ?????? ???? ?????? Excel. ???? ?? ??????? ???????? ???? ??? ?? ??????? ?????? ???? ???????? ?? ?????? ? ??????? ??? ????? ?????? ????? ??????? ???????? ?? ??? ???????.

??????? ????????? ????? ??????? ?????? ?? ???????? ???? ???????? ?????? ??:
  • ??? ???????? ???? ??? ????.
  • ??? ???????? ?? ???? ??? ???? ?? ???????.
  • ??? ???????? ?? ?????? ????? ADO ??? ???? ?? ??????? ???????? ????? CopyFromRecordset.
  • ????? ???? ???? ????????? ?? ???? ??? Excel ????? ??? ????? ??????? ??? ???? ?????? ODBC ?? OLEDB.
  • ??? ???????? ??? ??????? ?? ?? ???? ??????? ??????? ??? ???? ??? Excel.
????? ????? ??????? ?????? ?? ????? ???? ?? ????? ???????? ??????? ???? ???????? ??? Excel. ??? ??? ???? ?????? ?????? ?? ???? ?????? ?? ???? ??? ??????? ????? ??? ????? ??? ????? ?????? ???????? ?? ????? ????????? ???????.

?? ??? ??????? ????? ??????? ?? ??? ???????? ?????? ?? ???? ???????:
  • ??? ???????? ?????? ?? ??? ??? ??? ??????? ?? ?????? ???? ???? ????? Excel ?????? ??? ????? ?? ???? ???.
  • ??? ???????? ?????? ?? ??? ???? ??? ???????? ADO.NET.
  • ??? ?????? XML ??? (????? Excel 2002 ???) ?????? ???????? ???? ??? ??????? ????? ?????? ????????.

????????

??????? "???????" ??? ???????? ???? ??? ????

?? ??????? ????????? ????? ??? ???????? ??? ???? ???? ??? ????? ?? ?? ???? ??? ???.
        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()
				
??? ???????? ???? ??? ???? ???? ?? ???? ??? ????? ????? ??? ?? ??? ???? ????? ???????? ?????? ?????. ???? ??????? ?? ??? ?????? ?? ?? ???? ?? ?????? ?? ????? ????? ??????? ????? ?? ??? ???????. ??? ???? ????? ??? ??????? ??? ?????? ??? ??? ???? ???? ?? ???????? ???? ??? Excel ??????. ???? ?? ?? ???? ???? ?????? ??? ??? ??????? ??? ?????. ????? ??? ???????? ???? ??????? ???? ?? ???? ?????.
???????? ??? ???? Microsoft Windows Millennium Edition (Me) ? Microsoft Windows 98 ?? ???? ??????? Microsoft Windows 95 ???? ???? ???? ??? 64 ???????? ??? ????? ???????. ??? ??? ???? 64 ???? ???? ?? ???? ?? ????? ????? ???? ??????? (Excel) ?? ????? ?? ????????? ?? ?? ????? ????? ??? ???? ??? ?????? ???????. ????? ?? ?????????? ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ?? Microsoft:
216400???? ????? COM ??? ????? ????? ????? ?????? ??? Win95/98
??? ????? ??? ???????? ???? ??? ???? ??????? ??? ?? ??? ????? ???? ?? ????????. ??? ??? ??? ??? ??????? ?????? ????? ??? Excel "? ????? ??????? ???? ?? ????? ?????? ???? ??? ???????? ?? ??? ??????? ??? ???????? ?? ????? ?????.

?????? ??? ???? ?? ????????? ??????? ??? ???? ??? ?????? Excel ?? Visual Basic .NET ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ?? Microsoft:
301982????? ????? Microsoft Excel ?? Visual Basic .NET

??????? ????? ???? ???? ?????? ??? ???? ?? ???? ???

???? ?????? ???? ????? ??? ???? ????? ?????? ?? ??? ????? ???????.
        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()
				
???? ????? ????? ???? ???? ?? ???? ?? ???????? ??? ??? ???????? ???????? ???? ????? ?? ???? ?????. ?? ???? ???????? ??? ????? ?? ????????? ???????? ??????? ???? ???? ???????? ??? 300 ??????? ?? ???? ?????.
        oSheet.Range("A2").Resize(100, 3).Value = DataArray
				
???? ??? ????? ???? ?????: ??? ?????? ???? ????? ??????? ???? ? ??? ????? ???? ????? ??????? ????? ?????. ???? ?????? ?? ??? ???????? ???? ??? ???? ????? ????? ???????? 300 ??? ?????? ????. ???? ???? ??? ???? ?? ?????? ?? ??? ???????? ?????? ?? ?? ????? ?????? ??? ????? ????? ?????.

??????? ????? ???? ?????? ????? ADO ??? ???? ???? ?????

???? ???? ?????? ?? Excel 2000 ?Excel 2002 ??????? CopyFromRecordset ???? ?????? ????? ADO ??? ???? ???? ?????. ???? ???????? ???????? ??????? ????? ????? Excel ??? ??????? ???? "???????" ?? ????? ????? ?????? Northwind ?????? ??????? ??????? CopyFromRecordset.
        '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()
				
??????CopyFromRecordset ???? ??? ?? ?????? ?????? ????? ADO. ?? ???? ??????? DataSet ???? ???? ???????? ???????? ADO.NET ?? ????? CopyFromRecordset. ??? ?????? ?? ??????? ?? ??????? ??????? ????? ??? ???????? ??? Excel ???????? ADO.NET.

??????? ????? ?????? ???? ???? ??????? ?? ???? ???

???? ???? ???? ????????? ???? ???? ?? ?????? ?? ???????? ???? ??? ??????? ?? ???? ?????? ?????. ????? ??????? ???????? "Excel" ? ????? ????? ???? ????????? ?? ???? ????? ????? ????? OLEDB "??" ???? ?????? ODBC "?" ????? SQL. Excel ?????? ?????? ??????? ? ????? ?????? ??????? ?? ???? ????? ?? ?????? ???? ?????. ??????? ?????? ???? ????????? ???? ??????? ??????? ??? ????? CopyFromRecordset:
  • Excel ??????? ????? ?????? ??????? ????? ????? ?? ??? ???? ?????.
  • ???? ?? ??? ??? ????????? ?? ???? ???? ????????? ???? ??? ???? ?????? ???? ??? ?????? ??? ?????? ????? ?????.
  • ????? ??? ????? ???? ???? ????????? ??? ???? ?????, ????? ????? ??? ???????? ???????? ?????? ?? ??????? ?? ???? ????? ??????? ???????? ??????? (???? ??????? RefreshStyle ?????? ??? ????????).
???? ???????? ???????? ??????? ????? ????? Excel 2000 ?? 2002 ?????? ???? ???? ????????? ?? ???? ??? Excel ???????? ?????? ?? ????? ?????? ????? Northwind.
        '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
				

??????? "???????"

????? ??????? ??????? ???? ???????? ??? ???? ???. ???? ???????? ?? ??? ????? ?? ???? ???? ????? ??? ????? ???? ??? ????? ??????? ?????? ???? ??????? ??? ??? ???? ?????? ?????? ???????. ???? ???????? ???????? ?????? ????? ??????? Visual Basic .NET ??????? ???? ???????? ??? Excel.
        '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 ?? ???? ??????

???? Excel ??? ????? ??????? ????? ?? ????? ????? ?????? ? ???? ???? ????? ???????? ?? ???????. ????? ??????? ??? ?????? ????? ???? ??? ???? ?? ???????? ??? ???? ??? ????? ??????? ????? ????? ?? ?????. ?? ???? ??? ??????? ????? ?????? ???? ???? ??? ???? ?? ???? ????? ????? ???? ?? ?????? ?? ???? ??????. ????? ????? ??? ????? ????? ??? ?????? ???????? ??????? ??? ????????.

???? ???????? ???????? ?????? ????? ????? ??? ??? ???? ??????? ????? ?? ???????? ??????? ???????? ADO.NET.
        '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()
				
?? ??????? "??? ???????" ?? ????????? ???????? ???????. ??? ???? ????? ??????? ????? ?????? ???? ????? ????? ?? ??? ????? ?? Excel ????? ?????? ??? ???.
        '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

????? ??????? ???? Microsoft Jet OLE DB ?????? ????? ??? ???? ?? ???? Excel ?????. "????" ?? Excel ???? ???? ?? ???????; ?????? ?? ???? ??? ????. ????? ?? ????? ???? ????? ?? ?????? ??? ?????? (?? ????? ??????) ?????? ??? ???? ?????? ??????? ?? ?????? ???????.

????? ????????? ???????? ??????? ????? ?? ??????? ??????? ??? ???? ?? Book7.xls. ?? ??? ?????? ?? ?????? ??????1.
       '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()
				
??? ????? ????? ???????? ADO.NET ??? ?? ???? ??? ???????? ??????? ?? ??????. ?????? ?? ??? ??? ?????? ??? ?? ??????? ?? ???? ???. ??? ???? ??????? ??? ????? ?????? ??????? ???? ??? ??????? ??? ?????? B ?? ?????? ?????? ??? ?????? B1 ?????? ??? ??????.

???? ????? ??? ??? ???? ?? ????? ?? ???? ????? ???? ??? ??? ?????? ??? ??????? ???????? ??????. ????? ???? ?????? ?? ???? ????? ??? "??? ??? ??????" ??? ????? ????? ?????. ?? ??? ?? ???????? ??? ????? ????? ???????? ??? ????? ????? ??? ??? ???? ?? ????? ??????? ??????? ???????? ADO.NET.

?????? ??? ???? ?? ????????? ??? ????? ??????? ADO.NET ???? ??? ????? ???????? ??????? ?????? ?? "????? ??????? ?? Microsoft:
301075????? ??????? ?????? ?????? ?? ?? ?????? ??? ???????? ADO.NET ? Visual Basic .NET
301216????? ????? ???? DataSet ?? ????? ?????? ???????? Visual Basic .NET
301248????? ????? ????? ?????? ?? ???? DataSet ???????? Visual Basic .NET
????? ?? ????????? ??? ????? ??????? ???? Jet OLE DB ?? ????? ?????? Excel ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ?? Microsoft:
278973ExcelADO ???? ????? ??????? ADO ?????? ?????? ???????? ?? ?????? Excel
257819????? ??????? ADO ?? ?????? Excel ?? Visual Basic ?? VBA

??? ?????? XML (Excel 2002 ???)

???? ?? Excel 2002 ??? ?? ??? XML ??? ???????. ???? ??? ????? XML ?????? ?? ????? ??? ?? ??????? ??? ?? ??????? ???????? ?????? ??? ??? ?? OpenXML ??????? ??????. ??? ??? ?????? ????? XML ????????? ?? Excel? ????? ????? ????? ????? ??????? ?????? ????????.

????? ?? ????????? ??? ????? ??????? XML ?? Excel 2002 ???? ??? ????? ???????? ??????? ?????? ?? "????? ??????? ?? Microsoft:
307021????? ??? ?????? XML ??? Microsoft Excel 2002 ???????? Visual Basic .NET
288215Microsoft Excel 2002 ? XML

????? ????? Visual Basic .NET ????? ?????

  1. ????? ???? ???? ???????? ?????? Excel ?? ???? ??????? ?????? ?? ?? ?? ?????? ?????? C:\Exceldata\.
  2. ???? ??????? ??????? ?????? ???? ???? ????? ??????? ???:
    1. ??? ????? ???? ???? ?? Excel.
    2. ??? ????1 ?????? ??????? ???? ????? ????? ?? ?????? A1 ? ??? ??????? ?? ?????? A2.
    3. ???? ?????? C:\Exceldata\Book7.xls.
  3. ??? ????? Visual Studio .NET. ?? ??????? ???? ???? ??? ???? ?? ???? ??? ???????. ??? ???????? Visual Basic ? ??? ????? Windows. ?????????? ??? ????? Form1.
  4. ????? ???? ??? ????? ?????? Excel. ?????? ????? ???? ??????? ???????:
    1. ?? ??????? ??????? ???? ??? ????? ????.
    2. ??? ????? ??????? COM ????? ???? ????? ?????? Microsoft Excel 10.0 ?? ???? ??? ?????.

      ?????? ??? ?? ??? ?? ??? ?????? ?????? ??? ? ???? Microsoft ????? ?? ?? ?????? ?? Microsoft Office XP ???????? Interop ????????? (PIAs). ????? ?? ????????? ??? Office XP PIAs ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ?? Microsoft:
      328912Microsoft Office XP ??????? ??????? ??????? ???????? ????????? (PIAs) ???????? ???????
    3. ??? ????? ??????? COM ????? ???? ????? 2.7 ?????? ?????? ActiveX Microsoft ?? ???? ??? ?????.
    4. ???? ??? ????? ?? ???? ?????? ????? ????? ????? ?????????. ??? ????? ???? ?????? wrappers ???????? ???? ?????? ???? ??? "???".
  5. ????? ???? ???? ???? ????? ???? ????? ???? ?? ??? Form1 ".
  6. ????? ????????? ???????? ??????? ??? 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
    					
    ?????? ??? ?? ??? ????? Office ??? ?????? ????????? (C:\Program Files\Microsoft Office) ? ????? ???? sNorthwind ?? ????? ????????? ???????? ??????? ???? ????? ?? Northwind.mdb.

  7. ?? ?????? ????????? ???????? ??????? ??? ???? Form1.vb.
    Imports Microsoft.Office.Interop
    					
  8. ???? F5 ?????? ??????? ??????? ??.

?????

????? ?? ?????????? ?? ?????? ?????? Microsoft ??????? ???? ??? Network (MSDN):
http://msdn2.microsoft.com/en-us/library/aa188489(office.10).aspx
????? ?? ?????????? ???? ??? ??? ??????? ?????? ?????? ?? "????? ??????? ?? Microsoft:
247412???????? ???? ???????? ??? Excel ?? Visual Basic

???????

???? ???????: 306022 - ????? ??? ??????: 27/?? ?????/1427 - ??????: 6.3
????? ???
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Visual Basic .NET 2002 Standard Edition
  • Microsoft ADO.NET (included with the .NET Framework)
????? ??????: 
kbmt kbautomation kbhowtomaster KB306022 KbMtar
????? ????
???: ??? ????? ??? ?????? ???????? ?????? ????? ???? ????? ?????????? ????? ?? ????????? ?????? ????. ???? ???? ?????????? ???? ?? ???????? ???????? ?????? ????????? ????? ????????? ???????? ????? ???????? ?????? ?? ?????? ??? ?? ???????? ???????? ?? ????? ??????? ?????? ??? ??????? ?????? ??. ?????? ?? ???? ??? ??????? ???????? ????? ?? ???? ????? ?????? ??? ????? ??? ????? ??????? ?? ????? ?? ?????? ??? ??? ??????? ??????? ?? ????? ????? ????? ????? ?????. ?? ????? ???? ?????????? ??????? ??? ????? ?? ??????? ?? ????? ?????? ?? ??? ????? ?? ????? ??????? ?? ???????? ?? ??? ???????. ???? ???? ?????????? ???????? ??? ????? ?????? ??????? ??????
???? ??? ????? ??????? ?????? ??????????306022

????? ???????

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com