VSTO开发指南（VB2013版） 第二章 Office解决方案介绍

时间：2022-10-31 20:50:37

实例2.1 通过控制台实现对Excel的自动化处理 书本第32页

注：添加两个引用：

第一个：程序集—框架—“System.Windows.Forms 4.0.0.0”
第二个：程序集—扩展—“Microsoft.Office.Interop.Excel 14.0.0.0”

程序清单2.1通过控制台程序对Excel自动化处理

Imports Excel = Microsoft.Office.Interop.Excel

Module Module1

  Private exitXL As Boolean = False

  Dim WithEvents myExcelApp As Excel.Application

  Sub Main()

    myExcelApp = New Excel.Application

    myExcelApp.Visible = True

    myExcelApp.StatusBar = "Hello World"

    myExcelApp.Workbooks.Add()

    While exitXL = False

      System.Windows.Forms.Application.DoEvents()

    End While

  End Sub

  Private Sub myExcelApp_SheetBeforeDoubleClick(ByVal sheet _

    As Object, ByVal target As Excel.Range, ByRef cancel _

    As Boolean) Handles myExcelApp.SheetBeforeDoubleClick

    exitXL = True

  End Sub

End Module

实例代码：

Imports Excel = Microsoft.Office.Interop.Excel

Module Module1

    Private exitXL As Boolean = False

    Dim WithEvents myExcelApp As Excel.Application '有这句需添加引用“Microsoft.Office.Interop.Excel 14.0.0.0”

    Sub Main()

        myExcelApp = New Excel.Application    '运行顺序——1

        myExcelApp.Visible = True             '运行顺序——2

        myExcelApp.StatusBar = "Hello World"  '运行顺序——3

        myExcelApp.Workbooks.Add()            '运行顺序——4

        While exitXL = False                  '运行顺序——5 实质就是程序运行到这里，控制台程序不运行了，控制权交给了Excel程序

            System.Windows.Forms.Application.DoEvents() '有这句需添加引用“System.Windows.Forms 4.0.0.0”

        End While

        MsgBox("通过双击单元格，控制权又由Excel转移到控制台！") '运行顺序——7

    End Sub

    Private Sub myExcelApp_SheetBeforeDoubleClick(ByVal sheet As Object, ByVal target As Excel.Range, ByRef cancel As Boolean) Handles myExcelApp.SheetBeforeDoubleClick

        exitXL = True                         '运行顺序——6 实质就是给exitXL重新赋值并传递给While条件语句，接着运行While以后的语句

    End Sub

End Module

'**************************************************************************

'*双击单元格Office控制权会转回到自动化程序事件处理中，                            *

'*若没有System.Windows.Forms.Application.DoEvents()，控制台窗口将自动关闭，    *

'*System.Windows.Forms.Application.DoEvents()方法可以使窗体处理其他事件，      *

'*所以窗体能够进行重绘。不至于出现假死现象。                                     *

'**************************************************************************

实例效果：

VSTO开发指南（VB2013版） 第二章 Office解决方案介绍

VSTO开发指南（VB2013版） 第二章 Office解决方案介绍

 实例2.2 wiki文本表示形式 书本第33页

程序清单2.2 表2.1的wiki文本表示形式

||Property or Method||Name||Return Type||

||Property||Application||Application||

||Property||Autoload||Boolean||

||Property||Compiled||Boolean||

||Property||Creator||Int32||

||Method||Delete||Void||

||Property||Index||Int32||

||Property||Installed||Boolean||

||Property||Name||String||

||Property||Parent||Object||

||Property||Path||String||

实例2.3 将文本文件中的wiki形式的文本以表格的形式输出到Word中 书本37页

程序清单2.3 完整的WordWiki实现

Imports System.Collections.Generic

Imports System.Text

Imports System.IO

Imports Office = Microsoft.Office.Core

Imports Word = Microsoft.Office.Interop.Word

Module Module1

  Sub Main(ByVal args As String())

    Dim theApplication As New Word.Application

    theApplication.Visible = True

    Dim theDocument As Word.Document

    theDocument = theApplication.Documents.Add()

    Dim reader As TextReader

    reader = New System.IO.StreamReader(args())

    Dim separators() As String

    separators() = "||"

    Dim rowCount As Integer =

    Dim columnCount As Integer = 

    ' Read rows and calculate number of rows and columns

    Dim rowList As New System.Collections.Generic.List(Of String)

    Dim row As String = reader.ReadLine()

    While row IsNot Nothing

      rowCount +=

      rowList.Add(row)

      ' If this is the first row,

      ' calculate the number of columns

      If rowCount =  Then

        Dim splitHeaderRow As String() = _

          row.Split(separators, StringSplitOptions.None)

        ' Ignore the first and last separator

        columnCount = splitHeaderRow.Length -

      End If

      row = reader.ReadLine()

    End While

    ' Create a table

    Dim range As Word.Range = theDocument.Range()

    Dim table As Word.Table = range.Tables.Add(range, _

      rowCount, columnCount)

    ' Populate table

    Dim columnIndex As Integer =

    Dim rowIndex As Integer = 

    For Each r As String In rowList

      Dim splitRow As String() = r.Split(separators, _

        StringSplitOptions.None)

      For columnIndex =  To columnCount

        Dim cell As Word.Cell = table.Cell(rowIndex, columnIndex)

        cell.Range.Text = splitRow(columnIndex)

      Next

      rowIndex +=

    Next

    ' Format table

    table.Rows().Range.Bold =

    table.AutoFitBehavior( _

      Word.WdAutoFitBehavior.wdAutoFitContent)

    ' Wait for input from the command line before exiting

    System.Console.WriteLine("Table complete.")

    System.Console.ReadLine()

    ' Quit without saving changes

    theApplication.Quit(False)

  End Sub

End Module

实例代码：

Imports System.Collections.Generic '默认

Imports System.Text  '默认

Imports System.IO '默认

Imports Office = Microsoft.Office.Core '添加引用“Microsoft Office 14.0 Object Library 2.5

Imports Word = Microsoft.Office.Interop.Word '添加引用"Microsoft.Office.Interop.word 14.0.0.0"

Module Module1

    Sub Main(ByVal args As String())

        Dim theApplication As New Word.Application '定义word程序

        theApplication.Visible = True '使word程序可视

        Dim theDocument As Word.Document '定义word文档

        theDocument = theApplication.Documents.Add() '为程序添加word文档

        Dim reader As TextReader  '定义Txt文本读取器

        reader = New System.IO.StreamReader(My.Application.Info.DirectoryPath & "/test.txt") '实例化读取文本接口,My.Application.Info.DirectoryPath指的是本程序的\bin\Debug目录

        Dim separators() As String  '定义分隔符字符串

        separators() = "||"  '为分隔符变量赋值

        Dim rowCount As Integer =      '定义行数

        Dim columnCount As Integer =   '定义列数

        ' 读取行并计算行数和列数

        Dim rowList As New System.Collections.Generic.List(Of String) '定义字符串型的列表集对象

        Dim row As String = reader.ReadLine() '读取文本存储器中的一行

        While row IsNot Nothing  '读取行没有到结尾

            rowCount +=         '读取下一行

            rowList.Add(row)    '将所读取的一行文本存储在列表集对象中

            ' 如果这是第一行，就计算列数

            If rowCount =  Then

                Dim splitHeaderRow As String() = row.Split(separators, StringSplitOptions.None) 'StringSplitOptions.None，就是分开的数组元素包括空元素

                columnCount = splitHeaderRow.Length -    ' 忽略第一和最后一个分隔符，由于第一个和最后一个没有起到分隔作用，所以5个元素减去2个分隔符元素就是所需的列数3，

            End If

            row = reader.ReadLine()'自带逐行读取的功能

        End While

        ' 在word中创建一个表

        Dim range As Word.Range = theDocument.Range() '定义文档单元格

        Dim table As Word.Table = range.Tables.Add(range, rowCount, columnCount) '创建一个rowCount行columnCount列的表格

        ' 操作word中所创建的表

        Dim columnIndex As Integer =

        Dim rowIndex As Integer = 

        For Each r As String In rowList

            Dim splitRow As String() = r.Split(separators, StringSplitOptions.None)  'StringSplitOptions.None，就是分开的数组元素包括空元素

            For columnIndex =  To columnCount

                Dim cell As Word.Cell = table.Cell(rowIndex, columnIndex) '\bin\Debug目录中test.txt文件中的结尾不能有多余的空行，不然会提示超出索引范围而出现错误

                cell.Range.Text = splitRow(columnIndex)

            Next

            rowIndex +=

        Next

        ' 格式化表格

        table.Rows().Range.Bold =

        table.AutoFitBehavior(Word.WdAutoFitBehavior.wdAutoFitContent) 'AutoFitBehavior()方法的作用就是以某种方法调整表格，ord.WdAutoFitBehavior.wdAutoFitContent表示表格根据内容来调节

        ' 退出前等待命令输入

        System.Console.WriteLine("Table complete.")

        System.Console.ReadLine()

        ' 没有保存更改而退出

        theApplication.Quit(False)

    End Sub

End Module

test.txt文档中的内容

||Property or Method||Name||Return Type||

||Property||Application||Application||

||Property||Autoload||Boolean||

||Property||Compiled||Boolean||

||Property||Creator||Int32||

||Method||Delete||Void||

||Property||Index||Int32||

||Property||Installed||Boolean||

||Property||Name||String||

||Property||Parent||Object||

||Property||Path||String||

实例效果：

VSTO开发指南（VB2013版） 第二章 Office解决方案介绍

 续：对实例2.3逐步详细解读

        首先，总的功能就是在读取文本文档中的内容并在新创建word中显示出来

Imports Word = Microsoft.Office.Interop.Word '添加引用"Microsoft.Office.Interop.word 14.0.0.0"

Module Module1

    Sub Main(ByVal args As String())

        Dim App As Word.Application = New Word.Application '创建Word实例程序    *

        Dim myWord As Word.Document = App.Documents.Add()  '创建word文档       *

        Dim range As Word.Range = myWord.Range() '创建一个存放内容的区域         *

        App.Visible = True '显示Word程序                                       *

        Dim reader As New System.IO.StreamReader("D:/test.txt") '读取文本文档，注：文本文档不能为编码文档，不然读过来是乱码！！

        Dim str As String = reader.ReadLine() '读取文本文档中的一行，若要读取全部reader.ReadToEnd(),从当前位置读到结尾，

        '若真正将每行读取，最好使用System.Collections.Generic集合的概念将每行读取到集合中，用for each遍历即可

        range.Text = str   '将文本文档中的内容赋值给Word中

    End Sub

End Module

续2：将文本文档中的内容读取，存入集合中

Imports Word = Microsoft.Office.Interop.Word '添加引用"Microsoft.Office.Interop.word 14.0.0.0"

Module Module1

    Sub Main(ByVal args As String())

        Dim App As Word.Application = New Word.Application '创建Word实例程序  *

        Dim myWord As Word.Document = App.Documents.Add()  '创建word文档      *

        App.Visible = True '显示Word程序                                      *

        Dim reader As New System.IO.StreamReader("D:/test.txt") '读取文本文档，注：文本文档不能为编码文档，不然读过来是乱码！！

        Dim rowList As New System.Collections.Generic.List(Of String) '定义字符串型的列表集对象

        Dim row As String = reader.ReadLine() '读取文本存储器中的一行

        While row IsNot Nothing  '读取行没有到结尾

            rowList.Add(row)    '将所读取的一行文本存储在列表集对象中

            row = reader.ReadLine() '自带逐行读取的功能

        End While

        Dim range As Word.Range = myWord.Range() '定义文档单元格

        Dim table As Word.Table = range.Tables.Add(range, , ) '创建一个11行1列的表格,硬编码更易理解

        Dim rowIndex As Integer =

        For Each r As String In rowList

            Dim cell As Word.Cell = table.Cell(rowIndex, ) '目录中test.txt文件中的结尾不能有多余的空行，不然会提示超出索引范围而出现错误

            cell.Range.Text = r

            rowIndex +=

        Next

    End Sub

End Module

 续3：将内容倒着读取出来

Imports Word = Microsoft.Office.Interop.Word '添加引用"Microsoft.Office.Interop.word 14.0.0.0"

Module Module1

    Sub Main(ByVal args As String())

        Dim App As Word.Application = New Word.Application '创建Word实例程序  *

        Dim myWord As Word.Document = App.Documents.Add()  '创建word文档      *

        App.Visible = True '显示Word程序                                      *

        Dim reader As New System.IO.StreamReader("D:/test.txt") '读取文本文档，注：文本文档不能为编码文档，不然读过来是乱码！！

        Dim rowList As New System.Collections.Generic.List(Of String) '定义字符串型的列表集对象

        Dim row As String = reader.ReadLine() '读取文本存储器中的一行

        While row IsNot Nothing  '读取行没有到结尾

            rowList.Add(row)    '将所读取的一行文本存储在列表集对象中

            row = reader.ReadLine() '自带逐行读取的功能

        End While

        Dim range As Word.Range = myWord.Range() '定义文档单元格

        Dim rowIndex As Integer =

        For Each r As String In rowList

            range = myWord.Range(, )  'range方法的第一个参数是开头，第二个参数是结尾，这里指第几段，插入值，插入的值是倒着排列的

            range.Text = r & Chr() ' 注：chr(10) & chr(13)中，chr(10) 换行符，而 chr(13)回车，那么chr(10) & chr(13)就是既换行了又回车了

            rowIndex = rowIndex +

        Next

    End Sub

续4：myWord.Paragraphs.Add().Range()

Imports Word = Microsoft.Office.Interop.Word '添加引用"Microsoft.Office.Interop.word 14.0.0.0"

Module Module1

    Sub Main(ByVal args As String())

        Dim App As Word.Application = New Word.Application '创建Word实例程序  *

        Dim myWord As Word.Document = App.Documents.Add()  '创建word文档      *

        App.Visible = True '显示Word程序                                      *

        Dim reader As New System.IO.StreamReader("D:/test.txt") '读取文本文档，注：文本文档不能为编码文档，不然读过来是乱码！！

        Dim rowList As New System.Collections.Generic.List(Of String) '定义字符串型的列表集对象

        Dim row As String = reader.ReadLine() '读取文本存储器中的一行

        While row IsNot Nothing  '读取行没有到结尾

            rowList.Add(row)    '将所读取的一行文本存储在列表集对象中

            row = reader.ReadLine() '自带逐行读取的功能

        End While

        Dim range As Word.Range = myWord.Paragraphs.Add().Range() '定义文档单元格

        Dim rowIndex As Integer =

        For Each r As String In rowList

            range = myWord.Paragraphs(rowIndex).Range()

            range.Text = r & Chr() ' 注：chr(10) & chr(13)中，chr(10) 换行符，而 chr(13)回车，那么chr(10) & chr(13)就是既换行了又回车了

            rowIndex = rowIndex +

        Next

    End Sub

实例2.4 Outlook外接程序 书本第40页

  程序清单2.4 Outlook外接程序项目中ThisApplication类的初始化代码

Public Class ThisApplication

  Private Sub ThisApplication_Startup(ByVal sender As Object, _

    ByVal e As System.EventArgs) Handles Me.Startup

  End Sub

  Private Sub ThisApplication_Shutdown(ByVal sender As Object, _

    ByVal e As System.EventArgs) Handles Me.Shutdown

  End Sub

End Class

   实例代码：

Public Class ThisAddIn

    Private Sub ThisAddIn_Startup() Handles Me.Startup

    End Sub

    Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown

    End Sub

End Class

实例2.5 VSTO Outlook 外接程序 书本41页

注：添加引用”System.Windows.Forms”

程序清单2.5 VSTO Outlook 外接程序，用于处理Item事件和检查收件人数是否超过25

Imports Outlook = Microsoft.Office.Interop.Outlook

Public Class ThisApplication

  Private Sub ThisApplication_ItemSend(ByVal item As Object, _

    ByRef cancel As Boolean) Handles Me.ItemSend

    Dim myItem As Outlook.MailItem

    If TypeOf item Is Outlook.MailItem Then

      myItem = CType(item, Outlook.MailItem)

      For Each recip As Outlook.Recipient In myItem.Recipients

        If recip.AddressEntry.Members.Count >  Then

          ' Ask the user if she really wants to send this e-mail

          Dim message As String

          message = "Send mail to {0} with {1} people?"

          Dim caption As String = "More than 25 recipients"

          Dim buttons As MessageBoxButtons

          buttons = MessageBoxButtons.YesNo

          Dim result As DialogResult

          result = MessageBox.Show(String.Format(message, _

            recip.AddressEntry.Name, _

            recip.AddressEntry.Members.Count), _

            caption, buttons)

          If result = DialogResult.No Then

            cancel = True

            Exit For

          End If

        End If

      Next

    End If

  End Sub

End Class

实例2.6 VSTO Excel工作簿自定义机制  书本42页

程序清单 2.6 VSTO Excel工作簿自定义机制

Imports Excel = Microsoft.Office.Interop.Excel

Imports Office = Microsoft.Office.Core

Public Class Sheet1

  Private Sub Sheet1_Startup(ByVal sender As Object, _

      ByVal e As System.EventArgs) Handles Me.Startup

    ' Initial entry point.

    ' This code gets run first when the code behind is created

    ' The context is implicit in the Sheet1 class

    MsgBox("Code behind the document running.")

    MsgBox(String.Format("{0} is the sheet name.", Me.Name))

  End Sub

End Class

实例代码：

Imports Excel = Microsoft.Office.Interop.Excel

Imports Office = Microsoft.Office.Core

Public Class Sheet1

    Private Sub Sheet1_Startup(ByVal sender As Object, _

        ByVal e As System.EventArgs) Handles Me.Startup

        ' 初始化入口点

        ' 创建文档代码时将首次执行此代码

        ' 这里的上下环境是sheet1

        MsgBox("Code behind the document running.")

        MsgBox(String.Format("{0} is the sheet name.", Me.Name))

    End Sub

End Class

实例效果：

VSTO开发指南（VB2013版） 第二章 Office解决方案介绍   VSTO开发指南（VB2013版） 第二章 Office解决方案介绍

实例2.7 VSTO自定义机制：在文档操作任务面板中添加按钮控件以及将ListObject控件与DataTable进行数据绑定   书本46页

程序清单 2.7 VSTO自定义机制：在文档操作任务面板中添加按钮控件以及将ListObject控件与DataTable进行数据绑定

Imports Excel = Microsoft.Office.Interop.Excel

Imports Office = Microsoft.Office.Core

Public Class Sheet1

  Private WithEvents myButton As New Button

  Private table As DataTable

  Private Sub Sheet1_Startup(ByVal sender As Object, _

      ByVal e As System.EventArgs) Handles Me.Startup

    myButton.Text = "Databind!"

    Globals.ThisWorkbook.ActionsPane.Controls.Add(myButton)

  End Sub

  Private Sub myButton_Click(ByVal sender As Object, _

    ByVal e As EventArgs) Handles myButton.Click

    List1.DataSource = Nothing

    table = New DataTable

    Dim r As New Random

    For i As Integer =  To

      table.Columns.Add("Col" & i.ToString())

    Next

    For j As Integer =  To

      table.Rows.Add(r.NextDouble(), r.NextDouble(), _

        r.NextDouble(), r.NextDouble())

    Next

    List1.DataSource = table

  End Sub

End Class

实例代码：

Imports Excel = Microsoft.Office.Interop.Excel

Imports Office = Microsoft.Office.Core

Public Class Sheet1

    Private WithEvents myButton As New Button 'WithEvents的意思是告知VB编译器这是一个可以触发事件对象

    Private table As DataTable   'DataTable是Excel对象的

    Private Sub Sheet1_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup

        myButton.Text = "Databind!"

        Globals.ThisWorkbook.ActionsPane.Controls.Add(myButton)'Actionspane.Controls表示右侧的文档操作面板

    End Sub

    Private Sub myButton_Click(ByVal sender As Object, ByVal e As EventArgs) Handles myButton.Click

        List1.DataSource = Nothing  '设置数据源为空

        table = New DataTable   '定义数据表用关键字new，实质就是一个实例对象

        Dim r As New Random   '定义随机数用new关键字，实质就是一实例对象

        For i As Integer =  To

            table.Columns.Add("Col" & i.ToString())

        Next

        For j As Integer =  To

            table.Rows.Add(r.NextDouble(), r.NextDouble(), r.NextDouble(), r.NextDouble())'NextDouble大于或等于 0且小于 1 的随机浮点数

        Next

        List1.DataSource = table

    End Sub

End Class

代码另一种写法：

Imports Excel = Microsoft.Office.Interop.Excel

Imports Office = Microsoft.Office.Core

Public Class Sheet1

    Private WithEvents myButton As New Button

    Private table As DataTable = New DataTable

    Dim r As New Random

    Private Sub Sheet1_Startup(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Startup

        myButton.Text = "Databind!"

        Globals.ThisWorkbook.ActionsPane.Controls.Add(myButton)

    End Sub

    Private Sub myButton_Click(ByVal sender As Object, ByVal e As EventArgs) Handles myButton.Click

        List1.DataSource = Nothing

        table.Columns.Add() : table.Columns.Add() : table.Columns.Add() : table.Columns.Add() '添加4列,冒号接续

        For j As Integer =  To

            table.Rows.Add(r.NextDouble(), r.NextDouble(), r.NextDouble(), r.NextDouble()) '生成19行4列的数据

        Next

        List1.DataSource = table '将table中的值作为数据源

Dim str As String
         str = table.Rows(2).Item(1).ToString() 'Rows(2).Item(1)表示第3行第2列，索引从0开始
         MsgBox(str)

    End Sub

End Class

实例效果：

VSTO开发指南（VB2013版） 第二章 Office解决方案介绍

相关文章