Visual Basic 2017 操作Excel和word【1】持续更新……

我坚持在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

第四步:运行结果

Visual Basic 2017  操作Excel和word【1】持续更新……

清单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

第四步:运行结果

Visual Basic 2017  操作Excel和word【1】持续更新……  Visual Basic 2017  操作Excel和word【1】持续更新……  Visual Basic 2017  操作Excel和word【1】持续更新……   Visual Basic 2017  操作Excel和word【1】持续更新……   Visual Basic 2017  操作Excel和word【1】持续更新……  Visual Basic 2017  操作Excel和word【1】持续更新……  Visual Basic 2017  操作Excel和word【1】持续更新……   Visual Basic 2017  操作Excel和word【1】持续更新……

拓展假如要编辑工作表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


运行结果:

Visual Basic 2017  操作Excel和word【1】持续更新……

清单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

运行结果:

原图:

Visual Basic 2017  操作Excel和word【1】持续更新……       Visual Basic 2017  操作Excel和word【1】持续更新……

表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
上一篇:jvm实战-基本类型占多少内存


下一篇:《ServerSuperIO Designer IDE使用教程》-4.增加台达PLC驱动及使用教程,从0到1的改变。发布:v4.2.3版本