摘  要:  講述了在建立數據庫和SQL查詢的基礎上，使用程序方法訪問數據庫中的表和查詢，同時引用電子表格文件作為對象，對其進行自動填寫的思路和實現方法。將該方法與電子表格內嵌公式和程序的傳統方法進行了對比。最后使用該方法實現了對機場氣候表的自動填寫。

　　關鍵詞： 電子表格； 數據庫訪問； Excel引用； 民航氣象

　　制作填寫電子表格在今天已是一項非常常見的工作了。很多時候，都需要將手頭的數據統計匯總填寫成表格樣式以供閱讀和報告。為了完成復雜一些的功能，需要在電子表格中嵌入公式和程序代碼，以減少人工的工作量，并減少錯誤的發生，提高工作效率[1-2]。鑒于Microsoft Excel電子表格軟件的強大功能，它基本成為電子表格的代名詞,因此,以下主要以Excel為例進行討論。

　　然而，在Excel表格中嵌入公式和程序代碼(經常也被稱為宏)會帶來一些其他的問題。例如運行效率不高，在代碼比較多時，會使電子表格文件變得很大，不利于發布和交換等。

　　1 電子表格中嵌入公式和程序代碼的不足

　　內嵌公式和程序代碼的電子表格有它的好處，首先不需要安裝任何軟件,交換文件的同時就相當于交換了程序。然而,表數量多、代碼量較大時，就會存在下面的不足：(1) 電子表格文件變大

　　雖然簡單的代碼不會造成這種情況，或者說無關緊要，但當需要完成的計算和填寫代碼很多(往往表格只有幾百千字節的文件，加上代碼后就會有幾兆字節)時，對交換和保存這類表格文件顯然不利。

　　(2) 嵌入代碼不利于使用

　　當電子表格制作完成，就會將其文件用于發布和交換。這時大多數使用文件的人都是非專業人員，一旦打開文件時提示是否啟用宏時，他們往往會不知所措；另一方面，啟用內嵌代碼可能被人利用，增加惡意代碼，造成真正對計算機的危害；最后，內嵌的公式往往效率不高，其計算結果不易使用。

　　(3) 開發、調試困難

　　少量的程序代碼不會存在這樣的問題，但當代碼很多時，就會發現Excel內嵌的那個VBA功能不夠，調試也不方便，完全無法與Visual Studio這樣的開發工具相提并論，使得效率大大下降，今后的維護也十分困難。

　　2 另一種填寫電子表格的思路

　　大多數時，發布和交換電子表格只是作為一種可視化的資料保存或查閱，這時內嵌的公式和程序代碼就成了負擔。因此，考慮另一種思路，將電子表格只是作為模板，不使用內嵌的公式或程序代碼進行統計計算，在數據庫中使用SQL查詢進行統計、計算，另外編寫程序訪問數據庫中的查詢，在程序中引用電子表格文件作為對象，將查詢結果填寫到電子表格文件中。兩種方式的對比如圖1所示。

　　3 電子表格自動填寫的實現

　　如上所述，需要分三步實現，首先要制作最終的輸出表格，之后需要建立好數據庫，并在數據庫中建立SQL查詢以完成主要的統計計算；最后編寫程序訪問數據庫，并引用電子表格文件進行自動填寫。

　　3.1 模板中表格一致性的程序實現

　　首先設計表格的樣式，如果有類似的表格只需要復制后稍加修改即可。然而，經驗表明，往往在調試程序時，還需要更改表格中的樣式，這時又要進行前述的重復工作，這種工作在程序開發和調試過程中還要反復進行。因此，宜使用程序來對表格的樣式進行修改。

　　以Visual Basic 2008為例，要訪問電子表格文件，需在工程中添加引用COM中的Microsoft Excel,并選中命名空間Microsoft.Office.Interop(若存在的話)。示例1過程更改電子表格文件中的全部表顯示大小為100%,更改每個表第一行單元格格式為合并居中并自動縮小字體填充，顯示一位小數。

　　示例代碼1:

　　Sub RegSheets()

　　Dim a As New Excel.Application

　　Dim b As Excel.Workbook = a.Workbooks.Open

　　(“電子表格的文件名”)

　　For Each sht As Excel.Worksheet In b.Sheets

　　sht.Activate()

　　‘不能省略，Excel僅對活動工作表操作

　　a.ActiveWindow.Zoom = 100

　　s= "A1:" & Chr(64 + sht.UsedRange.Columns.

　　Count) & "1"

　　a.Range(s).Select()

　　With a.Selection

　　.HorizontalAlignment = xlCenter   ‘水平居中

　　.VerticalAlignment = xlCenter      ‘垂直居中

　　.WrapText = False             ‘取消自動換行

　　.ShrinkToFit = True

　　‘設置為自動縮小字體填滿

　　.NumberFormat = ”0.0”     ‘顯示一位小數

　　.MergeCells = True‘合并第一行使用的單元格

　　End With

　　Next sht

　　b.SaveCopyAs("副本文件名")

　　b.Close(False)

　　a.Quit()

　　End Sub

　　只需對以上過程稍加修改并調用一次就可以完成其他所需格式修改，當需要對大量表格更改格式時，這樣比人工修改格式大大提高了效率，減少了錯誤。

　　3.2 數據庫設計

　　原始數據不僅僅是數字，還包括文本記錄，這種混合數字和文本的數據在數據庫中是很難使用查詢進行統計的，而且會大大降低查詢的效率。因此，首先要考慮對原始數據進行合理的解析，其目的在于將文本中數字混合的原始數據轉換為數據庫中的數值記錄了[3]。之后將統計計算以SQL命令也放在數據庫中，這樣可以充分利用數據庫的優勢，減少程序代碼的開發和調試[4]。以Microsoft Access數據庫為例，若統計一個簡單的氣溫記錄表(年，月，日，小時，T)可使用一個查詢：

　　SELECT 月, Avg(T) AS 月平均, Max(T) AS 月最高氣溫, Min(T) AS 月最低氣溫

　　FROM 氣溫記錄表

　　WHERE 年≥起始年and年≤終止年

　　GROUP BY 月

　　將這一查詢命名為sMonthT，則其關系模式為：

　　sMonthT（月, 月平均, 月最高氣溫, 月最低氣溫）

　　該查詢統計起始年到終止年間的月氣溫平均、月最大、月最小值。顯然在程序中調用該查詢比在程序代碼中計算要簡單得多。

　　3.3 電子表格的自動填寫

　　前面已經設計好了表格和數據庫，為在程序代碼中填寫表格，需要調用數據庫和電子表格文件，示例2將上面數據庫查詢結果各月份的最高氣溫填寫在不同表的第一行、第一列中。

　　示例代碼2：

　　Sub Fill(ByVal DBFilename As String, ByVal b As Excel.Workbook, ByVal YearFrom As Long, ByVal YearTo As Long)

　　Dim cn As New Data.OleDb.OleDbConnection

　　Dim dc As New Data.OleDb.OleDbCommand

　　Dim m As String

　　Dim t As single

　　cn.ConnectionString = ("Provider=Microsoft.Jet.OLEDB.

　　4.0;Data Source=" & DBFilename)

　　cn.Open()

　　dc.CommandText = "SELECT * FROM sMonthT"

　　dc.Connection = cn

　　dc.Parameters.Add("起始年", Data.OleDb.OleDbType.

　　Integer, 4).Value = YearFrom

　　dc.Parameters.Add("終止年", Data.OleDb.OleDbType.

　　Integer, 4).Value = YearTo

　　Dim dr As OleDb.OleDbDataReader

　　dr = dc.ExecuteReader()

　　While dr.Read()

　　m= dr.GetValue(dr.GetOrdinal("月")).ToString

　　t = dr.GetValue(dr.GetOrdinal("月最高氣溫"))

　　b.Worksheets(m & "月").Cells(1, 1).value = t

　　‘使用表名訪問不同的表

　　End While

　　dr.Close()

　　cn.Close()

　　End Sub

　　示例中，使用數據庫OleDbConnection對象連接數據庫，使用OleDbCommand對象訪問數據庫中的查詢，使用OleDbDataReader對象讀取查詢結果。由于前面數據庫查詢中有類似“起始年”、“終止年”這樣的參數，因此需要在OleDbCommand對象中添加Parameter。對于多個表，可以使用表名稱來區分。

　　4 應用與實踐

　　在實際中遇到了機場氣候統計的問題，按照要求氣候統計結果需要輸出26類106張表，僅這些表形成的電子表格文件大小就約為1.2 MB，而完成數據統計填寫的全部程序源代碼合計約有2 MB。若將代碼嵌入電子表格，得到的電子表格文件將會超過3 MB。

　　通過上面的思路和方式，將程序代碼獨立出來，另外在數據庫中建立了19個表和202個查詢用于統計計算。由于程序代碼基于.NET Framework，經過編譯后的可執行文件大小僅有1 MB左右。這樣使得填寫輸出的電子表格文件大大減小，便于交換和使用；沒有內嵌代碼的文件，提高了安全性。另一方面，編譯過的代碼顯然比內嵌公式和源代碼有更高的運行效率，獨立的代碼也便于后期的維護工作。最后，建立在數據庫內的查詢非常容易使用，為今后的其他應用奠定了基礎。

　　參考文獻

　　[1] 張慶豐.三種Excel訪問oracle數據庫的方法比較[J].計算機系統應用，2004(12):64-66.

　　[2] 楊振宇,楊海智,楊信東.用Excel中的VBA編寫卡方測算相關程序[J].微型機與應用， 2012,31(19):18-20.

　　[3] 韓金濤.民航氣象觀測數據庫的建立及應用[J]. 民航科技,2011,147(5):110-112.

　　[4] ULLMAN J D, WIDOM J著.數據庫系統基礎教程[M].史嘉權,譯.北京：清華大學出版社，1999.