摘 要: 講述了在建立數(shù)據(jù)庫(kù)和SQL查詢的基礎(chǔ)上，使用程序方法訪問數(shù)據(jù)庫(kù)中的表和查詢，同時(shí)引用電子表格文件作為對(duì)象，對(duì)其進(jìn)行自動(dòng)填寫的思路和實(shí)現(xiàn)方法。將該方法與電子表格內(nèi)嵌公式和程序的傳統(tǒng)方法進(jìn)行了對(duì)比。最后使用該方法實(shí)現(xiàn)了對(duì)機(jī)場(chǎng)氣候表的自動(dòng)填寫。
關(guān)鍵詞： 電子表格； 數(shù)據(jù)庫(kù)訪問； Excel引用； 民航氣象
制作填寫電子表格在今天已是一項(xiàng)非常常見的工作了。很多時(shí)候，都需要將手頭的數(shù)據(jù)統(tǒng)計(jì)匯總填寫成表格樣式以供閱讀和報(bào)告。為了完成復(fù)雜一些的功能，需要在電子表格中嵌入公式和程序代碼，以減少人工的工作量，并減少錯(cuò)誤的發(fā)生，提高工作效率[1-2]。鑒于Microsoft Excel電子表格軟件的強(qiáng)大功能，它基本成為電子表格的代名詞,因此,以下主要以Excel為例進(jìn)行討論。
然而，在Excel表格中嵌入公式和程序代碼(經(jīng)常也被稱為宏)會(huì)帶來一些其他的問題。例如運(yùn)行效率不高，在代碼比較多時(shí)，會(huì)使電子表格文件變得很大，不利于發(fā)布和交換等。
1 電子表格中嵌入公式和程序代碼的不足
內(nèi)嵌公式和程序代碼的電子表格有它的好處，首先不需要安裝任何軟件,交換文件的同時(shí)就相當(dāng)于交換了程序。然而,表數(shù)量多、代碼量較大時(shí)，就會(huì)存在下面的不足：(1) 電子表格文件變大
雖然簡(jiǎn)單的代碼不會(huì)造成這種情況，或者說無關(guān)緊要，但當(dāng)需要完成的計(jì)算和填寫代碼很多(往往表格只有幾百千字節(jié)的文件，加上代碼后就會(huì)有幾兆字節(jié))時(shí)，對(duì)交換和保存這類表格文件顯然不利。
(2) 嵌入代碼不利于使用
當(dāng)電子表格制作完成，就會(huì)將其文件用于發(fā)布和交換。這時(shí)大多數(shù)使用文件的人都是非專業(yè)人員，一旦打開文件時(shí)提示是否啟用宏時(shí)，他們往往會(huì)不知所措；另一方面，啟用內(nèi)嵌代碼可能被人利用，增加惡意代碼，造成真正對(duì)計(jì)算機(jī)的危害；最后，內(nèi)嵌的公式往往效率不高，其計(jì)算結(jié)果不易使用。
(3) 開發(fā)、調(diào)試?yán)щy
少量的程序代碼不會(huì)存在這樣的問題，但當(dāng)代碼很多時(shí)，就會(huì)發(fā)現(xiàn)Excel內(nèi)嵌的那個(gè)VBA功能不夠，調(diào)試也不方便，完全無法與Visual Studio這樣的開發(fā)工具相提并論，使得效率大大下降，今后的維護(hù)也十分困難。
2 另一種填寫電子表格的思路
大多數(shù)時(shí)，發(fā)布和交換電子表格只是作為一種可視化的資料保存或查閱，這時(shí)內(nèi)嵌的公式和程序代碼就成了負(fù)擔(dān)。因此，考慮另一種思路，將電子表格只是作為模板，不使用內(nèi)嵌的公式或程序代碼進(jìn)行統(tǒng)計(jì)計(jì)算，在數(shù)據(jù)庫(kù)中使用SQL查詢進(jìn)行統(tǒng)計(jì)、計(jì)算，另外編寫程序訪問數(shù)據(jù)庫(kù)中的查詢，在程序中引用電子表格文件作為對(duì)象，將查詢結(jié)果填寫到電子表格文件中。兩種方式的對(duì)比如圖1所示。
3 電子表格自動(dòng)填寫的實(shí)現(xiàn)
如上所述，需要分三步實(shí)現(xiàn)，首先要制作最終的輸出表格，之后需要建立好數(shù)據(jù)庫(kù)，并在數(shù)據(jù)庫(kù)中建立SQL查詢以完成主要的統(tǒng)計(jì)計(jì)算；最后編寫程序訪問數(shù)據(jù)庫(kù)，并引用電子表格文件進(jìn)行自動(dòng)填寫。
3.1 模板中表格一致性的程序?qū)崿F(xiàn)
首先設(shè)計(jì)表格的樣式，如果有類似的表格只需要復(fù)制后稍加修改即可。然而，經(jīng)驗(yàn)表明，往往在調(diào)試程序時(shí)，還需要更改表格中的樣式，這時(shí)又要進(jìn)行前述的重復(fù)工作，這種工作在程序開發(fā)和調(diào)試過程中還要反復(fù)進(jìn)行。因此，宜使用程序來對(duì)表格的樣式進(jìn)行修改。
以Visual Basic 2008為例，要訪問電子表格文件，需在工程中添加引用COM中的Microsoft Excel,并選中命名空間Microsoft.Office.Interop(若存在的話)。示例1過程更改電子表格文件中的全部表顯示大小為100%,更改每個(gè)表第一行單元格格式為合并居中并自動(dòng)縮小字體填充，顯示一位小數(shù)。
示例代碼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僅對(duì)活動(dòng)工作表操作
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 ‘取消自動(dòng)換行
.ShrinkToFit = True
‘設(shè)置為自動(dòng)縮小字體填滿
.NumberFormat = ”0.0” ‘顯示一位小數(shù)
.MergeCells = True‘合并第一行使用的單元格
End With
Next sht
b.SaveCopyAs("副本文件名")
b.Close(False)
a.Quit()
End Sub
只需對(duì)以上過程稍加修改并調(diào)用一次就可以完成其他所需格式修改，當(dāng)需要對(duì)大量表格更改格式時(shí)，這樣比人工修改格式大大提高了效率，減少了錯(cuò)誤。
3.2 數(shù)據(jù)庫(kù)設(shè)計(jì)
原始數(shù)據(jù)不僅僅是數(shù)字，還包括文本記錄，這種混合數(shù)字和文本的數(shù)據(jù)在數(shù)據(jù)庫(kù)中是很難使用查詢進(jìn)行統(tǒng)計(jì)的，而且會(huì)大大降低查詢的效率。因此，首先要考慮對(duì)原始數(shù)據(jù)進(jìn)行合理的解析，其目的在于將文本中數(shù)字混合的原始數(shù)據(jù)轉(zhuǎn)換為數(shù)據(jù)庫(kù)中的數(shù)值記錄了[3]。之后將統(tǒng)計(jì)計(jì)算以SQL命令也放在數(shù)據(jù)庫(kù)中，這樣可以充分利用數(shù)據(jù)庫(kù)的優(yōu)勢(shì)，減少程序代碼的開發(fā)和調(diào)試[4]。以Microsoft Access數(shù)據(jù)庫(kù)為例，若統(tǒng)計(jì)一個(gè)簡(jiǎn)單的氣溫記錄表(年，月，日，小時(shí)，T)可使用一個(gè)查詢：
SELECT 月, Avg(T) AS 月平均, Max(T) AS 月最高氣溫, Min(T) AS 月最低氣溫
FROM 氣溫記錄表
WHERE 年≥起始年and年≤終止年
GROUP BY 月
將這一查詢命名為sMonthT，則其關(guān)系模式為：
sMonthT（月, 月平均, 月最高氣溫, 月最低氣溫）
該查詢統(tǒng)計(jì)起始年到終止年間的月氣溫平均、月最大、月最小值。顯然在程序中調(diào)用該查詢比在程序代碼中計(jì)算要簡(jiǎn)單得多。
3.3 電子表格的自動(dòng)填寫
前面已經(jīng)設(shè)計(jì)好了表格和數(shù)據(jù)庫(kù)，為在程序代碼中填寫表格，需要調(diào)用數(shù)據(jù)庫(kù)和電子表格文件，示例2將上面數(shù)據(jù)庫(kù)查詢結(jié)果各月份的最高氣溫填寫在不同表的第一行、第一列中。
示例代碼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
示例中，使用數(shù)據(jù)庫(kù)OleDbConnection對(duì)象連接數(shù)據(jù)庫(kù)，使用OleDbCommand對(duì)象訪問數(shù)據(jù)庫(kù)中的查詢，使用OleDbDataReader對(duì)象讀取查詢結(jié)果。由于前面數(shù)據(jù)庫(kù)查詢中有類似“起始年”、“終止年”這樣的參數(shù)，因此需要在OleDbCommand對(duì)象中添加Parameter。對(duì)于多個(gè)表，可以使用表名稱來區(qū)分。
4 應(yīng)用與實(shí)踐
在實(shí)際中遇到了機(jī)場(chǎng)氣候統(tǒng)計(jì)的問題，按照要求氣候統(tǒng)計(jì)結(jié)果需要輸出26類106張表，僅這些表形成的電子表格文件大小就約為1.2 MB，而完成數(shù)據(jù)統(tǒng)計(jì)填寫的全部程序源代碼合計(jì)約有2 MB。若將代碼嵌入電子表格，得到的電子表格文件將會(huì)超過3 MB。
通過上面的思路和方式，將程序代碼獨(dú)立出來，另外在數(shù)據(jù)庫(kù)中建立了19個(gè)表和202個(gè)查詢用于統(tǒng)計(jì)計(jì)算。由于程序代碼基于.NET Framework，經(jīng)過編譯后的可執(zhí)行文件大小僅有1 MB左右。這樣使得填寫輸出的電子表格文件大大減小，便于交換和使用；沒有內(nèi)嵌代碼的文件，提高了安全性。另一方面，編譯過的代碼顯然比內(nèi)嵌公式和源代碼有更高的運(yùn)行效率，獨(dú)立的代碼也便于后期的維護(hù)工作。最后，建立在數(shù)據(jù)庫(kù)內(nèi)的查詢非常容易使用，為今后的其他應(yīng)用奠定了基礎(chǔ)。
參考文獻(xiàn)
[1] 張慶豐.三種Excel訪問oracle數(shù)據(jù)庫(kù)的方法比較[J].計(jì)算機(jī)系統(tǒng)應(yīng)用，2004(12):64-66.
[2] 楊振宇,楊海智,楊信東.用Excel中的VBA編寫卡方測(cè)算相關(guān)程序[J].微型機(jī)與應(yīng)用， 2012,31(19):18-20.
[3] 韓金濤.民航氣象觀測(cè)數(shù)據(jù)庫(kù)的建立及應(yīng)用[J]. 民航科技,2011,147(5):110-112.
[4] ULLMAN J D, WIDOM J著.數(shù)據(jù)庫(kù)系統(tǒng)基礎(chǔ)教程[M].史嘉權(quán),譯.北京：清華大學(xué)出版社，1999.