基于Excel VBA的批量数据提取工具开发

刘欢 彭书强

摘要：为了批量提取管道力学计算软件PipeStress输出的结果文件中的数据，需要使用计算机从结果文件中提取支架的反力及反力方向矢量等数据。基于VBA简单易学易用的特点，提出使用Excel电子表格读取结果文件，并使用VBA语言编程进行数据处理的方法。经过实际应用表明，该工具能满足实际需求，大大提高了工作效率，节省了大量的人力和时间，使用、维护简便，可靠性高。

关键词：批量；数据提取；Excel；VBA

中图分类号：TP311 文献标识码：A 文章编号：1009-3044（2019）05-0196-03

The Development of Bulk Data Extraction Tool Based on Excel VBA

LIU Huan1， PENG Shu-qiang2

（1. Guangzhou Nansha Human Resources Development Co.， Ltd.， Guangzhou 510000， China； 2. Process Dept.， China Petroleum Engineering & Construction Corp. North China Company， Renqiu 062550， China）

Abstract： In order to extract bulk data from the output result file of PipeStress （Pipeline mechanics calculation software）， it is necessary to use computer to extract the reaction force and direction vector of the support from the result file. Based on the characteristics of VBA， which is easy to learn and use， this article invents a method of reading the result file in Excel spreadsheet and processing data by programming in VBA. The practical application shows that this method can satisfy real requirement， greatly improves the work efficiency and saves a lot of manpower and time. Its easy to use and maintain， and high reliability.

Key words： bulk； data extraction； Excel； VBA

某電站采用管道系统有限元分析软件PipeStress对电站的管道系统进行应力分析，软件输出的结果文件（*.ppo）包含了各工况组合下的支架（或阻尼器等）的支撑载荷、接管载荷、支架（和自由端、穿墙点等）处的节点位移。在实际的管道力学分析项目中，会根据管道系统结构的复杂程度将管道分成若干个计算单元。对于复杂的计算单元有多达40-50个支架、10个以上位移输出节点。配管专业需要从结果文件中提取各个支架的最大支撑载荷以及穿墙点等处的最大位移来分析管道布置的合理性，并根据分析的结果调整管道结构。对于一个大型的电站项目，计算的管道单元往往多达几百甚至上千个，涉及的支架达1万多个，从这么多的计算单元中找出各个支架的反力和位移需要耗费大量人力和时间，而且由于人为因素的影响还会存在一定的错误率，后期再人工校对数据又要耗费大量的时间，极大地影响工程进度。

VBA（ Visual Basic for Application） 是微软在 Office中内置的宏编程语言。它语法形式上与VB相同，易学易用，功能却非常强大，可以操控各种 Office对象，为用户文档增添强大的功能。例如 Excel VBA可控制各单元格的内容、格式，VBA 还具有各种图形用户界面控件，可开发出各种 Windows 图形界面的应用程序，可以使用程序批量处理大量文档数据，减轻手工文档处理的烦琐劳动。

1 工具设计的思想

限于篇幅，本文主要介绍支撑载荷提取工具的设计。为了使工具能够实现“一键操作”，支撑载荷数据提取工具主要实现以下功能：1）批量读取多个计算单元的结果文件；2）提取每个计算单元中的各个支架在指定组合工况局部坐标系下的最大反力以及对应的反力矢量方向；3）列出支架所在的计算单元号、版本号以及对应的节点号、支架功能。工具实现的功能流程图如下图1.

2 工具实现的关键技术

工具设计最关键的是支架名的统一，由于在使用PipeStress软件时支架的命名因人而异，如果不统一命名规则，就无法实现通过识别支架名读取支架对应的反力和力的方向矢量，因此在设计工具前先确定好支架及其他必要输出点对应的节点命名方式，如标准支架名为3BFX10ST0071-GL，“-”前为支架名，其后为支架功能。确定好命名规则后，就可以进行接下来的程序开发了。本工具的开发主要包括以下3点关键技术。

2.1 批量读取文件名

一个计算单元对应一个结果文件（*.ppo，下用PPO代替），如果有多个结果文件，就需要依次读取各个文件，作者通过识别文件名来实现依次读取，在此之前需要将所有要读取的PPO文件放入同一个路径下的某个文件夹中，然后程序将该路径下的所有的PPO文件名依次写入工作表，另外结果文件的命名方式采用的是计算单元号+版本号，这便为后续识别支架所在的单元号及版本号提供了思路，具体代码如下：

Public Sub readname（ ） '读取PPO文件名

On Error GoTo errhandle

n =0

i =5

Do

If n < 1 Then

Sheets（4）.Cells（i，1）=Dir（"D：＼VBA＼*.ppo"） '将指定路径下的第一个PPO文件名保存在工作表sheet4的A5单元格

i = i + 1

Else： Sheets（4）.Cells（i，1）=Dir '将同一路径下的下一个PPO文件名依次保存在A6，A7……

i = i + 1

End If

n = n + 1

Loop

errhandle：

End Sub

2.2 读取原始结果文件

识别完需要读取的PPO文件名后，就从第一个PPO文件开始依次读取PPO中的原始数据，由于是在Excel中完成读取过程，因此首先要将原始数据写入Excel中，具体实现方法如下：

Public Sub readdata（ ） '将PPO文件中每一行的数据读取到工作表sheet1的A列

On Error Resume Next

Dim i As Long，J As Long

Dim mydata As String

Open "D：＼VBA＼" & Sheets（4）.Cells（J ， 1） For Input As #1 '打开指定路径下的PPO文件（i和J是变量，初始值i=0，J=1，变量J是控制读取下一个ppo文件的关键变量）

Do While Not EOF（1） '循环至文件尾

Line Input #1， mydata '读取PPO文件一行数据并将其赋予变量mydata

Sheets（1）.Cells（1， 1）.Offset（i， 0）.Value = mydata '变量mydata的值赋给工作表sheet1的A列

i = i + 1

Loop

Close #1 '关闭指定的PPO文件

Sheets（1）.Cells（1， 1）.Value = ""

Sheets（1）.Cells（1， 2）.Value = ""

End Sub

2.3 提取支架相关数据

结果文件中支架及其反力的分布存在一定的规律，每页支架反力抬头有提示文字Mark，支架所在行带有特征记号Sign，在支架所在行提供了支架名、支架功能、节点号以及力的方向矢量，在接下来的若干行中提供了指定组合工况下支架的反力，根据这个规律，可以先根据Mark通过IF函数确定开始查找位置并记录行数row1，然后从row1+1行开始到其后的若干行通过FIND函数查找Sign直至找到支架所在行，从该行中提取支架名称、支架功能和力的方向矢量，用同样的方法提取支架反力，代码如下：

Public Sub getsupport（） '将PPO文件中局部坐标系下，指定组合工况CASE下的载荷提取出来放在sheet2中

Dim i As Long， j As Integer， k As Integer

Dim arr（200， 10）， es As Range

i = 1

j = 1

Do While i< Sheets（1）.Cells（Rows.Count，1）.

End（xlUp）.Row

If Left（Sheets（1）.Cells（i， 1）， numb） = "Mark" then '通过提示文字Mark确定开始查找位置

With Worksheets（1）.Range（"A" & i + 1， "A" & i + 100） '从A列第i+1行开始查找直至第i+100行

If Not .Find（"Sign"） Is Nothing Then

Set es = .Find（"Sign"）

Sheets（2）.Range（"N" & j） = Left（es， numb） '查找支架特征标记Sign，并将支架名返回给工作表sheet2的N列

Else

i = i + 1

End If

End With

With Worksheets（1）.Range（"a" & i + 1， "a" & i + 200） '从A列第i+1行开始查找直至第i+200行

If Not es = .Find（"CASE"） Is Nothing Then '如果找到工况组合CASE，则将查找值返回给数组arr（）

arr（k， j） = es.Value

Else

i = i + 1

End If

End With

End IF

Loop

End Sub

3 工具运行

利用VBA的图形控件设置一个启动按钮，如下图2所示，图中1即为控件，“读取力学报告”控件，单击该按钮即可实现一键解决批量读取结果文件、提取支架信息、自动输出读取结果等功能。读取的结果文件名依次写入区域2所在列，单元格3的值代表所有需要读取的结果文件数。

输出读取结果如下：

4 结束语

基于Excel VBA的数据读取工具已经开发完成并获得了实际应用，取得了非常好的效果，大大提高了工作效率，节省了人力和时间。利用Excel应用程序中的VBA编程功能非常好地解决了批量数据提取的问题，为此类问题的解决提供了思路和方法，也可以为类似大数据处理提供参考。通过实际使用效果可以发现，利用数组可以大大提高读写速度，对于大批量的数据处理具有非常明显的优势。

参考文献：

[1] 韩加国.Excel VBA从入门到精通[M].北京：化学工业出版社，2009.

[2] 马维峰.Excel VBA应用开发[M].北京：电子工业出版社，2006.

[3] Excel Home. Excel VBA实战技巧精粹[M].北京：人民邮电出版社，2013.

[4] 邓朝辉.基于Excel VBA的事业单位岗位设置评分系统的设计与实现[J].计算机与现代化， 2012， 206（12）：213-215.

[5] 王志军.运用Excel公式进行数据处理技巧八则[J].电脑知识与技术， 2015（10）：44-46.

【通聯编辑：梁书】