利用Excel VBA與MySQL開發航空維修用網絡錄入系統

摘 要：針對航空維修開發的一套網絡數據錄入系統。運用Excel VBA制作本地端界面和程序，并通過網絡訪問WAMP搭建的服務器端MySQL數據庫。從而實現多地操作和數據統一，同時保障運行效率和數據安全。

關鍵詞：Execl;VBA;MySQL;數據庫;航空維修;錄入系統

一、緒論

在航空維修業中工作記錄是必不可少的，傳統的Excel表單無法多地操作、分散的數據很難統一、越來越大容量也會減慢運行速度，還存在容易被篡改的風險。但是通過Excel鏈接MySQL數據庫可以解決這些問題，同時該數據庫是開源免費的，因此需要基于Excel VBA+MySQL運行環境的方案。Excel鏈接數據庫的資源較少，且主要集中在微軟自家的Access和MsSQL方面。鏈接MySQL的資料則更少，也成為項目的最大挑戰。

二、運行環境

開發軟件為Excel、MySQL和WAMP2.0。Excel作為一套優秀的電子表格軟件，內嵌的VBA語言是寄生于VB應用程序的版本。MySQL是一個關系型數據庫管理系統，其特色是體積小、速度快、代碼開源免費和強大的社區支持。WAMP則是搭建動態網站服務器的開源免費軟件，擁有一鍵構建服務器的強大功能。

首先在服務器端安裝WAMP來構建MySQL數據庫，然后在本地端的Excel內安裝了數據庫插件ADO和更新本地端的數據庫類。整個運行環境就構建完成。

三、本地端結構

（一）程序界面

通過VBA控件制作登入、數據錄入和查詢等窗體。在激活Excel時，隱藏Excel原始程序框體和所有表單界面，只顯示登入窗體，隨后根據不同的按鈕控件激活不同的功能窗體。使程序更具有界面感。

（二）表單界面

Excel的表單將全部被隱藏（不能被選中），起到保證數據安全;同時表單用于基礎信息保存、數據緩存和顯示確認。基礎信息主要包括使用說明、人員信息和登入權限。數據緩存主要用于緩存傳輸至服務器的數據，為了實現斷網（斷開服務器）操作的特性，和實現按批次（多條）傳輸的特性。顯示確認用于實現錄入和查詢時的信息確認特性。

（三）VBA代碼分布

位于Microsoft Excel對象的Thisworkbook內代碼的功能是在啟動時對框體和表單進行隱藏和格式化，在關閉時進行恢復。位于各窗體內代碼的功能是實現各種窗體間邏輯操作和與數據庫的傳輸。位于各模塊內的代碼類和函數的功能是提高代碼的復用性。

四、主要功能和代碼

（一）登入窗體

主要功能是登入、權限識別和隨機小提示。

（二）數據錄入窗體

主要功能是自動匹配（日期、機號和航班號的前綴、人員），數據緩存、多數據提交和顯示確認。

Private Sub CommandButton2_Click（）'”完成”按鈕執行本地數據緩存或數據庫傳輸操作;

If TextBox1.Value="" Or TextBox2.Value="" Or TextBox3.Value="" Or TextBox4.Value="" OrTextBox5.Value="" Then '判斷沒有新數據;

If Sheet3.Range（"A65536"）.End（xlUp）.Row > 2 Then '判斷緩存數據（除表頭）大于1條時;

F_Import '調用數據庫傳輸類;

End If

Else '有新數據時;

ForButton_F '調用數組類，是''下一條”按鈕的主要代碼，將數據緩存至本地表單中;

If Sheet3.Range（"A65536"）.End（xlUp）.Row > 2 Then

F_Import

End If

End If

End Sub

Public Sub F_Import（）'用insert語句在數據庫內插入記錄;

Dim rngCur As Range，Cell As Range，i As Integer

Dim sInsert As String，iRowscount As Integer

Dim Con As ADODB.Connection

With Worksheets（"未導入非例行"）

Set rngCur=.Range（.Range（"a2"），.Range（"a2"）.End（xlDown））'獲取數據區域;

End With

For Each Cell In rngCur '調用數據聯合函數，把數據添加到SQL命令字符串sInsert;

i=1+i

If i=1 Then

sInsert="（"+JOINFI（Cell.Offset（0，1）.Resize（1，17）.Value，"，"，""""）+"）"

Else

sInsert=sInsert+"，"+"（"+JOINFI（Cell.Offset（0，1）.Resize（1，17）.Value，"，"，""""）+"）"

End If

Next Cell

sInsert="inser`workrecord`（`fillday`，`groupname`，`day`，`linnum`，`bnum`，`cat1`，`yenum`，`comments`，`finder`，`mainworker`，`parnter1`，`parnter2`，`cat2`，`costhour`，`nolynot`，`shifter`，`last`）value"+sInsert

'構造插入SQL命令字符串sInsert，對應數據庫workrecord表和相應的表頭，注意空格和符號類型;

Set Con=New ADODB.Connection '執行插入數據操作;

Con.ConnectionString="Driver={MySQL ODBC 5.1 Driver};"+_ '調用驅動版本;

"Server=10.210.000.14;"+_ '服務器的IP地址，建議使用靜態地址;

"DB=workdatabase;"+_ '數據庫名;

"user=root;"+_ '賬號;

"PassWord='root';"+_ '密碼;

"OPTION=3;"+_

"Stmt=Set Names 'utf8_general_ci';" '中文格式;

Con.Open

Con.Execute sInsert，iRowscount，adCmdText '執行插入數據操作;

Con.Close：Set Con=Nothing

End Sub

Public Function JOINFI（arr As Variant，delimiter As String，Optional quotes As String=""）As String

For Each el In arr '數據聯合函數，用引號和分隔符對數據進行聯合。

i=1+i

If i =1 Then

JOINFI=quotes & el & quotes

Else

JOINFI=JOINFI & delimiter & quotes & el & quotes

End If

Next el

End Function

（三）查詢窗體

主要功能是按范圍查詢本月或上月數據。

'本月全部數據的查詢代碼;

Set Rec=Con.Execute（"select * from `workrecord` where

date_format（`fillday`，'%Y%M'）=date_format（curdate（），'%Y%M'）"，iRowscount，adCmdText）

Sheet2.Range（"a2"）.CopyFromRecordset Rec'復制到指定的表單位置;

'上月班組數據函數的查詢代碼，groupname為班組變量。

Set Rec=Con.Execute（"select * from `workrecord` where `groupname`='" & groupname & "' and date_format（`fillday`，'%Y-%m'）=date_format（DATE_SUB（curdate（），INTERVAL 1 MONTH），'%Y-%m'）"）

五、結語

運用WAMP構建MySQL服務器端和Excel VBA編寫的本地端程序（約702K）的方案不光運行環境構建快，軟件成本也低。還有多地操作、斷網操作和數據統一的特性，也不會隨數據量的增加而運行變慢，完善的保護機制也確保了可靠性。

參考文獻：

[1]高智超，張志揆.將Excel數據導入MySQL中的兩種技巧[J].電腦編程技巧與維護，2012，（19）：39-40，48.

[2]張旭.利用Excel VBA設計制作應用程序窗體[N].武漢工程職業技術學院學報，2010-12，22（4）：41-45.

[3]湯清.利用VBA在Excel中開發應用系統[J].電腦知識與技術，2002，（1）：31-33.

[4]王俊飛，唐克巖，向渝，陳偉.Excel VBA技術在中小制造企業信息管理系統中的應用[J].機械設計與制造工程，2018-8，47（8）：81-86.

作者簡介：鄧磊（1983—），男，本科，工程師，北京飛機維修工程有限公司上海分公司，從事民航維修工作。