JXLS使用按模板单sheet和多sheet excel生成

清疚 2022-04-12 07:10 1284阅读 0赞

### [JXLS使用（一）——Simple Sheet][JXLS_Simple Sheet] ###

JXLS是一个简单易用的一个用于生成和读入Excel的工具，有兴趣的朋友可以到Jxls的官网上面下载最新版本的Jxls，目前Jxls最新的版本是0.9.5，下载的Zip包里面有一个Doc和Example，不过，官网所提供的东西太过简单，如果，需要详细了解Jxls的功能和使用方法的话，可以仔细研究其源代码。

将Jxls源代码导入到Eclipse的环境中就可以查看和分析其源代码了，导入Eclipse的方法是：  
1）将下载的压缩包里面的Src目录下面的jxls-core和jxls-reader两个文件夹拷贝到一个目录下面（也可以不用拷贝）  
2）在DOS下面进入jxls-core目录下面，输入命令：mvn -eclipse:eclipse。jxls-reader的生成方式也是一样的  
3）打开Eclipse，将生成的两个项目导入  
使用jxls的时候其实就使用两个类就够了：XLSTransformer和Util，至少在我目前的使用过程中只使用到这么两个类。

jxls的使用方法：  
1）将jxls的jar文件放到我们的程序目录下面，如果，使用Maven的话，直接在Project的pom.xml里面配置依赖就可以了  
2）根据需要生成的Excel文件或者Report的格式，设置一个jxls的Template文件，Template其实就是一个特殊的一些Excel文件，里面包含了一些jxls的Tag标签。  
jxls的Tag其实有点想jstl标签库的使用方式，在取出一个对象的值的时候可以利用下面这样的Tag来将值输出：$\{object1.property1\}；如果需要将一个Map输出到Excel的时候定义方式如下：$\{map.get('property1')\}；  
3）声明一个XLSTransformer对象，生成方式就是使用new操作符  
XLSTransformer transformer = new XLSTransformer();  
4）得到Template的FIle：  
String xlsTemplateFileName = this.getClass().getClassLoader().getResource("template.xls");  
5）利用XLSTransformer的类的方法生成Excel文件  
String xlsFileName = "c:"+File.separator+"resule.xls";  
Map beans = new HashMap();  
beans.put("newBeans1","new beans1");  
beans.put("newBeans2","new beans2");  
transformer.transformXLS(xlsTemplateFileName , beans, xlsFileName);

XLSTransformer类的transformXLS方法的定义如下：  
public void transformXLS(String srcFilePath, Map beanParams, String destFilePath) throws ParsePropertyException, IOException其中：  
srcFilePath：是Template文件的全文件名（包含路径）  
beanParams：需要传入Excel里面的一个Map，jxls根据Template里面的定义和Map里面的对象对Template进行解析，将Map里面的对象值填入到Excel文件中  
destFilePath：需要生成的Excel文件的全文件名（包含路径）

注：jxls虽然提供了Example，但是，都过于简单，如果想详细了解jxls，可以仔细研究jxls-core，其实没有必要查看其全部代码，如果只是想了解其具体操作，可以详细查看类XLSTransformerTest，即XLSTransformer的测试代码。

### [jxls使用（二）——Multiple Sheets][jxls_Multiple Sheets] ###

jxls在生成Excel文件时，提供一个批量生成Excel Sheet的功能，即可以一次生成一个包含多个Sheet的Excel文件。但是，jxls在处理这类操作的时候会出现性能问题，当一次形成的Excel的Sheet数量超过500个的时候就会出现很严重的性能问题，运算到最后，系统会提示OutOfMemeroyError，这种问题的出现是由于jxls的实现方式决定的。

如果，仔细查看jxls的源代码就会发现，无论是处理单个Sheet还是处理多个Sheet的时候，在XLSTransformer类的Transform处理的时候都是在内存中对Sheet进行处理，然后，在生成WorkBook。假如，需要一次形成的Sheet为500以上的时候，势必会出现严重的性能问题。

针对以上的问题，如果我们的应用需要形成500Sheet以上的Excel时，我们可以采用人为干预的方式，即在进行处理前对Beans的个数进行限制，最好将Beans的个数限制在200个以内，即每次只生成包含200个Sheet的WorkBook，在WorkBook形成以后，再对WorkBook进行合并，然后，将合并的WorkBook输出形成Excel文件，这样处理的时候，对一个Excel的总的Sheet也需要进行控制，否则在运算的过程中也会出现OutOfMemeroyError，最好控制在3000个以内，如果，超过3000个建议形成多个Excel文件，下面我通过一个具体的企业应用来说明怎么来进行操作。

我这个应用是Payslip导出的一个应用，具体要求是：  
按照Division和Costcenter来组织Payslip卡片，每个Costcenter下面的员工组织到一个Excel文件里面

在具体实现的过程发现有一个Costcenter下面的员工数量超过了3000，开始是想是否能够一次将这个Costcenter下面的Payslip全部形成，结果遭到了可耻的失败。经过不断的测试以后，得到了以上的两个临界点的数字。下面说明一下我的具体实现：  
1）从数据库按照Costcenter取出Payslip，得到一个payslipList对象。具体就是组装出取数据的SQL Script，利用JDBCTemplate取出数据。  
2）形成Costcenter对应的Excel文件的名称，如果，得到的List的size大于3000，则每3000个记录形成一个Excel文件  
3）将得到的List按照200进行分块处理，每200处理一次  
4）利用jxls提供的方法transformMultipleSheetsList（）对数据进行处理，得到一个HSSFWorkbook对象  
5）合并得到的HSSFWorkbook，输出形成Excel文件  
具体代码如下：

java 代码

1.  publicvoid exportPayslip(String costcenterCode) throws IOException\{
2.  List payslipList = payslipDao.getPaylipByCostcenter(String costcenterCode);
3.  String xlsFileName;
4.  if (payslipList.size() <= 3000)\{
5.  xlsFileName = "c:"\+File.separator+costcenterCode+".xls";
6.  transformXLSFile(payslipList ,xlsFileName );
7.  \} else \{
8.  for (int i =0;i<(int)Math.ceil((double)payslipList.size()/3000;i++))\{
9.  xlsFileName = "c:"\+File.separator+costcenterCode+"\_"\+new Integer(i+1).toString()+".xls";
10. int k = ((i+1)\*3000 < payslipList.size()) ? (i+1)\*3000 : resultList.size();
11. List resultList = payslipList.subList(i\*3000,k);
12. transformXLSFile(resultList ,xlsFileName );
13. \}
14. \}
15. \}
16. privatevoid transformXLSFile(List resultList,String fileName)\{
17. XLSTransformer transformer = new XLSTransformer();
18. HSSFWorkBook resultWorkBook = null;
19. for (int i = 0;i<(int)Math.ceil((double)resultList.size()/200;i++))\{
20. List payslipMaps = new ArrayList();
21. List sheetNames = new ArrayList();
22. InputStream xlsTemplateIO = new BufferedInputStream(getClass().getResourceAsStream("payslip.xls"));
23. int k = ((i+1)\*200 < resultList.size()) ? (i+1)\*200 : resultList.size();
24. payslipMaps = resultList.subList(i\*200,k);
25. for (int j=0;j
26. Map payslip = (Map)resultList.get(i);
27. String sheetName = paysli.get("EMPLOYEE\_ID");
28. sheetNames.add(sheetName);
29. \}
30. HSSFWorkbook workBook = transformer.transformMultipleSheetsList(xlsTemplateIO , payslipMaps , sheetNames, "map", new HashMap(), 0);
31. if (i == 0)\{
32. resultWorkBook = workBook;
33. \} else \{
34. for (int h = 0;h < workBook.getNumberOfSheets();h++)\{
35. HSSFSheet newSheet = resultWorkBook.createSheet( (String) sheetNames.get(i) );
36. HSSFSheet hssfSheet = workBook.getSheetAt(h);
37. Util.copySheets(newSheet, hssfSheet);
38. Util.copyPageSetup(newSheet, hssfSheet);
39. Util.copyPrintSetup(newSheet, hssfSheet);
40. \}
41. \}
42. saveWorkbook(resultWorkbook, fileName);
43. xlsTemplateIO.close();
44. \}
45. \}
46. privatevoid saveWorkbook(HSSFWorkbook resultWorkbook, String fileName) throws IOException\{
47. OutputStream os = new BufferedOutputStream(new FileOutputStream(fileName));
48. resultWorkbook.write(os);
49. os.flush();
50. os.close();
51. \}

以上代码有部分代码需要被放在try/catch语句里面，确保Stream能够被关闭，否则，会影响程序的运行性能。代码只代表个人的一种实现方式。

以上代码有一个地方需要说明，就是jxls的transformMultipleSheetsList（）方法，该方法的声明如下：

java 代码

1.  public HSSFWorkbook transformMultipleSheetsList(InputStream is, List objects, List newSheetNames, String beanName, Map beanParams, int startSheetNum) throws ParsePropertyException \{

该方法里面的参数说明如下：  
1）is：即Template文件的一个输入流  
2）newSheetNames：即形成Excel文件的时候Sheet的Name  
3）objects：即我们传入的对应每个Sheet的一个Java对象，这里传入的List的元素为一个Map对象  
4）beanName：这个参数在jxls对我们传入的List进行解析的时候使用，而且，该参数还对应Template文件中的Tag，例如，beanName为map，那么在Template文件中取值的公式应该定义成$\{map.get("property1")\}；如果beanName为payslip，公式应该定义成$\{payslip.get("property1")\}  
5）beanParams：这个参数在使用的时候我的代码没有使用到，这个参数是在如果传入的objects还与其他的对象关联的时候使用的，该参数是一个HashMap类型的参数，如果不使用的话，直接传入new HashMap()即可  
6）startSheetNo：传入0即可，即SheetNo从0开始

以上代码的原理与操作Simple Sheet的时候差别不大，主要是Template的文件的取值公式和调用的方法有所改变而已。关键是在大数据量操作的时候注意对处理的数据进行分块处理即可，否则，系统运行性能将很难保证。以上代码，在我实际的使用过程中速度还是比较理想的，16000条数据的导出时间是15分钟左右。

[JXLS_Simple Sheet]: http://lijiye.javaeye.com/blog/108913
[jxls_Multiple Sheets]: http://lijiye.javaeye.com/blog/109165