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

Vol.35No.4Journal of ShijiazhuangUniversityopliedTechnology第35卷第4期Aug.2023石家庄职业技术学院学报2023年8 月文章编号：10 0 9-48 7 3（2 0 2 3)0 4-0 0 33-0 4ExcelVBA在河湖流域巡查数据处理过程中的应用王春华，子于京波，何莹，王三飞，张璟宇（北京市河湖流域管理事务中心科技科，北京102627)摘要：为提高河湖流域巡查监管水平，采用集智通APP，优点是可以快速采集目标点位的地理信息，并拍摄现场照片作为数据分析依据；缺点是拍摄照片的信息需要手动录入到Excel中.利用ExcelVBA的二次开发功能，可以自动实现照片信息的提取、加入，并生成链接，提高了巡查效率。关键词：巡查；数据处理；VBA；照片中图分类号：TP391.13文献标志码：A北京市有流域面积10 km及以上的河流共计425条，河流总长度6 413.7 2 kml17.北京属于海河流域，分为永定河、潮白河、北运河、大清河和蓟运河五大水系 2 .42 5条河流中，大清河水系6 0 条、永定河水系7 5条、北运河水系110 条、潮白河水系138条、蓟运河水系42 条.北京市河湖流域管理事务中心（以下简称“流域中心”）承担着北京市跨行政区流域上下游、左右岸重大涉水事务综合监管的事务性工作，河长制相关事务性工作，有关水利工程及相关设施运行监管的事务性工作 3.由于监管范围大，巡查周期内覆盖点位多，为提高监管水平，流域中心配备了集思宝的北斗手持GPS采集终端一集智通APP，在巡查过程中可以快速采集目标点位的地理信息，并拍摄现场照片作为数据分析依据.由于集智通APP的功能设计中没有将点位现场拍摄的照片信息集成到Excel中，巡查结束后，还需要人工梳理登记对应的照片，手动将照片信息录人到Excel中，工作量大且容易出错.本文利用ExcelVBA开发了根据Excel中相应行中记录的点位信息，自动查找该点位的所有照片信息，并在Excel相应行的不同列中加人链接的程序，自动实现照片信息的提取、加入，并生成链接，节省了人工处理数据的时间，提高了巡查效率，1天开发程序工具简介Excel是一款电子表格制作和数据分析软件，能通过运用各种函数和工具快速便捷地进行批量文字与数据的处理，它集成的VBA（V i s u a l Ba s i c f o rApplications）语言可以对Excel进行二次开发，极大地拓展了Excel的功能，使它具有了程序改造、插件开发及多任务批量处理的功能.VBA衍生于VB语言，主要应用于微软Office的各项组件之中，而尤其以Excel和Access应用VBA较多，它有着与VB几乎相同的开发环境 5 和语法.2程序开发思路将所有现场拍摄的照片名称添加到Excel的某一空白列中待用.表格中每一行的第一列即为点位名称，运用VBA中的函数提取出照片名称中的点位名称，即可知道每张照片应加人到哪一行中.通过循环遍历，得出一个点位对应的照片张数，再通过一遍循环，将多张照片分不同的列插人到对应的点位行中.3程序实现(1)统计Excel表格中一共有多少字段列，增加末尾临时列，自动加人照片名称，便于对照片信息进收稿日期：2 0 2 3-0 1-16作者简介：王春华（19 7 8-），女，北京市河湖流域管理事务中心高级工程师，硕士，研究方向：水利工程运行维护.CO1-DoWhileVBA.enCells(linephoto共有多少张照片(4)统计照）片信息列中linedian=linedianLooplinedian=linedian0Do WhileVBA.Value)(3)统计excel 中登记的巡查点位数sum一0linenum=2linephoto=2linedian=2NextlinenumIntegerNextDimlinephotoLinenuimsumAsineciarEnd IfElse第3 5卷石家庄职业技术学院学报34行处理.程序如下：colnum=1Do WhileVBA.Len(Cells(l,colnum).Value)0colnum=colnum+1Loop（2)搜索照片文件夹，将文件夹内的所有照片按名称登记到末尾列中。mypath=e:testlphotomyfile=Dir(mypath,vbDirectory)a=2DoWhile myfileIfmyfile,AndmyfileThenCells(a,colnum)=myfilea=a+1myfile=DirElsemyfile=DirEnd IfLoopnum).Value)linephoto=linephoto+1Looplinephoto=linephoto-1（5）利用双层循环将照片按照点位名称分别插人到相应行中。如果一个点位拍摄有多张照片，则将多张照片信息分别放在不同的列中，并将照片信息设置成超级链接的形式，点击后直接能看到照片.Forlinenum=2 To linediansum=oFor i=2To linephotoIfStrComp(Cells(linenum,1).Value,VBA.Left(Cells(i,colnum).Value,Application.Work-sheetFunction.Search(_,Cells(i,colnum).Val-ue)-1),1)=0 Then（6）如果一个点位有多张照片，则将每张照片分别插人一列中。sum=sum+1(7)将照片信息变成超级链接的形式.将照片信息变成超级链接的形式Cells(linenum,colnum+sum).Hyperlinks.Add Anchor:=Cells(linenum,colnum+sum),Address:=Cells(i,colnum).Value,SubAddress:=,ScreenTip:=,TextToDisplay:=Cells(i,colnum).Value（8)删除程序初始增加的末尾临时列.Columns(colnum).DeleteEnd Sub4应用实例流域中心在2 0 2 2 年8 月19 日的例行监督巡查过程中，检查了14个点位的现场情况.在九鼎画室小微水体处拍摄了3张现场照片，在前疃村1号坑处拍摄了3张照片，在月牙河处拍摄了2 张照片，其他巡查点位处由于没有发现问题，未拍摄照片.巡查设备导出的Excel中包含了点位的地理信息，截图见图1，设备导出的照片信息见图2.运行本文设计的程序，得到的结果见图3.由图3可知，现场拍摄的照片以超链接的形式插入到相应的点位信息行内.有多张照片时，则被插人到不同的列内.查看有问题的点位现场情况时，只需要在Excel中点击链接照片即可，不再需要去照片文件夹中进行人工搜索。王春华等：ExcelVBA在河湖流域巡查数据处理过程中的应用第4期35ABCDFG工名称记录时间天线高坐标X坐标Y高程H备注原始经度原始纬度2新风河2022-08-198:46:180116.285465939.7610147439.369116.285465940.013750463江南渠2022-08-199:45:290116.285450339.7609946437.506116.285450339.971300984怀九河2022-08-199:46:180116.285465539.7609895736.447116.285465539.865399855温榆河2022-08-1910:45:290116.757718840.0135922714.536116.757718839.7606396末庄2022-08-1911:45:290116.756659340.0136184615.271116.756659340.013750467白河湾202208-1912:45:290116.754953640.013702715.941116.754953639.971300988前嚏村1号坑2022-08-1913:45:290116.75497240.0137504615.737116.75497240.013750469九鼎画室小微水体2022-08-1914:45:290116.738566739.9713009813.791116.738566739.9713009810小泉河202208-1915:45:290116.879448239.8390712824.651116.879448239.8390712811雁栖河2022-08-1916:45:290116.844213439.8501543517.543116.844213439.8501543512怀河2022-08-1917:45:290116.773133639.9102962622.827116.773133639.9102962613月牙河2022-08-1918:45:290116.774358339.9175547428.143116.774358339.9175547414七分干渠2022-08-1919:45:290116.772227639.9333093818.497116.772227639.9333093815蔡家河2022-08-1920:45:290116.779139839.9367736938.618116.779139839.93677369图1集智通导出的Excel中包含点位信息截图名称日期类型大小标记九鼎画室小微水体_2 0 2 2 0 8 19 11512 6 _照片2022/8/3111:52JPEG图像5,224 KB九鼎画室小微水体_2 0 2 2 0 8 19 115154_照片2022/8/3111:52JPEG图像8,866KB九鼎画室小微水体_2 0 2 2 0 8 19 1152 14_照片2022/8/3111:52JPEG图像4,901KB前幢村1号坑_2 0 2 2 0 8 19 144439 照片2022/8/3114:48JPEG图像4,652KB前幢村1号坑_2 0 2 2 0 8 19 144455_照片2022/8/3114:48JPEG图像5,265KB前幢村1号坑_2 0 2 2 0 8 19 1446 15照片2022/8/3114:48JPEG图像4,935KB月牙河_2 0 2 2 0 8 19 11512 9 _照片2022/8/3111:52JPEG图像8,866KB月牙河_2 0 2 2 0 8 19 115158 _照片2022/8/3111:52JPEG图像8,866KB图2集智通APP导出的点位所拍摄的照片信息截图ABCDGHJKL1名称记录时间天线坐标X坐标Y高程旺备注原始经度原始纬度2新风河2022-08-19.8:4(0116.28546639.7610147439.369116.285465940.013750463江南集2022-08-199:40116.2854539.7609946437.506116.285450339.971300984怀九河2022-08-199:4(0116.28546639.7609895736.447116.285465539.865399855温榆河2022-08-1910:40116.757719940.0135922714.536116.757718839.7606396未庄2022-08-1911:40116.75665940.0136184615.271116.756659340.013750467白河湾2022-08-1912:40116.75495440.013702715.941116.754953639.971300988前村1号2 0 2 2-0 8-19 13:40116.75497240.0137504615.737116.75497240.01375046前曦村1号坑2 0 2 2 0 8 19 144439 照片，12 8煎暖村1号坑_2 0 2 2 0 8 19 144455照片，ip8前暖村1号坑2 0 2 2 0 8 19 1446 15照片，1九鼎画92022-08-1914:40116.73856739.9713009813.791116.7385667 39.97130098 九鼎画室小微水体2 0 2 2 0 8 19 11512 6 照片.jp&九鼎画室小微水体 2 0 2 2 0 8 19 115154照片.jp九鼎画室小微水体2 0 2 2 0 8 19 1152 14照室小微10小泉河2022-08-1915:50116.87944839.8390712824.651116.879448239.8390712811雁栖河2022-08-1916:40116.84421339.8501543517.543116.844213439.8501543512环河2022-08-1917:0116.77313439.9102962622.827116.773133639.9102962613月牙河2022-08-1918:0116.77435839.9175547428.143116.774358339.91755474月牙河2 0 2 2 0 8 19 11512 9 照片io月牙河2 0 2 2 0 8 19 115158 照片，10 814七分干渠2 0 2 2-0 8-19 19:0116.77222839.9333093818.497116.772227639.9333093815蔡家河2022-08-1920:40116.7791439.9367736938.618116.779139839.93677369图3程序运行应用结果信息截图在日常工作尤其是业务资料整理过程中，存在着大量机械性的重复工作，利用VBA这一通用的自动化语言，基于Excel,可将多步骤的手工操作和大量重复性工作转化为简单的操作，既减少了工作量，提高了工作效率，又避免了手工操作可能产生的错误.责任编辑：吴瑞红第3 5卷石家庄职业技术学院学报36参考文献：1北京市水务局，北京市统计局.北京市第一次水务普查公报J.北京水务，2 0 13（3）：59-6 2.2苏建心，吕红霞，袁汉章，等.北京市河湖流域管理与行政区域管理模式探讨 J.北京水务，2 0 14（3）：52-56.3北京市河湖流域管理事务中心.流域“一盘棋”奋力谱写新时代首都河湖治理新篇章一一北京市河湖流域管理事务中心.北京水务，2 0 2 2（5）：8-9.4马志翔.ExcelVBA在桥梁桩柱钢筋辅助绘图中的应用 J.建筑建材装饰，2 0 2 2（13）：19 6-19 8.5徐利明.三维激光扫描技术在现代测绘中的应用 J.技术与市场，2 0 2 0,2 7(11):9 5-9 7.Application of Excel VBA in sortiout patrol data of rivers and lakesWANG Chun-hua,YU Jing-bo,HE Ying,WANG Fei,ZHANG Jing-yu(Department of Science and Technology,Beijing River and Lake Management Center,Beijing 102627,China)Abstract:In the process of river and lake patrol,a designated APP is employed to improve supervisionmanagement level.Its advantage is that it can quickly collect geographic information of target points andtake on-site photos,although the photo information taken on site needs to be manually added to Excel.Yet,photo information can be automatically extracted,added,and linked,improving supervision manage-ment efficiency,by utilizing the secondary development function of Excel VBA.Key words:patrol;data processing;VBA;photo