Excel二次开发技术在公路技术状况评定系统中的应用.doc

Excel二次开发技术在公路技术状况评定系统中的应用修林岩（烟台市长岛公路管理局，山东 烟台265800）摘 要：本公路技术状况评定系统利用Excel自身强大的运算功能，并结合VBA二次开发技术，可实现人工录入基本信息和检测数据，自动进行汇总和等级评定，并提供方便快捷的查询功能，不仅可提高公路技术状况评定过程中后期内业的工作效率，而且可保证评定结果准确、可靠（请确认摘要可否如此改动？可以）。关键词：公路；Excel二次开发；技术状况评定。Excel在公路相关业务中的应用已经较为广泛，是目前工程技术人员掌握最熟练、最乐于使用和接受的数据处理工具，但具体的应用大都局限在普通的函数运算功能，而基于VBA的二次开发技术，则在实际工作应用较少。基于VBA的Excel二次开发技术可以更加高效的利用Excel本身的强大功能，实现更多更复杂的高级功能，同时VBA作为一种以Visual Basic语言为基础的自动化语言，比较容易为公路管理、技术人员所掌握，为Excel二次开发的广泛应用创造了前提条件。本公路技术状况评定系统主要利用Excel自身的公式计算功能，利用相对简易的VBA技术，实现了评定过程的智能化、自动化。1 公路技术状况评定系统简介公路技术状况检测与调查以1000m路段为基本检测或调查单元，包括路面、路基、桥隧构造物和沿线设施四部分内容。路面检测包括路面损坏、平整度、车辙、抗滑性能和结构强度5项指标，桥隧构造物调查包括桥梁、隧道和涵洞3类构造物，如图1所示。图1 公路技术状况评定指标（框内所有物理指标应有完整中文意思 这张图原样来自公路技术状况评定规范，如果逐一标明会不会太繁琐？指标说明见图）根据公路等级和路面类型的不同，需要确定是否划分上下行，并且计算路面使用性能指数PQI所采用的分项指标（即PCI、RQI、RDI和SRI）的范围和评价计算公式均有所不同（PSSI为抽样检测指标，单独进行计算与评定，不参与PQI的计算）。若待评定路线的公路等级（主编说看不懂，想表达何意？说明：在公路等级和路面类型变化处需要对路线进行分段评定，采用不同的计算表格和公式。一条路线可以有一级、二级共存的情况）和路面类型有变化，需要及时选用不同的计算表格和评定公式，这正是手动汇总计算的繁琐易错之处。Excel作为是微软公司开发的办公自动化软件Office的组件，是目前应用最为广泛的电子表格处理软件之一，其强大、成熟、高效的运算及图表处理能力一直为广大公路技术人员所青睐。VBA是Visual Basic for Application的简写，它以VB语言为基础，经过修改并运行在Microsoft Office的应用程序。本公路技术状况评定系统，根据JTG H202007公路技术状况评定标准的要求，利用VBA二次开发，高效整合Excel自身功能，最大程度减少用户的工作量，即使是Excel的初级用户也能轻松上手，即提高了工作效率，又保证了评定结果的快速准确。在本系统中，用户根据公路等级、路面类型和路面宽度等因素划分MQI评定单元，然后将实际调查数据按段填入损坏调查表和明细表，系统自动进行累计汇总并进行权重扣分计算，根据求得的指数确定相应指标的评价等级。对于评价路线中存在路面类型变化、等级变化等复杂情况，系统自动切换相应表格和计算指标公式，免除了手工操作时在不同表格公式间切换的不便。2 编程思想与技术实现在本系统中，调查数据的采集与评定结果（何为“成果结果”？应为评定结果）均采用规范中的表格向用户进行实时反馈，做到了“所见即所得”，数据的保存也在一个Excel文件中实现，之所以没有采用Access数据库，一是减少系统后台跨平台处理的工作量，提高运行速度，降低对用户软件环境的要求（主编认为此处不通）；二是便于后期本系统与路面管理系统（CPMS）进行数据共享，避免（什么开发时？是程序吗？本程序后期计划与前面提到的CPMS进行数据共享）后期开发时数据库调用上的冲突。系统采用8张工作表来实现系统的全部功能，其中sheet1sheet3、sheet8直接作为交互界面，用来接收用户数据、反馈成果信息，sheet4sheet7用来保存路段信息、调查数据及表格模板，其对用户是隐藏的，仅在设计模式下可见。具体如图2所示：图2 系统工作表组成（Name是否就为表号？Name是编程后台对各表的定义，本可译为表名，但与下面的“表名”重复 故保留）2.1 用户操作与控制系统利用excel表格作为与用户的交互界面，不仅迎合了技术人员熟悉的Excel操作，输出成果可以直接归档，而且缩短了程序开发的周期，借助Excel本身强大的公式与表格处理能力，实现了大部分计算工作的自动化，减少了后台编程的工作量。用户首先需要在基础数据表中填写路段基本信息，将划分路段起点桩逐行填入，并完善路线等级、路面类型和宽度信息，然后进行“数据初始化”操作。再将实际损坏调查数据分段填入调查表，将平整度、车辙、抗滑性能和结构强度数据填入明细表中，全路段的汇总评价结果也已同时完成,汇总表、明细表和调查表均可以直接打印输出，供内业归档用。2.2 基于VBA的Excel二次开发 本评定系统实现过程如图3：图3 系统实现及表间关系示意（前面Name是否应为表号？然后表名、内容作为图示，这样表示太不规范 同上）1）用户在完成基本信息录入后进行的初始化操作，目的在于将路段信息按行写入DATA1表中，并清空DATA2、DATA3。这3张表用来保存全部的原始调查数据，每个路段在3张DATA表中占1行，调查表在调用和储存数据时以路段的序号对应的表格行号作为定位依据，根据Excel2003表格的行数限制，理论上系统最多可以支持60000多个调查路段。在此过程中需要考虑公路等级的不同是否需要区分上下行的问题。系统利用了一个直观的Command控件，实现初始化功能，代码为： Sheet4.Cells.ClearContents Dim i As Integer，j As Integer，ii As Integer For i = 0 To Sheet1.Cells(8, 2) - 1 If Sheet1.Cells(i + 8, 6) = Sheet1.Cells(10, 12) Then For ii = 4 To 8 Sheet4.Cells(j, ii - 3) = Sheet1.Cells(i + 8, ii) Sheet4.Cells(j, 6) = 上下行 Next ii Else For ii = 4 To 8 Sheet4.Cells(j, ii - 3) = Sheet1.Cells(i + 8, ii) Sheet4.Cells(j, 6) = 上行2）初始化完成后，用户即可以在调查表中利用一个微调按钮前后切换不同路段，填写或查询调查数据，并可以随时将需要的调查表予以打印输出。此编程过程中需要要解决的问题主要有：非标准长度路段SCI、BCI、TCI指标的换算以及路面类型不同时表格的相应变化。系统利用GRIDS表来保存不同类型的路面调查表模板，通过判断用户已经录入路面类型信息，复制相应模板到调查表中，然后再读取用户数据，从而实现表格类型的实时切换。同时在切换的过程中，根据用户设置，进行文档的自动保存，以确保数据安全性。部分代码如下： Sheet2.Range(lm).Clear If LMLX = 沥青路面 Then Sheet5.Range(LQlm).Copy Sheet2.Range(lm).PasteSpecial Sheet2.Cells(1, 1) = 沥青路面损坏情况调查表 For i = 0 To LmLin For j = 0 To 9 Sheet4.Cells(Lin, 21 + i * 10 + j) = Sheet2.Cells(6 + i, 7 + j) Next j Next i3）在明细表中，利用VBA调用Excel的高级筛选功能结合指定单元格的有效性控制形成的下拉菜单，实现用户对于不同调查方向上数据查询的需求，高级筛选代码如下： Range(Q1:Q393).AdvancedFilterAction:=xlFilterInPlace, _CriteriaRange:=Range(R4:R5), Unique:=False在本表中还要处理好存在五类桥梁（哪来的？公路桥梁养护规范中对桥梁状况分类）、危险隧道和涵洞时路段MQI直接为0的问题。系统利用调查表自动计算后在DATA表中留下的特殊标记进行判断，如果符合要求，则不计算MQI，直接取0值（请确认可否如此改动？可以）。4）汇总表中的功能通过编辑公式即可实现，要用到的数组公式稍显复杂，但原理较为简单。4 结语目前，通过本系统在263线公路技术状况评定中的使用情况来看，在该系统中，很好地实现了检测调查评定同步化处理，提高了评定工作的工作效率，保证了评定结果的科学性准确性。本评定系统远没有将Excel的强大功能发挥到极致，希望借此系统的开发为Excel和VBA开发技术在公路管理中推广应用做些尝试（主编认为此句不通，请修改 这样修改可否？）。参考文献1 交通部公路科学研究院与上海市公路管理处.JTG H202007公路技术状况评定标准S.北京：中国知识出版社，2008.2 陕西省公路局.JTG H112004公路桥涵养护规范S.北京：人民交通出版社，2004.3 重庆市交通委员会.JTG H122003公路隧道养护技术规范S.北京：人民交通出版社，2003.4 王松根，马庆雷，张玉宏.山东公路养护技术应用与研究M. 北京：人民交通出版社，2005.5 戴维斯（Davis,G.H.）.VBA从入门到精通（第二版）M.北京：电子工业出版社，2008.6 史玉磊