我坚持在VB的路上走到黑…………
清单1.1 从应用程序对象导航到Excel中的工作表
Dim myWorkbooks As Excel.Workbooks = app.Workbooks
Dim myWorkbook As Excel.Workbook = myWorkbooks.Item()
Dim myWorksheets As Excel.Sheets = myWorkbook.Worksheets
Dim myWorksheet As Excel.Worksheet
myWorksheet = CType(myWorksheets.Item(), Excel.Worksheet)
如果代码不需要在变量中缓存每个对象模型对象,但只需要获取一个Worksheet对象,则编写此代码的更有效的方法如下所示:
Dim myWorksheet As Excel.Worksheet
myWorksheet = CType(app.Workbooks.Item().Worksheets.Item(), Excel.Worksheet)
活代码:第一步:创建visual basic 2017的窗体应用程序(运行环境:win 10+visual studio 2017+office 2010)
第二步:添加引用:COM的 "Microsoft Office 14.0 Object Library 2.5"(指的是Office 2010)和“程序集”的"扩展"中的"Microsoft.office.Interop.Excel 14.0.0.0"
第三步:代码
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim app As Excel.Application = New Excel.Application
Dim myWorkbook As Excel.Workbook
Dim myWorksheet As Excel.Worksheet
app.Visible = True
myWorkbook = app.Workbooks.Add()
myWorksheet = CType(myWorkbook.Sheets.Add(), Excel.Worksheet)
myWorksheet.Cells(, ) = "这是A1"
End Sub
End Class
第四步:运行结果
清单1.2 使用整数或String作为Count属性和Item属性索引遍历工作表集合
Dim myWorkbooks As Excel.Workbooks = app.Workbooks Dim workbookCount As Integer = myWorkbooks.Count
For i As Integer = To workbookCount
' Get the workbook by its integer index
Dim myWorkbook As Excel.Workbook = myWorkbooks.Item(i) ' Get the workbook by its string index
Dim workbookName As String = myWorkbook.Name Dim myWorkbook2 As Excel.Workbook = myWorkbooks.Item(workbookName)
MsgBox(String.Format("Workbook {0}", myWorkbook2.Name))
Next
活代码:
活代码:第一步:创建visual basic 2017的窗体应用程序(运行环境:win 10+visual studio 2017+office 2010)
第二步:添加引用:COM的 "Microsoft Office 14.0 Object Library 2.5"(指的是Office 2010)和“程序集”的"扩展"中的"Microsoft.office.Interop.Excel 14.0.0.0"
第三步:代码
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim app As Excel.Application = New Excel.Application
Dim myWorkbook As Excel.Workbook
Dim myWorksheet As Excel.Worksheet
app.Visible = True
myWorkbook = app.Workbooks.Add()
myWorksheet = CType(myWorkbook.Sheets.Add(), Excel.Worksheet)
myWorksheet.Cells(, ) = "这是A1" Dim worksheetCount As Integer = myWorkbook.Worksheets.Count
For i As Integer = To worksheetCount
Dim str As String
str = myWorkbook.Worksheets.Item(i).Name '以整数作为索引
MessageBox.Show(str, "获取工作表名称")
Next For i As Integer = To worksheetCount
Dim str As String
str = myWorkbook.Worksheets.Item("sheet" & i).Name
MessageBox.Show(str, "获取工作表名称") '以字符串作为索引
Next End Sub
End Class
第四步:运行结果
拓展假如要编辑工作表sheet2中D3单元格,并填入“我是丑丑”,该如何实现呢
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim app As Excel.Application = New Excel.Application
Dim myWorkbook As Excel.Workbook
Dim myWorksheet As Excel.Worksheet
app.Visible = True
myWorkbook = app.Workbooks.Add()
'myWorksheet = CType(myWorkbook.Sheets.Add(, , 3,), Excel.Worksheet) '第3个参数表示添加多少工作表,这里添加3个,
Dim C4_sheet As Excel.Worksheet = myWorkbook.Worksheets.Item("sheet2") '工作表的索引从0开始,
C4_sheet.Cells(, ) = "我是丑丑" 'Cells(行,列),也就是D3单元格
End Sub
End Class
运行结果:
清单1.3 使用For Each结构遍历工作簿集合
Dim myWorkbooks As Excel.Workbooks = app.Workbooks For Each workbook As Excel.Workbook In myWorkbooks
MsgBox(String.Format("Workbook {0}", workbook.Name))
Next
活代码:略
清单1.4 当删除对象时使用辅助集合
Dim myWorkbook As Excel.Workbook = app.ActiveWorkbook
Dim myCollection As New Collections.Generic.List(Of Excel.Name) For Each name As Excel.Name In myWorkbook.Names
myCollection.Add(name)
Next For Each name As Excel.Name In myCollection
name.Delete()
Next
活代码:第一步:创建visual basic 2017的窗体应用程序(运行环境:win 10+visual studio 2017+office 2010)
第二步:添加引用:COM的 "Microsoft Office 14.0 Object Library 2.5"(指的是Office 2010)和“程序集”的"扩展"中的"Microsoft.office.Interop.Excel 14.0.0.0"
第三步:代码
Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
Dim app As Excel.Application = New Excel.Application
Dim myWorkbook As Excel.Workbook = app.ActiveWorkbook
app.Visible = True
myWorkbook = app.Workbooks.Add("E:\工作簿1")
Dim myCollection As New Collections.Generic.List(Of Excel.Range)
For Each Name As Excel.Range In myWorkbook.Worksheets("Sheet1").Range("A1:D5") '由于是删除,所以下面的行会自动向上缩进
myCollection.Add(Name)
Next
For Each name As Excel.Range In myCollection
name.Delete() '由于是删除,所以下面的行会自动向上缩进
Next
End Sub
End Class
运行结果:
原图:
表1.2 从Word的应用程序对象中选择的属性,方法和事件
名称 |
作用 |
---|---|
属性 |
|
ActiveDocument |
返回一个用户正在编辑的word文档对象 |
ActivePrinter |
获取并设置默认打印机 |
Caption |
为word应用程序设置标题,默认被设置为 "Microsoft Word" |
Documents |
返回一个打开的word文档集 |
方法 |
|
Activate |
将word显示在最前,并且作为活动窗口 |
NewWindow |
创建一个新的Word窗口来显示活动窗口,并返回一个新的窗口模型对象 。 |
Quit |
关闭word程序 |
事件 |
|
DocumentBeforeClose |
这是一个在文档关闭之前发生的事件。如果代码句柄将参数 Cancel 设置为true, 文档将不会关闭 |
DocumentOpen |
打开文档时引发的事件。 要打开的文档的Document对象作为参数传递给事件。 |
WindowActivate |
当用户激活Word窗口时,通常通过单击非活动窗口,从而使其处于活动状态,引发事件。 正在激活的文档的Document对象作为参数传递给事件以及激活的窗口的Window对象(因为两个窗口可能显示相同的文档)。 |
清单1.5 返回值类型的属性:Word应用程序对象上的布尔CapsLock属性
If app.CapsLock Then
MsgBox("CapsLock is on")
Else
MsgBox("CapsLock is off")
End If
清单1.6 返回枚举的属性:Word应用程序对象上的WindowState属性
Select Case app.WindowState
Case Word.WdWindowState.wdWindowStateMaximize
MsgBox("Maximized")
Case Word.WdWindowState.wdWindowStateMinimize
MsgBox("Minimized")
Case Word.WdWindowState.wdWindowStateNormal
MsgBox("Normal")
End Select
清单1.7 返回另一个对象模型对象的属性:Word应用程序对象上的ActiveDocument属性
Dim myDocument As Word.Document = app.ActiveDocument
MsgBox(myDocument.Name)
列表1.8 可能抛出异常的属性:Word应用程序对象上的ActiveDocument属性
Dim myDocument As Word.Document
Try
myDocument = app.ActiveDocument
MsgBox(myDocument.Name)
Catch ex As Exception
MsgBox(String.Format("No active document: {0}", ex.Message)
End Try
清单1.9 可以返回的属性:Excel应用程序对象上的ActiveWorkbook属
Dim myWorkbook As Excel.Workbook = app.ActiveWorkbook If myWorkbook Is Nothing Then
MsgBox("No active workbook")
Else
MsgBox(myWorkbook.Name)
End If
清单1.10 枚举参数并返回对象模型对象的参数化属性:Word应用程序对象上的FileDialog属性
Dim dialog As Office.FileDialog
dialog = app.FileDialog(Office.MsoFileDialogType. _
msoFileDialogFilePicker)
dialog.Show()
清单1.11 具有可选参数的参数化属性:Excel应用程序对象上的范围属性
' 删除第二个可选参数
Dim myRange As Excel.Range = app.Range("A1") ' 指定第二个可选参数
Dim myRange2 As Excel.Range = app.Range("A1", "B2")
默认参数化属性
Dim myWorksheet As Excel.Worksheet
myWorksheet = CType(app.Workbooks.Item().Worksheets.Item(), Excel.Worksheet) '重写上面的代码 Dim myWorksheet As Excel.Worksheet
myWorksheet = CType(app.Workbooks().Worksheets(), Excel.Worksheet)
清单1.12 无参数无返回类型的方法:Word应用对象的激活方法
MsgBox("Activating the Word window.") app.Activate()
清单1.13 具有参数和无返回类型的方法:Word应用程序对象上的ChangeFileOpenDirectory方法
app.ChangeFileOpenDirectory("c:\temp")
MsgBox("Will open out of temp for this session.")
清单1.14 无参数和返回类型的方法:Word应用程序对象的DefaultWebOptions方法
Dim options As Word.DefaultWebOptions = app.DefaultWebOptions()
MsgBox(String.Format("Pixels per inch is {0}.", options.PixelsPerInch))
清单1.15 具有参数和返回类型的方法:Word应用程序对象上的CentimetersToPoints方法
Dim centimeters As Single = 15.0
Dim points As Single = app.CentimetersToPoints(centimeters)
MsgBox(String.Format("{0} centimeters is {1} points.", centimeters, points))
'将计量单位从厘米转换为磅。1磅=0.035厘米
'将Excel表的左边距设置为5厘米
Worksheets("Sheet1").PageSetup.LeftMargin = Application.CentimetersToPoints()
清单1.16 具有可选参数和返回类型的方法:Excel应用程序对象上的CheckSpelling方法
Dim phrase1 As String = "Thes is spelled correctly."
Dim phrase2 As String = "This is spelled correctly AFAIK." Dim isCorrect1 As Boolean = app.CheckSpelling(phrase1)
Dim isCorrect2 As Boolean = app.CheckSpelling(phrase2, , True)
'用法
expression.CheckSpelling(Word, CustomDictionary, IgnoreUppercase)
'Word String 类型(仅与 Application对象一起使用),必需。要检查的单词。
'CustomDictionary Variant 类型,可选。用于表示自定义词典文件名的字符串,如果在主词典中找不到单词,则会到此词典中查找。如果忽略此参数,则将使用当前指定词典。
'IgnoreUppercase Variant 类型,可选。如果为 True,则 Microsoft Excel 忽略那些所有字母都是大写的单词。如果为 False 则 Microsoft Excel 检查那些所有字母都是大写的单词。如果省略该参数,则使用当前设置。
表1.3 Excel应用对象引发的事件
事件的名称 |
什么时候它被激活 |
---|---|
NewWorkbook |
当一个工作簿被创建是 |
SheetActivate |
当某个工作表被激活时 |
SheetBeforeDoubleClick |
当某个工作表被双击时 |
SheetBeforeRightClick |
当某个工作表被右击时 |
SheetCalculate |
在某个工作表被重新计算后 |
SheetChange |
当某个工作表单元格被用户改变时 |
SheetDeactivate |
当某个工作表被停用时 |
SheetFollowHyperlink |
当某个工作表中超链接被单击时 |
SheetPivotTableUpdate |
在数据透视表被更新后 |
SheetSelectionChange |
当工作表上的选择更改时 |
WindowActivate |
当工作表窗口被激活时 |
WindowDeactivate |
当工作表窗口被停用时 |
WindowResize |
当工作表窗口调整大小时 |
WorkbookActivate |
当工作簿被激活时 |
WorkbookAddinInstall |
当工作簿作为加载项安装时 |
WorkbookAddinUninstall |
当工作簿作为加载项卸载时 |
WorkbookAfterXmlExport |
在工作簿数据作为XML文件导出后 |
WorkbookAfterXmlImport |
在工作簿中导入XML文件后 |
WorkbookBeforeClose |
工作簿关闭前 |
WorkbookBeforePrint |
在打印工作簿前 |
WorkbookBeforeSave |
在工作簿保存前 |
WorkbookBeforeXmlExport |
工作簿数据作为XML文件导出前 |
WorkbookBeforeXmlImport |
工作簿导入XML文件前 |
WorkbookDeactivate |
当工作簿停用时 |
WorkbookNewSheet |
当在工作簿中创建工作表时 |
WorkbookOpen |
当工作簿打开时 |
WorkbookPivotTableCloseConnection |
当透视报表连接关闭时 |
WorkbookPivotTableOpenConnection |
当透视报表连接打开时 |
WorkbookSync |
当作为文档工作区的一部分的工作簿与服务器上的副本同步时 |
申明事件处理
Public WithEvents app As Excel.Application
'WithEvents表明Excel.Aplication的实例对象是一个可以引发事件的对象
Event WindowActivate(ByVal Wb As Excel.Workbook, ByVal Wn As Excel.Window)
Private Sub app_WindowActivate(ByVal Wb As Excel.Workbook, ByVal Wn As Excel.Window) Handles app.WindowActivate
MsgBox("The window " & Wn.Caption & " was just activated.")
End Sub
清单1.17 处理Excel应用程序对象的WindowActivate事件的VSTO自定义
Public Class Sheet1 Public WithEvents app As Excel.Application Private Sub app_WindowActivate(ByVal Wb As Excel.Workbook, ByVal Wn As Excel.Window) Handles app.WindowActivate
MsgBox("The window " & Wn.Caption & " was just activated.")
End Sub Private Sub Sheet1_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
app = Me.Application
End Sub End Class
高级主题:动态处理事件
AddHandler app.WindowActivate, AddressOf Me.MyWindowActivateHandler
'AddHandler和RemoveHandler语句将传递要处理的事件以及将处理事件的事件处理程序方法。 当指定事件处理程序方法时,使用AddressOf关键字。 以下代码使用AddHandler动态添加事件处理程序MyWindowActivateHandler来处理应用程序对象的WindowActivate事件:
RemoveHandler app.WindowActivate, AddressOf Me.MyWindowActivateHandler
'删除事件处理方法
Private Sub app_WindowActivate(ByVal Wb As Excel.Workbook, ByVal Wn As Excel.Window)
MsgBox("The window " & Wn.Caption & " was just activated.")
End Sub
'与动态事件处理程序一样,事件处理程序签名必须与事件的预期签名相匹配。 但是,当您动态处理事件时,Handles关键字不会用于事件处理程序签名。 因此,WindowActivate事件的动态事件处理程序看起来像声明性事件处理程序,但省略了Handles子句:
清单1.18 动态添加和删除Excel应用程序对象的WindowActivate事件的事件处理程序的VSTO自定义
Public Class Sheet1 Public app As Excel.Application Private Sub MyWindowActivateHandler(ByVal Wb As Excel.Workbook, ByVal Wn As Excel.Window)
MsgBox("The window " & Wn.Caption & " was just activated.")
RemoveHandler app.WindowActivate, AddressOf Me.MyWindowActivateHandler
End Sub Private Sub Sheet1_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup
app = Me.Application
AddHandler app.WindowActivate, AddressOf Me.MyWindowActivateHandler
End Sub End Class
'本示例使工作簿窗口激活时最大化。
Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window)
Wn.WindowState = xlMaximized
End Sub
清单1.19 一个无法处理CommandBarButton单击事件的类
Imports Excel = Microsoft.Office.Interop.Excel
Imports Office = Microsoft.Office.Core Class SampleListener
Private app As Excel.Application Public Sub New(ByVal application As Excel.Application)
app = application
End Sub ' 这里的作用是关联Click事件,但会失败,因为btn没有放在一个更永久的变量中。
Public Sub ConnectEvents()
Dim bar As Office.CommandBar = app.CommandBars("Standard")
Dim btn As Office.CommandBarButton = bar.Controls.Add()
If btn IsNot Nothing Then
btn.Caption = "My Button"
btn.Tag = "SampleListener.btn"
AddHandler btn.Click, AddressOf Me.btn_Click
End If
End Sub ' Click事件永远不会到达此处理程序.
Public Sub btn_Click(ByVal ctrl As Office.CommandBarButton, ByRef cancelDefault As Boolean)
MessageBox.Show("Button was clicked")
End Sub End Class
清单1.20 一个无法处理Outlook检查器对象的NewInspectorEvent的类
Imports Outlook = Microsoft.Office.Interop.Outlook Class SampleListener
Private app As Outlook.Application Public Sub New(ByVal application As Outlook.Application)
app = application
End Sub ' This will appear to connect to the NewInspector event, but
' will fail because Inspectors is not put in a more permanent
' variable.
Public Sub ConnectEvents()
AddHandler app.Inspectors.NewInspector, _
AddressOf Me.MyNewInspectorHandler
End Sub ' The NewInspector event will never reach this handler.
Public Sub MyNewInspectorHandler(ByVal inspector As Outlook
.Inspector)
MessageBox.Show("New inspector.")
End Sub
End Class
清单1.21 一个类成功处理CommandBarButton单击事件,因为它将CommandBarButton对象存储在一个类成员变量中
Imports Excel = Microsoft.Office.Interop.Excel
Imports Office = Microsoft.Office.Core Class SampleListener
Private app As Excel.Application
Private myBtn As Office.CommandBarButton Public Sub New(ByVal application As Excel.Application)
app = application
End Sub Public Sub ConnectEvents()
Dim bar As Office.CommandBar = app.CommandBars("Standard")
myBtn = bar.Controls.Add()
If myBtn IsNot Nothing Then
myBtn.Caption = "My Button"
myBtn.Tag = "SampleListener.btn"
AddHandler myBtn.Click, AddressOf Me.myBtn_Click
End If
End Sub Public Sub myBtn_Click(ByVal ctrl As Office.CommandBarButton, ByRef cancelDefault As Boolean) MessageBox.Show("Button was clicked")
End Sub End Class
清单1.22 一个类成功处理Outlook检查器对象的NewInspector事件,因为它将检查器对象存储在一个类成员变量中
Imports Outlook = Microsoft.Office.Interop.Outlook Class SampleListener
Private app As Outlook.Application
Private myInspectors As Outlook.Inspectors Public Sub New(ByVal application As Outlook.Application)
app = application
End Sub Public Sub ConnectEvents()
myInspectors = app.Inspectors
AddHandler myInspectors.NewInspector, _
AddressOf Me.MyNewInspectorHandler
End Sub Public Sub MyNewInspectorHandler( _
ByVal inspector As Outlook.Inspector)
MessageBox.Show("New inspector.")
End Sub
End Class