EasyExcel

使用背景

全链路平台要做报表打出（批量多个月份导出到同一个excel）。

1
2
3
全链路平台要做报表打出（批量多个月份导出到同一个excel）。
百度了下,貌似阿里的easyExcel不错.
学-->用

为什么用easyExcel?

1
Java解析、生成Excel比较有名的框架有Apache poi、jxl。但他们都存在一个严重的问题就是非常的耗内存，poi有一套SAX模式的API可以一定程度的解决一些内存溢出的问题，但POI还是有一些缺陷，比如07版Excel解压缩以及解压后存储都是在内存中完成的，内存消耗依然很大。easyexcel重写了poi对07版Excel的解析，能够原本一个3M的excel用POI sax依然需要100M左右内存降低到几M，并且再大的excel不会出现内存溢出，03版依赖POI的sax模式。在上层做了模型转换的封装，让使用者更加简单方便。

easyExcel相关资料

使用

导入依赖:

1
2
3
4
5
<dependency> 
    <groupId>com.alibaba</groupId> 
    <artifactId>easyexcel</artifactId> 
    <version>2.0.5</version> 
</dependency>

写的例子:

1
https://github.com/alibaba/easyexcel/blob/master/src/test/java/com/alibaba/easyexcel/test/demo/write/WriteTest.java

导出做数据校验

  • 依赖
1
2
3
4
5
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>2.2.6</version>
        </dependency>
  • 实体
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
package com.example.jddemo.excel.upload.excellistener;

import java.util.ArrayList;
import java.util.List;

public class ExcelDto {

    private List<List<String>> head        = new ArrayList<>();//表头
    private List<List<Object>> tmpContent  = new ArrayList<>();//临时数据
    private List<List<Object>> content     = new ArrayList<>();//保存全量数据  高并发或者数据量较大时不建议使用  [如需开启  重写 enableContent()方法 返回true]
    private boolean            isHaveError;//默认false
    private int                count       = 3000;//三千条数据 提交一次
    private String             url;//文件上传后的地址

    public List<List<String>> getHead() {
        return head;
    }
    public void setHead(List<List<String>> head) {
        this.head = head;
    }
    public List<List<Object>> getTmpContent() {
        return tmpContent;
    }
    public void setTmpContent(List<List<Object>> tmpContent) {
        this.tmpContent = tmpContent;
    }
    public boolean isHaveError() {
        return isHaveError;
    }
    public void setHaveError(boolean haveError) {
        isHaveError = haveError;
    }
    public int getCount() {
        return count;
    }
    public void setCount(int count) {
        this.count = count;
    }
    public String getUrl() {
        return url;
    }
    public void setUrl(String url) {
        this.url = url;
    }
    public List<List<Object>> getContent() {
        return content;
    }
    public void setContent(List<List<Object>> content) {
        this.content = content;
    }
    @Override
    public String toString() {
        return "ExcelDto{" +
                "head=" + head +
                ", tmpContent=" + tmpContent +
                ", saveContent=" + content +
                ", isHaveError=" + isHaveError +
                ", count=" + count +
                ", url='" + url + '\'' +
                '}';
    }
}
  • AbstractEasyExcelDataListener 为抽象公共类 用户只需要继承它 重写 validator, save 方法
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
package com.example.jddemo.excel.upload.excellistener;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.alibaba.excel.write.metadata.WriteSheet;
import org.apache.commons.lang3.StringUtils;

import java.io.File;
import java.io.IOException;
import java.util.*;

public abstract class AbstractEasyExcelDataListener extends AnalysisEventListener<Map<Integer, String>> {

    protected ExcelDto excelDto = null;

    private File tempFile = null;//临时文件
    private ExcelWriter excelWriter = null;
    private WriteSheet writeSheet = null;
    private StringBuilder msg = null;// 行数据校验 提示信息
    private List<List<String>> head =null;//生成动态表头

    public AbstractEasyExcelDataListener() {

    }
    public AbstractEasyExcelDataListener buildSheetName(String sheetName){
        excelDto.setSheetName(sheetName);
        return this;
    }
    public AbstractEasyExcelDataListener buildFileName(String  fileName){
        excelDto.setFileName(fileName);
        return this;
    }

    public void open() {
        //生成临时文件
        try {
            excelDto = new ExcelDto();
            head = new ArrayList<>();
            tempFile = File.createTempFile("tmp", ".xlsx");
            writeSheet = EasyExcel.writerSheet(StringUtils.isBlank(excelDto.getSheetName())?"模板":excelDto.getSheetName()).sheetNo(0).build();
            //写入临时文件
            //excelWriter = EasyExcel.write(tempFile).head(head).build();
            excelWriter = EasyExcel.write("D:\\abc.xlsx").head(head).build();
            excelDto.setHead(head);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) {
        List<String> list = new ArrayList<>(headMap.values());
        //初始化表头
        if (head.size() == 0) {
            head.add(new ArrayList<String>(Collections.singleton("错误列信息提示")));
            list.forEach(item -> {
                head.add(new ArrayList<String>(Collections.singleton(item)));
            });
            return;
        }
        for (int i = 0; i < list.size(); i++) {
            String str = list.get(i);
            List<String> headI = this.head.get(i + 1);
            headI.add(str);
        }
    }

    @Override
    public void invoke(Map<Integer, String> row, AnalysisContext context) {
        List<Object> responseRow = new ArrayList<>(row.values());
        Integer rowIndex = context.readRowHolder().getRowIndex();
        System.out.println("读取行" + rowIndex + "数据");
        msg = new StringBuilder("");
        //校验数据
        this.validator(responseRow, msg);
        responseRow.add(0, msg.toString());
        excelDto.getTmpContent().add(responseRow);
        //保存全量数据  高并发或者数据量较大时不建议使用
        if (this.enableContent()) {
            excelDto.getContent().add(responseRow);
        }
        // 三千条数据 写入一次
        if (excelDto.getTmpContent().size() >= excelDto.getCount()) {
            if (!enableContent()) {
                excelDto.setContent(excelDto.getTmpContent());
                this.save(excelDto);//数据落库
            }
            excelWriter.write(excelDto.getTmpContent(), writeSheet);//写入新文件
            excelDto.getTmpContent().clear();
        }
    }

    /**
     * 返回 true  ExcelDto content 会保存excel 中全量数据
     * 返回 false ExcelDto content 不会保存数据
     *
     * @return
     */
    protected boolean enableContent() {
        return false;
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        System.out.println("数据读取完毕");
        if (!enableContent()) {
            excelDto.setContent(excelDto.getTmpContent());
        }
        excelWriter.write(excelDto.getTmpContent(), writeSheet);//写入新文件
        // 千万别忘记finish 会帮忙关闭流
        if (excelWriter != null) {
            excelWriter.finish();
        }
        this.save(excelDto);//数据落库
        this.saveFile(tempFile);// 保存临时文件 到服务器
        excelDto.getTmpContent().clear();//清除临时数据
    }

    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        //手动抛出异常 读操作终止
        throw new RuntimeException("excel处理数据监听异常:" + exception.getMessage());
    }

    /**
     * 保存附件到服务器
     *
     * @param tempFile
     */
    protected void saveFile(File tempFile) {
        System.out.println("保存文件到文件服务器");
        this.excelDto.setUrl("url://文件服务器地址");
    }

    public void close() {
        // 千万别忘记finish 会帮忙关闭流
        if (excelWriter != null) {
            excelWriter.finish();
        }
        if (tempFile != null && tempFile.exists()) {
            tempFile.delete();
        }

    }

    /**
     * 行数据校验
     *
     * @param row
     * @param msg
     */
    protected abstract void validator(List<Object> row, StringBuilder msg);

    /**
     * 数据库操作  保存数据
     *
     * @param excelDto
     */
    protected abstract void save(ExcelDto excelDto);

    public ExcelDto getExcelDto() {
        return excelDto;
    }

    public void setExcelDto(ExcelDto excelDto) {
        this.excelDto = excelDto;
    }

}
  • 创建读监听器 继承 AbstractEasyExcelDataListener 并重写 validator, save 方法 校验数据和保存数据库操作 到此就大功告成
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
package com.example.jddemo.excel.upload.excellistener;

import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
@Scope("prototype")// 开启多例   单例存在并发安全问题 [使用如下]
/**
*     @Resource
*     private ObjectFactory<MyDataEasyExcelListener> obj;
*
*     使用: AbstractEasyExcelDataListener listener=obj.getObject();
*/
public class MyDataEasyExcelListener extends AbstractEasyExcelDataListener {

    @Override
    protected void validator(List<Object> row, StringBuilder msg) {
        // 数据校验
        Object number = row.get(1);
        if (!isNumber(number.toString(), 2)) {
            // System.out.println(number + "不是数字");
            msg.append(number + "不是数字");
            excelDto.setHaveError(true);
        }
        //校验 。。。。。。。。。
        if (true) {

        }
    }

    /**
     * 保存数据库
     *
     * @param excelDto
     */
    @Override
    protected void save(ExcelDto excelDto) {
        if(excelDto.isHaveError()){
            System.out.println("校验数据有错误信息  不执行数据库操作");
            return;
        }
        List<List<Object>> content = excelDto.getContent();
        System.out.println("保存的数据:"+content);
    }

    /**
     * 数字类型  最多保留两位小数
     *
     * @param str   校验的字符串
     * @param scale 保留小数位
     * @return
     */
    public static boolean isNumber(String str, int scale) {
        String reg = "^(\\d{1,8})(\\.\\d{1," + scale + "})?$";
        return str.matches(reg);

    }
}

JXLS

使用背景

最近的一个项目,大部分都是报表.很多地方都要导出导入。

项目组的领导有时会提出各种各样的定制需求。比如颜色,下拉框,批注等等。

java中操作excel就POI包,我一开始用的是easyExcel。一些简单的场景使用起来真的很方便。但是复杂的业务你就只能写监听去处理。还是代码繁琐。

在代码层次来写,还是太麻烦了。所以开始使用jxls。可以很方便的通过操作模板填充数据。用作者的话说: jxls takes this approach to a higher level.(避免了一些复杂的功能在java中的繁琐编写)

最简单的模板导出

  • 使用背景
1
2
3
4
    Java中实现excel根据模板导出数据的方法有很多，一般简单的可以通过操作POI进行。还可以使用一些工具很轻松的实现模板导出。这些工具现在还在维护，而且做得比较好的国内的有easyPOI，国外的就是这个JXLS了。
　　笔者使用jxls 2也有半年的时间了，半年前因项目原因需要导出大量的excel文件，所以找到了jxls2，这是目前我用过最好的excel导出工具，基本可以完全满足所有的项目需要。不使用easypoi的原因是那时候测试时候效果不是很好，项目中有很多复杂的报表（大量单元格合并和单元格样式），easyPOI处理合并单元格时候容易出现残损的情况。
    今天我们着重介绍一下JXLS 2.4.0 ，写文章前我搜索了下JXLS的教程，发现半年前我看到的是什么文章，现在的还是什么文章，大量的文章停留在JXLS1.0时代（作者2.0后重写了代码，使用方法完全不同）。唯一最新的一篇中文文章是klguang 写的《jxls2.3-简明教程》。剩下的就是官方文档了。其实官方文档也很不错，就是系统化不够，值此国庆之际，正好把我的工作中使用JXLS的经验写一下，让更多人接触这款优秀的工具。
　　首先，我推荐各位有能力的先上官网下载最新版本和了解下基础功能。（有没有被墙看缘分，公司的电信网络可以直接上，家里联通的要挂VPN）
  • 需要依赖的jar包文件
1
2
好，现在我们开始。
　　我这里使用jxls 2.4.0进行教程（不使用最新的2.4.2是因为我懒，教程用的jar包是我直接从公司项目拷的，懒得去测试新版本会不会有什么幺蛾子，不过我看了下更新说明，只是修复了一个bug，应该问题不大）。2.4和2.3在操作上没什么变化，但是在jar包的依赖上发生了变化，所以在使用2.4时候主要看我提供需要依赖的jar包文件

image-20230928122619783

1
　除了官方需要的jar包外，还需要加入几个依赖的包（都是必须的，少一个就报错）。文章后我给出依赖包的下载地址。用Maven的朋友我就不说怎么引包了，写上就自动下载依赖了。

maven:

1
2
3
4
5
<dependency>
    <groupId>org.jxls</groupId>
    <artifactId>jxls</artifactId>
    <version>2.8.1</version>
</dependency>
  • 接下来，我们在项目中复制util工具类：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
package com.test.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.jxls.common.Context;
import org.jxls.expression.JexlExpressionEvaluator;
import org.jxls.transform.Transformer;
import org.jxls.transform.poi.PoiTransformer;
import org.jxls.util.JxlsHelper;

/**
* @author klguang
*/public class JxlsUtils{
    
    public static void exportExcel(InputStream is, OutputStream os, Map<String, Object> model) throws IOException{
        Context context = PoiTransformer.createInitialContext();
        if (model != null) {
            for (String key : model.keySet()) {
                context.putVar(key, model.get(key));
            }
        }
        JxlsHelper jxlsHelper = JxlsHelper.getInstance();
        Transformer transformer  = jxlsHelper.createTransformer(is, os);
        //获得配置
        JexlExpressionEvaluator evaluator = (JexlExpressionEvaluator)transformer.getTransformationConfig().getExpressionEvaluator();
        //设置静默模式，不报警告
        //evaluator.getJexlEngine().setSilent(true);
        //函数强制，自定义功能
        Map<String, Object> funcs = new HashMap<String, Object>();
        funcs.put("utils", new JxlsUtils());    //添加自定义功能        
        evaluator.getJexlEngine().setFunctions(funcs);
        //必须要这个，否者表格函数统计会错乱
        jxlsHelper.setUseFastFormulaProcessor(false).processTemplate(context, transformer);
    }

    public static void exportExcel(File xls, File out, Map<String, Object> model) throws FileNotFoundException, IOException {
            exportExcel(new FileInputStream(xls), new FileOutputStream(out), model);
    }
    
    public static void exportExcel(String templatePath, OutputStream os, Map<String, Object> model) throws Exception {
        File template = getTemplate(templatePath);
        if(template != null){
            exportExcel(new FileInputStream(template), os, model);    
        } else {
            throw new Exception("Excel 模板未找到。");
        }
    }
    
    //获取jxls模版文件
    public static File getTemplate(String path){
        File template = new File(path);
        if(template.exists()){
            return template;
        }
        return null;
    }    
    
    // 日期格式化
    public String dateFmt(Date date, String fmt) {
        if (date == null) {
            return "";
        }
        try {
            SimpleDateFormat dateFmt = new SimpleDateFormat(fmt);
            return dateFmt.format(date);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return "";
    }
    
    // if判断
    public Object ifelse(boolean b, Object o1, Object o2) {
        return b ? o1 : o2;
    }
}

我在klguang 的基础上略作了修改，有两点需要讲一下：

　　1.静默模式是在导出excel模板时候，如果模板中标签名没有在传入的map中找到数值，会打印报告某某某标签没有赋值。如果开启静默模式后则不会报告。放心，出现严重异常还是会报错的。

　　2.函数统计错乱，这个必须设置（setUseFastFormulaProcessor(false)），要不后续文章中会写到怎么做excel分sheet输出，如果不设置成false，以后用excel自带函数统计相加会加错地方。

工欲善其事必先利其器，接着我们可以写一个Main方法了。

  • main
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
ublic class TestMain {
    public static void main(String[] args) throws Exception {
        // 模板路径和输出流
        String templatePath = "E:/template.xls";
        OutputStream os = new FileOutputStream("E:/out.xls");
        // 定义一个Map，往里面放入要在模板中显示数据
        Map<String, Object> model = new HashMap<String, Object>();
        model.put("id", "001");
        model.put("name", "张三");
        model.put("age", 18);
        //调用之前写的工具类，传入模板路径，输出流，和装有数据Map        
        JxlsUtils.exportExcel(templatePath, os, model);
        os.close();
        System.out.println("完成");
    }
}

就这么简单，接下来我们做事写模板。

在E盘建立一个名字叫template.xls的文件，然后在里面加入以下的内容。

image-20230928122703586

1
2
3
4
　如果你前面看了klguang的教程，你应该知道里面的注释是什么意思。不过我还是简单讲一下：
　　第一步，在报表中最左上角（A1）加入一个注释jx:area(lastCell="D3")，含义为模板的区域由A1（加注释的单元格）到D3。有一点说明：
　　区域最好比你设计的模板大一圈就是你的模板内容只到C2，而你要设置区域到D3。理由是在2.3版本中lastCell的值如果在有表达式的单元格或者在合并后的单元格，容易报空指针异常，2.4好像没有这个问题了，不太确定。
　　第二步，在你设定的模板区域内写入表达式${ }，表达式中写入前面model中put的“键”。

好了，模板写完，保存，执行java代码，我们就可以看到效果了：

image-20230928122653685

最后说明一下：

　　1.jxls会自动根据你model中put的值来判断写入进excel中的是字符串还是数值。

　　2.A1单元格这个被注释使用的单元格也是可以写表达式的。

　　3.如果你在模板中写了一个model 中找不到对应键的表达式，比如我在A3中写${aaaa}，再运行代码，则会报提示 警告: org.jxls.expression.JexlExpressionEvaluator.evaluate@61![0,4]: 'aaaa;' undefined variable aaaa

如果不想要提示就在JxlsUtils类中设置静默模式：evaluator.getJexlEngine().setSilent(true);

JXLS是一个很强大的excel操作工具，可以导入导出，模板导出，代码导出，xml导出，导出显示图片，连接数据库等等功能。具体的功能有需要的同学请上官网查询。

附录

循环导出一个链表的数据

1
2
上一篇文章我们介绍了JXLS和模板导出最简单的应用，现在我们要更进一步，介绍在模板中循环导出链表中的数据，在日常开发中，循环导出应该才是最常用的功能吧！
　　首先，我们要建立一个类模拟javabean对象。我们定义一个person类，里面有id，name和age。
  • 实体
1
2
3
4
5
6
7
8
9
10
11
12
13
14
public class Person {
    String id;
    String name;
    Integer age;
    public Person(String id, String name, Integer age) {
        super();
        this.id = id;
        this.name = name;
        this.age = age;
    }
    public Person() {
    }
    /** 以下省略了对应的get/set方法，请自行补齐 */
}
  • 测试

然后我们修改main 方法，大体上和上一篇文章是一致的，只是模拟了从数据库取出javabean的过程，将取出的javabean对象放入model中，然后传给JxlsUtils。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
public class TestMain {
    public static void main(String[] args) throws Exception {
        // 模板位置，输出流
        String templatePath = "E:/template2.xls";
        OutputStream os = new FileOutputStream("E:/out.xls");
        
        // 一个装有对象数据的链表
        List<Person> persons = new ArrayList<Person>();
        Person p1 = new Person("001", "张三", 18);
        Person p2 = new Person("002", "李四", 19);
        Person p3 = new Person("003", "王五", 20);
        persons.add(p1);
        persons.add(p2);
        persons.add(p3);
        
        Map<String, Object> model = new HashMap<String, Object>();
        model.put("person", persons);    // 把链表放进model中        
        JxlsUtils.exportExcel(templatePath, os, model);
        os.close();
        System.out.println("完成");
    }
}

接下来我们设计模板：

image-20230928122936314