Excel VBA 在河湖流域巡查数据处理过程中的应用

王春华,于京波,何 莹,王 飞,张璟宇

(北京市河湖流域管理事务中心 科技科,北京 102627)

北京市有流域面积10 km2及以上的河流共计425条,河流总长度6 413.72 km[1].北京属于海河流域,分为永定河、潮白河、北运河、大清河和蓟运河五大水系[2].425条河流中,大清河水系60条、永定河水系75条、北运河水系110条、潮白河水系138条、蓟运河水系42条.北京市河湖流域管理事务中心(以下简称“流域中心”)承担着北京市跨行政区流域上下游、左右岸重大涉水事务综合监管的事务性工作,河长制相关事务性工作,有关水利工程及相关设施运行监管的事务性工作[3].由于监管范围大,巡查周期内覆盖点位多,为提高监管水平,流域中心配备了集思宝的北斗手持GPS采集终端——集智通APP,在巡查过程中可以快速采集目标点位的地理信息,并拍摄现场照片作为数据分析依据.由于集智通APP的功能设计中没有将点位现场拍摄的照片信息集成到Excel中,巡查结束后,还需要人工梳理登记对应的照片,手动将照片信息录入到Excel中,工作量大且容易出错.本文利用Excel VBA 开发了根据Excel中相应行中记录的点位信息,自动查找该点位的所有照片信息,并在Excel相应行的不同列中加入链接的程序,自动实现照片信息的提取、加入,并生成链接,节省了人工处理数据的时间,提高了巡查效率.

1 开发程序工具简介

Excel 是一款电子表格制作和数据分析软件,能通过运用各种函数和工具快速便捷地进行批量文字与数据的处理,它集成的VBA(Visual Basic for Applications)语言可以对Excel进行二次开发,极大地拓展了Excel的功能,使它具有了程序改造、插件开发及多任务批量处理的功能[4].VBA 衍生于VB语言,主要应用于微软Office的各项组件之中,而尤其以Excel和Access应用VBA 较多,它有着与VB几乎相同的开发环境[5]和语法.

2 程序开发思路

将所有现场拍摄的照片名称添加到Excel的某一空白列中待用.表格中每一行的第一列即为点位名称,运用VBA 中的函数提取出照片名称中的点位名称,即可知道每张照片应加入到哪一行中.通过循环遍历,得出一个点位对应的照片张数,再通过一遍循环,将多张照片分不同的列插入到对应的点位行中.

3 程序实现

(1)统计Excel表格中一共有多少字段列,增加末尾临时列,自动加入照片名称,便于对照片信息进行处理.

程序如下:

(2)搜索照片文件夹,将文件夹内的所有照片按名称登记到末尾列中.

(3)统计excel中登记的巡查点位数.

(4)统计照片信息列中一共有多少张照片.

(5)利用双层循环将照片按照点位名称分别插入到相应行中.如果一个点位拍摄有多张照片,则将多张照片信息分别放在不同的列中,并将照片信息设置成超级链接的形式,点击后直接能看到照片.

(6)如果一个点位有多张照片,则将每张照片分别插入一列中.

(7)将照片信息变成超级链接的形式.

(8)删除程序初始增加的末尾临时列.

4 应用实例

流域中心在2022年8月19日的例行监督巡查过程中,检查了14个点位的现场情况.在九鼎画室小微水体处拍摄了3张现场照片,在前疃村1号坑处拍摄了3张照片,在月牙河处拍摄了2张照片,其他巡查点位处由于没有发现问题,未拍摄照片.巡查设备导出的Excel中包含了点位的地理信息,截图见图1,设备导出的照片信息见图2.

图1 集智通导出的Excel中包含点位信息截图

图2 集智通APP导出的点位所拍摄的照片信息截图

运行本文设计的程序,得到的结果见图3.

图3 程序运行应用结果信息截图

由图3可知,现场拍摄的照片以超链接的形式插入到相应的点位信息行内.有多张照片时,则被插入到不同的列内.查看有问题的点位现场情况时,只需要在Excel中点击链接照片即可,不再需要去照片文件夹中进行人工搜索.

在日常工作尤其是业务资料整理过程中,存在着大量机械性的重复工作,利用VBA 这一通用的自动化语言,基于Excel,可将多步骤的手工操作和大量重复性工作转化为简单的操作,既减少了工作量,提高了工作效率,又避免了手工操作可能产生的错误.