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