利用Excel VBA与MySQL开发航空维修用网络录入系统

2023.07.05

摘要：针对航空维修开发的一套网络数据录入系统。运用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 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—），男，本科，工程师，北京飞机维修工程有限公司上海分公司，从事民航维修工作。