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

劉歡 彭書強(qiáng)

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

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

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

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

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

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

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

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

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

2.1 批量讀取文件名

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

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 讀取原始結(jié)果文件

識別完需要讀取的PPO文件名后，就從第一個PPO文件開始依次讀取PPO中的原始數(shù)據(jù)，由于是在Excel中完成讀取過程，因此首先要將原始數(shù)據(jù)寫入Excel中，具體實現(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 '打開指定路徑下的PPO文件（i和J是變量，初始值i=0，J=1，變量J是控制讀取下一個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ī)律，每頁支架反力抬頭有提示文字Mark，支架所在行帶有特征記號Sign，在支架所在行提供了支架名、支架功能、節(jié)點號以及力的方向矢量，在接下來的若干行中提供了指定組合工況下支架的反力，根據(jù)這個規(guī)律，可以先根據(jù)Mark通過IF函數(shù)確定開始查找位置并記錄行數(shù)row1，然后從row1+1行開始到其后的若干行通過FIND函數(shù)查找Sign直至找到支架所在行，從該行中提取支架名稱、支架功能和力的方向矢量，用同樣的方法提取支架反力，代碼如下：

Public Sub getsupport（） '將PPO文件中局部坐標(biāo)系下，指定組合工況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） '查找支架特征標(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行開始查找直至第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 工具運行

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

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

4 結(jié)束語

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

參考文獻(xiàn)：

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

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

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

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

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

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