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

ExcelCtr使用说明

源码： https://github.com/jackletter/ExcelCtr.Standard

一、概述

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

二、功能

(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 #或parameters.name,直接将它嵌入引用的位置即可。

5.3 idbs

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

5.4 calitems

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

5.5 caldts

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

5.6 sheets

这个节点表示excel模板中的Sheets集合，子节点sheet按照name属性与excel中的Sheet对应。

sheet节点可以有子节点rowmass(表示一堆按顺序的行，注意：只能有一个rowmass节点)、pic(表示输出的图片)。

5.6.1 输出行：

Sheet节点下的rowmass子节点下包含一堆按顺序输出的行（row节点）。

row节点的model属性表示这个模板行的类型(single表示这是一个简单的行，cycle表示这是一个循环行) ，position属性表示这个模板行引用的excel中的位置的计算方式(absolute表示绝对计算方式，relative表示相对计算方式)。

绝对计算方式就是根据index属性的值直接定位到excel中Sheet的行，而相对计算方式则是在上一个模板行的基础上加上index属性值得到xls中sheet行的。

对于model属性为cycle的row，它的属性binddt表示这个循环行要使用哪个表来循环(可以引用传递进来的参数或模板中配置的查询表)。
row节点下是子节点coltmp，coltmp的属性index表示列索引，value属性表示这个列的值（可以引用传递进来的参数和模板中配置的计算项，以#calitems.name#方式嵌入即可，如果当前行是循环行则还可以使用#binddt.NianDu#方式引用当前行所绑定到的表的某一列），mergekey属性代表这一列的合并标志（没有这一属性就不参数合并），这个合并标志会计算出结果后比较相同则把相邻的两行进行合并。

5.6.2 输出图片

Sheet节点下的pic子节点表示输出项为图片。

pic节点下的from子节点表示输出图片的来源（目前支持：“QRCode”/“File”，前者表示二维码，后者表示本地图片）。

pic节点下的stretch子节点表示输出的图片的位置及拉伸信息。它有model、width、height三个属性。

model属性表示拉伸的类型，取值为：ratio或none，当为ratio时表示按比例拉伸，此时width和height属性只能有一个（width优先）；当为none时表示不按照比例，此时width和height都可以设置。

注意：也可以不设置model属性，表示不进行拉伸。

from节点的value属性值表示的是二维码承载的字符串信息或图片的绝对路径。

from节点的QRSize属性表示的是二维码的尺寸（当输出为二维码时有效）。

pic节点下的stretch下的start节点表示图片的位置信息，col和row属性分别表示图片位置参照的单元格的索引，offx和offy属性表示图片的左上角偏离参照单元格左上角的大小。