Excel导入导出组件

        当前的程序开发中（Struts + Spring + Hibernate/Ibatis），有很多需要用Excel文件进行数据的导入，导出操作。
不同的人有不同的处理方式，我也尝试过一些方法，但是Excel文件不确定的情况下总是有些变动，每次都要修改程序。
于是就想能不能把Excel文件每行记录填充成一个javaBean，封装成一个List返回，然后把List交给Hibernate/Ibatis来处理。
如果Excel文件有什么小的变动，只需要修改配制文件就可以解决呢。

可以，产生一个xml配制文件，在配制文件中，把excel中的每一列和javabean中的属性对应些来，就可以解决啦。

1、Excel组件可以把现有的格式化的Excel文件每笔记录填充指定的JavaBean,封装成一个List返回给用户。用户可以利用封装之后的javabean　list　进行相关操作（交给hibernate,ibatis等处理）。
2、技术来源于网络，回归于网络。但这种设计的想法，希望能给大家带来方便。
3、本人技术有限，请各位朋友多多指教。

实例说明：
Excel文件：

部门名称	部门编号	发文简称	排序	操作结果
总部	A001	永久	2	00000
一分部	A002	长期	3	111111
分部	A003	长期	5

 

目标：对每行记录转换成JavaBean，封装一个List返回给用户。

XML配制：

<!---->

<!---->＜?xml version="1.0" encoding="UTF-8" ?＞
＜models＞
 ＜model id="deptModel" class="com.javayjm.test.model.DeptModel"＞
  ＜property name="deptName" column="1" excelTitleName="部门名称" dataType="String" maxLength="15" fixity="" codeTableName="" default=""/＞
  ＜property name="deptCode" column="2" excelTitleName="部门编号" dataType="String" maxLength="15" fixity="" codeTableName="" default=""/＞
  ＜property name="sendFileName" column="3" excelTitleName="发文简称" dataType="String" maxLength="15" fixity="" codeTableName="bgqx" default="C"/＞
  ＜property name="deptNo" column="4" excelTitleName="排序" dataType="String" maxLength="15" fixity="yes" codeTableName="" default="10"/＞
 
 ＜/model＞
 
 ＜!-- column :指excel没有表头名称时,可以对应所排的列号 --＞
＜/models＞<!---->

实例调用：

  Map map = new HashMap();
  map.put("deptNo", "1");
  map.put("bgqx永久", "Y");
  ExcelManager test = new ExcelManager("E:\\workspace\\test.xls","deptModel",map);
  List modelList = test.getModelList();

System.out.println("--" + ToStringBuilder.reflectionToString(test.getConfigTitle().entrySet().toArray()));
  
  for(int i = 0;i    Object obj = modelList.get(i);
   System.out.println("--" + obj.getClass().getSimpleName());
   System.out.println("--" + ToStringBuilder.reflectionToString(obj));
  }

输出结果：

init Class = com.javayjm.test.model.DeptModel
init Class = com.javayjm.test.model.DeptModel
init Class = com.javayjm.test.model.DeptModel
--[Ljava.lang.Object;@4413ee[{deptName=部门名称,deptCode=部门编号,deptNo=排序,sendFileName=发文简称}]
--DeptModel
--com.javayjm.test.model.DeptModel@197a37c[id=0,deptName=总部,deptCode=A001,sendFileName=Y,receiveFileName=,deptNo=1,flag=0,message=]
--DeptModel
--com.javayjm.test.model.DeptModel@17fa65e[id=0,deptName=分部中华人民共各国万元日期要分,deptCode=A002,sendFileName=C,receiveFileName=,deptNo=1,flag=0,message=]
--DeptModel
--com.javayjm.test.model.DeptModel@18385e3[id=0,deptName=分部,deptCode=A003,sendFileName=C,receiveFileName=,deptNo=1,flag=0,message=]

 

 

lib 清单

 

commons-lang-2.0.jar
commons-logging.jar
dom4j-1.6.jar
jxl.jar
log4j-1.2.8.jar
spring.jar

 

评论

21 楼 guooscar 2008-10-31  

其实可以考虑的深一点。
转换的工作其实就是一个讲多行数据到对象列表的过程。
可以将多种不同的数据来源抽象成数据源
比如说
interface DataSource{
     boolean hasNext();
     String[] next();
}
然后在用一个转换器讲数据源提供的数据转换成对象列表。这个过程可以使用
反射(或者动态字节码生成)，自动检测每个属性的类型，然后设置一套转换器，来进行String到Object的转换。
当然在转换器中还可以添加一些Listener Filter之类的东东，这样就能更方便的控制转换过程。
我已经基本实现了这样的一套东西，准备过几天放到sourceforge上。

20 楼 yanjiajun 2008-10-31  

曾经在项目中也做了一个类似的excel导入功能。当时做得比较简单，excel列到VO的转换协议是直接写在excel标题中了。。没有你做的细致，感谢你的工作，值得参考

19 楼 fuliang 2008-01-13  

曾经使用泛型做的，一个通用的Excel和Javabean相互转换的东东。
写到Excel的时候很容易，读的时候可以考虑使用Spring的BeanWapper
和PropertyEditor来把字符串类型进行转换，转化成javabean。

18 楼 xianglei 2008-01-13  

最好用poi对多语言支持比较好!!

17 楼 nanyjm 2008-01-11  

myyate 写道

楼主，看了你的代码不错，比我简洁多了。
我最近也写了类似的基础包，做一个通用程序，在web系统中把查询的数据导出到Excel和把上传过来的Excel数据导入DB。也是通过类似的XML配置Excel列和Dto（临时存放）成员对应。
我提几点与你交流一下：
1，既然你这个是要导入到DB中，就要有验证，我看你代码的验证主要是根据property元素里设置类似dataType、maxLength...来限制的，这样验证功能有限，比如还有某些列他的值是一些固定的值、日期必须符合yyyy-MM-dd...；还有比如你的部门，在Excel中用户她选择一些具体的部门名称，而不是编号之类，这个时候也需要验证这个部门名称是否合法？这些您是如何验证的？我把验证独立出来进行配置验证，参考了common-validator，分为简单的验证（如类型长度最大值...），列表验证，还有DB中是否存在...这些都实现一个Validator接口。不知楼主有什么更好的办法？
2. 你从Excel读取数据到Bean（Dto）中的时候，不知道你有没有碰到，我是用POI读的，它读出来就那几种格式，而且只要Cell中全是数字，他就默认认为是Number型，整数也给你带上小数点，日期他默认是的也是Number型，这点有点麻烦。我做的时候是根据Dto中对应的成员类型把读出的数据进行相应的转换，这些类型转换也写了一个Converter接口，当然也可以直接用common包。
3. 既然这些数据是在Excel中写入或者从别的DB导入进去的，就难免有错误，而且这些错误不能以Exception直接抛出，必须生成一个txt或者Html文件格式的错误列表，指出错误的行号和列以及错误的详细信息，以便其修改再次上传。不知道您这个做了没有？如果做了可以写出来交流交流。我是验证的时候把所有错误全部记录下来（验证过程中有了错误就不转换数据只进行整个Excel数据的验证），然后把所有错误导出到Html。
4. 导出就简单了，但是我们多出了一个，可以导出到任意文件，如比较重要的是XML而且东航给出的十几个ATA XML Schema文件都是5 600kb的，看了很是头晕。不知道楼主对导出到XML有没有什么研究？

就这些吧，前天基本已经写完了，就剩导出到XML。

目前我还在项目中，功能已经做了很多改进和加强。
1、验证的功能我在下一个版本中已经做了加强。格式为dateType="Date yyyy-MM-dd"。
　只要单元格内是日期，能按给定的格式转换都是可以通过的。
　在jxl中单元格主要设计是：字符串，数字，日期格式。
2、像男（F）　女（M）代码之类的互像转换可以在组件中实现，
  Excel中存储的是男，而实际数据库存储时需要的是 F .见：

  Map map = new HashMap();
  map.put("deptNo", "1");
  map.put("bgqx永久", "Y");

　而像部门名称这种非标准代码表的验证，我想还是把他交给程序员来处理比较好。

　说明：功能上加入了，错误数据的回写功能，就是用户上传要导入的Excel文件，
  把正确的数据导入数据库中，出现错误行数据保留在Excel中，回传给用户，并做错误说明。
 
3、导出已经实现EXCEL格式，XML格式导出转未实现。

16 楼 myyate 2008-01-10  

楼主，看了你的代码不错，比我简洁多了。
我最近也写了类似的基础包，做一个通用程序，在web系统中把查询的数据导出到Excel和把上传过来的Excel数据导入DB。也是通过类似的XML配置Excel列和Dto（临时存放）成员对应。
我提几点与你交流一下：
1，既然你这个是要导入到DB中，就要有验证，我看你代码的验证主要是根据property元素里设置类似dataType、maxLength...来限制的，这样验证功能有限，比如还有某些列他的值是一些固定的值、日期必须符合yyyy-MM-dd...；还有比如你的部门，在Excel中用户她选择一些具体的部门名称，而不是编号之类，这个时候也需要验证这个部门名称是否合法？这些您是如何验证的？我把验证独立出来进行配置验证，参考了common-validator，分为简单的验证（如类型长度最大值...），列表验证，还有DB中是否存在...这些都实现一个Validator接口。不知楼主有什么更好的办法？
2. 你从Excel读取数据到Bean（Dto）中的时候，不知道你有没有碰到，我是用POI读的，它读出来就那几种格式，而且只要Cell中全是数字，他就默认认为是Number型，整数也给你带上小数点，日期他默认是的也是Number型，这点有点麻烦。我做的时候是根据Dto中对应的成员类型把读出的数据进行相应的转换，这些类型转换也写了一个Converter接口，当然也可以直接用common包。
3. 既然这些数据是在Excel中写入或者从别的DB导入进去的，就难免有错误，而且这些错误不能以Exception直接抛出，必须生成一个txt或者Html文件格式的错误列表，指出错误的行号和列以及错误的详细信息，以便其修改再次上传。不知道您这个做了没有？如果做了可以写出来交流交流。我是验证的时候把所有错误全部记录下来（验证过程中有了错误就不转换数据只进行整个Excel数据的验证），然后把所有错误导出到Html。
4. 导出就简单了，但是我们多出了一个，可以导出到任意文件，如比较重要的是XML而且东航给出的十几个ATA XML Schema文件都是5 600kb的，看了很是头晕。不知道楼主对导出到XML有没有什么研究？

就这些吧，前天基本已经写完了，就剩导出到XML。

15 楼 nanyjm 2008-01-08  

commons-lang-2.0.jar
commons-logging.jar
dom4j-1.6.jar
jxl.jar
log4j-1.2.8.jar
spring.jar

14 楼 afacd 2007-12-25  

建议楼主lib加一下所需jar清单

附件汇进来一堆错误，很难测试跑看看，还有少一些东西在，谢谢

13 楼 downpour 2007-12-12  

Java有个CSV的读取组件，可以应对多数的Excel导入工作，至于那些复杂的单元格合并等动能，就算是XML，也需要很复杂的定义，不妨直接操作。

12 楼 nanyjm 2007-12-12  

已经在增加新的功能，可支持整列，整行合并，一列中的多行合并。
如果合并的太多，且完全没有规律，也不太好办。

11 楼 ahau205109 2007-12-12  

can't control

10 楼 coder1982 2007-12-12  

问题是有些EXCEL文档 的格子是 不规律的
比如遇到单元格是合并的 那如何判断？

9 楼 xiaoych 2007-12-06  

不好意思，是 POI 不是POL

8 楼 xiaoych 2007-12-06  

apache 的 pol 好像也可以实现类似的功能。

7 楼 shy_1119 2007-12-06  

恩 应该研究一下

6 楼 nanyjm 2007-12-05  

pandaprince 写道

XML配置数据库字段和EXCEL各列的映射关系

XML配置javaBean属性和EXCEL各列的映射关系，别走弯路。
一些新功能正在开发中。

5 楼 pandaprince 2007-12-05  

这个组件的设计思想还是很清楚的。
XML配置数据库字段和EXCEL各列的映射关系，
ExcelToModel通过JXL解析文件，根据配置每行生成JAVABEAN
遍历得到list

谢谢楼主，晚上回去试试

4 楼 nanyjm 2007-12-01  

看一下：com\javayjm\excel\config\ConfigConstant.java
package com.javayjm.excel.config;

public class ConfigConstant {

/*
* model 的查找编号  id
*/
public static final String MODEL_ID = "id";

/*
* 从Excel 到 Model 转换的目标 javabean class
*/
public static final String MODEL_CLASS="class";

/*
* excel 列中对应到javabean中的 javabean属性名称
*/
public static final String PROPERTY_NAME = "name";

/*
* excel中的标标题中的列数 (  取值的时候可以用javabean属性和对应的列数取值)
*/
public static final String PROPERTY_CLOUMN="cloumn";

/*
* excel 列中对应到javabean中的 excel列标题名称  (取值的时候,可以用 javabean属性和Excel中的标题对应取值)
*/
public static final String PROPERTY_EXCEL_TITLE_NAME = "excelTitleName";

/*
* excel中的列数据要被转换的数据类型
*/
public static final String PROPERTY_DATA_TYPE = "dataType";

/*
* excel中的数据最大长度
*/
public static final String PROPERTY_MAX_LENGTH="maxLength";

/*
* 在excel中没有列数据,需要系统对javabean属性中的某一个值设置一个动态传入的值(指所有JavaBean的这个属性值，都是统一传入的固定值).设置些属性,必须设置默认值
*
* 他的值设置为 fixity = "yes" ,默认为 no.
*/
public static final String PROPERTY_FIXITY="fixity";

/*
* 在导入excel时,有些值在存入系统时,使用的值需要转换
* 如:一个保管期限的下拉列表中有{永久(Y),长期(C),短期(D)}
* 系统中存入的只能是 Y,C,D之类的值,导入Excel的值却是永久,长期,短期这类的值,需要转换.
* 转换方式:取到Excel中的具体值,加上保管期限前辍,从传入的Map中取值.如 取值长期  C = Map.get("bgqx长期");
*
*/
public static final String PROPERTY_CODE_TABLE_NAME="codeTableName";
/*
* 如果值为空,设置的默认值
*/
public static final String PROPERTY_DEFAULT="default";
}

3 楼 nanyjm 2007-12-01  

godson_2003 写道

有个问题不明白:
column :指excel没有表头名称时,可以对应所排的列号 是啥意思?

Excel文件：
部门名称 部门编号 发文简称 排序 操作结果 (没有这一行?)
总部 A001 永久 2 00000
分部 A002 长期 3 111111
分部 A003 长期 5

column:这个版本还没有支持。指属性值取Excel第几列的值。有两各方式取值：一是按配制文件中ExcelTitleName对应Excel中的列标题取值，二是按column对应的Excel中第几列取值。
操作结果：可以去掉，是新功能测试的数据。那是用来做错误信息回写的。（在web应用中，正确的数据写入到数据库中，当有验证未通过的记录时，把错误原因写入到Excel中，再回传给用户。这些是导入不成功的数据。）

map 中传入的是当一列数据有对应的代码表，需要替换。如：长期（C），永久（Y）　长期是显示值，Y是数据库中保存的值
＜property name="sendFileName" column="3" excelTitleName="发文简称" dataType="String" maxLength="15" fixity="" codeTableName="bgqx" default="C"/＞

map.put("deptNo", "1");
map.put("bgqx永久", "Y");

2 楼 godson_2003 2007-12-01  

有个问题不明白:
column :指excel没有表头名称时,可以对应所排的列号 是啥意思?

Excel文件：
部门名称 部门编号 发文简称 排序 操作结果 (没有这一行?)
总部 A001 永久 2 00000
分部 A002 长期 3 111111
分部 A003 长期 5