實(shí)例2.1 通過控制臺實(shí)現(xiàn)對Excel的自動化處理 書本第32頁
注：添加兩個引用：
第一個：程序集—框架—“System.Windows.Forms 4.0.0.0”
第二個：程序集—擴(kuò)展—“Microsoft.Office.Interop.Excel 14.0.0.0”
程序清單2.1通過控制臺程序?qū)xcel自動化處理
Imports Excel = Microsoft.Office.Interop.ExcelModule 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 SubEnd Module
實(shí)例代碼：
Imports Excel = Microsoft.Office.Interop.ExcelModule 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 '運(yùn)行順序——1 myExcelApp.Visible = True '運(yùn)行順序——2 myExcelApp.StatusBar = "Hello World" '運(yùn)行順序——3 myExcelApp.Workbooks.Add() '運(yùn)行順序——4 While exitXL = False '運(yùn)行順序——5 實(shí)質(zhì)就是程序運(yùn)行到這里，控制臺程序不運(yùn)行了，控制權(quán)交給了Excel程序 System.Windows.Forms.Application.DoEvents() '有這句需添加引用“System.Windows.Forms 4.0.0.0” End While MsgBox("通過雙擊單元格，控制權(quán)又由Excel轉(zhuǎn)移到控制臺！") '運(yùn)行順序——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 '運(yùn)行順序——6 實(shí)質(zhì)就是給exitXL重新賦值并傳遞給While條件語句，接著運(yùn)行While以后的語句 End SubEnd Module'**************************************************************************'*雙擊單元格Office控制權(quán)會轉(zhuǎn)回到自動化程序事件處理中， *'*若沒有System.Windows.Forms.Application.DoEvents()，控制臺窗口將自動關(guān)閉， *'*System.Windows.Forms.Application.DoEvents()方法可以使窗體處理其他事件， *'*所以窗體能夠進(jìn)行重繪。不至于出現(xiàn)假死現(xiàn)象。 *'**************************************************************************
實(shí)例效果：
實(shí)例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||
實(shí)例2.3 將文本文件中的wiki形式的文本以表格的形式輸出到Word中 書本37頁
程序清單2.3 完整的WordWiki實(shí)現(xiàn)
Imports System.Collections.GenericImports System.TextImports System.IOImports Office = Microsoft.Office.CoreImports Word = Microsoft.Office.Interop.WordModule 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(0)) Dim separators(1) As String separators(0) = "||" Dim rowCount As Integer = 0 Dim columnCount As Integer = 0 ' 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 += 1 rowList.Add(row) ' If this is the first row, ' calculate the number of columns If rowCount = 1 Then Dim splitHeaderRow As String() = _ row.Split(separators, StringSplitOptions.None) ' Ignore the first and last separator columnCount = splitHeaderRow.Length - 2 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 = 1 Dim rowIndex As Integer = 1 For Each r As String In rowList Dim splitRow As String() = r.Split(separators, _ StringSplitOptions.None) For columnIndex = 1 To columnCount Dim cell As Word.Cell = table.Cell(rowIndex, columnIndex) cell.Range.Text = splitRow(columnIndex) Next rowIndex += 1 Next ' Format table table.Rows(1).Range.Bold = 1 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 SubEnd Module
實(shí)例代碼：
Imports System.Collections.Generic '默認(rèn)Imports System.Text '默認(rèn)Imports System.IO '默認(rèn)Imports Office = Microsoft.Office.Core '添加引用“Microsoft Office 14.0 Object Library 2.5Imports 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") '實(shí)例化讀取文本接口,My.Application.Info.DirectoryPath指的是本程序的\bin\Debug目錄 Dim separators(1) As String '定義分隔符字符串 separators(0) = "||" '為分隔符變量賦值 Dim rowCount As Integer = 0 '定義行數(shù) Dim columnCount As Integer = 0 '定義列數(shù) ' 讀取行并計算行數(shù)和列數(shù) Dim rowList As New System.Collections.Generic.List(Of String) '定義字符串型的列表集對象 Dim row As String = reader.ReadLine() '讀取文本存儲器中的一行 While row IsNot Nothing '讀取行沒有到結(jié)尾 rowCount += 1 '讀取下一行 rowList.Add(row) '將所讀取的一行文本存儲在列表集對象中 ' 如果這是第一行，就計算列數(shù) If rowCount = 1 Then Dim splitHeaderRow As String() = row.Split(separators, StringSplitOptions.None) 'StringSplitOptions.None，就是分開的數(shù)組元素包括空元素 columnCount = splitHeaderRow.Length - 2 ' 忽略第一和最后一個分隔符，由于第一個和最后一個沒有起到分隔作用，所以5個元素減去2個分隔符元素就是所需的列數(shù)3， End If row = reader.ReadLine()'自帶逐行讀取的功能 End While ' 在word中創(chuàng)建一個表 Dim range As Word.Range = theDocument.Range() '定義文檔單元格 Dim table As Word.Table = range.Tables.Add(range, rowCount, columnCount) '創(chuàng)建一個rowCount行columnCount列的表格 ' 操作word中所創(chuàng)建的表 Dim columnIndex As Integer = 1 Dim rowIndex As Integer = 1 For Each r As String In rowList Dim splitRow As String() = r.Split(separators, StringSplitOptions.None) 'StringSplitOptions.None，就是分開的數(shù)組元素包括空元素 For columnIndex = 1 To columnCount Dim cell As Word.Cell = table.Cell(rowIndex, columnIndex) '\bin\Debug目錄中test.txt文件中的結(jié)尾不能有多余的空行，不然會提示超出索引范圍而出現(xiàn)錯誤 cell.Range.Text = splitRow(columnIndex) Next rowIndex += 1 Next ' 格式化表格 table.Rows(1).Range.Bold = 1 table.AutoFitBehavior(Word.WdAutoFitBehavior.wdAutoFitContent) 'AutoFitBehavior()方法的作用就是以某種方法調(diào)整表格，ord.WdAutoFitBehavior.wdAutoFitContent表示表格根據(jù)內(nèi)容來調(diào)節(jié) ' 退出前等待命令輸入 System.Console.WriteLine("Table complete.") System.Console.ReadLine() ' 沒有保存更改而退出 theApplication.Quit(False) End SubEnd Module
test.txt文檔中的內(nèi)容
||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||
實(shí)例效果：
續(xù)：對實(shí)例2.3逐步詳細(xì)解讀
首先，總的功能就是在讀取文本文檔中的內(nèi)容并在新創(chuàng)建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 '創(chuàng)建Word實(shí)例程序 * Dim myWord As Word.Document = App.Documents.Add() '創(chuàng)建word文檔 * Dim range As Word.Range = myWord.Range() '創(chuàng)建一個存放內(nèi)容的區(qū)域 * App.Visible = True '顯示W(wǎng)ord程序 * Dim reader As New System.IO.StreamReader("D:/test.txt") '讀取文本文檔，注：文本文檔不能為編碼文檔，不然讀過來是亂碼?。?Dim str As String = reader.ReadLine() '讀取文本文檔中的一行，若要讀取全部reader.ReadToEnd(),從當(dāng)前位置讀到結(jié)尾， '若真正將每行讀取，最好使用System.Collections.Generic集合的概念將每行讀取到集合中，用for each遍歷即可 range.Text = str '將文本文檔中的內(nèi)容賦值給Word中 End SubEnd Module
續(xù)2：將文本文檔中的內(nèi)容讀取，存入集合中
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 '創(chuàng)建Word實(shí)例程序 * Dim myWord As Word.Document = App.Documents.Add() '創(chuàng)建word文檔 * App.Visible = True '顯示W(wǎng)ord程序 * 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 '讀取行沒有到結(jié)尾 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) '創(chuàng)建一個11行1列的表格,硬編碼更易理解 Dim rowIndex As Integer = 1 For Each r As String In rowList Dim cell As Word.Cell = table.Cell(rowIndex, 1) '目錄中test.txt文件中的結(jié)尾不能有多余的空行，不然會提示超出索引范圍而出現(xiàn)錯誤 cell.Range.Text = r rowIndex += 1 Next End SubEnd Module
續(xù)3：將內(nèi)容倒著讀取出來
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 '創(chuàng)建Word實(shí)例程序 * Dim myWord As Word.Document = App.Documents.Add() '創(chuàng)建word文檔 * App.Visible = True '顯示W(wǎng)ord程序 * 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 '讀取行沒有到結(jié)尾 rowList.Add(row) '將所讀取的一行文本存儲在列表集對象中 row = reader.ReadLine() '自帶逐行讀取的功能 End While Dim range As Word.Range = myWord.Range() '定義文檔單元格 Dim rowIndex As Integer = 1 For Each r As String In rowList range = myWord.Range(0, 0) 'range方法的第一個參數(shù)是開頭，第二個參數(shù)是結(jié)尾，這里指第幾段，插入值，插入的值是倒著排列的 range.Text = r & Chr(10) ' 注：chr(10) & chr(13)中，chr(10) 換行符，而 chr(13)回車，那么chr(10) & chr(13)就是既換行了又回車了 rowIndex = rowIndex + 1 Next End Sub
續(xù)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 '創(chuàng)建Word實(shí)例程序 * Dim myWord As Word.Document = App.Documents.Add() '創(chuàng)建word文檔 * App.Visible = True '顯示W(wǎng)ord程序 * 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 '讀取行沒有到結(jié)尾 rowList.Add(row) '將所讀取的一行文本存儲在列表集對象中 row = reader.ReadLine() '自帶逐行讀取的功能 End While Dim range As Word.Range = myWord.Paragraphs.Add().Range() '定義文檔單元格 Dim rowIndex As Integer = 1 For Each r As String In rowList range = myWord.Paragraphs(rowIndex).Range() range.Text = r & Chr(10) ' 注：chr(10) & chr(13)中，chr(10) 換行符，而 chr(13)回車，那么chr(10) & chr(13)就是既換行了又回車了 rowIndex = rowIndex + 1 Next End Sub
實(shí)例2.4 Outlook外接程序 書本第40頁
程序清單2.4 Outlook外接程序項(xiàng)目中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 SubEnd Class
實(shí)例代碼：
Public Class ThisAddIn Private Sub ThisAddIn_Startup() Handles Me.Startup End Sub Private Sub ThisAddIn_Shutdown() Handles Me.Shutdown End SubEnd Class
實(shí)例2.5 VSTO Outlook 外接程序 書本41頁
注：添加引用”System.Windows.Forms”
程序清單2.5 VSTO Outlook 外接程序，用于處理Item事件和檢查收件人數(shù)是否超過25
Imports Outlook = Microsoft.Office.Interop.OutlookPublic 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 > 25 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 SubEnd Class
實(shí)例2.6 VSTO Excel工作簿自定義機(jī)制 書本42頁
程序清單 2.6 VSTO Excel工作簿自定義機(jī)制
Imports Excel = Microsoft.Office.Interop.ExcelImports Office = Microsoft.Office.CorePublic 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 SubEnd Class
實(shí)例代碼：
Imports Excel = Microsoft.Office.Interop.ExcelImports Office = Microsoft.Office.CorePublic Class Sheet1 Private Sub Sheet1_Startup(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Startup ' 初始化入口點(diǎn) ' 創(chuàng)建文檔代碼時將首次執(zhí)行此代碼 ' 這里的上下環(huán)境是sheet1 MsgBox("Code behind the document running.") MsgBox(String.Format("{0} is the sheet name.", Me.Name)) End SubEnd Class
實(shí)例效果：
實(shí)例2.7 VSTO自定義機(jī)制：在文檔操作任務(wù)面板中添加按鈕控件以及將ListObject控件與DataTable進(jìn)行數(shù)據(jù)綁定 書本46頁
程序清單 2.7 VSTO自定義機(jī)制：在文檔操作任務(wù)面板中添加按鈕控件以及將ListObject控件與DataTable進(jìn)行數(shù)據(jù)綁定
Imports Excel = Microsoft.Office.Interop.ExcelImports Office = Microsoft.Office.CorePublic 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 = 0 To 3 table.Columns.Add("Col" & i.ToString()) Next For j As Integer = 0 To 19 table.Rows.Add(r.NextDouble(), r.NextDouble(), _ r.NextDouble(), r.NextDouble()) Next List1.DataSource = table End SubEnd Class
實(shí)例代碼：
Imports Excel = Microsoft.Office.Interop.ExcelImports Office = Microsoft.Office.CorePublic Class Sheet1 Private WithEvents myButton As New Button 'WithEvents的意思是告知VB編譯器這是一個可以觸發(fā)事件對象 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表示右側(cè)的文檔操作面板 End Sub Private Sub myButton_Click(ByVal sender As Object, ByVal e As EventArgs) Handles myButton.Click List1.DataSource = Nothing '設(shè)置數(shù)據(jù)源為空 table = New DataTable '定義數(shù)據(jù)表用關(guān)鍵字new，實(shí)質(zhì)就是一個實(shí)例對象 Dim r As New Random '定義隨機(jī)數(shù)用new關(guān)鍵字，實(shí)質(zhì)就是一實(shí)例對象 For i As Integer = 0 To 3 table.Columns.Add("Col" & i.ToString()) Next For j As Integer = 0 To 19 table.Rows.Add(r.NextDouble(), r.NextDouble(), r.NextDouble(), r.NextDouble())'NextDouble大于或等于 0且小于 1 的隨機(jī)浮點(diǎn)數(shù) Next List1.DataSource = table End SubEnd Class
代碼另一種寫法：
Imports Excel = Microsoft.Office.Interop.ExcelImports Office = Microsoft.Office.CorePublic 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列,冒號接續(xù) For j As Integer = 0 To 19 table.Rows.Add(r.NextDouble(), r.NextDouble(), r.NextDouble(), r.NextDouble()) '生成19行4列的數(shù)據(jù) Next List1.DataSource = table '將table中的值作為數(shù)據(jù)源
Dim str As String
str = table.Rows(2).Item(1).ToString() 'Rows(2).Item(1)表示第3行第2列，索引從0開始
MsgBox(str)
End SubEnd Class
實(shí)例效果：
