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

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

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

• ???? ?? ???? ?? ???? ??????????? ?????
• ???? ???? ??? ???? ?????? ?? ?????? ??? ??????????? ?????
• ?? ?????? ?? ?????? ?? ????? ?? ???? ??? ??? ADO recordset ??????????CopyFromRecordset?????? ???
• ????? ???QueryTable??? 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()
				

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

        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
				

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

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

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

• Excel recordset ?? ???? ????????? ?????????? ??? ?? ??????? ??????? ???
• ?????? ?? ??? ????? ?? ????QueryTable????????, ????? ?? it ???? ???? ???? ??? ??? ?? ?????? recordset ??????? ???? ?? ????
• ?? ??? ???QueryTable????? ??? ?? ???? ????????????? ?? ??? ????????? ?? ???? ?? ?? ???? ?? ??? ???? ?? ??? ???? ?? ?? ?????? ?????????? ?? ?????? ??? ???? shifted ???? ?? ???? ?? (?????RefreshStyle??? ????? ?? ???)?

        '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
				

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

        '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 ??? ????????? ?? ??????? ?? ????? ?? ???? ??? ?? ???????? ??? ???? ?????

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

Transfer data to a worksheet by using 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()
				

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

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

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 ???????? ??? ?????::
      328912  (http://support.microsoft.com/kb/328912/ ) Microsoft 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 ??????

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