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

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

?? ????? ??

??????

?? ??? ?? ??? ???? ????? ???? ?? ?? ???? Visual Basic .NET ????????? ?? ???? Excel 2002 ?? ??????????? ???? ?? ??? ?? ???????? ?? ???? ?? presents ????? ?? ???????? ???? ?? disadvantages ?? ?????? ???? ????????? ?? ??? ??????? ????? ???? ?? ?? ??? ?? ???? ????

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

????? ?? ????? ??? Excel ????????????? ?? ??? ???? ??????????? ???? ?? ??? ????? ????? ?????????. ???????, ?? ??? ?? ??????? ?? Excel ????? ???? ?? ??? ??????? ????? ??? ?? ????? ??????? ???? ??? greatest ????????????? ????????????? ?? ????????????? ?? ???????? ?? ??????? ???????? ?? ????? ??? ????? ?? ?????? ??? ???? ???? ?? ????? ????????? ???? ?? ??? ???

???????, ?? ??? ????? ?? ???? ??? ?? ??????? ???? ???? ??????????? ???? ?? ???:
  • ???? ?? ???? ?? ???? ??????????? ?????
  • ???? ???? ??? ???? ?????? ?? ?????? ??? ??????????? ?????
  • ?? ?????? ?? ?????? ?? ????? ?? ???? ??? ??? ADO recordset ??????????CopyFromRecordset?????? ???
  • ????? ???QueryTable??? ODBC ?? OLEDB ???? ????? ?? ?? ?????? ?? ?????? ??? Excel ?????????? ???????? ???
  • ?????????? ?? ???? ??????????? ????, ?? ???? ??? ?????????? ??????? ?? ???? Excel ????? ????? ??? ????????
?? ?? ??????? ??? ?????? ???? necessarily Excel ??? ???? ??????????? ???? ?? ??? ??????? ?? ????? ?? ????? ??? ?? ???? ?????-???? ????????? ?? ??? ??? ???, ?? ?? ????? approach ??????? ???? ?? ??? ???? ???? ???? ?? ???? ?????? ??? ???? ?? ??? ?? ???? ???

????? approaches ??????? ??? ???? ???? ???? ??????????? ???? ?? ??? ????? ???? ?? ???? ??:
  • ???? ???? ??????????? ???? ?? ??? ??? ?? ?????????-???????? ??? ???? 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()
				
???? ?? ???? ?? ???? ??????????? ?? ??? ?? ???? ??? ??? ????????? approach ??????????? ???? ?? ??? ???? ???? ???? ??? ?? ????????????? ??? ???? ?? ???? ???? ??? ??? ?? ?? ?? ???????? ???? ?????? ???? ?? ????? ???? ???????, ?? approach ???????? ???? ?? ??? ???? ??? ??? Excel ??? ??????????? ???? ?? ??? ???? ?? ?? ???? ?????????????? ?????????????????????? ????? ??? ??????? ???? ??? ??? ???????? ?? ?????? ?????? ??? ?????, ?? ????? ?? ???? ??????????? ?? ??? ?? ???? ???? ???
??? ??, Microsoft Windows 95, Microsoft Windows 98, ?? Microsoft Windows Millennium Edition (Me) ??? 64 KB ?? ???? ?? ???????? ?? ??????? ??? ???? ??? 64 KB ?? ???? ???????? ?? ?????? ???? (Excel) ??????? ????? ??????????? ???? ??? ?? ???? ??, ?? ????? ?????? ????? ?????? ????? ??????? ?? ???? ??????? ??????? ?? ???, Microsoft ?????? ??? ??? ???? ????? ?? ??? ????? ???? ?????? ????? ????::
216400?????-????????? COM ??????? ??????? ????????? Win95/98 ?? ???? ?? ???? ???
???, ???? ?? ???? ?? ???? ??????????? ?? ??? ?? ???? ???? ?? ?????? ?? ???? ?? ??? ?????????? ??? ?? Excel ?? ??? ??? ???? ??? ??????????? ???? ????, ?? ?? ???? approaches ?? ???? ?????? ??? ???? ??????????? ???? ?? ??? ?? ???? ??? discussed ????? ?? ????? ?????

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

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

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

??? ADO recordset ???? ?????????? ?????? ??? ??????????? ???? ?? ??? ??????? ?? ????? ????

Excel 2000 ?? 2002 ?? Excel ?? ??? ???????? ???? ?????? ?????CopyFromRecordset??? ADO recordset ???? ?????? ?? ??? ???? ?????????? ?? ??????????? ???? ?? ??? ?????? ??? ????? ??? illustrates ?? ????? ???? ????????? ????? ??????? ??? ???? ?????? ?? ??????? ?? ??????????? ???? ?? ??? Excel ?? ???????? ???? ?? ??? ???? ????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 ?? ??? ?????Recordset???????? ??? ADataSetADO.NET ?? ????? ?? ????? ?? ????? ???? ???? ?? ?? ???CopyFromRecordset?????? ??? ???????? ?? ???? ???? ?? ??? ?? ?????? demonstrate ???? ADO.NET ?? Excel ??? ???? ??????????? ???? ?? ??? ???

???? ?????????? ?? ??? QueryTable ???????? ?? ????? ?? ??? ??????? ?? ????? ????

AQueryTable???????? ???? ?????? ?? ???? ?? ???? ????? ???? ????? ?? ?? ????? ?? ?? ?? ???????????? ???? ??? ?? ?? Excel ????????, ??? ???? ??? ???QueryTable?? ?????? ???? OLEDB ?? ??? ODBC ???? ????? ?? ???? SQL ???????? ?? ??? ??? ??????? ???????? ?????? ????? Excel recordset ????? ?? ?? ???? ????????? ????? ?? ?????????? ??? recordset ???????? ???? ??? ?? ????? ????QueryTable???????? ????? ????? ?? ??????????CopyFromRecordset????:
  • Excel recordset ?? ???? ????????? ?????????? ??? ?? ??????? ??????? ???
  • ?????? ?? ??? ????? ?? ????QueryTable????????, ????? ?? it ???? ???? ???? ??? ??? ?? ?????? recordset ??????? ???? ?? ????
  • ?? ??? ???QueryTable????? ??? ?? ???? ????????????? ?? ??? ????????? ?? ???? ?? ?? ???? ?? ??? ???? ?? ??? ???? ?? ?? ?????? ?????????? ?? ?????? ??? ???? shifted ???? ?? ???? ?? (?????RefreshStyle??? ????? ?? ???)?
????????? ???? ?? ????? ??? ?? ???? ?? ??? ????? ?? ??? Excel 2000 ?? 2002 ?? ???????? ???? ?? ???QueryTable??? ????????? ????? ??????? ?? ???? ?? ????? ?? ???? Excel ????? ??????
        '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
				

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

?? ???? ?????????? ??? ???? ??????????? ???? ?? ??? ?????????? ?? ????? ?? ???? ??? ??????? ?? ??? ???? ???? ?????????? ?? ?? ?? ???? ?????? ???, ?? ?? ???????? ?? ?????? ????? ???????? ??? ???? ?? ??? ???, ?? ????????? carriage ???? ?????? ???????? ??? ????????? ??? ????? ????? ??? ?? ????? ???? Visual Basic .NET ???? ?????????? Excel ??? ???? ??????????? ???? ?? ??? illustrates ???
        '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 ???-???????? ?????? ?? ?????????-???????? ?????? ?? ????? ?? ?????? ??? ???? ??? ?????? ?? ????? ?? ???? ???? ?? ?? ?????? ?? ????? ?? ???? ??? ?? ?? ???? ????????????? ??? ???? ?? ?? ???? ??, ??? ??? ??, ??????? ?? ????? ???? ??? ??????????? ???? ????? ???? ?? ?? ????? approach ???????-????? ??? ????????? ?? ??? ??? ???? ???? ?????-???? ?? ???? ??????? ?? ???? ??? ?? ?? ???? ?? ???? ??????? ?? ?????, ??????? ?? ??? ????? ?? ??? ?????

The following code illustrates how to generate a tab-delimited text file from data that is read with 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()
				
No Automation was used in the previous code. However, you can use minimal Automation to open the text file and save the file in the Excel workbook format, as follows.
        '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()
				

Transfer data to a worksheet by using ADO.NET

You can use the Microsoft Jet OLE DB provider to add records to a table in an existing Excel workbook. A "table" in Excel is merely a range of cells; the range may have a defined name. Typically, the first row of the range contains the headers (or field names), and all later rows in the range contain the records.

The following code adds two new records to a table in Book7.xls. The table in this case is Sheet1.
       '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()
				
When you add records with ADO.NET as shown, the formatting in the workbook is maintained. Each record that is added to a row borrows the format from the row before it. For example, new fields that are added to column B are formatted with right alignment because cell B1 is right-aligned.

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

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

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

Excel 2002 ??? ?????? ??? ?? ?? ???? XML ???? ?? ??? ????? XML ???? ?? ???? ?? ???? ?? ??????????? command????????, ?? ????????? ?? ?? ?? ????? ?????????,OpenXML?? ??? ?????????????????????????? ??? ??? ?? Excel ??? ????? ?? ??? XML ????? ?? ????? ???, ?? ?? ???? ?? ???????? ???? ?? ??? ???? ????? ?? ??? ???? ????

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

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

  1. Excel ????????????? ?? ????? ???? ???? ?? ??? ?????, ?? ?? C:\Exceldata\ ??????? ??? ?? ???? ?? ??? ?? ??? ??????? ??????
  2. ????? ????? ?? ??? ?? ?? ????????????? ?? ????? ?? ???, ????? ????? ?? ???? ????:
    1. ??? ?? ????????????? ??? Excel ??????? ?????
    2. ?? ????????????? Sheet1, ?? ?????FirstName??? A1 ??? ??LastName??? ??? 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 Assemblies ??????? ???? (PIAs)?Office XP PIA ?? ???? ??? ???? ??????? ?? ???, ????? ???? ?????? ?? ????? ?? ???? ?? Microsoft ???????? ??? ?????::
      328912Microsoft Office XP ???????? ?????? assemblies (PIAs) ??????? ???? ?? ??? ?????? ???
    3. ????? ????COM??? ??, ??????Microsoft ActiveX ???? 2.7 ???????? ??????????? ????-????? ????, ?? ???? ?????? ????.
    4. ????? ????,OK??????????? ??????????? ????? ???? ??? ?? ??????? ???? ?? ??? ??? ??? ???? ?? ????????? ?? ?? ???? ?????? ????? ???? ?? ??? 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 ?????? ??????? (MSDN) ??? ???? ?? ????::
http://msdn2.microsoft.com/en-us/library/aa188489(office.10).aspx
???? ??????? ?? ???, Microsoft ?????? ??? ??? ???? ????? ?? ??? ????? ???? ?????? ????? ????::
247412Visual Basic ?? Excel ??? ???? ??????????? ???? ?? ??? ???????

???

???? ID: 306022 - ????? ???????: 04 ?????? 2010 - ??????: 2.0
???? ???? ???? ??:
  • Microsoft Excel 2002 Standard Edition
  • Microsoft Visual Basic .NET 2002 Standard Edition
??????: 
kbautomation kbhowtomaster kbmt KB306022 KbMthi
???? ?????? ????????
??????????: ?? ???? ?? ???? ??????? ?? ????? ?? Microsoft ????-?????? ?????????? ?????? ?????? ???? ??? ??. Microsoft ???? ??? ????-???????? ?? ????-???????? ????? ?????? ?? ???? ???????? ???? ?? ???? ????? ????? ??? ?? ??? ?????? ?? ???? ???? ???? ??? ????? ??. ???????, ????-???????? ???? ????? ???? ???? ???? ???. ?????, ????????, ?????-???? ?? ??????? ?? ???????? ?? ???? ???, ???? ?? ??? ?????? ???? ???? ??? ????? ??? ?? ???? ??. Microsoft ??????? ??? ???? ?? ?????? ?? ??????????, ????????? ?? ??? ?????? ?? ???? ????? ?? ???? ???????? ?? ??? ???? ????? ?? ??? ????????? ???? ??. Microsoft ????-?????? ?????????? ?? ????? ?????? ?? ?? ??? ??.
?????????? ?? ??????? ????????? ??????? ??: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