NPOI在开发Excel报表中的应用

NPOI在开发Excel报表中的应用

主要研究工程测绘。

冯洋，王仲锋

(长春工程学院勘查与测绘工程学院，长春 130021)

摘要：为了从土地收储管理系统中高效批量地导出样式复杂的Excel报表，研究探讨了基于.NET平台，利用ADO.NET连接Access数据库，利用C#调用NPOI组件进行操作生成Excel文档，使用SQL语句对数据进行操作的方法和过程。结果表明，在土地收储管理系统中利用NPOI开发Excel报表行之有效。

关键词：NPOI；Excel报表；土地收储；SQL；Access

0引言

对于管理系统，能动态高效地导出Excel报表是其重要的功能组成。目前，基于.NET平台开发报表常采用ASP技术调用Excel的Application对象来实现[1-3]，但这种方式需要服务器启动Excel进程，运行速度慢。为了实现在无需安装Office的情况下，高效导出Excel报表，开发人员通常会采用NPOI技术开发报表。目前，李硕、昌兆文等针对NPOI技术如何高效开发Excel报表做了大量研究，并将NPOI技术与.NET自带的Excel组件做了对比研究[4-9]。但如何实现利用NPOI技术批量导出Excel报表的研究，尚未见报道。

笔者鉴于NPOI的优势与用户的需求，选择了用NPOI技术开发土地收储管理系统报表，并结合TreeView树控件实现了批量导出报表的功能，提高了作业人员的工作效率。

1NPOI在土地收储管理系统中开发Excel报表的应用

1.1土地收储管理系统报表功能的设计思路

土地收储管理系统的Excel报表的表头样式复杂，每一类报表汇总规则也有差异，是典型的中国式报表，为了便于后期程序的维护以及提高程序的运行速度，在应用程序中设计了一套模板和一套生成表头的方法。此模板是在应用程序的Debug文件中存储一套Excel模板，用于存储每类报表数据来源的字段名称，此方式避免了因后期更改数据库中表的字段名称而引发需要修改程序代码的问题，只需要对应地更改Excel模板中的字段名称即可；另一套生成表头的方法是指将报表表头写成固有样式的方法，便于重复调用从而批量导出相同样式的报表。

土地收储业务的用户要求不仅能够任意导出所需的报表，更希望能够拥有批量导出不同的Excel报表的功能，以达到提高工作效率的目的。为了实现导出Excel报表的任意化、批量化的要求，报表功能模块利用TreeView树控件的节点展示了所有报表的归属类别，方便用户根据自己的需求选择报表并导出。每一个叶子节点为一张报表，通过遍历树获取选中叶子节点Node的Name和Text 2个属性信息并存储在2个数组列表中，然后同步遍历2个数组列表内部元素，通过传递的Name、Text属性值确定需要导出报表的生成方法，最后导出Excel报表。

综合以上要求以及批量导出Excel报表的设计方案，设计了土地收储管理系统中报表导出功能的思路图，如图1所示。

1.2批量导出Excel报表的实现

为了充分展示NPOI深入操作Excel文件的优势以及批量生成报表的立体效果，以导出征收集体汇总表和征收集体明细表为案例，详细讲述如何实现批量导出Excel报表。

1.2.1利用ADO.NET连接数据库

土地收储业务中很多报表需要按不同的年份导出形成年度报表，采用TreeView动态生成报表窗体需要与数据库进行动态交互，这样可以避免数据的冗杂。而生成Excel报表也需要与数据库进行动态交互以获取数据，因此连接数据库是生成Excel报表的钥匙。以下为利用ADO.NET连接Access数据库的主要代码：

//获取数据库的起始路径

string strPath=System.Windows.Forms.Application.StartupPath+"\土地收储管理数据库.mdb"；

//获取连接字符串

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

//实例化连接字符串

OleDbConnection oleCon=new OleDbConnection(ConStr)；

oleCon.Open()；//打开数据库连接

图1　设计思路图

1.2.2遍历树TreeView

遍历树TreeView以获取选中的叶子节点的Name、Text的属性值是实现批量导出Excel报表的关键，这2个属性值决定了报表的归属类别与报表的导出方法。在这里，采用通用的递归算法遍历树达到了目的。以下为遍历树并获取选中叶子节点属性值的代码：

foreach(TreeNode children in tn.Nodes)//利用foreach语句遍历当前树的二级节点

{

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

{

paras_name.Add(children.Name)；//将叶子节点的Name值存储在名为paras_name数组列表中

paras_text.Add(children.Text)；//将叶子节点的Text值存储在名为paras_text数组列表中

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的引用

土地管理系统是基于C#.NET3.5平台开发的，而NPOI组件是第三方开源组件，需要先从NPOI官方网站上下载NPOI.2.0的DLL文件，再在.NET添加引用的选项卡中单击浏览选项卡选择对应的DLL文件，这里，要将NPOI解压目录下的所有DLL添加至项目中。最后在代码开始处添加引用：

using NPOI.HSSF.UserModel；

using NPOI.POIFS.FileSystem；

using NPOI.HPSF；

using NPOI.SS.UserModel ；

using NPOI.SS.Util ；

1.2.4遍历数组列表

以征收集体汇总表与征收集体明细表为例批量导出Excel报表，涉及到2次传参的问题，而这里利用foreach遍历数组列表优于for、while等其他循环语句，这是因为foreach语句无需知道数组列表存储元素的个数，访问最后一个元素后，自动跳出循环。

利用foreach语句遍历数组列表，确定报表的导出方法，是批量生成Excel报表的核心。主要代码如下：

int indext_name=0；

foreach(string pstring in Parasname)//利用foreach语句遍历参数的数组列表

{

#region//判断传递参数属于第几种表格，按方法导出

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参数的索引值每循环依次加1，为了使引用到此参数时能与Parasname一致

switch(method)/

{

case"0"：

ISheet sheet1=hssfworkbook.CreateSheet("净月高新区征收集体土地汇总表")；//创建工作表

……

case "1"：

ISheet sheetzm=hssfworkbook.CreateSheet("征收集体明细表")；//创建工作表

//调用征收集体明细表表头的方法

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

……

}

}

//导出Excel文件

FileStream file=new FileStream(text+@"征收集体统计表.xls"，FileMode.Create)；

hssfworkbook.Write(file)；

file.Close()；

1.2.5NPOI对单元格的基本操作

每一个类报表都有自己的固定样式的表头，将生成报表表头写成方法，便于应用程序的调用是批量导出Excel报表的一大特点。以生成征收集体明细表表头的方法为例，深刻体会NPOI对单元格操作的简易优势。

1.2.5.1生成征收集体明细表表头的方法

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

{

IRow rowTitlem=sheet.CreateRow(0)；//创建单元格行

ICell cellTitlem=rowTitlem.CreateCell(0)；//创建单元格

cellTitlem.SetCellValue("净月高新区征收集体明细表")；//写入标题

rowTitlem.Height=40*20；//设置标题单元格的高度//设置单元格类字体的布局以及样式

cellTitlem.CellStyle=style；

SetCellRangeAddress(sheet，0，0，0，19)；//调用合并单元格的方法

……

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

SetCellRangeAddress(sheet，2，3，0，0)；//调用合并单元格的方法

sheet.GetRow(2).GetCell(1).SetCellValue("序号")；

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

……

//设置列宽

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

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

……

}

1.2.5.2合并单元格方法

CellRangeAddress cellRangeAddress=new CellRangeAddress(0，0，0，19)；//设置合并区域为0行0列到0行11列

sheetzm.AddMergedRegion(cellRangeAddress)；//为工作簿sheetzm添加合并区域

1.2.5.3单元格的样式

ICellStyle style=hssfworkbook.CreateCellStyle()；//创建样式对象

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

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

IFont font=hssfworkbook.CreateFont()；//创建字体样式对象

font.FontHeightInPoints=18；//设置字号

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

font.FontName="宋体"；//设置字体

style.SetFont(font)；//将字体赋给单元格样式

cellTitlem.CellStyle=style；//将单元格样式赋给表标题单元格的样式

1.2.6读取Excel模板

在土地收储管理系统的思路设计中已提到Excel模板的重要作用是获取报表所需的Access表内部的字段名称。为了灵活的运用Excel模板写入的表字段名称，不仅需要读取Excel模板，而且要利用数组列表独特的无需设置内存长处的特点存储这些表字段名称。以下为读取模板的代码以及结合SQL语言灵活运用存储在数组列表中的字段。

1.2.6.1读取Excel模板

string TDZYSHP_modelname="……-征收集体土地统计表"；//Excel模板的表名

ArrayList TDZYSHP_fieldname=new ArrayList()；//定义一个获取模板字段名的数组列表

string TDZYSHP_datatablename="TDZYSHP"；//定义一个数据库中的表名

string TDZYSHP_connectdatatable=""；//定义一个查询语句空字符串

ReadModel(TDZYSHP_modelname，ref TDZYSHP_fieldname，TDZYSHP_datatablename，ref TDZYSHP_connectdatatable)；//调用读取模板方法

///

///读取模板，获取数据查询语句的字段

///

///Excel模板的表名

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

///所需要数据库中的表名

///SQL查询语句

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，FileMode.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=""；//定义一个查询语句的字符串变量

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

for(int j=1；j

{

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

}

connectdatatable=datablefield+"FROM"+datatablename；

}

以上为读取某征收集体土地统计表模板为例，图2为此模板的截图。

1.2.6.2利用SQL语句生成DataTable

通过利用读取模板的返回值fieldname和connectdatatable，就可以利用SQL语句灵活地从数据库中提取需要的数据，生成DataTable，最后将DataTable中的数据按报表的汇总规则填到Excel报表中。以生成征收集体明细表为例：

//SQL语句

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语句进行数据的筛选

DataSet ds=new DataSet()；

oleDap.Fill(ds，"table")；//将筛选后的数据添加到数据集

dt=ds.Tables[0]；//dt为获取的DataTable

图2　征收集体土地统计表模板

1.2.7成果展示

利用NPOI技术对Excel报表进行开发，批量导出的征收集体汇总表与征收集体明细表的报表如图3所示。

图3是一个Excel文件含有多个Excel形式报表的批量导出结果，同时含有征收集体汇总表与征收集体明细表2个报表，既充分展示了批量导出Excel文件报表的立体效果，又证明了NPOI开发Excel报表的可行性。

图3　征收集体汇总表

2结语

本文介绍了如何在.NET平台上使用C#并结合NPOI对Excel文档的开发技术批量导出Excel报表的方法，并以实例验证了NPOI对Excel报表开发的简易、高效等特点。掌握了NPOI对Excel报表的开发技术，不仅能够提高制作报表的效率，更为重要的是能够在工程实践中为数据批量处理问题提供一种简便、高效率、可行的方法。

参考文献

[1] 刘政敏.ASP.NET中用Excel实现报表设计[J].计算机时代，2003(5)：26-28.

[2] 葛华.B/S模式下EXCEL报表功能的实现[J].沙洲职业工学院学报，2003(2)：19-20.

[3] 石红春，钟新文.浅谈Asp生成Excel报表的两种常见方法[J].信息与电脑(理论版)，2010(2)：169.

[4] 张丽妮.Excel技术在大学生创新项目管理中的应用[J].软件导刊，2011(3)：58-60.

[5] 徐斌，陈员义.基于.NET自动生成Excel高级报表[J].电脑编程技巧与维护，2011，24：69-70.

[6] 昌兆文，刘湖平，曾绍军.基于NPOI导出Excel文件的研究与实现[J].中国管理信息化，2013，15：93-94.

[7] 李硕.一种高效率的.NET平台Excel文件控制方法[J].软件导刊，2013，11：26-28.

[8] 钱立，邓绯.高校新生入学宿舍管理系统的设计与实现[J].重庆三峡学院学报，2014(3)：48-51.

[9] 李志秀，张军，杨丽红.独立应用系统间数据交换的研究及实现[J].云南大学学报(自然科学版)，2013(S2)：135-137.

doi:10.3969/j.issn.1009-8984.2015.02.029

收稿日期：2015-05-27

作者简介：冯洋(1990-)，女(汉)，长春，在读硕士

中图分类号：TP311.52

文献标志码：A

文章编号：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