基于XLS模板的城市地下管線成果報(bào)表自動(dòng)生成方法研究

蔣利+韓李濤+劉建勛

摘要：城市地下管線探測(cè)成果上交時(shí)，探測(cè)管線成果數(shù)據(jù)需按照規(guī)定的格式生成報(bào)表。數(shù)據(jù)成果報(bào)表存在許多單元格合并、表格跨頁(yè)、跨頁(yè)保留表頭等要求，對(duì)此，提出一種基于XLS模板的地下管線成果數(shù)據(jù)生成方法。先結(jié)合規(guī)范要求自定義報(bào)表模板，然后在管線成果數(shù)據(jù)與模板單元之間建立關(guān)聯(lián)，最后基于NPOI組件庫(kù)生成符合規(guī)范要求的城市地下管線數(shù)據(jù)成果Excel報(bào)表。實(shí)驗(yàn)結(jié)果表明，該方法能夠生成符合規(guī)范要求的復(fù)雜Excel報(bào)表。

關(guān)鍵詞：Excel報(bào)表；XLS模板；地下管線；NPOI

DOIDOI：10.11907/rjdk.172024

中圖分類號(hào)：TP301

文獻(xiàn)標(biāo)識(shí)碼：A 文章編號(hào)：1672-7800（2017）012-0009-03

Abstract：When the results of urban underground pipeline detection are submitted， the achievements of detecting pipeline data to generate report forms in accordance with the prescribed format. For the productive report forms of urban underground pipeline data have complex demand such as merging cells、generating forms across the page and retaining headers across the page， this paper proposes a method based on the XLS template to generate report forms of urban underground pipeline data， firstly， designing custom report template， then establishing connections between the results of pipeline data and template units， finally， generating Excel reports of urban underground pipeline result data based on NPOI library normatively. The experimental results show that the method can generate complex Excel report forms which meet the specification requirements.

Key Words：Excel report forms； XLS templates； underground pipeline； NPOI

0 引言

自動(dòng)生成數(shù)據(jù)報(bào)表不僅是城市地下管線數(shù)據(jù)上報(bào)系統(tǒng)的必備功能之一，而且在許多管理應(yīng)用系統(tǒng)都需要將大量數(shù)據(jù)成果按照自定義格式生成Excel報(bào)表，從而減輕勞動(dòng)量，提高生產(chǎn)效率。常用的Excel報(bào)表生成方式有兩種：①調(diào)用Office的COM組件進(jìn)行報(bào)表導(dǎo)出[1]；②調(diào)用NPOI[2]第三方組件進(jìn)行報(bào)表導(dǎo)出。

第一種方式需要在安裝Office軟件的前提下，調(diào)用Office的COM組件、引入Microsoft Excel object library完成導(dǎo)出工作[3-5]，該過程冗雜，進(jìn)程較慢，若要完成多數(shù)據(jù)導(dǎo)出或多用戶操作，需要產(chǎn)生多個(gè)Office進(jìn)程，這一缺陷在導(dǎo)出自定義復(fù)雜表格的時(shí)候頗為顯著[6]。

第二種方式依賴NPOI組件庫(kù)，能夠脫離Excel相關(guān)組件，可同時(shí)支持Web應(yīng)用導(dǎo)出及本地應(yīng)用導(dǎo)出Excel。利用NPOI還可進(jìn)行Excel報(bào)表樣式、字體、文字位置等設(shè)置[7]，生成復(fù)雜報(bào)表更為容易。文獻(xiàn)[6]、文獻(xiàn)[7]利用NPOI組件實(shí)現(xiàn)了對(duì)規(guī)則數(shù)據(jù)、簡(jiǎn)單樣式報(bào)表的自動(dòng)生成與導(dǎo)出，主要利用NPOI組件直接創(chuàng)建工作簿，然后初始化工作簿并進(jìn)行規(guī)則數(shù)據(jù)的插入。文獻(xiàn)[8]引入Excel模板進(jìn)行數(shù)據(jù)報(bào)表生成，能夠生成復(fù)雜格式的報(bào)表，表頭可以合并單元格，但表格主體數(shù)據(jù)行格式較為簡(jiǎn)單，無(wú)單元合并問題，沒有實(shí)現(xiàn)表格循環(huán)。

城市地下管線成果報(bào)表格式要求較為復(fù)雜，且需導(dǎo)出的成果數(shù)據(jù)量較多。因此，本文在對(duì)比分析上述方法的基礎(chǔ)上，設(shè)計(jì)一種基于Excel模板調(diào)用NPOI組件進(jìn)行城市地下管線成果報(bào)表自動(dòng)生成的方法。

1 報(bào)表生成設(shè)計(jì)

1.1 報(bào)表格式分析

城市地下管線成果數(shù)據(jù)Excel報(bào)表格式較為復(fù)雜，如圖1所示。

（1）Excel報(bào)表不單純解決如何合并表頭單元格實(shí)現(xiàn)復(fù)雜表頭的問題，表格表頭中包含的數(shù)值并非一成不變，標(biāo)題以及管線信息標(biāo)題等為不變的數(shù)據(jù)量，而空格處的信息會(huì)隨著報(bào)表的下次循環(huán)而發(fā)生相應(yīng)改變。

（2）除表頭外，表格主體數(shù)據(jù)行中的數(shù)據(jù)填充也不僅僅是將數(shù)據(jù)按照規(guī)則順序填充。數(shù)據(jù)本身是管線的相關(guān)屬性信息，包含點(diǎn)號(hào)、連接點(diǎn)信息以及距離等信息。點(diǎn)號(hào)之間有順序的先后關(guān)系。管線種類多樣，導(dǎo)出的管線信息報(bào)表需要將不同種類管線信息按照管線方向進(jìn)行歸納整理，而距離等信息需要在單元格之間進(jìn)行相關(guān)計(jì)算。

（3）在表尾部分，隨著下一表格的生成，還需進(jìn)行相關(guān)注記與分頁(yè)、添加備注、總長(zhǎng)度信息以及增添檢查者等相關(guān)變量信息。

1.2 技術(shù)流程

鑒于地下管線成果報(bào)表格式的復(fù)雜性，選擇基于xls模板的報(bào)表生成方法，將導(dǎo)出數(shù)據(jù)與Excel格式相互分離。在開發(fā)過程中，將數(shù)據(jù)報(bào)表的表格樣式、字體樣式等不變內(nèi)容由Excel模板實(shí)現(xiàn)，而報(bào)表中的管線數(shù)據(jù)、探測(cè)單位信息等變化內(nèi)容由程序中的代碼實(shí)現(xiàn)填入。這樣，在程序開發(fā)過程中只需關(guān)心變化的管線數(shù)據(jù)，而不必花費(fèi)精力關(guān)注Excel表格樣式，從而實(shí)現(xiàn)高效導(dǎo)出表格，如圖2所示。

2 導(dǎo)出表格

城市地下管線成果表自動(dòng)生成系統(tǒng)開發(fā)環(huán)境：Visual Studio2010，基于.net Framework 4.0，開發(fā)語(yǔ)言為C#，實(shí)現(xiàn)包括模板制作、NPOI引用添加、數(shù)據(jù)準(zhǔn)備、基于NPOI的報(bào)表生成幾個(gè)關(guān)鍵步驟。

2.1 制作Excel模板

模板制作較為簡(jiǎn)單，只需制作與要求格式相同的表格即可。無(wú)論是表頭還是表尾的表格合并，只需要在表格工具中利用合并單元格功能完成。在需要填充數(shù)據(jù)內(nèi)容的單元格中添加參數(shù)變量，設(shè)置參數(shù)變量的格式、數(shù)值類型，利用Excel工具或其它表格制作工具都行，只需保存的格式為xls文件即可。地下管線導(dǎo)出制作模板如圖3所示。

2.2 添加NPOI引用

在進(jìn)行開發(fā)前需要調(diào)用第三方NPOI組件。在NPOI官方網(wǎng)站中下載相關(guān)的DLL文件，在開發(fā)平臺(tái)的解決方案管理器中，添加DLL文件，添加的文件有NPOI、NPOI.OpenXml4Net、NPOI.OOXML、NPOI.OpenXmlFormats共4個(gè)DLL文件。添加完成后需要添加相關(guān)的命名空間。

2.3 數(shù)據(jù)準(zhǔn)備

城市地下管線數(shù)據(jù)存儲(chǔ)在Acess數(shù)據(jù)庫(kù)中，導(dǎo)出工作前需要進(jìn)行數(shù)據(jù)庫(kù)連接。城市地下管線數(shù)據(jù)庫(kù)包含污水、雨水、電信、天然氣等許多類型的管線數(shù)據(jù)，每一類管線數(shù)據(jù)包含一個(gè)管點(diǎn)表與管段表。為了方便管線成果報(bào)表自動(dòng)生成，在數(shù)據(jù)導(dǎo)出前需要進(jìn)行數(shù)據(jù)準(zhǔn)備工作。構(gòu)造TablePoints和TableLines，分別按照管線相關(guān)順序遍歷數(shù)據(jù)。準(zhǔn)備工作如下：

（1）讀入成果表管線連接關(guān)系文件。地下管線數(shù)據(jù)庫(kù)中管段類型多樣，包含污水管線、雨水、電信、天然氣等多類管線，各類管線連接方式多樣。所以，為了方便管線數(shù)據(jù)導(dǎo)出，在導(dǎo)出前，需要進(jìn)行管線連接關(guān)系文件的讀入。在系統(tǒng)中，該文件可由用戶自定義輸入，對(duì)管線的連接進(jìn)行自定義編輯。

（2）TablePoints數(shù)據(jù)的填充。數(shù)據(jù)的準(zhǔn)備工作主要以管點(diǎn)號(hào)為依據(jù)展開，建立管點(diǎn)匯總數(shù)組，對(duì)管點(diǎn)數(shù)目與類型匯總，依據(jù)管點(diǎn)號(hào)從DataGridView中獲取相應(yīng)的數(shù)據(jù)填充。

（3）TableLines數(shù)據(jù)填充。在初始化TableLines時(shí)，其行數(shù)為TablePoints行數(shù)的2倍，填充過程分3步：①對(duì)沒有前驅(qū)管點(diǎn)的第一個(gè)管點(diǎn)進(jìn)行處理；②對(duì)既有前驅(qū)管點(diǎn)進(jìn)行后繼管點(diǎn)、中間管點(diǎn)處理；③對(duì)后繼管點(diǎn)的最后管點(diǎn)進(jìn)行處理。

（4）補(bǔ)齊TablePoints和TableLines，使其行數(shù)正好為單個(gè)表格行數(shù)的整倍數(shù)，以滿足每頁(yè)為同一類型管段數(shù)據(jù)要求。

2.4 NPOI組件導(dǎo)出Excel表格關(guān)鍵技術(shù)

（1）讀取Excel模板，獲取IWorkbook對(duì)象。

using （var fileStream=new FileStream（file， FileMode.Open， FileAccess.Read））

（2）關(guān)聯(lián)數(shù)據(jù)到導(dǎo)出模板。對(duì)模板中的各個(gè)參數(shù)常量進(jìn)行定義，將數(shù)據(jù)準(zhǔn)備階段生成的兩個(gè)Table表格中的數(shù)據(jù)綁定到模板參數(shù)變量中。

2.5 成果展示

基于XLS模板，結(jié)合NPOI技術(shù)批量自動(dòng)生成城市地下管線成果報(bào)表如圖4所示。報(bào)表包含類型為雨水管道相關(guān)信息，表頭為調(diào)查單位、個(gè)人相關(guān)信息以及管線類型等信息；表格主體數(shù)據(jù)行中包含管點(diǎn)的連接方式、距離、埋深、材質(zhì)等信息，每一頁(yè)表格中包含的管段種類都為同一類型。管點(diǎn)的前驅(qū)連接點(diǎn)與后繼連接點(diǎn)在表格中都能體現(xiàn)，管段之間以空白單元格隔開。當(dāng)本頁(yè)表格容量無(wú)法滿足此類型管點(diǎn)個(gè)數(shù)時(shí)，將會(huì)循環(huán)到下一頁(yè)，以此實(shí)現(xiàn)數(shù)據(jù)的批量導(dǎo)出。表尾部分包含頁(yè)碼、備注以及統(tǒng)計(jì)信息。該表格展現(xiàn)了基于XLS模板結(jié)合NPOI技術(shù)，批量導(dǎo)出自定義報(bào)表的效果。

3 結(jié)語(yǔ)

本文基于C#語(yǔ)言開發(fā)平臺(tái)，基于XLS模板并結(jié)合NPOI組件實(shí)現(xiàn)了Excel表格的導(dǎo)出。地下管線成果數(shù)據(jù)導(dǎo)出案例證明，模板與NPOI組件的結(jié)合可以高效實(shí)現(xiàn)海量數(shù)據(jù)的循環(huán)，滿足對(duì)導(dǎo)出表格格式的復(fù)雜要求。在工程實(shí)踐項(xiàng)目中運(yùn)用本文方法，可以高效處理批量數(shù)據(jù)導(dǎo)出工作。

參考文獻(xiàn)：

[1] 蔡小艷，李龍騰，葛玉，等.基于C#的Excel數(shù)據(jù)導(dǎo)入導(dǎo)出研究與實(shí)現(xiàn)[J].智能計(jì)算機(jī)與應(yīng)用，2014（5）：83-85.

[2] 陳峰.使用NPOI有效讀寫Office文件[J].金融科技時(shí)代，2011（6）：69-70.

[3] 李菲菲，劉天時(shí).一種基于ASP.NET的Excel報(bào)表導(dǎo)入與導(dǎo)出方法[J].軟件導(dǎo)刊，2012（8）：29-30.

[4] 康維虎，柳浪濤.基于.NET的Excel報(bào)表設(shè)計(jì)與實(shí)現(xiàn)[J].軟件導(dǎo)刊，2016（5）：101-103.

[5] 蘇慶，李忠良，吳偉民.在C#.NET下實(shí)現(xiàn)數(shù)據(jù)導(dǎo)入Excel的方法研究[J].計(jì)算機(jī)與現(xiàn)代化，2011（4）：29-31.

[6] 昌兆文，劉湖平，曾紹軍.基于NPOI導(dǎo)出Excel文件的研究與實(shí)現(xiàn)[J].中國(guó)管理信息化，2013（15）：93-94.

[7] 張麗妮.Excel技術(shù)在大學(xué)生創(chuàng)新項(xiàng)目管理中的應(yīng)用[J].軟件導(dǎo)刊，2011（3）：58-60.

[8] 馮洋，王仲鋒.NPOI在開發(fā)Excel報(bào)表中的應(yīng)用[J].長(zhǎng)春工程學(xué)院學(xué)報(bào)：自然科學(xué)版，2015（2）：109-121.

（責(zé)任編輯：杜能鋼）