利用Excel VBA與MySQL開發(fā)航空維修用網(wǎng)絡(luò)錄入系統(tǒng)

摘 要：針對航空維修開發(fā)的一套網(wǎng)絡(luò)數(shù)據(jù)錄入系統(tǒng)。運(yùn)用Excel VBA制作本地端界面和程序，并通過網(wǎng)絡(luò)訪問WAMP搭建的服務(wù)器端MySQL數(shù)據(jù)庫。從而實現(xiàn)多地操作和數(shù)據(jù)統(tǒng)一，同時保障運(yùn)行效率和數(shù)據(jù)安全。

關(guān)鍵詞：Execl;VBA;MySQL;數(shù)據(jù)庫;航空維修;錄入系統(tǒng)

一、緒論

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

二、運(yùn)行環(huán)境

開發(fā)軟件為Excel、MySQL和WAMP2.0。Excel作為一套優(yōu)秀的電子表格軟件，內(nèi)嵌的VBA語言是寄生于VB應(yīng)用程序的版本。MySQL是一個關(guān)系型數(shù)據(jù)庫管理系統(tǒng)，其特色是體積小、速度快、代碼開源免費(fèi)和強(qiáng)大的社區(qū)支持。WAMP則是搭建動態(tài)網(wǎng)站服務(wù)器的開源免費(fèi)軟件，擁有一鍵構(gòu)建服務(wù)器的強(qiáng)大功能。

首先在服務(wù)器端安裝WAMP來構(gòu)建MySQL數(shù)據(jù)庫，然后在本地端的Excel內(nèi)安裝了數(shù)據(jù)庫插件ADO和更新本地端的數(shù)據(jù)庫類。整個運(yùn)行環(huán)境就構(gòu)建完成。

三、本地端結(jié)構(gòu)

（一）程序界面

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

（二）表單界面

Excel的表單將全部被隱藏（不能被選中），起到保證數(shù)據(jù)安全;同時表單用于基礎(chǔ)信息保存、數(shù)據(jù)緩存和顯示確認(rèn)。基礎(chǔ)信息主要包括使用說明、人員信息和登入權(quán)限。數(shù)據(jù)緩存主要用于緩存?zhèn)鬏斨练?wù)器的數(shù)據(jù)，為了實現(xiàn)斷網(wǎng)（斷開服務(wù)器）操作的特性，和實現(xiàn)按批次（多條）傳輸?shù)奶匦浴ｏ@示確認(rèn)用于實現(xiàn)錄入和查詢時的信息確認(rèn)特性。

（三）VBA代碼分布

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

四、主要功能和代碼

（一）登入窗體

主要功能是登入、權(quán)限識別和隨機(jī)小提示。

（二）數(shù)據(jù)錄入窗體

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

Private Sub CommandButton2_Click（）'”完成”按鈕執(zhí)行本地數(shù)據(jù)緩存或數(shù)據(jù)庫傳輸操作;

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

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

F_Import '調(diào)用數(shù)據(jù)庫傳輸類;

End If

Else '有新數(shù)據(jù)時;

ForButton_F '調(diào)用數(shù)組類，是''下一條”按鈕的主要代碼，將數(shù)據(jù)緩存至本地表單中;

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

F_Import

End If

End If

End Sub

Public Sub F_Import（）'用insert語句在數(shù)據(jù)庫內(nèi)插入記錄;

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

Dim sInsert As String，iRowscount As Integer

Dim Con As ADODB.Connection

With Worksheets（"未導(dǎo)入非例行"）

Set rngCur=.Range（.Range（"a2"），.Range（"a2"）.End（xlDown））'獲取數(shù)據(jù)區(qū)域;

End With

For Each Cell In rngCur '調(diào)用數(shù)據(jù)聯(lián)合函數(shù)，把數(shù)據(jù)添加到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

'構(gòu)造插入SQL命令字符串sInsert，對應(yīng)數(shù)據(jù)庫workrecord表和相應(yīng)的表頭，注意空格和符號類型;

Set Con=New ADODB.Connection '執(zhí)行插入數(shù)據(jù)操作;

Con.ConnectionString="Driver={MySQL ODBC 5.1 Driver};"+_ '調(diào)用驅(qū)動版本;

"Server=10.210.000.14;"+_ '服務(wù)器的IP地址，建議使用靜態(tài)地址;

"DB=workdatabase;"+_ '數(shù)據(jù)庫名;

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

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

"OPTION=3;"+_

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

Con.Open

Con.Execute sInsert，iRowscount，adCmdText '執(zhí)行插入數(shù)據(jù)操作;

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 '數(shù)據(jù)聯(lián)合函數(shù)，用引號和分隔符對數(shù)據(jù)進(jìn)行聯(lián)合。

i=1+i

If i =1 Then

JOINFI=quotes & el & quotes

Else

JOINFI=JOINFI & delimiter & quotes & el & quotes

End If

Next el

End Function

（三）查詢窗體

主要功能是按范圍查詢本月或上月數(shù)據(jù)。

'本月全部數(shù)據(jù)的查詢代碼;

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'復(fù)制到指定的表單位置;

'上月班組數(shù)據(jù)函數(shù)的查詢代碼，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'）"）

五、結(jié)語

運(yùn)用WAMP構(gòu)建MySQL服務(wù)器端和Excel VBA編寫的本地端程序（約702K）的方案不光運(yùn)行環(huán)境構(gòu)建快，軟件成本也低。還有多地操作、斷網(wǎng)操作和數(shù)據(jù)統(tǒng)一的特性，也不會隨數(shù)據(jù)量的增加而運(yùn)行變慢，完善的保護(hù)機(jī)制也確保了可靠性。

參考文獻(xiàn)：

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

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

[3]湯清.利用VBA在Excel中開發(fā)應(yīng)用系統(tǒng)[J].電腦知識與技術(shù)，2002，（1）：31-33.

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

作者簡介：鄧?yán)冢?983—），男，本科，工程師，北京飛機(jī)維修工程有限公司上海分公司，從事民航維修工作。