NPOI在開發(fā)Excel報(bào)表中的應(yīng)用

NPOI在開發(fā)Excel報(bào)表中的應(yīng)用

主要研究工程測(cè)繪。

馮洋，王仲鋒

(長(zhǎng)春工程學(xué)院勘查與測(cè)繪工程學(xué)院，長(zhǎng)春 130021)

摘要：為了從土地收儲(chǔ)管理系統(tǒng)中高效批量地導(dǎo)出樣式復(fù)雜的Excel報(bào)表，研究探討了基于.NET平臺(tái)，利用ADO.NET連接Access數(shù)據(jù)庫(kù)，利用C#調(diào)用NPOI組件進(jìn)行操作生成Excel文檔，使用SQL語(yǔ)句對(duì)數(shù)據(jù)進(jìn)行操作的方法和過程。結(jié)果表明，在土地收儲(chǔ)管理系統(tǒng)中利用NPOI開發(fā)Excel報(bào)表行之有效。

關(guān)鍵詞：NPOI；Excel報(bào)表；土地收儲(chǔ)；SQL；Access

0引言

對(duì)于管理系統(tǒng)，能動(dòng)態(tài)高效地導(dǎo)出Excel報(bào)表是其重要的功能組成。目前，基于.NET平臺(tái)開發(fā)報(bào)表常采用ASP技術(shù)調(diào)用Excel的Application對(duì)象來實(shí)現(xiàn)[1-3]，但這種方式需要服務(wù)器啟動(dòng)Excel進(jìn)程，運(yùn)行速度慢。為了實(shí)現(xiàn)在無需安裝Office的情況下，高效導(dǎo)出Excel報(bào)表，開發(fā)人員通常會(huì)采用NPOI技術(shù)開發(fā)報(bào)表。目前，李碩、昌兆文等針對(duì)NPOI技術(shù)如何高效開發(fā)Excel報(bào)表做了大量研究，并將NPOI技術(shù)與.NET自帶的Excel組件做了對(duì)比研究[4-9]。但如何實(shí)現(xiàn)利用NPOI技術(shù)批量導(dǎo)出Excel報(bào)表的研究，尚未見報(bào)道。

筆者鑒于NPOI的優(yōu)勢(shì)與用戶的需求，選擇了用NPOI技術(shù)開發(fā)土地收儲(chǔ)管理系統(tǒng)報(bào)表，并結(jié)合TreeView樹控件實(shí)現(xiàn)了批量導(dǎo)出報(bào)表的功能，提高了作業(yè)人員的工作效率。

1NPOI在土地收儲(chǔ)管理系統(tǒng)中開發(fā)Excel報(bào)表的應(yīng)用

1.1土地收儲(chǔ)管理系統(tǒng)報(bào)表功能的設(shè)計(jì)思路

土地收儲(chǔ)管理系統(tǒng)的Excel報(bào)表的表頭樣式復(fù)雜，每一類報(bào)表匯總規(guī)則也有差異，是典型的中國(guó)式報(bào)表，為了便于后期程序的維護(hù)以及提高程序的運(yùn)行速度，在應(yīng)用程序中設(shè)計(jì)了一套模板和一套生成表頭的方法。此模板是在應(yīng)用程序的Debug文件中存儲(chǔ)一套Excel模板，用于存儲(chǔ)每類報(bào)表數(shù)據(jù)來源的字段名稱，此方式避免了因后期更改數(shù)據(jù)庫(kù)中表的字段名稱而引發(fā)需要修改程序代碼的問題，只需要對(duì)應(yīng)地更改Excel模板中的字段名稱即可；另一套生成表頭的方法是指將報(bào)表表頭寫成固有樣式的方法，便于重復(fù)調(diào)用從而批量導(dǎo)出相同樣式的報(bào)表。

土地收儲(chǔ)業(yè)務(wù)的用戶要求不僅能夠任意導(dǎo)出所需的報(bào)表，更希望能夠擁有批量導(dǎo)出不同的Excel報(bào)表的功能，以達(dá)到提高工作效率的目的。為了實(shí)現(xiàn)導(dǎo)出Excel報(bào)表的任意化、批量化的要求，報(bào)表功能模塊利用TreeView樹控件的節(jié)點(diǎn)展示了所有報(bào)表的歸屬類別，方便用戶根據(jù)自己的需求選擇報(bào)表并導(dǎo)出。每一個(gè)葉子節(jié)點(diǎn)為一張報(bào)表，通過遍歷樹獲取選中葉子節(jié)點(diǎn)Node的Name和Text 2個(gè)屬性信息并存儲(chǔ)在2個(gè)數(shù)組列表中，然后同步遍歷2個(gè)數(shù)組列表內(nèi)部元素，通過傳遞的Name、Text屬性值確定需要導(dǎo)出報(bào)表的生成方法，最后導(dǎo)出Excel報(bào)表。

綜合以上要求以及批量導(dǎo)出Excel報(bào)表的設(shè)計(jì)方案，設(shè)計(jì)了土地收儲(chǔ)管理系統(tǒng)中報(bào)表導(dǎo)出功能的思路圖，如圖1所示。

1.2批量導(dǎo)出Excel報(bào)表的實(shí)現(xiàn)

為了充分展示NPOI深入操作Excel文件的優(yōu)勢(shì)以及批量生成報(bào)表的立體效果，以導(dǎo)出征收集體匯總表和征收集體明細(xì)表為案例，詳細(xì)講述如何實(shí)現(xiàn)批量導(dǎo)出Excel報(bào)表。

1.2.1利用ADO.NET連接數(shù)據(jù)庫(kù)

土地收儲(chǔ)業(yè)務(wù)中很多報(bào)表需要按不同的年份導(dǎo)出形成年度報(bào)表，采用TreeView動(dòng)態(tài)生成報(bào)表窗體需要與數(shù)據(jù)庫(kù)進(jìn)行動(dòng)態(tài)交互，這樣可以避免數(shù)據(jù)的冗雜。而生成Excel報(bào)表也需要與數(shù)據(jù)庫(kù)進(jìn)行動(dòng)態(tài)交互以獲取數(shù)據(jù)，因此連接數(shù)據(jù)庫(kù)是生成Excel報(bào)表的鑰匙。以下為利用ADO.NET連接Access數(shù)據(jù)庫(kù)的主要代碼：

//獲取數(shù)據(jù)庫(kù)的起始路徑

string strPath=System.Windows.Forms.Application.StartupPath+"\土地收儲(chǔ)管理數(shù)據(jù)庫(kù).mdb"；

//獲取連接字符串

string ConStr="Provider=Microsoft.Jet.OLEDB.4.0；Data source='"+strPath+"'"；

//實(shí)例化連接字符串

OleDbConnection oleCon=new OleDbConnection(ConStr)；

oleCon.Open()；//打開數(shù)據(jù)庫(kù)連接

圖1　設(shè)計(jì)思路圖

1.2.2遍歷樹TreeView

遍歷樹TreeView以獲取選中的葉子節(jié)點(diǎn)的Name、Text的屬性值是實(shí)現(xiàn)批量導(dǎo)出Excel報(bào)表的關(guān)鍵，這2個(gè)屬性值決定了報(bào)表的歸屬類別與報(bào)表的導(dǎo)出方法。在這里，采用通用的遞歸算法遍歷樹達(dá)到了目的。以下為遍歷樹并獲取選中葉子節(jié)點(diǎn)屬性值的代碼：

foreach(TreeNode children in tn.Nodes)//利用foreach語(yǔ)句遍歷當(dāng)前樹的二級(jí)節(jié)點(diǎn)

{

if(children.Nodes.Count==0 && children.Checked==true)

{

paras_name.Add(children.Name)；//將葉子節(jié)點(diǎn)的Name值存儲(chǔ)在名為paras_name數(shù)組列表中

paras_text.Add(children.Text)；//將葉子節(jié)點(diǎn)的Text值存儲(chǔ)在名為paras_text數(shù)組列表中

readNode(children，paras_name，paras_text)；//遞歸遍歷樹

}

}

foreach(TreeNode children1 in children.Nodes)

{

if(children1.Nodes.Count==0 && children1.Checked==true)

{

paras_name.Add(children1.Name)；

paras_text.Add(children1.Text)；

}

……

}

}

}

1.2.3添加NPOI的引用

土地管理系統(tǒng)是基于C#.NET3.5平臺(tái)開發(fā)的，而NPOI組件是第三方開源組件，需要先從NPOI官方網(wǎng)站上下載NPOI.2.0的DLL文件，再在.NET添加引用的選項(xiàng)卡中單擊瀏覽選項(xiàng)卡選擇對(duì)應(yīng)的DLL文件，這里，要將NPOI解壓目錄下的所有DLL添加至項(xiàng)目中。最后在代碼開始處添加引用：

using NPOI.HSSF.UserModel；

using NPOI.POIFS.FileSystem；

using NPOI.HPSF；

using NPOI.SS.UserModel ；

using NPOI.SS.Util ；

1.2.4遍歷數(shù)組列表

以征收集體匯總表與征收集體明細(xì)表為例批量導(dǎo)出Excel報(bào)表，涉及到2次傳參的問題，而這里利用foreach遍歷數(shù)組列表優(yōu)于for、while等其他循環(huán)語(yǔ)句，這是因?yàn)閒oreach語(yǔ)句無需知道數(shù)組列表存儲(chǔ)元素的個(gè)數(shù)，訪問最后一個(gè)元素后，自動(dòng)跳出循環(huán)。

利用foreach語(yǔ)句遍歷數(shù)組列表，確定報(bào)表的導(dǎo)出方法，是批量生成Excel報(bào)表的核心。主要代碼如下：

int indext_name=0；

foreach(string pstring in Parasname)//利用foreach語(yǔ)句遍歷參數(shù)的數(shù)組列表

{

#region//判斷傳遞參數(shù)屬于第幾種表格，按方法導(dǎo)出

string method=null；

string Character_Text=null；

if(pstring=="0")

{

method="0"；

}

if(pstring=="1")

{

method="1"；

Character_Text=Parastext[indext_name].ToString()；

}

……

indext_name++；//將Parastext參數(shù)的索引值每循環(huán)依次加1，為了使引用到此參數(shù)時(shí)能與Parasname一致

switch(method)/

{

case"0"：

ISheet sheet1=hssfworkbook.CreateSheet("凈月高新區(qū)征收集體土地匯總表")；//創(chuàng)建工作表

……

case "1"：

ISheet sheetzm=hssfworkbook.CreateSheet("征收集體明細(xì)表")；//創(chuàng)建工作表

//調(diào)用征收集體明細(xì)表表頭的方法

TDZYSHP_tablehead(hssfworkbook，sheetzm，style，stylexh，styled，stylef1)；

……

}

}

//導(dǎo)出Excel文件

FileStream file=new FileStream(text+@"征收集體統(tǒng)計(jì)表.xls"，F(xiàn)ileMode.Create)；

hssfworkbook.Write(file)；

file.Close()；

1.2.5NPOI對(duì)單元格的基本操作

每一個(gè)類報(bào)表都有自己的固定樣式的表頭，將生成報(bào)表表頭寫成方法，便于應(yīng)用程序的調(diào)用是批量導(dǎo)出Excel報(bào)表的一大特點(diǎn)。以生成征收集體明細(xì)表表頭的方法為例，深刻體會(huì)NPOI對(duì)單元格操作的簡(jiǎn)易優(yōu)勢(shì)。

1.2.5.1生成征收集體明細(xì)表表頭的方法

public static void TDZYSHP_tablehead(HSSFWorkbook hssfworkbook，ISheet sheet，ICellStyle style，ICellStyle stylexh，ICellStyle styled，ICellStyle stylef1)

{

IRow rowTitlem=sheet.CreateRow(0)；//創(chuàng)建單元格行

ICell cellTitlem=rowTitlem.CreateCell(0)；//創(chuàng)建單元格

cellTitlem.SetCellValue("凈月高新區(qū)征收集體明細(xì)表")；//寫入標(biāo)題

rowTitlem.Height=40*20；//設(shè)置標(biāo)題單元格的高度//設(shè)置單元格類字體的布局以及樣式

cellTitlem.CellStyle=style；

SetCellRangeAddress(sheet，0，0，0，19)；//調(diào)用合并單元格的方法

……

sheet.GetRow(2).GetCell(0).SetCellValue("年度")；

SetCellRangeAddress(sheet，2，3，0，0)；//調(diào)用合并單元格的方法

sheet.GetRow(2).GetCell(1).SetCellValue("序號(hào)")；

SetCellRangeAddress(sheet，2，3，1，1)；

……

//設(shè)置列寬

sheet.SetColumnWidth(0，7*256)；//A

sheet.SetColumnWidth(1，7*256)；//B

……

}

1.2.5.2合并單元格方法

CellRangeAddress cellRangeAddress=new CellRangeAddress(0，0，0，19)；//設(shè)置合并區(qū)域?yàn)?行0列到0行11列

sheetzm.AddMergedRegion(cellRangeAddress)；//為工作簿sheetzm添加合并區(qū)域

1.2.5.3單元格的樣式

ICellStyle style=hssfworkbook.CreateCellStyle()；//創(chuàng)建樣式對(duì)象

style.Alignment=NPOI.SS.UserModel.HorizontalAlignment.Center；//水平居中

style.VerticalAlignment=VerticalAlignment.Justify；//垂直居中

IFont font=hssfworkbook.CreateFont()；//創(chuàng)建字體樣式對(duì)象

font.FontHeightInPoints=18；//設(shè)置字號(hào)

font.Boldweight=(short)NPOI.SS.UserModel.FontBoldWeight.Bold；//字體加粗

font.FontName="宋體"；//設(shè)置字體

style.SetFont(font)；//將字體賦給單元格樣式

cellTitlem.CellStyle=style；//將單元格樣式賦給表標(biāo)題單元格的樣式

1.2.6讀取Excel模板

在土地收儲(chǔ)管理系統(tǒng)的思路設(shè)計(jì)中已提到Excel模板的重要作用是獲取報(bào)表所需的Access表內(nèi)部的字段名稱。為了靈活的運(yùn)用Excel模板寫入的表字段名稱，不僅需要讀取Excel模板，而且要利用數(shù)組列表獨(dú)特的無需設(shè)置內(nèi)存長(zhǎng)處的特點(diǎn)存儲(chǔ)這些表字段名稱。以下為讀取模板的代碼以及結(jié)合SQL語(yǔ)言靈活運(yùn)用存儲(chǔ)在數(shù)組列表中的字段。

1.2.6.1讀取Excel模板

string TDZYSHP_modelname="……-征收集體土地統(tǒng)計(jì)表"；//Excel模板的表名

ArrayList TDZYSHP_fieldname=new ArrayList()；//定義一個(gè)獲取模板字段名的數(shù)組列表

string TDZYSHP_datatablename="TDZYSHP"；//定義一個(gè)數(shù)據(jù)庫(kù)中的表名

string TDZYSHP_connectdatatable=""；//定義一個(gè)查詢語(yǔ)句空字符串

ReadModel(TDZYSHP_modelname，ref TDZYSHP_fieldname，TDZYSHP_datatablename，ref TDZYSHP_connectdatatable)；//調(diào)用讀取模板方法

///

///Excel模板的表名

///返回模板中的字段名

///所需要數(shù)據(jù)庫(kù)中的表名

///SQL查詢語(yǔ)句

public static void ReadModel(string modelname，ref ArrayList fieldname，string datatablename，ref string connectdatatable)

{

string strPathExcel=System.Windows.Forms.Application.StartupPath+\模板\+modelname+".xls"；//獲取Excel模板

FileStream fileExcel=new FileStream(strPathExcel，F(xiàn)ileMode.Open)；

HSSFWorkbook work=new HSSFWorkbook(fileExcel)；

string field=work.GetSheet("Sheet1").GetRow(2).GetCell(0).ToString()；

int jcol=0；//字段所在的列

while(!field.Trim().Equals(""))//判斷單元格是否為空字符串

{

field=work.GetSheet("Sheet1").GetRow(2).GetCell(jcol).ToString()；

fieldname.Add(field)；

jcol++；

if(work.GetSheet("Sheet1").GetRow(2).GetCell(jcol)!=null)

{

field=work.GetSheet("Sheet1").GetRow(2).GetCell(jcol).ToString()；

}

else

{

break；

}

}

fileExcel.Close()；

fileExcel.Dispose()；

string datablefield=""；//定義一個(gè)查詢語(yǔ)句的字符串變量

datablefield="select"+fieldname[0].ToString()；

for(int j=1；j

{

datablefield=datablefield+"，"+fieldname[j].ToString()；

}

connectdatatable=datablefield+"FROM"+datatablename；

}

以上為讀取某征收集體土地統(tǒng)計(jì)表模板為例，圖2為此模板的截圖。

1.2.6.2利用SQL語(yǔ)句生成DataTable

通過利用讀取模板的返回值fieldname和connectdatatable，就可以利用SQL語(yǔ)句靈活地從數(shù)據(jù)庫(kù)中提取需要的數(shù)據(jù)，生成DataTable，最后將DataTable中的數(shù)據(jù)按報(bào)表的匯總規(guī)則填到Excel報(bào)表中。以生成征收集體明細(xì)表為例：

//SQL語(yǔ)句

string cmdtxt=TDZYSHP_connectdatatable+"where"+TDZYSHP_fieldname[0].ToString()+"is not null"……."order by"+TDZYSHP_fieldname[0].ToString()+"asc，"….；

//生成DataTable

DataTable dt=new DataTable()；

OleDbDataAdapter oleDap=new OleDbDataAdapter(cmdtxt，oleCon)；//通過SQL語(yǔ)句進(jìn)行數(shù)據(jù)的篩選

DataSet ds=new DataSet()；

oleDap.Fill(ds，"table")；//將篩選后的數(shù)據(jù)添加到數(shù)據(jù)集

dt=ds.Tables[0]；//dt為獲取的DataTable

圖2　征收集體土地統(tǒng)計(jì)表模板

1.2.7成果展示

利用NPOI技術(shù)對(duì)Excel報(bào)表進(jìn)行開發(fā)，批量導(dǎo)出的征收集體匯總表與征收集體明細(xì)表的報(bào)表如圖3所示。

圖3是一個(gè)Excel文件含有多個(gè)Excel形式報(bào)表的批量導(dǎo)出結(jié)果，同時(shí)含有征收集體匯總表與征收集體明細(xì)表2個(gè)報(bào)表，既充分展示了批量導(dǎo)出Excel文件報(bào)表的立體效果，又證明了NPOI開發(fā)Excel報(bào)表的可行性。

圖3　征收集體匯總表

2結(jié)語(yǔ)

本文介紹了如何在.NET平臺(tái)上使用C#并結(jié)合NPOI對(duì)Excel文檔的開發(fā)技術(shù)批量導(dǎo)出Excel報(bào)表的方法，并以實(shí)例驗(yàn)證了NPOI對(duì)Excel報(bào)表開發(fā)的簡(jiǎn)易、高效等特點(diǎn)。掌握了NPOI對(duì)Excel報(bào)表的開發(fā)技術(shù)，不僅能夠提高制作報(bào)表的效率，更為重要的是能夠在工程實(shí)踐中為數(shù)據(jù)批量處理問題提供一種簡(jiǎn)便、高效率、可行的方法。

參考文獻(xiàn)

[1] 劉政敏.ASP.NET中用Excel實(shí)現(xiàn)報(bào)表設(shè)計(jì)[J].計(jì)算機(jī)時(shí)代，2003(5)：26-28.

[2] 葛華.B/S模式下EXCEL報(bào)表功能的實(shí)現(xiàn)[J].沙洲職業(yè)工學(xué)院學(xué)報(bào)，2003(2)：19-20.

[3] 石紅春，鐘新文.淺談Asp生成Excel報(bào)表的兩種常見方法[J].信息與電腦(理論版)，2010(2)：169.

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

[5] 徐斌，陳員義.基于.NET自動(dòng)生成Excel高級(jí)報(bào)表[J].電腦編程技巧與維護(hù)，2011，24：69-70.

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

[7] 李碩.一種高效率的.NET平臺(tái)Excel文件控制方法[J].軟件導(dǎo)刊，2013，11：26-28.

[8] 錢立，鄧緋.高校新生入學(xué)宿舍管理系統(tǒng)的設(shè)計(jì)與實(shí)現(xiàn)[J].重慶三峽學(xué)院學(xué)報(bào)，2014(3)：48-51.

[9] 李志秀，張軍，楊麗紅.獨(dú)立應(yīng)用系統(tǒng)間數(shù)據(jù)交換的研究及實(shí)現(xiàn)[J].云南大學(xué)學(xué)報(bào)(自然科學(xué)版)，2013(S2)：135-137.

doi:10.3969/j.issn.1009-8984.2015.02.029

收稿日期：2015-05-27

作者簡(jiǎn)介：馮洋(1990-)，女(漢)，長(zhǎng)春，在讀碩士

中圖分類號(hào)：TP311.52

文獻(xiàn)標(biāo)志碼：A

文章編號(hào)：1009-8984(2015)02-0109-05

The application of NPOI in the development of Excel reports

FENG Yang，et al.

(SchoolofProspecting&SurveyEngineering，

ChangchunInstituteofTechnology，Changchun130021，China)

Abstract：To export complex style Excel report forms efficiently and in batch from land purchasing and storage management system，this study discusses the method and process which base on the.NET platform，utilizes ADO.NET to connect ACCESS database，and utilizes C # to invoke NPOI components to create Excel documents by operating the components，as well as uses the SQL statements to manipulate the data.The results show that it is an efficient method to develop Excel report forms by means of NPOI in land purchasing and storage management system.

Key words：NPOI；excel report forms；land purchasing and storage；SQL；Access