excel导入导出demo笔记

Excel导入导出demo笔记

前言

excel 导入导出的算是项目里很常见的一个功能，以前都是使用POI来，一直知道阿里有个easyExcel 官网 这次尝试去了解了下，感觉确实很方便，上手容易。这里记录下方便后续来捞代码。

pom.xml 依赖

        <dependency>
            <groupId>com.alibabagroupId>
            <artifactId>fastjsonartifactId>
            <version>1.2.83version>
        dependency>
        <dependency>
            <groupId>com.alibabagroupId>
            <artifactId>easyexcelartifactId>
            <version>3.1.1version>
        dependency>
        
        <dependency>
            <groupId>io.springfoxgroupId>
            <artifactId>springfox-swagger2artifactId>
            <version>2.9.2version>
        dependency>

        
        <dependency>
            <groupId>com.github.xiaoymingroupId>
            <artifactId>swagger-bootstrap-uiartifactId>
            <version>1.9.6version>
        dependency>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

代码案例

主要涉及：Excel的更具指定标题进行的导入导出

• 需要有 实体基类使用easyExcel的相关注解进行修饰
• 导入：需要使用监听器，可以在监听器里的invoke做业务处理
• 导出：组装数据然后通过Excel附件的形式进行下载

（一）基础类

要导出的数据类 ExportBusinessMeta,官网的 DemoData

/** 官网的 DemoData
 * @author lvzb
 * @date 2022/11/23  15:45
 **/
@Getter
@Setter
@EqualsAndHashCode
public class DemoData implements Serializable {
    private static final long serialVersionUID = 7382510541716722119L;
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

/**
 * @author lvzb
 * @date 2022/11/23  15:34
 **/
@Data
@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 24)
public class ExportBusinessMeta {
    @ExcelProperty("数据库类型")
    @ColumnWidth(value = 20)
    @HeadStyle(fillForegroundColor = 13)
    private String dataBaseType;

    @ExcelProperty("所属库")
    @ColumnWidth(value = 20)
    @HeadStyle(fillForegroundColor = 13)
    private String dataBase;

    @ExcelProperty("所属表")
    @ColumnWidth(value = 20)
    @HeadStyle(fillForegroundColor = 13)
    private String table;

    @ExcelProperty("字段名")
    @ColumnWidth(value = 20)
    @HeadStyle(fillForegroundColor = 13)
    private String column;

    @ExcelProperty(value = "英文全称")
    @ColumnWidth(value = 25)
    private String english_full_name;
    /**
     * 英文简称
     */
    @ExcelProperty("英文简称")
    @ColumnWidth(value = 25)
    private String english_abbr;
    /**
     * 业务术语
     */
    @ExcelProperty("业务术语")
    @ColumnWidth(value = 25)
    private String business_term;
    /**
     * 变量属性
     */
    @ExcelProperty("变量属性")
    @ColumnWidth(value = 25)
    private String variable_properties;
    /**
     * 含义及用法
     */
    @ExcelProperty("含义及用法")
    @ColumnWidth(value = 30)
    private String meaning_usage;
    /**
     * 报文域或标签
     */
    @ExcelProperty("报文域或标签")
    @ColumnWidth(value = 30)
    private String messagedomain_label;
    /**
     * 备注
     */
    @ExcelProperty("备注")
    @ColumnWidth(value = 15)
    private String remark;

    /**
     * 分类
     */
    @ExcelProperty(value = "分类")
    private String classification;

    /**
     * 分级
     */
    @ExcelProperty(value = "分级")
    private String grading;

}

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

@Slf4j
public class DemoDAO {

    public void save(List<ExportBusinessMeta> list) {
        // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
        if (!CollectionUtils.isEmpty(list)) {
            for (ExportBusinessMeta data : list) {
                log.warn(">>>>>> data::{}", data);
            }
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12

（二）读监听器

读监听器在执行导入解析时会被用到

/**
 * @author lvzb
 * @date 2022/10/24  17:57
 **/
@Slf4j
public class IndexOrNameDataListener implements ReadListener<ExportBusinessMeta> {
    private static final int BATCH_COUNT = 100;
    private List<ExportBusinessMeta> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    private DemoDAO demoDAO;


    public IndexOrNameDataListener() {
        this.demoDAO = new DemoDAO();
    }

    public IndexOrNameDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data
     * @param analysisContext
     */
    @Override
    public void invoke(ExportBusinessMeta data, AnalysisContext analysisContext) {
        log.info("解析到一条数据:{}", JSON.toJSONString(data));
        cachedDataList.add(data);
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", cachedDataList.size());
        demoDAO.save(cachedDataList);
        log.info("存储数据库成功！");
    }
}
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

（三）导出/下载/导入 controller

/**
 * @author lvzb
 * @date 2022/11/15  17:37
 **/
@Slf4j
@RestController
@Api(tags = "控制层")
@RequestMapping("/swagger")
public class SwaggerTestController {
    /**
     * demo Data
     *
     * @return
     */
    private List<DemoData> data() {
        List<DemoData> list = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + i);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }

    private List<ExportBusinessMeta> busiData() {
        List<ExportBusinessMeta> list = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            ExportBusinessMeta data = new ExportBusinessMeta();
            data.setBusiness_term("字符串" + i);
            data.setGrading(new Date() + "分级：" + i);
            data.setEnglish_full_name("英文全称：" + i);
            list.add(data);
        }
        return list;
    }

    /**
     * 文件下载并且失败的时候返回json（默认失败了会返回一个有部分数据的Excel）
     *
     * @since 2.1.1
     */
    @GetMapping("downloadFailedUsingJson")
    @ApiOperation(value = "EasyExcel文件下载失败时/JSON返回", tags = "V1")
    public void downloadFailedUsingJson(HttpServletResponse response) throws IOException {
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            // 这里需要设置不关闭流
            EasyExcel.write(response.getOutputStream(), ExportBusinessMeta.class).autoCloseStream(Boolean.FALSE).sheet("模板")
                    .doWrite(data());
            throw new BusinessException("aaaa");
        } catch (Exception e) {
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = MapUtils.newHashMap();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }
    }

    @GetMapping("/downLoad")
    @ApiOperation(value = "EasyExcel文件下载/导出案例", tags = "V1")
    public void downLoad(EchoRequest echoRequest, HttpServletRequest request, HttpServletResponse response) throws IOException {
        // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
        String fileName = URLEncoder.encode("业务元数据导入模板", StandardCharsets.UTF_8).replaceAll("\\+", "%20");
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        // filename*=utf-8'' 是一种协议规范 标识fileName的value也进行编码 具体参考 ：https://juejin.cn/post/6890692829719298061
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        /// 直接的 filename= 则标识fileName的value不进行编码，那么就会在不同的浏览器上导致附件名乱码
//        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

        try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream()).build()) {
            // 设置文件头
            WriteSheet sheet = EasyExcel.writerSheet(0, "业务元数据11").head(ExportBusinessMeta.class).build();
            // busiData() 换成 new ArrayList()就是变成模版导出了
            excelWriter.write(busiData(), sheet);
            excelWriter.finish();
        }
    }

    @GetMapping("/downLoadTxt")
    @ApiOperation(value = "Txt文件下载案例", tags = "V1")
    public void downLoadTxt(HttpServletRequest request, HttpServletResponse response) {
        String content = "Hello world \n 你好世界!";
        String fileName = URLEncoder.encode("生成的文本", StandardCharsets.UTF_8);
        response.setContentType("text/plain");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
        response.setHeader("Content-disposition", "attachment; filename*=utf8''" + fileName + ".txt");
        try (ServletOutputStream ots = response.getOutputStream();
             BufferedOutputStream bufferOts = new BufferedOutputStream(ots)) {
            bufferOts.write(content.getBytes(StandardCharsets.UTF_8));
            bufferOts.flush();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    
    @PostMapping("/upload")
    @ApiOperation(value = "excel上傳解析案例", tags = "V1")
    public String upload(MultipartFile file) throws IOException {
        // 解析之前做前置校验： 文件大小、文件类型等
        // .......

        EasyExcel.read(file.getInputStream(), ExportBusinessMeta.class, new IndexOrNameDataListener(new DemoDAO())).sheet().doRead();
        return "success";
    }

}

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

（四）扩展(项目内的文件获取方式)

涉及从项目内下载文件，例如某些模板，这里也记录一下。最好使用类似这种方式找。DemApplication.class.getResource("/".concat("static/excel/").concat(fileName)).toURI().getPath()

拿的是target/classes 开始往下找。

/**
 *
 * @author lvzb
 * @date 2022/10/24  17:16
 **/
@Slf4j
public class ReadTest {

    public static final String fileName = "业务元数据1.xlsx";

    @Test
    void testPath() {
        // 只到项目的目录上
        String workDir = System.getProperty("user.dir");
        // C:\Users\zlv11\IdeaProjects\dem-backend
        System.out.println(workDir);
    }

    @SneakyThrows
    @Test
    void classLoaderGetFilePath() {
        // 拿的是target/classes 开始往下找 我们预存在项目的 resources/static/excel
        String path = DemApplication.class.getResource("/".concat("static/excel/").concat(fileName)).getPath();
        // /C:/Users/zlv11/IdeaProjects/dem-backend/target/classes/static/excel/%e4%b8%9a%e5%8a%a1%e5%85%83%e6%95%b0%e6%8d%ae1.xlsx
        System.out.println(path);
        // 如果路径中带有中文会被URLEncoder,因此这里需要解码
        // /C:/Users/zlv11/IdeaProjects/dem-backend/target/classes/static/excel/业务元数据1.xlsx
        String filePath = URLDecoder.decode(path, "UTF-8");
        System.out.println(filePath);
    }

    @SneakyThrows
    @Test
    void getFilePath() {
        String path = DemApplication.class.getResource("/".concat("static/excel/").concat(fileName)).getPath();
        // /C:/Users/zlv11/IdeaProjects/dem-backend/target/classes/static/excel/%e4%b8%9a%e5%8a%a1%e5%85%83%e6%95%b0%e6%8d%ae1.xlsx
        System.out.println(path);
        // toURI.getPath()也能解码中文
        // /C:/Users/zlv11/IdeaProjects/dem-backend/target/classes/static/excel/业务元数据1.xlsx
        System.out.println(DemApplication.class.getResource("/".concat("static/excel/").concat(fileName)).toURI().getPath());
    }

    @Test
    void readTestMethodOne() {
        // 未处理路径上的中文字符会导致如下报错
        // com.alibaba.excel.exception.ExcelCommonException: Convert excel format exception.You can try specifying the 'excelType' yourself
        String path = DemApplication.class.getResource("/".concat("static/excel/").concat(fileName)).getPath();
        System.out.println(path);
        String fileName = path;
        EasyExcel.read(fileName, ColumnEntityWithBusiness.class, new PageReadListener<ColumnEntityWithBusiness>(dataList -> {
            for (ColumnEntityWithBusiness c : dataList) {
                log.info("读取到一条数据{}", JSON.toJSONString(c));
            }
        })).sheet().doRead();
    }

    @SneakyThrows
    @Test
    void readTestMethodTwo() {
//        String path = DemApplication.class.getResource("/".concat("static/excel/").concat(fileName)).getPath();
        String path = DemApplication.class.getResource("/".concat("static/excel/").concat(fileName)).toURI().getPath();
        try (ExcelReader reader = EasyExcel.read(path, ColumnEntityWithBusiness.class, new IndexOrNameDataListener()).build()) {
            ReadSheet readSheet = EasyExcel.readSheet(0).build();
            reader.read(readSheet);
        }
//        EasyExcel.read(fileName, ColumnEntityWithBusiness.class, new DemoDataListener()).sheet().doRead();
    }
}
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