基于Excel VBA的批量數(shù)據(jù)提取工具開(kāi)發(fā)

劉歡 彭書強(qiáng)

摘要：為了批量提取管道力學(xué)計(jì)算軟件PipeStress輸出的結(jié)果文件中的數(shù)據(jù)，需要使用計(jì)算機(jī)從結(jié)果文件中提取支架的反力及反力方向矢量等數(shù)據(jù)。基于VBA簡(jiǎn)單易學(xué)易用的特點(diǎn)，提出使用Excel電子表格讀取結(jié)果文件，并使用VBA語(yǔ)言編程進(jìn)行數(shù)據(jù)處理的方法。經(jīng)過(guò)實(shí)際應(yīng)用表明，該工具能滿足實(shí)際需求，大大提高了工作效率，節(jié)省了大量的人力和時(shí)間，使用、維護(hù)簡(jiǎn)便，可靠性高。

關(guān)鍵詞：批量；數(shù)據(jù)提取；Excel；VBA

中圖分類號(hào)：TP311 文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：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

某電站采用管道系統(tǒng)有限元分析軟件PipeStress對(duì)電站的管道系統(tǒng)進(jìn)行應(yīng)力分析，軟件輸出的結(jié)果文件（*.ppo）包含了各工況組合下的支架（或阻尼器等）的支撐載荷、接管載荷、支架（和自由端、穿墻點(diǎn)等）處的節(jié)點(diǎn)位移。在實(shí)際的管道力學(xué)分析項(xiàng)目中，會(huì)根據(jù)管道系統(tǒng)結(jié)構(gòu)的復(fù)雜程度將管道分成若干個(gè)計(jì)算單元。對(duì)于復(fù)雜的計(jì)算單元有多達(dá)40-50個(gè)支架、10個(gè)以上位移輸出節(jié)點(diǎn)。配管專業(yè)需要從結(jié)果文件中提取各個(gè)支架的最大支撐載荷以及穿墻點(diǎn)等處的最大位移來(lái)分析管道布置的合理性，并根據(jù)分析的結(jié)果調(diào)整管道結(jié)構(gòu)。對(duì)于一個(gè)大型的電站項(xiàng)目，計(jì)算的管道單元往往多達(dá)幾百甚至上千個(gè)，涉及的支架達(dá)1萬(wàn)多個(gè)，從這么多的計(jì)算單元中找出各個(gè)支架的反力和位移需要耗費(fèi)大量人力和時(shí)間，而且由于人為因素的影響還會(huì)存在一定的錯(cuò)誤率，后期再人工校對(duì)數(shù)據(jù)又要耗費(fèi)大量的時(shí)間，極大地影響工程進(jìn)度。

VBA（ Visual Basic for Application） 是微軟在 Office中內(nèi)置的宏編程語(yǔ)言。它語(yǔ)法形式上與VB相同，易學(xué)易用，功能卻非常強(qiáng)大，可以操控各種 Office對(duì)象，為用戶文檔增添強(qiáng)大的功能。例如 Excel VBA可控制各單元格的內(nèi)容、格式，VBA 還具有各種圖形用戶界面控件，可開(kāi)發(fā)出各種 Windows 圖形界面的應(yīng)用程序，可以使用程序批量處理大量文檔數(shù)據(jù)，減輕手工文檔處理的煩瑣勞動(dòng)。

1 工具設(shè)計(jì)的思想

限于篇幅，本文主要介紹支撐載荷提取工具的設(shè)計(jì)。為了使工具能夠?qū)崿F(xiàn)“一鍵操作”，支撐載荷數(shù)據(jù)提取工具主要實(shí)現(xiàn)以下功能：1）批量讀取多個(gè)計(jì)算單元的結(jié)果文件；2）提取每個(gè)計(jì)算單元中的各個(gè)支架在指定組合工況局部坐標(biāo)系下的最大反力以及對(duì)應(yīng)的反力矢量方向；3）列出支架所在的計(jì)算單元號(hào)、版本號(hào)以及對(duì)應(yīng)的節(jié)點(diǎn)號(hào)、支架功能。工具實(shí)現(xiàn)的功能流程圖如下圖1.

2 工具實(shí)現(xiàn)的關(guān)鍵技術(shù)

工具設(shè)計(jì)最關(guān)鍵的是支架名的統(tǒng)一，由于在使用PipeStress軟件時(shí)支架的命名因人而異，如果不統(tǒng)一命名規(guī)則，就無(wú)法實(shí)現(xiàn)通過(guò)識(shí)別支架名讀取支架對(duì)應(yīng)的反力和力的方向矢量，因此在設(shè)計(jì)工具前先確定好支架及其他必要輸出點(diǎn)對(duì)應(yīng)的節(jié)點(diǎn)命名方式，如標(biāo)準(zhǔn)支架名為3BFX10ST0071-GL，“-”前為支架名，其后為支架功能。確定好命名規(guī)則后，就可以進(jìn)行接下來(lái)的程序開(kāi)發(fā)了。本工具的開(kāi)發(fā)主要包括以下3點(diǎn)關(guān)鍵技術(shù)。

2.1 批量讀取文件名

一個(gè)計(jì)算單元對(duì)應(yīng)一個(gè)結(jié)果文件（*.ppo，下用PPO代替），如果有多個(gè)結(jié)果文件，就需要依次讀取各個(gè)文件，作者通過(guò)識(shí)別文件名來(lái)實(shí)現(xiàn)依次讀取，在此之前需要將所有要讀取的PPO文件放入同一個(gè)路徑下的某個(gè)文件夾中，然后程序?qū)⒃撀窂较碌乃械腜PO文件名依次寫入工作表，另外結(jié)果文件的命名方式采用的是計(jì)算單元號(hào)+版本號(hào)，這便為后續(xù)識(shí)別支架所在的單元號(hào)及版本號(hào)提供了思路，具體代碼如下：

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"） '將指定路徑下的第一個(gè)PPO文件名保存在工作表sheet4的A5單元格

i = i + 1

Else： Sheets（4）.Cells（i，1）=Dir '將同一路徑下的下一個(gè)PPO文件名依次保存在A6，A7……

i = i + 1

End If

n = n + 1

Loop

errhandle：

End Sub

2.2 讀取原始結(jié)果文件

識(shí)別完需要讀取的PPO文件名后，就從第一個(gè)PPO文件開(kāi)始依次讀取PPO中的原始數(shù)據(jù)，由于是在Excel中完成讀取過(guò)程，因此首先要將原始數(shù)據(jù)寫入Excel中，具體實(shí)現(xiàn)方法如下：

Public Sub readdata（ ） '將PPO文件中每一行的數(shù)據(jù)讀取到工作表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 '打開(kāi)指定路徑下的PPO文件（i和J是變量，初始值i=0，J=1，變量J是控制讀取下一個(gè)ppo文件的關(guān)鍵變量）

Do While Not EOF（1） '循環(huán)至文件尾

Line Input #1， mydata '讀取PPO文件一行數(shù)據(jù)并將其賦予變量mydata

Sheets（1）.Cells（1， 1）.Offset（i， 0）.Value = mydata '變量mydata的值賦給工作表sheet1的A列

i = i + 1

Loop

Close #1 '關(guān)閉指定的PPO文件

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

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

End Sub

2.3 提取支架相關(guān)數(shù)據(jù)

結(jié)果文件中支架及其反力的分布存在一定的規(guī)律，每頁(yè)支架反力抬頭有提示文字Mark，支架所在行帶有特征記號(hào)Sign，在支架所在行提供了支架名、支架功能、節(jié)點(diǎn)號(hào)以及力的方向矢量，在接下來(lái)的若干行中提供了指定組合工況下支架的反力，根據(jù)這個(gè)規(guī)律，可以先根據(jù)Mark通過(guò)IF函數(shù)確定開(kāi)始查找位置并記錄行數(shù)row1，然后從row1+1行開(kāi)始到其后的若干行通過(guò)FIND函數(shù)查找Sign直至找到支架所在行，從該行中提取支架名稱、支架功能和力的方向矢量，用同樣的方法提取支架反力，代碼如下：

Public Sub getsupport（） '將PPO文件中局部坐標(biāo)系下，指定組合工況CASE下的載荷提取出來(lái)放在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 '通過(guò)提示文字Mark確定開(kāi)始查找位置

With Worksheets（1）.Range（"A" & i + 1， "A" & i + 100） '從A列第i+1行開(kāi)始查找直至第i+100行

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

Set es = .Find（"Sign"）

Sheets（2）.Range（"N" & j） = Left（es， numb） '查找支架特征標(biāo)記Sign，并將支架名返回給工作表sheet2的N列

Else

i = i + 1

End If

End With

With Worksheets（1）.Range（"a" & i + 1， "a" & i + 200） '從A列第i+1行開(kāi)始查找直至第i+200行

If Not es = .Find（"CASE"） Is Nothing Then '如果找到工況組合CASE，則將查找值返回給數(shù)組arr（）

arr（k， j） = es.Value

Else

i = i + 1

End If

End With

End IF

Loop

End Sub

3 工具運(yùn)行

利用VBA的圖形控件設(shè)置一個(gè)啟動(dòng)按鈕，如下圖2所示，圖中1即為控件，“讀取力學(xué)報(bào)告”控件，單擊該按鈕即可實(shí)現(xiàn)一鍵解決批量讀取結(jié)果文件、提取支架信息、自動(dòng)輸出讀取結(jié)果等功能。讀取的結(jié)果文件名依次寫入?yún)^(qū)域2所在列，單元格3的值代表所有需要讀取的結(jié)果文件數(shù)。

輸出讀取結(jié)果如下：

4 結(jié)束語(yǔ)

基于Excel VBA的數(shù)據(jù)讀取工具已經(jīng)開(kāi)發(fā)完成并獲得了實(shí)際應(yīng)用，取得了非常好的效果，大大提高了工作效率，節(jié)省了人力和時(shí)間。利用Excel應(yīng)用程序中的VBA編程功能非常好地解決了批量數(shù)據(jù)提取的問(wèn)題，為此類問(wèn)題的解決提供了思路和方法，也可以為類似大數(shù)據(jù)處理提供參考。通過(guò)實(shí)際使用效果可以發(fā)現(xiàn)，利用數(shù)組可以大大提高讀寫速度，對(duì)于大批量的數(shù)據(jù)處理具有非常明顯的優(yōu)勢(shì)。

參考文獻(xiàn)：

[1] 韓加國(guó).Excel VBA從入門到精通[M].北京：化學(xué)工業(yè)出版社，2009.

[2] 馬維峰.Excel VBA應(yīng)用開(kāi)發(fā)[M].北京：電子工業(yè)出版社，2006.

[3] Excel Home. Excel VBA實(shí)戰(zhàn)技巧精粹[M].北京：人民郵電出版社，2013.

[4] 鄧朝輝.基于Excel VBA的事業(yè)單位崗位設(shè)置評(píng)分系統(tǒng)的設(shè)計(jì)與實(shí)現(xiàn)[J].計(jì)算機(jī)與現(xiàn)代化， 2012， 206（12）：213-215.

[5] 王志軍.運(yùn)用Excel公式進(jìn)行數(shù)據(jù)處理技巧八則[J].電腦知識(shí)與技術(shù)， 2015（10）：44-46.

【通聯(lián)編輯：梁書】