VSTO开发指南（VB2013版） 第四章 Excel编程

 1 Imports Excel = Microsoft.Office.Interop.Excel
 2 Imports System.Windows.Forms
 3 
 4 Module Module1
 5 
 6   Private WithEvents app As Excel.Application
 7   Private WithEvents workbook As Excel.Workbook
 8   Private exitXL As Boolean = False
 9 
10   Sub Main()
11     app = New Excel.Application()
12     app.Visible = True
13 
14     ‘ We cast to AppEvents_Event when adding an event handler
15     ‘ dynamically using AddHandler because NewWorkbook
16     ‘ is the name of both a property and an event.
17     AddHandler CType(app, Excel.AppEvents_Event).NewWorkbook, _
18       AddressOf App_NewWorkbook
19 
20     workbook = app.Workbooks.Add()
21 
22     While exitXL = False
23       System.Windows.Forms.Application.DoEvents()
24     End While
25 
26     app.Quit()
27   End Sub
28   Private Sub App_NewWorkbook(ByVal workbook As Excel.Workbook)
29     Console.WriteLine(String.Format( _
30       "Application.NewWorkbook({0})", workbook.Name))
31   End Sub
32 
33   Private Sub App_WorkbookNewSheet(ByVal workbook As _
34     Excel.Workbook, ByVal sheet As Object) _
35     Handles app.WorkbookNewSheet
36 
37     If TypeOf sheet Is Excel.Worksheet Then
38       Dim worksheet As Excel.Worksheet
39       worksheet = CType(sheet, Excel.Worksheet)
40       Console.WriteLine(String.Format( _
41           "Application.WorkbookNewSheet({0},{1})", _
42           workbook.Name, worksheet.Name))
43     End If
44 
45     If TypeOf sheet Is Excel.Chart Then
46       Dim chart As Excel.Chart = CType(sheet, Excel.Chart)
47       Console.WriteLine(String.Format( _
48         "Application.WorkbookNewSheet({0},{1})", _
49         workbook.Name, chart.Name))
50     End If
51 
52   End Sub
53 
54   Private Sub Workbook_NewSheet(ByVal sheet As Object) _
55     Handles workbook.NewSheet
56 
57     If TypeOf sheet Is Excel.Worksheet Then
58       Dim worksheet As Excel.Worksheet
59       worksheet = CType(sheet, Excel.Worksheet)
60       Console.WriteLine(String.Format( _
61         "Workbook.NewSheet({0})", worksheet.Name))
62     End If
63 
64     If TypeOf sheet Is Excel.Chart Then
65       Dim chart As Excel.Chart = CType(sheet, Excel.Chart)
66       Console.WriteLine(String.Format( _
67         "Workbook.NewSheet({0})", chart.Name))
68     End If
69 
70   End Sub
71 
72   Private Sub Workbook_BeforeClose(ByRef cancel As Boolean) _
73     Handles workbook.BeforeClose
74 
75     exitXL = True
76 
77   End Sub
78 
79 End Module

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Windows.Forms

Module Module1

    Private WithEvents app As Excel.Application
    Private WithEvents workbook As Excel.Workbook
    Private WithEvents worksheet As Excel.Worksheet
    Private exitXL As Boolean = False

    Sub Main()
        app = New Excel.Application()
        app.Visible = True
        ‘由于NewWorkbook既是事件名又是属性名，所以我们用AddHandler语句动态地添加事件句柄，“Addhandler [Event], addressof [Sub or Function]”
        AddHandler CType(app, Excel.AppEvents_Event).NewWorkbook, AddressOf App_NewWorkbook ‘运行App_NewWorkbook方法
        workbook = app.Workbooks.Add()
        worksheet = app.Worksheets.Add()
        While exitXL = False
            System.Windows.Forms.Application.DoEvents()
        End While
        app.Quit()
    End Sub
    Private Sub App_NewWorkbook(ByVal workbook As Excel.Workbook) ‘方法名
        Console.WriteLine(String.Format("Application.NewWorkbook({0})", workbook.Name))
    End Sub

    Private Sub App_WorkbookNewSheet(ByVal workbook As Excel.Workbook, ByVal sheet As Object) Handles app.WorkbookNewSheet ‘对象_事件

        If TypeOf sheet Is Excel.Worksheet Then ‘typeof 放在一个运算数之前，运算数可以是任意类型,这里传递的是工作表
            Dim worksheet As Excel.Worksheet
            worksheet = CType(sheet, Excel.Worksheet)
            Console.WriteLine(String.Format("Application.WorkbookNewSheet({0},{1})", workbook.Name, worksheet.Name))
        End If

        If TypeOf sheet Is Excel.Chart Then ‘typeof 放在一个运算数之前，运算数可以是任意类型,这里传递的是透视表
            Dim chart As Excel.Chart = CType(sheet, Excel.Chart)
            Console.WriteLine(String.Format("Application.WorkbookNewSheet({0},{1})", workbook.Name, chart.Name))
        End If

    End Sub

    Private Sub Workbook_NewSheet(ByVal sheet As Object) Handles workbook.NewSheet ‘对象_事件

        If TypeOf sheet Is Excel.Worksheet Then ‘typeof 放在一个运算数之前，运算数可以是任意类型,这里传递的是工作表
            Dim worksheet As Excel.Worksheet
            worksheet = CType(sheet, Excel.Worksheet)
            Console.WriteLine(String.Format("Workbook.NewSheet({0})", worksheet.Name))
        End If

        If TypeOf sheet Is Excel.Chart Then ‘‘typeof 放在一个运算数之前，运算数可以是任意类型,这里传递的是透视表
            Dim chart As Excel.Chart = CType(sheet, Excel.Chart)
            Console.WriteLine(String.Format("Workbook.NewSheet({0})", chart.Name))
        End If

    End Sub

    Private Sub Workbook_BeforeClose(ByRef cancel As Boolean) Handles workbook.BeforeClose ‘对象_事件

        exitXL = True

    End Sub

End Module