VSTO开发指南（VB2013版） 第一章 Office对象模型

完美地将visual basic和office 办公软件结合起来。来自微软公司VSTO小组的权威专家所编著。

全书共992页，内容极其全面而深入，猛一看，厚地犹如庞然大物。看完离大神就不远了哦<^ . ^>!!!!!

《VSTO开发指南》是2008年2月电子工业出版社出版的图书，

作者是（美国）Eric Carter   Eric Lippert

实例1：从Excel程序到Excel表 书本中的内容（第7页）：

  程序清单1.1. 在Excel中从Application对象到Worksheet

Dim myWorkbooks As Excel.Workbooks = app.Workbooks
Dim myWorkbook As Excel.Workbook = myWorkbooks.Item(1)
Dim myWorksheets As Excel.Sheets = myWorkbook.Worksheets
Dim myWorksheet As Excel.Worksheet
myWorksheet = CType(myWorksheets.Item(1), Excel.Worksheet)

Visual basic 2013 中的控制台应用程序：

实现目标：获取第一个工作表的名称。

实现步骤：项目——>添加引用——>程序集——>扩展——>Microsoft.Office.Interop.Excel——>确定

实例代码：

Imports excel = Microsoft.Office.Interop.Excel
Module Module1

    Sub Main()
        Dim app As excel.Application = New excel.Application
        Dim myWorkbooks As excel.Workbooks = app.Workbooks
        Dim myWorkbook As excel.Workbook = myWorkbooks.Add()
        Dim myWorkbook1 As excel.Workbook = myWorkbooks.Item(1)
        Dim myWorksheets As excel.Sheets = myWorkbook1.Worksheets
        Dim myWorksheet As excel.Worksheet
        myWorksheet = CType(myWorksheets.Item(1), excel.Worksheet)
        MsgBox(myWorksheet.Name)
    End Sub

End Module

也可以为代码：

Imports excel = Microsoft.Office.Interop.Excel
Module Module1
    Sub Main()
        Dim app As excel.Application = New excel.Application
        Dim myWorkbooks As excel.Workbooks = app.Workbooks
        Dim myWorkbook As excel.Workbook = myWorkbooks.Add()
        Dim myWorksheet As excel.Worksheet
        myWorksheet = CType(myWorkbook.Worksheets.Item(1), excel.Worksheet)
        MsgBox(myWorksheet.Name)
    End Sub
End Module

也可以为代码：

Imports excel = Microsoft.Office.Interop.Excel
Module Module1
    Sub Main()
        Dim app As excel.Application = New excel.Application
        Dim myWorkbooks As excel.Workbooks = app.Workbooks
        Dim myWorksheet As excel.Worksheet
        myWorksheet = CType(myWorkbooks.Add().Worksheets.Item(1), excel.Worksheet)
        MsgBox(myWorksheet.Name)
    End Sub
End Module

也可以为代码：

Imports excel = Microsoft.Office.Interop.Excel
Module Module1
    Sub Main()
        Dim app As excel.Application = New excel.Application
        Dim myWorksheet As excel.Worksheet
        myWorksheet = CType(app.Workbooks.Add().Worksheets.Item(1), excel.Worksheet)
        MsgBox(myWorksheet.Name)
    End Sub
End Module

实例效果：

              

简化程序清单1.1中的代码为：

书中的内容：

Dim myWorksheet As Excel.Worksheet
myWorksheet = CType(app.Workbooks.Item(1).Worksheets.Item(1), Excel.Worksheet)

编写代码：

Imports excel = Microsoft.Office.Interop.Excel
Module Module1

    Sub Main()
        Dim app As excel.Application = New excel.Application
        Dim myWorksheet As excel.Worksheet
        myWorksheet = CType(app.Workbooks.Add().Worksheets.Add(), excel.Worksheet)
        MsgBox(myWorksheet.Name)
    End Sub

End Module

实例效果：

              

 注：区分程序、工作簿、工作表的概念，程序就是我们安装的Excel，程序可以创建多个工作簿，一个工作簿可以创建多个工作表。我们平时所说的Excel文件就是工作簿，而工作簿是要用程序来创建的。

 实例1自续：创建Excel工作簿，再创建工作表，并在A1单元格中输入内容“这是A1”

 实例代码：

Imports excel = Microsoft.Office.Interop.Excel
Module Module1

    Sub Main()
        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(1, 1) = "这是A1"
    End Sub
End Module

 实例效果：

                

实例2：Excel表的count属性和item属性 书本中的内容（第7页）：     

程序清单1.2 以整数或字符串索引形式使用count和item属性对集合进行循环处理

Dim myWorkbooks As Excel.Workbooks = app.Workbooks

Dim workbookCount As Integer = myWorkbooks.Count
For i As Integer = 1 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

实例代码：       

Imports excel = Microsoft.Office.Interop.Excel
Module Module1

    Sub Main()
        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(1, 1) = "这是A1"

        Dim worksheetCount As Integer = myWorkbook.Worksheets.Count
        For i As Integer = 1 To worksheetCount
            Dim str As String
            str = myWorkbook.Worksheets.Item(i).Name  '以整数作为索引
            MsgBox(str, , "获取工作表名称")
        Next

        For i As Integer = 1 To worksheetCount
            Dim str As String
            str = myWorkbook.Worksheets.Item("sheet" & i).Name
            MsgBox(str, , "获取工作表名称")    '以字符串作为索引
        Next
    End Sub
End Module

实例效果：

                 

实例2自续：编辑工作表sheet2中D3单元格，并填入“我是丑丑”

Imports excel = Microsoft.Office.Interop.Excel
Module Module1

    Sub Main()
        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(3, 4) = "我是丑丑" 'Cells(行，列)，也就是D3单元格 这里不能用range属性代替，应为range属性是只读属性
    End Sub
End Module

实例效果：

实例3：学习For each循环结构 书本内容（第8页）

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

实例代码;

Imports excel = Microsoft.Office.Interop.Excel
Module Module1

    Sub Main()
        Dim app As excel.Application = New excel.Application
        Dim myWorkbooks As excel.Workbooks = app.Workbooks
        Dim myWorkbook As excel.Workbook = myWorkbooks.Add()
        'Dim myWorkbook As excel.Workbook = myWorkbooks.Add("D:\职工人员表.xls")
        For Each workbook As excel.Workbook In myWorkbooks
            MsgBox(String.Format("Workbook {0}", workbook.Name))
        Next

    End Sub
End Module

实例效果（不尽如人意）：

改进实例：删除工作表

实例代码：       

Imports excel = Microsoft.Office.Interop.Excel
Module Module1

    Sub Main()
        Dim app As excel.Application = New excel.Application
        Dim myWorkbook As excel.Workbook = app.ActiveWorkbook
        app.Visible = True
        myWorkbook = app.Workbooks.Add("E:\工作簿1") '"工作簿1.xls"是一个模板，相当另外新建了一个"工作簿11.xls"并打开了
        Dim myCollection As New Collections.Generic.List(Of excel.Worksheet)
        For Each Name As excel.Worksheet In myWorkbook.Worksheets
            myCollection.Add(Name)
        Next
        For Each name As excel.Worksheet In myCollection
            name.Delete()
        Next
    End Sub
End Module

实例效果：

 

 实例4：删除对象用集合 书本内容（8页）

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

    注：代码中的Excel.Name并不字符串，而是一个对象。

实例代码：

Imports excel = Microsoft.Office.Interop.Excel
Module Module1

    Sub Main()
        Dim app As excel.Application = New excel.Application
        Dim myWorkbook As excel.Workbook = app.ActiveWorkbook
        app.Visible = True
        myWorkbook = app.Workbooks.Add("E:\工作簿1") '"工作簿1.xls"是一个模板，相当另外新建了一个"工作簿11.xls"并打开了
        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 Module

实例效果：

 

   

实例7：Word的application判断CapsLock属性值，书本第9页。

  注：从实例7开始了Word，所以在引用里要添加“Microsoft.Office.Interop.Word”

  程序清单1.5 返回值类型的属性：Word应用程序对象上的布尔型CapsLock属性

If app.CapsLock Then
  MsgBox("CapsLock is on")
Else
  MsgBox("CapsLock is off")
End If

实例代码：

Imports word = Microsoft.Office.Interop.Word
Module Module1

    Sub Main()
        Dim app As word.Application = New word.Application
        If app.CapsLock Then
            MsgBox("CapsLock is on")
        Else
            MsgBox("CapsLock is off")
        End If
    End Sub
End Module

实例效果：

   

 实例7：判断word文档是最大化、最小化还是常规化，书本第11页

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

实例代码：

Imports word = Microsoft.Office.Interop.Word
Module Module1

    Sub Main()
        Dim APP As word.Application = New word.Application
        Dim myDocuments As word.Documents = APP.Documents
        Dim myDocument As word.Document = myDocuments.Add()
        APP.Visible = True
        Select Case app.WindowState
            Case word.WdWindowState.wdWindowStateMaximize
                MsgBox("Maximized")
            Case word.WdWindowState.wdWindowStateMinimize
                MsgBox("Minimized")
            Case word.WdWindowState.wdWindowStateNormal
                MsgBox("Normal")
        End Select
    End Sub
End Module

实例效果：

      

 实例7：活动文档（ActiveDocument），书本第11页

 程序清单1.7 返回对象模型中的另一个对象属性：Word应用程序对象中的ActiveDocument属性

Dim myDocument As Word.Document = app.ActiveDocument
MsgBox(myDocument.Name)

实例代码：

Imports word = Microsoft.Office.Interop.Word
Module Module1

    Sub Main()
        Dim APP As word.Application = New word.Application
        Dim myDocuments As word.Documents = APP.Documents
        Dim myDocument As word.Document = myDocuments.Add()
        APP.Visible = True
        Dim myDocument1 As word.Document = APP.ActiveDocument
        MsgBox(myDocument1.Name)
    End Sub
End Module

实例效果：

 实例8：没有活动文档的异常，书本第11页

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

示例代码：

Imports word = Microsoft.Office.Interop.Word
Module Module1

    Sub Main()
        Dim APP As word.Application = New word.Application
        Dim myDocument As word.Document
        APP.Visible = True
        Try
            myDocument = APP.ActiveDocument
            MsgBox(myDocument.Name)
        Catch ex As Exception
            MsgBox(String.Format("No active document: {0}", ex.Message))
        End Try
    End Sub
End Module

实例效果：

 实例9：没有打开Excel，就返回nothing书本第11页。

程序清单1.9 可能返回Nothing的属性：Excel应用程序对象中的ActiveWorkBook属性

Dim myWorkbook As Excel.Workbook = app.ActiveWorkbook

If myWorkbook Is Nothing Then
  MsgBox("No active workbook")
Else
  MsgBox(myWorkbook.Name)
End If

实例代码：

Imports excel = Microsoft.Office.Interop.Excel
Module Module1

    Sub Main()
        Dim APP As excel.Application = New excel.Application
        Dim myWorkbook As Excel.Workbook = app.ActiveWorkbook

        If myWorkbook Is Nothing Then
            MsgBox("No active workbook")
        Else
            MsgBox(myWorkbook.Name)
        End If
    End Sub
End Module

 实例效果：

 实例10：Word应用程序的文件对话框（FileDialog）属性 第12页

  注：实例10中出现了文件对话框，所以引用里要添加“Microsoft office 14.0 object library”，FileDialog来源于Microsoft.office.core.Filedialog。

  程序清单1.10 需要枚举型参数并返回对象模型的属性：Word应用程序对象上的FileDialog属性

Dim dialog As Office.FileDialog
dialog = app.FileDialog(Office.MsoFileDialogType. _
  msoFileDialogFilePicker)
dialog.Show()

  实例代码：

Imports word = Microsoft.Office.Interop.Word
Module Module1
    Sub Main()
        Dim app As word.Application = New word.Application
        Dim dialog As word.Dialog
        dialog = app.Dialogs(word.WdWordDialog.wdDialogFileOpen)
        dialog.Show()
    End Sub
End Module

  实例效果：

 实例拓展（来自MSDN）：

Imports word = Microsoft.Office.Interop.Word
Module Module1
    Sub Main()
        Dim app As word.Application = New word.Application
        Dim dlg As word.Dialog = app.Dialogs(word.WdWordDialog.wdDialogFileOpen)
        Dim dlgType As Type = GetType(word.Dialog)
        ' Set the Name property of the dialog box.
        dlgType.InvokeMember("Name", _
        Reflection.BindingFlags.SetProperty Or _
        Reflection.BindingFlags.Public Or _
        Reflection.BindingFlags.Instance, _
        Nothing, dlg, New Object() {"Testing"}, _
        System.Globalization.CultureInfo.InvariantCulture)
        ' Display the dialog box.
        dlg.Show()
        ' Show the Name property.
        MsgBox(dlgType.InvokeMember("Name", _
        Reflection.BindingFlags.GetProperty Or _
        Reflection.BindingFlags.Public Or _
        Reflection.BindingFlags.Instance, _
        Nothing, dlg, Nothing, _
        System.Globalization.CultureInfo.InvariantCulture))
    End Sub
End Module

实例效果：

 

 实例11：Excel应用程序对象上的Range属性 书本第13页

程序清单1.11 具有可选参数的属性：Excel应用程序对象上的Range属性

' Omit the optional second parameter
Dim myRange As Excel.Range = app.Range("A1")

' Specify the optional second parameter
Dim myRange2 As Excel.Range = app.Range("A1", "B2")

实例代码：

Imports excel = Microsoft.Office.Interop.Excel
Module Module1
    Sub Main()
        Dim app As excel.Application = New excel.Application
        Dim myWorkbooks As excel.Workbooks = app.Workbooks
        Dim myWorkbook As excel.Workbook = myWorkbooks.Add("E:\工作簿1.xlsx")
        app.Visible = True
        Dim myRange As String = myWorkbook.Worksheets("sheet1").Range("A1").text
        'Dim myRange2 As String = myWorkbook.Worksheets("sheet1").Range("A1", "B2").text
        MsgBox(String.Format("单元格中的值：{0}", myRange))
    End Sub
End Module

实例效果：

 

 

 小结一下：

最单的一步到位的一种从程序到我们常见的工作表的代码写法：

        'Earlier in this chapter, we presented this code as a simple way of navigating the object hierarchy of Excel to get a Worksheet object:
        Dim myWorksheet As excel.Worksheet
        myWorksheet = CType(app.Workbooks.Item(1).Worksheets.Item(1), excel.Worksheet)
        'There is an even simpler way to write this code. It can be rewritten like this:
        Dim myWorksheet As excel.Worksheet
        myWorksheet = CType(app.Workbooks(1).Worksheets(1), excel.Worksheet)

Imports excel = Microsoft.Office.Interop.Excel
Module Module1
    Sub Main()
        Dim app As excel.Application = New excel.Application
        Dim myWorksheet As excel.Worksheet
        myWorksheet = CType(app.Workbooks.Add("E:\工作簿1.xlsx").Worksheets.Item("sheet1"), excel.Worksheet)
        Dim myRange As String = myWorksheet.Range("A1").text
        MsgBox(String.Format("单元格中的值：{0}", myRange))
    End Sub
End Module

 实例12 activate方法激活Word 书本第14页

程序清单 1.12 没有参数没有返回类型的方法：Word应用程序对象中的Activate方法

MsgBox("Activating the Word window.")

app.Activate()

实例代码：

Imports word = Microsoft.Office.Interop.Word
Module Module1
    Sub Main()
        Dim app As word.Application = New word.Application
        Dim myDocuments As word.Documents = app.Documents
        Dim myDocument As word.Document = myDocuments.Add()
        app.Visible = True
        MsgBox("Activating the Word window.")
        app.Activate()
    End Sub
End Module

实例效果：

 实例13 Word程序改变打开路径 书本第14页

程序清单1.13 具有参数但没有返回值的方法：Word应用程序对象中的ChangeFileOpenDirectory方法

app.ChangeFileOpenDirectory("c:\temp")

MsgBox("Will open out of temp for this session.")

实例代码：

Imports word = Microsoft.Office.Interop.Word
Module Module1
    Sub Main()
        Dim app As word.Application = New word.Application
        app.ChangeFileOpenDirectory("c:\temp")
        Dim dialog As word.Dialog
        dialog = app.Dialogs(word.WdWordDialog.wdDialogFileOpen)
        dialog.Show()
    End Sub
End Module

实例效果：

 

 实例14 获得Word的像素值 

程序清单1.14 没有参数但有返回值的方法：Word应用程序对象中的DefaultWebOptions方法

Dim options As Word.DefaultWebOptions = app.DefaultWebOptions()

MsgBox(String.Format("Pixels per inch is {0}.", _
  options.PixelsPerInch))

实例代码：

Imports word = Microsoft.Office.Interop.Word
Module Module1
    Sub Main()
        Dim app As word.Application = New word.Application
        Dim options As word.DefaultWebOptions = app.DefaultWebOptions
        MsgBox(String.Format("pixels per inch is {0}", options.PixelsPerInch))
    End Sub
End Module

实例效果：

 实例15 Word的属性CentimetersToPoints将厘米转换为对应的点数  书本第14页

程序清单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))

实例代码：

Imports word = Microsoft.Office.Interop.Word
Module Module1
    Sub Main()
        Dim app As word.Application = New word.Application
        Dim centimeters As Single = 15.0
        Dim points As Single = app.CentimetersToPoints(centimeters)
        MsgBox(String.Format("{0} centimeters is {1} points.", centimeters, points))
        '1 centimeter = 28.34646 point
    End Sub
End Module

实例效果：

 

 实例16 Excel程序的CheckSpelling方法检查单词拼写   书本第15页

程序清单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)

示例代码：

Imports excel = Microsoft.Office.Interop.Excel
Module Module1
    Sub Main()
        Dim app As excel.Application = New excel.Application
        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)
        MsgBox("phrase1: " & isCorrect1 & Chr(13) & Chr(10) & "phrase2: " & isCorrect2)
    End Sub

实例效果：

 实例17  处理Excel Application 对象中WindowActivate事件 书本第17页

程序清单 1.17 处理Excel Application对象中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

实例代码：

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

实例效果：

实例18 WindowActivate事件动态添加和删除事件处理器 书本第19页

程序清单1.18 VSTO自定义机制，用于为Excel应用程序对象的WindowActivate事件动态添加和删除事件处理器

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

实例代码：

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

实例效果：