实例1:处理NewWorkbook和WorkSheet事件的控制台程序 书本第70页
程序清单 4.1 处理NewWorkbook和WorkSheet事件的控制台程序
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 exitXL As Boolean = False
Sub Main()
app = New Excel.Application()
app.Visible = True
' We cast to AppEvents_Event when adding an event handler
' dynamically using AddHandler because NewWorkbook
' is the name of both a property and an event.
AddHandler CType(app, Excel.AppEvents_Event).NewWorkbook, _
AddressOf App_NewWorkbook
workbook = app.Workbooks.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
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
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
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
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
实例代码:
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
实例效果: