基于NPOI导入导出excel的工具,方便灵活

ExcelCtr参考手册

github(https://github.com/jackletter/ExcelCtr)

一、概述

基于NPOI的一种导出Excel工具,访问数据库操作依赖于DBUtil(https://github.com/jackletter/DBUtil)

二、功能

(1). 直接将DataTable或DataSet导出成excel
(2). 使用模板将DataTable或DataSet导出成excel
(3). 使用模板将泛型List导出成excel
(4). 支持导出二维码
(5). 直接使用模板去数据库里面根据sql语句导出成excel

三、快速开始(不使用模板)

3.1 环境准备

添加ExcelCtr.dll引用，访问数据库时还需要DBUtil.dll

3.2 代码实现

3.2.1 将DatSet导出

DataSet ds = new DataSet();//手动给ds填充数据//...//直接导出成文件ExcelCtr.ExcelOP.Write("c:\\demo.xls", ds);

3.2.2 将dataset导出具有表格头和合并列的excel

DataSet ds = new DataSet();//手动给ds填充数据//...//直接导出成文件,两个sheet的第一行都有一个标题(不是列标题,列标题手动修改每个datatable的datacolumn的name属性),并且两个sheet的第1和第2列都参数合并ExcelCtr.ExcelOP.Write("c:\\年度收支统计.xls", ds, new List<string>() { "2016年收支统计", "2015年收支统计" }, new List<string>() { "1,2", "1,2" });

四、使用模板导出excel

4.1 使用方法

(1) 要准备一个xml文件和一个同名的xls文件,比如准备使用模板c:\\demo.xls作为导出的控制项,那么在要有c:\\demo.xml文件来对导出进行配置。
(2) 可以向模板中传递参数,使用Hashtable
(3) 代码使用

ExcelCtr.ExcelOP.WriteWithTemplate(new Hashtable(), "c:\\demo.xml", "c:\\年度收支统计.xls");

4.2 仅使用xml配置文件实现dataset导出(包括sheet标题和列合并功能)

4.2.1 简单导出(无列合并和sheet标题)

(1) Xml配置文件书写为”c:\\demo.xml”

<?xml version="1.0" encoding="utf-8" ?><WorkBook version="1.0"> <!--定义参数--><parameters><parameter name="ds" receive="ds" type="System.Data.DataSet" /></parameters> <!--快速导出--><fastsheets useds="parameters.ds" /></WorkBook>

(2) 代码调用

Hashtable ht = new Hashtable();DataSet ds = new DataSet();//手动给ds填充数据//...//将dataset传递给模板使用ht.Add("ds", ds);ExcelCtr.ExcelOP.WriteWithTemplate(ht, "c:\\demo.xml", "c:\\年度收支统计.xls");

4.2.2 导出dataset带列合并和sheet标题

(1) xml配置文件书写为”c:\\demo.xml”

<?xml version="1.0" encoding="utf-8" ?><WorkBook version="1.0"><!--快速带参数导出--><fastsheets><fastsheet usedt="parameters.Person" name="人员表"><!--如果有title就写在第一行最上面--><title value="人员信息表" /></fastsheet><fastsheet usedt="parameters.TJ"><!--如果有title就写在第一行最上面--><title value="年度统计表" /><combinecolindexs value="A,B" /></fastsheet></fastsheets></WorkBook>

(2) 代码调用

Hashtable ht = new Hashtable();DataTable dt = new DataTable();DataTable dt2 = new DataTable();//手动给dt,dt2填充数据//...//将两个datatable传递给模板使用ht.Add("Person", dt);ht.Add("TJ", dt2);ExcelCtr.ExcelOP.WriteWithTemplate(ht, "c:\\demo.xml", "c:\\年度收支统计.xls");

4.3 从数据库中去数据和从参数去数据导入到具体sheet中

(1) xml配置文件书写为”c:\\demo.xml”

<?xml version="1.0" encoding="utf-8" ?><WorkBook version="20161017"><!--定义参数--><parameters><!--默认参数--><!--用户姓名--><parameter name="uname" receive="uname" type="System.String" /><!--用户ID--><parameter name="uid" receive="uid" type="System.String" /><!--当前日期如:2016-10-23--><parameter name="date" receive="date" type="System.String" /><!--当前日期2如:2016年10月23日--><parameter name="date2" receive="date2" type="System.String" /><!--当前日期3如:二零一六年十月二十三日--><parameter name="date3" receive="date3" type="System.String" /><!--当前日期4如:2016年10月--><parameter name="date4" receive="date4" type="System.String" /><!--当前日期5如:二零一六年十月--><parameter name="date5" receive="date5" type="System.String" /><!--当前时间如:2016-10-23 13:58:23--><parameter name="datetime" receive="datetime" type="System.String" /><!--当前时间2如:2016年10月23日 13时58分23秒--><parameter name="datetime2" receive="datetime2" type="System.String" /><!--其他参数--><parameter name="id" receive="id" type="System.String" /><parameter name="ordercol" receive="ordercol" type="System.String" /> </parameters><!--定义数据库访问对象--><idbs><idb name="default"><connstr value="Data Source=.;Initial Catalog=JACKOA;User ID=sa;Password=sa;" /><dbtype value="SQLSERVER" /></idb></idbs><!--定义计算项--><calitems><calitem name="XMMC"><sqltmp><![CDATA[select top 1 XMMC from ZJBF where ID0='{0}']]></sqltmp><useidb value="idbs.default" /><usepara value="parameters.id" /></calitem><calitem name="ZDWZ"><sqltmp><![CDATA[select top 1 ZDWZ from ZJBF where ID0='{0}']]></sqltmp><useidb value="idbs.default" /><usepara value="parameters.id" /></calitem><calitem name="BCJE"><sqltmp><![CDATA[select top 1 BCJE from ZJBF where ID0='{0}']]></sqltmp><useidb value="idbs.default" /><usepara value="parameters.id" /></calitem></calitems><!--定义计算结果表--><caldts><caldt name="TJ"><sqltmp><![CDATA[select top 1 * from TJ order by {0} asc]]></sqltmp><useidb value="idbs.default" /><usepara value="parameters.ordercol" /></caldt></caldts><!--使用打印模板--><sheets><sheet name="Sheet1"><rowmass><row model="single" position="absolute" index="2"><coltmp index="B" value="#parameters.id#" /></row><row model="single" position="absolute" index="3"><coltmp index="B" value="#calitems.XMMC#【#parameters.date2#】" /></row><row model="single" position="absolute" index="4"><coltmp index="B" value="#calitems.ZDWZ#" /></row><row model="single" position="absolute" index="5"><coltmp index="B" value="#calitems.BCJE#" /></row><row model="single" position="absolute" index="6"><coltmp index="B" value="#parameters.uname#" /><coltmp index="G" value="#parameters.date2#" /></row></rowmass><pic><from model="QRCode" value="项目【#calitems.XMMC#】补偿金额【#calitems.BCJE#】" QRSize="100" /><stretch model="original_size"><start col="G" row="8" offx="0" offy="0" /></stretch></pic></sheet><sheet name="Sheet2"><rowmass><row model="cycle" binddt="caldts.TJ" position="absolute" index="3"><coltmp index="A" value="#binddt.NianDu#" mergekey="#binddt.NianDu#" /><coltmp index="B" value="#binddt.YueFen#月" /><coltmp index="C" value="#binddt.ChengBen#" mergekey="#binddt.YueFen##binddt.ChengBen#" /><coltmp index="D" value="#binddt.ShouLiang#" /><coltmp index="E" value="#binddt.JingZhuan#" /></row><row model="single" position="relative" index="1"><coltmp index="B" value="#parameters.uname#" /><coltmp index="E" value="#parameters.datetime#" /></row></rowmass></sheet></sheets></WorkBook>

(2) 模板文件demo.xls

(3) 代码调用

Hashtable ht = new Hashtable();ht.Add("uname", "胡庆杰");ht.Add("uid", "3");ht.Add("date", DateTime.Now.ToString("yyyy-MM-dd"));ht.Add("date2", DateTime.Now.ToString("yyyy年MM月dd日"));ht.Add("date3", "二零一六年十月二十三日");ht.Add("date4", DateTime.Now.ToString("yyyy年MM月"));ht.Add("date5", DateTime.Now.ToString("二零一六年十月"));ht.Add("datetime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));ht.Add("datetime2", DateTime.Now.ToString("yyyy年MM月dd日 HH时mm分ss秒"));ht.Add("id", 1);ht.Add("ordercol", "ID0");ExcelOP.WriteWithTemplate(ht, "c:\\demo.xml", "c:\\out.xls");

五、模板配置说明

5.1 概述

模板的配置定点是WorkBook下面是5类节点parameters、idbs、calitems、caldts、sheets

5.2 parameters

这个节点是声明由调用代码传递进来的参数,其下面可以有多个parameter节点，parameter节点的name属性是在模板中使用的名字, receive属性是由调用代码传递进来的名字,type属性是参数类型(暂时没用可忽略)，注意可以不声明仍可使用传递进来的参数。
这里的参数可以在下面的节点的属性值中使用，引用格式为#parameters.name #,直接将它嵌入引用的字符串中即可。

5.3 idbs

这个几点是用来声明模板中访问数据库使用的连接的。可以同时访问多个数据库，每个要访问的数据库对应一个idb子节点 ，每个idb有一个属性name、两个节点(connstr、dbtype)，name属性作为下面查询的引用，connstr、dbtype分别作为连接的字符串和连接类型。

5.4 calitems

这个节点是用来从数据库中查询单个值的，每个查询对应一个calitem节点。每个calitem有一个那么属性(被引用的标识)三个子节点sqltmp、useidb、usepara，sqltmp节点的内容作为查询语句的模板，useidb标识要使用的连接，直接以idbs.name标识即可，usepara标识要使用的参数(按顺序可以有多个)，直接以parameters.id表示即可。

5.5 caldts

这个节点表示从数据库中查询出来的表。用法类似于calitems。

5.6 sheets

这个节点表示xls模板中的sheets集合，子节点sheet按照name属性与xls中的sheet对应。 Sheet节点可以有子节点rowmass(表示一堆按顺序的行)、pic(表示输出的图片)。Rowmass节点下就是一堆按顺序的row节点。Row节点的model属性表示这个模板行的类型(single表示这是一个简单的行，cycle表示这是一个循环行) ，position属性表示这个模板行引用的xls中的位置的计算方式(absolute表示绝对计算方式，relative表示相对计算方式)，绝对计算方式就是根据index属性的值直接定位到xls中sheet的行而相对计算方式则是在上一个模板行的基础上加上index属性值得到xls中sheet行的。对于model属性为cycle的row它的属性binddt表示这个循环行要使用哪个表来循环(可以引用传递进来的参数或模板中配置的查询表)。
Row节点下是节点coltmp，coltmp的属性index表示列索引，value属性表示这个列的值（可以引用传递进来的参数和模板中配置的计算项，以#calitems.name#方式嵌入即可，如果当前行是循环行则还可以使用#binddt.NianDu#方式引用当前行所绑定到的表的某一列），mergekey属性代表这一列的合并标志（没有这一属性就不参数合并），这个合并标志会计算出结果后比较相同则把相邻的两行进行合并。
Sheet节点下的pic节点表示输出项为图片子节点from表示输出的图片的信息来源，stretch表示输出的图片的位置信息。From的节点model属性目前仅支持QRCode值，代表输出的是二维码。From节点的value属性值表示的是二维码承载的字符串信息，from节点的QRSize属性表示的是二维码的尺寸。Pic节点下的stretch的model属性暂时不用可忽略，stretch下的start节点表示图片的位置信息，col和row属性分别表示图片位置参照的单元格的索引，offx和offy属性表示图片的左上角偏离参照单元格左上角的大小。