使用c 開發excel插件 (第2章深入excel)

關於excel的功能相信大家都比較了解，而且介紹如何使用excel的書籍在市場也是隨處可見，這裡就不再多說了。這裡要著重深入了解一下Excel的內部組織結構，常用數據結構等等。2.1、Excel組織結構Excel 中文檔就是工作簿，每個工作簿中又包含多個工作表，圖表以及宏模塊。工作表是保存數據的第一級結構，每個工作表是一個二維表格，其中中包含許216行28列共65536個單元格，工作表中還包含公式，圖表等等。而單元格中又含有格式，字體，顏色等信息。任何一個工作簿都可以作為一個模板（*.xlt）保存起來，以作為以後新建的工作簿的樣板。模板中可以包含以下一些信息：u 單元格格式u 自定義菜單，宏，和工具條u 自定義的每個工作簿中的工作表的數量和類型u 頁碼格式u 行和列的風格u 自定義文本，日期，公式，圖表等等通常默認的工作簿模板為Book.xlt，默認的工作表模板是Sheet.xlt。你也可以把一個含有宏的工作簿保存為Excel載入宏（*.xla），以便為你的Excel增加功能。下面是Excel各部分的結構圖：Excel應用程序工作簿（Workbook）菜單條工具條工作表(Worksheet)菜單工具條按鈕單元格區域(Range)和單個單元格(Cell)圖標以及其他非excel對象控制項對象以及控制項按鈕菜單項子菜單項但是這個表對我們實現Excel插件並沒有多大用處，我們需要知道更詳細的關於Excel內部對象之間的關係。Excel 5中有128個不同的對象，每個對象都代表著excel的一個獨特的特性，每個對象都有唯一的屬性和方法，通過vba或c++可以調用這些屬性和方法。屬性代表著對象的某個特性，可以獲得某個屬性，也可以改變部分屬性。方法則是這個對象的功能，通過調用對象的方法，可以執行一些特定的操作。比如刪除工作表等等。在excel中對象有兩種不同的形式，一種是單個對象，它代表唯一的一個實例，比如Application對象只代表一個excel實例。另一種是對象集，excel中的許多對象都是代表一個集合，比如Wroksheets對象就代表工作表集合。你可以直接操作對象集來操作集合中的所有成員，也可以通過索引編號或名字操作集合中的某個對象。Excel中的對象並不都存在於同一級中，在引用時需要逐級引用。所以對於開發人員來說牢記它們之間的引用關係是非常有必要的。在最頂層是Application對象，即Excel本身，其次是第二層，這一層中包含Workbook、Addin、Debug、Dialog、Menubar、Toolbar、Window共7個對象，這幾個對象都比較直觀，而且都體現在Excel界面上了，其餘對象分別位於它們之下。在下面的圖例中可以看到所有對象之間的層次關係。

看到這個圖你是否覺得不夠128個對象呢，其實這個圖只是列出了所有典型的對象，對於有些像Font，Bonder等對象在大多對象中都存在，就沒有全列出來。通過這個引用關係圖我們就可以知道如何引用其中的某個對象了。例如我們要通過Range對象引用第一個單元格，並為它賦值，可以寫成這樣：Application.Workbooks(1).Worksheets(1).Range(「A1」).Value = 1;意思是為第1個工作簿的第1個工作表的「A1」單元格賦值為1。我了更直觀的說明對象，我們在這一章中的例子使用VBA代碼格式。2.1.1頂層對象（Application Object）在Excel中最頂層只有包含一個對象，那就是Application Object，這個對象就是指Excel應用程序本身，所以也可以認為Application對象的屬性代表著整個excel程序的環境，改變了它的屬性就改變了其他對象所處的環境。通過這個對象就可以改變或獲得Excel應用程序本身的屬性，它自身有100多個屬性，約60個方法。我們只簡單的介紹幾個以說明如何使用Application對象。常用屬性：Capiton屬性：用於設置Excel標題欄的內容，通常標題欄里顯示當前編輯的文件的文件名等，通過這個屬性就可以自定義標題欄的內容。Application.Capiton = 「My Custom Application」DisplayAlerts屬性：如果DisplayAlerts的屬性設置為True，則顯示警告信息，否則不顯示。Application.DisplayAlerts = FalsePath屬性：Path屬性中保存著Excel的安裝路徑，如果你要查找Excel安裝目錄下的某個文件時，就可以先調用它來獲得正確的安裝路徑。ExcelPath = Applicaton.PathScreenUpdating屬性：用於設置在宏執行時是否刷新Excel窗口的內容，如果設置為True則刷新，否則不刷新。默認值為True。Application.ScreenUpdating = FalseWindowState屬性：用來設置Excel窗口的顯示狀態，它可以接收以下三種狀態值：xlNormal 普通狀態，通常為默認值xlMaximized 窗口最大化。xlMinimized 窗口最小化。Application.WindowState = xlNormal常用方法：Calculate：這個方法強制所有打開的工作簿中的所有工作表中的公式都重新計算。不用提供任何參數。Application.CalculateHelp：打開指定的Help文件，並顯示指定的幫助內容，需要傳遞兩個參數：第一個為幫助文件路徑，第二個為要顯示的幫助的ID值。Application.Help helpFile:=」Mainxl.hlp」,helpContextId:=100Quit：調用這個函數將關閉Excel應用程序，但是如果DisplayAlerts屬性被設置為False則在退出時不提示用戶保存文件。此函數無參數。Application.QuitRun：通過這個函數可以執行自定義的宏，它的參數數量隨所執行的宏的參數變化，第一個參數是宏名，其餘為這個宏所需要的參數。Application.Run macro:=」OldMacro」,arg1:=100,arg2:=」Revenue」Application.WindowState = xlNormal常用方法：Calculate：這個方法強制所有打開的工作簿中的所有工作表中的公式都重新計算。不用提供任何參數。Application.CalculateHelp：打開指定的Help文件，並顯示指定的幫助內容，需要傳遞兩個參數：第一個為幫助文件路徑，第二個為要顯示的幫助的ID值。Application.Help helpFile:=」Mainxl.hlp」,helpContextId:=100Quit：調用這個函數將關閉Excel應用程序，但是如果DisplayAlerts屬性被設置為False則在退出時不提示用戶保存文件。此函數無參數。Application.QuitRun：通過這個函數可以執行自定義的宏，它的參數數量隨所執行的宏的參數變化，第一個參數是宏名，其餘為這個宏所需要的參數。Application.Run macro:=」OldMacro」,arg1:=100,arg2:=」Revenue」2.1.2、Wrokbooks對象Workbooks對象處在Application對象之下，Workbook是Excel的文檔，也可以看作是格容器，它裡面包含了多個用於編輯的工作表。Workbook保存在磁碟上有兩種文件格式，一個是.xls文件,就是通常的excel保存的文件。一個是xla文件格式，這種文件是excel載入宏文件，它裡面可以包含VBA程序。.xla文件容許你的程序代碼和用戶是分離的，這樣可以避免用戶無意的修改。常用屬性：Name屬性：Name屬性是指workbook的名字，通常這個屬性保存著這個工作簿的文件名，而且這個屬性不容許直接修改，如果你要改變它，必須用SaveAs把工作簿保存成你想要的名字。WrokbookNmae = ThisWorkbook.NamePath屬性：這個屬性保存著工作簿的文件路徑。WorkbookPath = ActiveWorkbook.PathSaved屬性：Saved屬性表示對工作簿所做的修改是否都被保存了，如果為True則所有修改都被保存了。If Not(ActiveWorkbook.Saved) ThenActiveWorkbook.SaveEnd If常用方法：Activate：激活Workbook窗口。Workbooks（「book1.xls」）.ActivateClose：關閉工作簿，參數如下：saveChanges 如果為True則在關閉時保存，否則關閉時不保存。fileName 要保存的的工作簿的目的文件名。routeWorkbook 如果為True則在關閉前發送郵件。ActiveWorkbook.Close saveChanges：=FalseProtect：設置工作簿為保護狀態，以使用戶不能修改。參數含義如下：Password 工作簿的密碼。Structure 如果為True則工作簿的結構受到保護。Windows 如果為True則工作簿窗口受到保護。Workbooks（1）.Protect 「password」，True，TrueSave：保存工作簿，參數是文件名。ActiveWrokbook.Save2.1.3、Worksheet對象Worksheet是個功能強大的2維表格，每個表格都可以接收任意類型的數據，而且Worksheet提供了大約400個函數，通過這些數據可以非常快速的計算各種數據，包括存儲，計算，查找，分析等等，所以許多人把excel當作小型的資料庫使用。常用屬性：Index：表示工作表在所有工作表中的位置索引。ActiveSheet.Name = 「WkSheet」 & ActiveSheet.IndexName：表示工作表的名字。Worksheets（1）.Name = 「My Worksheet」UsedRange：表示工作表中的已使用的單元格的範圍。Dim Range1 As RangeSet Range1 = Worksheets（1）.UsedRangeVisible：表示工作表是否顯示。如果為True則顯示，否則False。Worksheets（「Main」）.Visible = xlVeryHidden常用方法：Activate：激活工作表Worksheets（「My Worksheet」）.ActivateCalculate：強迫工作表中所有單元格數據都從新計算。Worksheets（1）.CalculateDelete：刪除工作表。Worksheets（「My Worksheet」）.DeleteProtect：設置工作表為保護狀態。參數如下：Password 密碼字元串。drawingObjects 如果為True，所有圖形對象受保護。Contents 如果為True則單元格受保護。Scenarios2.1.4、Range對象Range對象用來表示工作表中的一個或多個單元格，它是單元格的集合。通過Range對象可以訪問超過400多個excel函數，也可以調用vba函數，甚至可以建立與其他單元格之間的關係。所以有了Range對象，我們就可以建立功能強大的分析程序。常用屬性：Count：返回Range對象中所包含的單元格數量。NumOfCells = Worksheets（1）.UsedRange.CountName：表示一個Range對象的名字。Worksheet（1）.Range(「A1」).Name = 「FirstCell」Value：表示Range對象的值，如果Range對象包含多個單元格，則此屬性表示的是個數組。Worksheets（1）.Range(「FirstCell」).Value = 1常用方法：Calculate：強制Range對象中包含的所有單元格重新計算。Worksheet（1）.Range(「A21:F20」).CalculateClearContents：清除Range對象中所有單元格中的值。Worksheets（1）.Range(「A1:F20」).ClearContentsCopy：複製Range對象的值到剪切板或到其他Range對象中。參數：Destination 目的對象，如果不設置目的參數，則複製到剪切板。Worksheets（1）.Range(「A1」).Copy2.2、引用單元格或區域引用的作用在於標識工作表上的單元格或單元格區域，並指明公司中所使用的數據的位置。通過引用，可以在公式中使用不同部分的數據。通過引用，可以在公式中使用工作表不同部分的數據，或者在多個公式中使用同一單元格的數據。還可以引用同一工作簿不同工作表的單元格、不同工作簿的單元格、甚至其它引用程序中的數據。引用不同工作簿中的單元格稱為外部引用。引用其它程序中的數據稱為遠程引用。在excel中有兩種引用樣式：A1和R1C1引用樣式。A1引用樣式：在默認情況下，Excel使用A1引用類型。這種類型引用字母標誌列（從A到IV，共256列）和數字標誌行（從1到65536）。這些字母和數字被稱為行和列的標題。如果要引用單元格，須順序輸入列字母和數字。例如，D50引用了列D和行50交叉處的單元格。如果要引用單元格區域，須輸入區域左上角單元格的引用、冒號（：）和區域右下角單元格的引用。下面是引用的示例。圖2.2 A下面我們列出了常見的幾種使用A1引用的例子要引用的單元格引用方法在列A和行10中的單元格A10從A列10行到20行的單元格區域A10:A20從B列15行到E列的單元格區域B15:E15第5行中所有單元格5:5從第5行到第10行的所有單元格5:10第H列的所有單元格H:H從H列到J列的所有單元格H:J從A列第10行到E列第20行的單元格區域A10:E20R1C1引用樣式：即用行數和列數來表示單元格，R代表行（Row），C代表列（Column）。R1C1引用樣式對於計算位於宏內的行和列非常有用。而且當錄製宏時，Excel也使用R1C1應用樣式錄製宏命令，而不是使用A1引用樣式。但是這並不代表A1樣式不能完全代表R1C1樣式，R1C1能表示的A1樣式同樣也能表示。只是，在宏里使用R1C1更容易些。對單元格的應用分為絕對引用和相對引用，在創建公式時，單元格或單元格區域的引用通常是相對包含公式的單元格的相對位置，並且在複製包含相對引用的公式時，Excel會自動調整公式中的引用，以便引用相對於當前公式位置的其它單元格。例如在圖2.2A中，單元格F2中包含公式=SUM（B2:E2），當把F2單元格複製到F3單元格時，F3單元格中的公式被Excel修改成=SUM（B3:E3）。但是如果我們不希望Excel調整引用，就可以使用絕對引用。絕對引用是在引用前加上$符號，比如$B$3表示對B3單元格的絕對引用。這個寫法看起來挺麻煩，幸好Excel可以通過F4鍵使公式在相對引用和絕對引用之間切換。如果你要切換引用就選中包含公式的單元格，並在公式編輯欄里選中你要更改的引用，然後按F4鍵即可。Excel會以一定順序進行切換：絕對列與絕對行（例如，$C$1）；相對列與絕對行（C$1）；絕對列與相對行（$C1）；相對列與相對行（C1）.使用上面所說的引用方式固然可以表示任意單元格和區域。但在實際中有時我們需要更直觀的引用方式，比如在上面圖2.2A的表格中如果我們能夠用=SUM（類型1）來計算所有「類型1」的產品的數量，豈不是比=SUM（B2:E2）更容易理解。這就是標誌和名稱，工作表每列的首行和每行的最左列通常含有標誌以描述數據。在公式中可以使用這些標誌來引用相應的數據。當然你也可以不用工作表中的標誌，而用描述性的名稱來代表單元格、單元格區域、公式和常量。在默認情況下，excel不會識別公式中的標誌。如果要在公式中使用標誌，點擊菜單「工具/選項」，在「重新計算」選項卡中選中「接受公式標誌」複選框。注意：標誌默認使用的是相對引用，而名稱使用的絕對引用。2.3、單元格中的數據類型從用戶角度來看，excel單元格可以接受的數據類型不外乎以下幾種：u 數字類型，包括整數，小數等等u 布爾類型，包括TRUE和FALSEu 字元串u Excel錯誤代碼，包括##### 錯誤，#VALUE! 錯誤，#DIV/O! 錯誤，#NAME? 錯誤，#N/A 錯誤，#REF! 錯誤，#NUM! 錯誤，#NULL! 錯誤等。u 由上面類型組成的一個數組。有時excel的函數在計算時會返回一些其它數據類型，比如單元格的引用之類，但在顯示時單元格仍會把它轉換成上面的幾種類型。當然你也可以通過設置單元格的數據格式來改變轉換方式。例如，單元格中原數據為6.8，通過設置，把這個單元格格式改為整型數值，則單元格中顯示為7。下面表格中顯示了excel單元格中能表示的數據類型和其範圍。數字類型浮點型數據範圍：1.0×10-307 ≤ |X | ＜ 1.0×10+308布爾類型TRUE；FALSE字元串最大長度是32767 = 215-1個ASCII碼字元，在單元格中只能顯示1024個字元，但在公式編輯欄里能輸入32767個字元。要注意的是C API限制是255個字元。Excel錯誤代碼#####，#VALUE!，#DIV/0!，#NAME?，#N/A，#REF!，#NUM!，#NULL!數組又上面幾種類型組成的數組當你使用公式可能會出現錯誤，此時Excel會顯示一個錯誤代碼，了解這些代碼的含義有助於查找錯誤的原因，下面我們列出了這些代碼的含義：錯誤代碼含義及可能原因#####列寬不足，無法顯示數據。#VALUE!值錯誤，使用的參數或操作數錯誤時，導致公式無法正確計算。#DIV/0！除零錯誤，公式中含有除數為0的操作。#NAME?無效名稱，Excel無法識別公式中的符號。#N/A值不可用，公式無法使用傳遞給它的數值。#REF!無效的單元格引用，公式中引用的單元格不存在。#NUM!數字錯誤，公式中使用了無效數字值。#NULL!空值，公式中交叉使用了不允許交叉在一起的區域2.4、公式中對單元格的引用Excel會根據實際需要把對單元格的引用轉換成對應單元格中的數據。比如如果你在一個單元格中輸入公式=SUM（A1，B2），A1中有數值123，而B2中有字元串456，Excel將會把對A1單元格的引用轉換成數值123，而把對B1中字元串的引用也轉換成數值456，然後的得到公式的值579。但有時不是這樣，對於公式=ROW（B9），則返回的是B9單元格的行號。公式中也可以直接使用單元格區域作為參數，比如=SUM（A1:C3），對單元格的引用也有多種方式，在單元格引用概念中有一個很重要的概念就是三維引用，這種引用在分析同一工作簿中多張工作表中的數據時就顯得非常有用，三維引用工作表名稱，單元格或區域引用。下面的表格將列出常見的幾種飲用方式：引用單元格/區域含義=SUM(A1)對A1單元格中的值求和。=SUM(A1,B2)對A1和B2兩個單元格中值求和。=SUM(A1:C3)對單元格A1到C3區域中的單元格中的值求和。=SUM([Book1.xls].Sheet1!Name)對工作簿Book1中工作表Sheet1的名字為Name的區域求和，這就是所謂的三維引用。注意：1、在excel中引用含有非數值的單元格時，值不被轉換，且當作0處理。例如A1中是123，B2中是「456」則公式=SUM(A1，B2)的值為123，但是如果寫成=SUM(A1，「456」)，值就成了579了。2、當引用的其它工作簿是關閉的，則在引用時必須使用工作簿的完整路徑，且如果名字中含有空格則應用單引號括起來。例如：="C:/Documents and Settings/wxy/My Documents/[testBook1.xls]Sheet1"!A12.5、在公式中使用運算符Excel中定義了常用的運算符，通過這些運算符可以直接處理一些簡單的計算，也可以把它們和其他公式結合起來處理更複雜的業務。和我們在數學中用到的運算符號一樣，在excel中運算符同樣有優先順序別，也可以通過圓括弧改變運算符的優先順序，但在excel中只能使用圓括弧。Excel支持的運算符和優先順序定義如下：假設A1中是15，B2中是21，在C3中輸入示例符號名稱優先順序示例結果+加號5=A1+B236-減號/負號5/1=A1-B2-6*乘號4=A1*B2315/除號4=A1/B20.714286%百分號2=A1%0.15&字元串連接符6=A1&B21521^求冪符號3=A1^2225=等號(邏輯比較)7=A1=B2FALSE>大於號7=A1>B2FALSE<小於號7=A1<B2TRUE>=大於或等於號7=A1>=B2FALSE<=小於或等於號7=A1<=B2TRUE<>不等於號7=A1<>B2TRUE
推薦閱讀：