Springboot整合EasyExcel用于导入_springboot easyexcel导入

业务背景

接着Springboot整合EasyExcel用于导出之后，写EasyExcel导入话不多说，直接上代码

具体使用

控制层代码

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.zyp.config.StudentReadListener;
import com.zyp.model.vo.StudentImport;
import com.zyp.service.StudentService;
import com.zyp.util.Result;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;

/**
 * @author leishen
 */
@Api(tags = "测试EasyExcel的导入")
@RestController
@RequestMapping("EasyExcelImport/")
@Slf4j
public class EasyExcelImportController {

    @Autowired
    private StudentService studentService;

    @PostMapping("test")
    @ApiOperation("导入")
    public Result easyExcelImport(@RequestParam("file") MultipartFile file) throws IOException {
        if(file==null){
            return new Result().error("文件不能为空");
        }
        ExcelReaderBuilder readerBuilder = EasyExcel.read(file.getInputStream(), StudentImport.class, new StudentReadListener(studentService));
        //doReadAll()异步读取所有
        readerBuilder.doReadAll();
        //doReadAllSync()同步读取所有
        readerBuilder.doReadAllSync();
        //headRowNumber(int):0-此工作表没有标题，因为第一行是数据,
        // 默认为1，表示只有一个行首
        // 2表示由于第三行是数据，因此该表有两行标题
        //readerBuilder.headRowNumber(2).doReadAllSync();
        //sheet()读取第一个sheet
        //readerBuilder.sheet().doReadSync();
        //sheet(1)读取第二个sheet,默认从0开始
        readerBuilder.sheet(0).doReadSync();
        return new Result().ok(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
40
41
42
43
44
45
46
47
48
49
50
51
52

设置监听器

监听导入的过程

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.exception.ExcelDataConvertException;
import com.alibaba.excel.metadata.CellExtra;
import com.alibaba.excel.read.listener.ReadListener;
import com.baomidou.mybatisplus.extension.api.Assert;
import com.google.common.collect.Lists;
import com.zyp.model.vo.StudentImport;
import com.zyp.service.StudentService;
import lombok.extern.slf4j.Slf4j;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Component;

import java.util.List;
import java.util.Map;

/**
 * @author syl
 * @description 监听导入过程
 * @since 2022/3/27
 */
@Slf4j
@Component
public class StudentReadListener implements ReadListener<StudentImport> {

    /**
     * 每隔5条存储数据库，实际使用中可以3000条，然后清理list ，方便内存回收
     */
    private static final int BATCH=500;

    private StudentService studentService;

    private static List<StudentImport> studentImportList= Lists.newArrayList();

    private static final Logger LOGGER = LoggerFactory.getLogger(StudentReadListener.class);

    public StudentReadListener(StudentService studentService) {
        this.studentService=studentService;
    }

    /**
     * 在转换异常 获取其他异常下会调用本接口。抛出异常则停止读取。如果这里不抛出异常则 继续读取下一行。
     */

    @Override
    public void onException(Exception exception, AnalysisContext context) throws Exception {
        // 如果是某一个单元格的转换异常 能获取到具体行号
        // 如果要获取头的信息 配合invokeHeadMap使用
        if (exception instanceof ExcelDataConvertException) {
            ExcelDataConvertException excelDataConvertException = (ExcelDataConvertException) exception;
            LOGGER.error("第{}行，第{}列解析异常,错误数据为:{}", excelDataConvertException.getRowIndex(),
                    excelDataConvertException.getColumnIndex(), excelDataConvertException.getCellData().getStringValue());
        }
    }

    @Override
    public void invokeHead(Map headMap, AnalysisContext context) {

    }

    /**
     * 一行一行的读取
     *
     * @param studentImport
     * @param context
     */
    @Override
    public void invoke(StudentImport studentImport, AnalysisContext context) {
        System.out.println("studentImport = " + studentImport);
        studentImportList.add(studentImport);
        if(studentImportList.size()>=BATCH){
            studentService.saveBatch(studentImportList);
            studentImportList.clear();
        }
    }

    @Override
    public void extra(CellExtra extra, AnalysisContext context) {
        LOGGER.info("读取到了一条额外信息:{}", extra.toString());
        switch (extra.getType()) {
            case COMMENT:
                LOGGER.info("额外信息是批注,在rowIndex:{},columnIndex;{},内容是:{}", extra.getRowIndex(), extra.getColumnIndex(),
                        extra.getText());
                break;
            case HYPERLINK:
                if ("Sheet1!A1".equals(extra.getText())) {
                    LOGGER.info("额外信息是超链接,在rowIndex:{},columnIndex;{},内容是:{}", extra.getRowIndex(),
                            extra.getColumnIndex(), extra.getText());
                } else if ("Sheet2!A1".equals(extra.getText())) {
                    LOGGER.info(
                            "额外信息是超链接,而且覆盖了一个区间,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{},"
                                    + "内容是:{}",
                            extra.getFirstRowIndex(), extra.getFirstColumnIndex(), extra.getLastRowIndex(),
                            extra.getLastColumnIndex(), extra.getText());
                } else {
                    Assert.fail("Unknown hyperlink!");
                }
                break;
            case MERGE:
                LOGGER.info(
                        "额外信息是超链接,而且覆盖了一个区间,在firstRowIndex:{},firstColumnIndex;{},lastRowIndex:{},lastColumnIndex:{}",
                        extra.getFirstRowIndex(), extra.getFirstColumnIndex(), extra.getLastRowIndex(),
                        extra.getLastColumnIndex());
                break;
            default:
        }
    }

    /**
     * 读取一个sheet页完成后执行
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        String sheetName = context.readSheetHolder().getSheetName();
        log.info("sheet:{}读取完成", sheetName);
        studentService.saveBatch(studentImportList);
        studentImportList.clear();
    }

    /**
     * 验证是否存在另一条数据。您可以通过返回false来停止读取
     * @param context
     * @return
     */
    @Override
    public boolean hasNext(AnalysisContext context) {
        return true;
    }
}
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

导入实体类

import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;

/**
 * @author syl
 * @description 导入实体类
 * @since 2022/3/27
 */
@Data
@TableName("student")
public class StudentImport {

    /**
     * ExcelProperty：value()导出：当你有多少个头时，它会自动合并
     *                       导入：当你有多少个头时，先拿第一个头
     *                       列名指定的话指定的名，不指定则默认是属性名
     *                index():默认为-1，index越小列越靠前
     *                order():默认Integer.MAX_VALUE，order越小越靠前
     *                注：优先级:index>order>默认
     *                converter:自定义转换器,修饰符必须为public
     */
    @ExcelProperty(value = {"学生信息","序号"},order = 0)
    @TableId
    private Integer id;

    @ExcelProperty(value = {"学生信息","姓名"},order = 1)
    private String name;

    @ExcelProperty(value = {"学生信息","年龄"},order = 2)
    private Integer age;

    @ExcelProperty(value = {"创建时间"})
    @JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
    private String createTime;
}
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

实体类对应的表结构：

CREATE TABLE `student` (
  `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '序号',
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '姓名',
  `age` int(2) DEFAULT NULL COMMENT '年龄',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8 COMMENT='学生表';
1
2
3
4
5
6
7

实体类相关的增删改类

Maper层

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.zyp.model.vo.StudentImport;

/**
 * <p>
 * 学生表 Mapper 接口
 * </p>
 *
 * @author syl
 * @since 2023-02-01
 */
public interface StudentMapper extends BaseMapper<StudentImport> {

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

xml文件：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zyp.mapper.StudentMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.zyp.model.vo.StudentImport">
        <id column="id" property="id" />
        <result column="name" property="name" />
        <result column="age" property="age" />
        <result column="create_time" property="createTime" />
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, name, age, create_time
    </sql>

</mapper>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

service层

import com.baomidou.mybatisplus.extension.service.IService;
import com.zyp.model.vo.StudentImport;

/**
 * <p>
 * 学生表 服务类
 * </p>
 *
 * @author syl
 * @since 2023-02-01
 */
public interface StudentService extends IService<StudentImport> {

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14

service的实现类：

import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.zyp.mapper.StudentMapper;
import com.zyp.model.vo.StudentImport;
import com.zyp.service.StudentService;
import org.springframework.stereotype.Service;

/**
 * <p>
 * 学生表 服务实现类
 * </p>
 *
 * @author syl
 * @since 2023-02-01
 */
@Service
public class StudentServiceImpl extends ServiceImpl<StudentMapper, StudentImport> implements StudentService {

}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18