Spring boot 导出/导入 Excel 步骤

在入职公司做项目期间，发现很多功能需求需要导出或是导入Excel文件，因此就想要写一篇文章来总结一下导出导入Excle的步骤。

导出Excel

1、创建一个XxxExcel的POJO类，用来保存Excel中的相关信息，主要有表的ID、表名、错误信息、以及列名等字段，如果有很多功能模块需要导出Excel，可以创建一个静态实体类保存公共字段，其它的Excel实体类只需要继承这个静态类，之后则只需要Excel文件中的列对应的字段即可；

Excel基类：

public abstract class Sheet {

    @ColumnWidth(30)
    @HeadStyle(fillForegroundColor = Font.COLOR_RED)
    @ContentFontStyle(color = Font.COLOR_RED)
    @ExcelProperty("错误信息")
    private String error;

    /**
     * 表格ID由各个模块进行自定义，各个模块ID不能重复，且定义后不可修改
     *
     * @return 表格ID
     */
    public abstract String getSheetId();

    /**
     * 模块名称
     *
     * @return 表格名称
     */
    public abstract String getSheetName();

    public String getError() {
        return error;
    }

    public Sheet setError(String error) {
        this.error = error;
        return this;
    }
}
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

@ColumnWidth() ：列宽 @ExcelProperty("")：表头 @HeadStyle(fillForegroundColor = Font.COLOR_RED)：表头风格 @ContentFontStyle(color = Font.COLOR_RED)：内容风格 @HeadRowHeight(40)：表头高度 @ContentRowHeight(20)：内容行高度
XxxExcel POJO类：

@HeadRowHeight(40)
@ContentRowHeight(20)
public class XxxExcel extends Sheet {

	@ColumnWidth(10)
    @ExcelProperty("姓名")
    private String userName;

    @ColumnWidth(20)
    @ExcelProperty("电话")
    private String phoneNum;

    @ColumnWidth(30)
    @ExcelProperty("地址")
    private String address;

	public String getUserName() {
        return userName;
    }

    public void setUserName(String userName) {
        this.userName= userName;
    }

	public String getPhoneNum() {
        return phoneNum;
    }

    public void setPhoneNum(String phoneNum) {
        this.phoneNum= phoneNum;
    }

	public String getAddress() {
        return address;
    }

    public void setAddress(String address) {
        this.address= address;
    }

}
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

2、将需要导出的信息保存为一个List<XxxPojo>；

3、将List<XxxPojo>转换为List<XxxExcel>；

4、编写表格导入组件；

public class DocumentService {

    @Resource
    private OSSConfigProperties ossConfigProperties;

    @Resource
    private OSS oss;

    private static final List<String> DEFAULT_EXCLUDE_FIELDS = Collections.singletonList("error");

    public ByteArrayOutputStream exportRecord(List<?> list) {
        return exportRecord(list, DEFAULT_EXCLUDE_FIELDS);
    }

    public ByteArrayOutputStream exportRecord(List<?> list, Collection<String> excludeFields) {
        Sheet sheet = (Sheet) list.get(0);
        try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            EasyExcel.write(outputStream, list.get(0).getClass()).excludeColumnFieldNames(excludeFields).sheet(sheet.getSheetName()).doWrite(list);
            return outputStream;
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    public String exportErrorRecord(List<?> list) {
        return exportErrorRecord(list, null);
    }

    public String exportErrorRecord(List<?> list, List<String> excludeFields) {
        Sheet sheet = (Sheet) list.get(0);
        try (ByteArrayOutputStream outputStream = new ByteArrayOutputStream()) {
            if (CollectionUtils.isNotEmpty(excludeFields)) {
                EasyExcel.write(outputStream, list.get(0).getClass())
                        .sheet(sheet.getSheetName())
                        .excludeColumnFieldNames(excludeFields)
                        .doWrite(list);
            } else {
                EasyExcel.write(outputStream, list.get(0).getClass())
                        .sheet(sheet.getSheetName())
                        .doWrite(list);
            }
            try (ByteArrayInputStream inputStream = new ByteArrayInputStream(outputStream.toByteArray())) {
                String documentName = String.format("xxx/temp/excel/%s/%s/%s.xlsx", sheet.getSheetId(),
                        LocalDate.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd")), UUID.randomUUID());
                oss.putObject(ossConfigProperties.getBucketName(), documentName, inputStream);
                return String.format("%s/%s/%s", ossConfigProperties.getPublicEndpoint().endsWith("/") ? ossConfigProperties.getPublicEndpoint().substring(ossConfigProperties.getPublicEndpoint().length() - 1) : ossConfigProperties.getPublicEndpoint(),
                        ossConfigProperties.getBucketName(), documentName);
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    public ByteArrayOutputStream exportDocx(Docx docx) {
        InputStream inputStream;
        ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
        try {
            inputStream = new ClassPathResource(docx.getTemplateName()).getInputStream();
            IXDocReport report = XDocReportRegistry
                    .getRegistry()
                    .loadReport(inputStream, TemplateEngineKind.Freemarker);

            FieldsMetadata metadata = report.createFieldsMetadata();
            metadata.addFieldAsImage("images", "image", NullImageBehaviour.RemoveImageTemplate);
            report.setFieldsMetadata(metadata);

            IContext context = report.createContext();
            Field[] fields = docx.getClass().getDeclaredFields();
            for (Field field : fields) {
                field.setAccessible(true);
                String key = field.getName();
                String method = String.format("%s%s", key.substring(0, 1).toUpperCase(), key.substring(1));
                Method getMethod = docx.getClass().getMethod("get" + method);
                if ("getImages".equals(getMethod.getName())) {
                    List<String> imageUrls = (List<String>) getMethod.invoke(docx);
                    List<IImageProvider> images = new ArrayList<>();
                    for (String imageUrl : imageUrls) {
                        String fileName = imageUrl.replace(ossConfigProperties.getPublicEndpoint().concat("/").concat(ossConfigProperties.getBucketName()).concat("/"), "");
                        OSSObject object = oss.getObject(ossConfigProperties.getBucketName(), fileName);
                        IImageProvider imageProvider = new ByteArrayImageProvider(object.getObjectContent());
                        images.add(imageProvider);
                        context.put("image1", imageProvider);
                    }
                    context.put(key, images);
                } else {
                    context.put(key, getMethod.invoke(docx));
                }
            }
            report.process(context, outputStream);
            inputStream.close();
            return outputStream;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }

    }
}
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

5、使用exportRecord(List<XxxExcel>)方法获取ByteArrayOutputStream的对象out；

6、将out对象转为byte[]返回给前端，byte[] body = out.toByteArray()。

导入Excel

1、同导入Excel，需要XxxExcel Pojo类；

2、配置单行数据读取带列数据验证监听器；

public class ColumnValidationListener<T> implements ReadListener<T> {

    private static final Logger log = LoggerFactory.getLogger(ColumnValidationListener.class);

    private RowReadExceptionConsumer exceptionConsumer;
    private Consumer<T> rowDataConsumer;
    private ColumnValidationRule<T> rules;
    private int batchSize = 0;
    private Consumer<List<T>> batchRowConsumer;
    private int rowCount = 0;
    private List<T> batchRowList;

    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        if (batchSize > 0 && rowCount % batchSize == 0) {
            batchRowList.clear();
        }
        if (Objects.nonNull(exceptionConsumer)) {
            exceptionConsumer.accept(new RowReadException(exception, context));
            return;
        }
        ReadListener.super.onException(exception, context);
    }

    @Override
    public void invoke(T data, AnalysisContext context) {
        rowCount++;
        if (Objects.nonNull(rules)) {
            rules.validate(data);
        }
        if (Objects.nonNull(rowDataConsumer)) {
            rowDataConsumer.accept(data);
        }
        if (Objects.isNull(batchRowConsumer)) {
            return;
        }
        batchRowList.add(data);
        if (rowCount % batchSize == 0) {
            batchRowConsumer.accept(batchRowList);
            batchRowList.clear();
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        if (batchRowList.size() > 0 && Objects.nonNull(batchRowConsumer)) {
            batchRowConsumer.accept(batchRowList);
            batchRowList.clear();
        }
        log.info("导入成功，已导入{}条数据", rowCount);
    }

    public void setExceptionConsumer(RowReadExceptionConsumer exceptionConsumer) {
        this.exceptionConsumer = exceptionConsumer;
    }

    public void setRowDataConsumer(Consumer<T> rowDataConsumer) {
        this.rowDataConsumer = rowDataConsumer;
    }

    public void setRules(ColumnValidationRule<T> rules) {
        this.rules = rules;
    }

    public void setBatchRowConsumer(int batchSize, Consumer<List<T>> batchRowConsumer) {
        this.batchSize = batchSize;
        this.batchRowConsumer = batchRowConsumer;
        this.batchRowList = new ArrayList<>(batchSize);
    }

    public static class Builder<T> {
        private RowReadExceptionConsumer exceptionConsumer;
        private Consumer<T> rowDataConsumer;
        private ColumnValidationRule<T> rules;
        private int batchSize = 0;
        private Consumer<List<T>> batchRowConsumer;

        public Builder<T> exceptionConsumer(RowReadExceptionConsumer exceptionConsumer) {
            this.exceptionConsumer = exceptionConsumer;
            return this;
        }

        public Builder<T> rowDataConsumer(Consumer<T> rowDataConsumer) {
            this.rowDataConsumer = rowDataConsumer;
            return this;
        }

        public Builder<T> batchRowConsumer(int batchSize, Consumer<List<T>> batchRowConsumer) {
            this.batchRowConsumer = batchRowConsumer;
            this.batchSize = batchSize;
            return this;
        }

        public Builder<T> rules(ColumnValidationRule<T> rules) {
            this.rules = rules;
            return this;
        }

        public ColumnValidationListener<T> build() {
            ColumnValidationListener<T> listener = new ColumnValidationListener<>();
            listener.setExceptionConsumer(exceptionConsumer);
            listener.setRules(rules);
            listener.setRowDataConsumer(rowDataConsumer);
            listener.setBatchRowConsumer(batchSize, batchRowConsumer);
            return listener;
        }
    }
}
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

3、使用getInputStream()方法获取MultipartFile对象的inputStream();

4、通过inputStream导入Excel，若有error，则返回errorRecord的url；

public String xxxExcelImport(InputStream inputStream) {
        List<XxxExcel> errList = new ArrayList<>();

        EasyExcel.read(
                inputStream,
                XxxExcel.class,
                new ColumnValidationListener.Builder<XxxExcel>()
                        .rules(ColumnValidationRule.<XxxExcel>rules()
                                .func(XxxExcel::getUserName, Objects::nonNull, ERROR_NULL)
                                .func(XxxExcel::getPhoneNum, Objects::nonNull, ERROR_NULL)
                                .func(XxxExcel::getAddress, Objects::nonNull, ERROR_NULL)
                        ).exceptionConsumer(exception -> {
                            exception.printStackTrace();
                            XxxExcel errRow = (XxxExcel) exception
                                    .getContext()
                                    .readRowHolder()
                                    .getCurrentRowAnalysisResult();
                            errRow.setError(exception.getMessage());
                            errList.add(errRow);
                        })
                        .batchRowConsumer(100, bathRow -> {
                            List<XxxPojo> list = new ArrayList<>();
                            bathRow.forEach(row -> {
                                XxxPojo one = new XxxPojo();
                                one.setUserName(row.getUserName());
                                one.setPhoneNum(row.getPhoneNum());
                                one.setAddress(row.getAddress());
                                list.add(one);
                            });
                            saveOrUpdateBatch(list);
                        })
                        .build()
        ).sheet().doRead();

        if (errList.size() > 0) {
            return documentService.exportErrorRecord(errList);
        }
        return null;
    }
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

这是一套导出/导入Excel的通用模板，需要根据具体业务需求进行修改！