EasyExcel导入和导出数据_easyexcel导入导出

pom

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

# 实体类

```java
/**
 * @author:lzp
 * @create: 2022-08-01 15:06
 * @Description: 员工表excel增强
 */

/**
 * @author:lzp
 * @create: 2022-07-03 15:57
 * @Description: excel
 */

@Data
public class EmpExcelVo {
    /**
     * 员工编号
     */
    @ExcelProperty(value = "编号" ,index = 0)
    private Integer id;

    /**
     * 员工姓名
     */
    @ExcelProperty(value = "姓名" ,index = 1)
    private String name;


    /**
     * 工号
     */
    @ExcelProperty(value = "工号" ,index = 2)
    private String workId;

    /**
     * 性别
     */
    @ExcelProperty(value = "性别" ,index = 3)
    private String gender;

    /**
     * 出生日期
     */
    @ExcelProperty(value = "出生日期" ,index =4 )
    @DateTimeFormat("yyyy-MM-dd")
    private java.util.Date birthday;

    /**
     * 身份证号
     */
    @ExcelProperty(value = "身份证号码" ,index = 5)
    private String idCard;

    /**
     * 婚姻状况
     */
    @ExcelProperty(value = "婚姻状况" ,index = 6)
    private String wedlock;

    /**
     * 民族
     */
    @ExcelProperty(value = "民族" ,index = 7)
    private String nationName;

    /**
     * 籍贯
     */
    @ExcelProperty(value = "籍贯" ,index = 8)
    private String nativePlace;

    /**
     * 政治面貌
     */
    @ExcelProperty(value = "政治面貌" ,index = 9)
    private String politicName;

    /**
     * 邮箱
     */
    @ExcelProperty(value = "邮箱" ,index = 10)
    private String email;

    /**
     * 电话号码
     */
    @ExcelProperty(value = "电话号码" ,index = 11)
    private String phone;

    /**
     * 联系地址
     */
    @ExcelProperty(value = "联系地址" ,index = 12)
    private String address;

    /**
     * 所属部门
     */
    @ExcelProperty(value = "所属部门" ,index = 13)
    private String departmentName;

    /**
     * 职称ID
     */
    @ExcelProperty(value = "职称" ,index = 14)
    private String jobLevelName;

    /**
     * 职位ID
     */
    @ExcelProperty(value = "职位" ,index = 15)
    private String posName;

    /**
     * 聘用形式
     */
    @ExcelProperty(value = "聘用形式" ,index = 16)
    private String engageForm;

    /**
     * 最高学历
     */
    @ExcelProperty(value = "最高学历" ,index = 17)
    private String tiptopDegree;

    /**
     * 所属专业
     */
    @ExcelProperty(value = "专业" ,index = 18)
    private String specialty;

    /**
     * 毕业院校
     */
    @ExcelProperty(value = "毕业院校" ,index = 19)
    private String school;

    /**
     * 入职日期
     */
    @ExcelProperty(value = "入职日期" ,index = 20)
    @DateTimeFormat("yyyy-MM-dd")
    private Date beginDate;

    /**
     * 在职状态
     */
    @ExcelProperty(value = "在职状态" ,index = 21)
    private String workState;



    /**
     * 合同期限
     */
    @ExcelProperty(value = "合同期限" ,index = 22)
    private Double contractTerm;

    /**
     * 转正日期
     */
    @ExcelProperty(value = "转正日期" ,index = 23)
    @DateTimeFormat("yyyy-MM-dd")
    private java.util.Date conversionTime;

    /**
     * 离职日期
     */
    @ExcelProperty(value = "离职日期" ,index = 24)
    @DateTimeFormat("yyyy-MM-dd")
    private java.util.Date notWorkDate;

    /**
     * 合同起始日期
     */
    @ExcelProperty(value = "合同起始日期" ,index = 25)
    @DateTimeFormat("yyyy-MM-dd")
    private java.util.Date beginContract;

    /**
     * 合同终止日期
     */
    @ExcelProperty(value = "合同终止日期" ,index = 26)
    @DateTimeFormat("yyyy-MM-dd")
    private java.util.Date endContract;

    /**
     * 工龄
     */
    @ExcelProperty(value = "工龄" ,index = 27)
    private Integer workAge;
}
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
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195

1.导出

1.cmtroller

调用service方法，完成导出

     /**
     * @Author lzp
     * @Description:  导出员工数据
     * @Date: 16:09 2022/7/3
     * @Param: []
     * @return: com.lzp.vhrserver.utils.R
     */
    @GetMapping ("/exportEmpData")
    public void  exportEmpData(HttpServletResponse response) throws IOException {
        employeeEntityService.exportEmpData(response);
    }
1
2
3
4
5
6
7
8
9
10
11

2.service
调用工具类的方法完成导出
传入response，标题控制类(标题名称，合并的列数)，员工列表，文件名称，excel的标题名称，要导出的数据类

    /**
     * 导出
     * @param response
     * @throws IOException
     */
    public void exportEmpData(HttpServletResponse response) throws IOException {
        List<EmpExcelVo> employeeEntities = this.getAllEmp();
        ExportExcelUtil.exportExcel(response,
                new MonthSheetWriteHandler("员工表",9),
                employeeEntities,"员工表", EmpExcelVo.class);
    }
1
2
3
4
5
6
7
8
9
10
11

3.工具类中的方法

/**
 * @author:lzp
 * @create: 2022-08-02 13:28
 * @Description: excel导出工具类
 */

public class ExportExcelUtil {
    /**
     * @Author lzp
     * @Description:
     * @Date: 13:32 2022/8/2
     * @Param: [response 响应, sheetWriteHandler 控制标题样式, list 要导出的数据, fileName 文件名,实体类 表头使用]
     * @return: void
     */
    public static void exportExcel(HttpServletResponse response,
                                   @Nullable SheetWriteHandler sheetWriteHandler, List<?> list,
                                   String fileName, Class baseEntity) throws IOException {
        response.setContentType("application/vnd.ms-excel");//格式excel
        response.setCharacterEncoding("utf-8");
        //这里URLEncoder.encode可以防止中文乱码
        fileName = URLEncoder.encode(fileName, "UTF-8");
        //Content-disposition：以下载的方式执行此操作
        response.setHeader("Content-Disposition", "attachment;filename="+ fileName + ".xlsx");
        //内容样式策略
        WriteCellStyle writeCellStyle = new WriteCellStyle();
        //垂直居中 水平居中
        writeCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        writeCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
        writeCellStyle.setBorderLeft(BorderStyle.THIN);
        writeCellStyle.setBorderTop(BorderStyle.THIN);
        writeCellStyle.setBorderRight(BorderStyle.THIN);
        writeCellStyle.setBorderBottom(BorderStyle.THIN);
        //设置自动换行 这里不设置 采用自适应宽度
        //writeCellStyle.setWrapped(true);
        //字体策略
        WriteFont writeFont = new WriteFont();
        writeFont.setFontHeightInPoints((short)12);
        writeCellStyle.setWriteFont(writeFont);
        //头策略
        WriteCellStyle headWriteStyle = new WriteCellStyle();

        ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(),baseEntity)
                //设置输出excel，不设置默认为xlsx
                //.excelType(ExcelTypeEnum.XLS)
                //设置拦截器自定义样式
                //宽度自适应 自定义handler
                .registerWriteHandler(new CustomCellWriteWeightConfig())
                //设置标题（小标题）行高和内容行高
                .registerWriteHandler(new SimpleRowHeightStyleStrategy((short)40,(short)30))

                //自定义文件的标题（大标题） 在调用方法时传入的参数 MonthSheetWriteHandler类型
                .registerWriteHandler(sheetWriteHandler)
                .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteStyle,writeCellStyle))
                //.sheet(fileName)
                //设置默认样式及写入头信息开始的行数 第一行为大标题 这里1代表从第二行开始
                .useDefaultStyle(true).relativeHeadRowIndex(1)
                .build();
                excelWriter.write(list,EasyExcel.writerSheet(fileName).build());
                excelWriter.finish();

        //EasyExcel.write(response.getOutputStream(),baseEntity)
        //        //设置输出excel，不设置默认为xlsx
        //        //.excelType(ExcelTypeEnum.XLS)
        //        //设置拦截器自定义样式
        //        //宽度自适应
        //        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
        //        //设置标题（小标题）行高和内容行高
        //        .registerWriteHandler(new SimpleRowHeightStyleStrategy((short)40,(short)30))
        //        //自定义文件的标题（大标题）
        //        .registerWriteHandler(sheetWriteHandler)
        //        .registerWriteHandler(new HorizontalCellStyleStrategy(headWriteStyle,writeCellStyle))
        //        .sheet(fileName)
        //        //设置默认样式及写入头信息开始的行数
        //        .useDefaultStyle(true).relativeHeadRowIndex(3)
        //        .doWrite(list);
    }
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

4.控制标题类
这个类控制数据之前的显示内容

/**
 * @author:lzp
 * @create: 2022-08-01 10:51
 * @Description: excel拦截器 定义内容中的大标题
 */


public class MonthSheetWriteHandler implements SheetWriteHandler {
    //excel的标题内容
    private String title;
    //对应参数 标题合并单元格使用 实际参数数量减1
    private Integer params;

    public MonthSheetWriteHandler(String title, Integer params){
        this.title=title;
        this.params=params;
    }
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        Sheet sheet = workbook.getSheetAt(0);

        //第一行
        // 设置标题
        Row row2 = sheet.createRow(0);
        row2.setHeight((short)800);
        Cell cell1 = row2.createCell(0);
        cell1.setCellValue(this.title);
        CellStyle cellStyle = workbook.createCellStyle();
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeight((short)400);
        cellStyle.setFont(font);
        cell1.setCellStyle(cellStyle);
        //调整标题的居中显示位置 0 0 代表第几行 这里是只用第0行，后面两个参数是合并的列 0开始，结束根据参数数量决定
        sheet.addMergedRegionUnsafe(new CellRangeAddress(0,0,0,this.params));

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

效果

2022-10-28 乱码解决
因为文件名经过了 “URLEncoder.encode(fileName, “UTF-8”)”转换，可以保证excel文件名中文正常，但是sheet名就会变成转换的格式。
解决办法是 将文件名单独定义，使用URLEncoder.encode方法转换格式，sheet名用方法传递的参数即可

2.导入

创建一个listener，逐条插入数据
1.controller

    @PostMapping("/importEmpData")
    public R importEmpData(MultipartFile file){
        this.employeeEntityService.importEmpData(file);
        return R.ok("导入成功！");
    }
1
2
3
4
5

service

    public void importEmpData(MultipartFile file) {
        try {
            //headRowNumber 2 代表从第3行开始导入 第一行为大标题 第二行为表头
            EasyExcel.read(file.getInputStream(),EmpExcelVo.class,
                    new MyExcelListener(this)).headRowNumber(2).sheet().doRead();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
1
2
3
4
5
6
7
8
9

2.通用listener

调用invoke方法，实现数据导入，这里定义通用类型，避免每有一个导入任务就创建一个listener。
定义一个包含添加的接口，业务实现类实现这个接口，重写添加方法，保证数据准确的添加进数据库。
创建listener时，传入对应的业务实现类。

/**
 * @author:lzp
 * @create: 2022-08-02 14:05
 * @Description: 导入数据通用监听器
 */

@Slf4j
public class MyExcelListener<T> extends AnalysisEventListener<T> {

    private Map<String, Object> param;
    //3000条保存一次数据
    private static final int BATCH_COUNT=3000;
    //数据缓存
    private List<T> list = new ArrayList<>(BATCH_COUNT);
    //mapper
    private SaveInterface<T> saveInterface;

    //public MyExcelListener(SaveInterface<T> saveInterface, Map<String, Object> param) {
    //    this.saveInterface = saveInterface;
    //    this.param = param;
    //}
	//构造器
    public MyExcelListener(SaveInterface<T> saveInterface) {
        this.saveInterface = saveInterface;
    }

    @Override
    public void invoke(T data, AnalysisContext analysisContext) {

        try {
            //通用方法数据校验
            ExcelImportValid.valid(data);
        }catch (ExceptionCustom e){
            // System.out.println(e.getMessage());
            //在easyExcel监听器中抛出业务异常
            throw new ExcelAnalysisException(e.getMessage());
        }
        log.info("解析到一条数据：{}",data.toString());
        //先将数据加到list中
        list.add(data);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (list.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            list = new ArrayList<>(BATCH_COUNT);
        }
    }

    //最后再存储一次数据
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();

        //logger.info("所有数据解析完成！");
    }
    /**
     * 加上存储数据库
     */
    private void saveData() {
        //logger.info("{}条数据，开始存储数据库！", list.size());
        saveInterface.save(list,param);
        //logger.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
58
59
60
61
62
63
64
65
66

3.接口

public interface SaveInterface <T>{
    void save(List<T> list, Map<String, Object> param);
}
1
2
3

4.业务实现类
实现第三步创建的接口

实现方法
将dto转为和数据库对应的实体类

    @Override
    public void save(List<EmpExcelVo> list, Map<String, Object> param) {
        //遍历list集合 逐条保存数据
        for(EmpExcelVo empExcelVo:list){
            EmployeeEntity employeeEntity = new EmployeeEntity();
            //对象拷贝
            BeanUtils.copyProperties(empExcelVo,employeeEntity);
            //时间相关的需要先获取java.util.Date类型的 然后转换为sql.date类型的
            java.util.Date beginContract = empExcelVo.getBeginContract();
            java.util.Date endContract = empExcelVo.getEndContract();
            java.util.Date birthday = empExcelVo.getBirthday();
            java.util.Date beginDate = empExcelVo.getBeginDate();
            java.util.Date conversionTime = empExcelVo.getConversionTime();
            //vo中存的为名称 需要转换为id 存到数据库
            Integer idByName = politicsstatusEntityService.getIdByName(empExcelVo.getPoliticName());
            employeeEntity.setBeginContract(new Date(beginContract.getTime()));
            employeeEntity.setEndContract(new Date(endContract.getTime()));
            employeeEntity.setBirthday(new Date(birthday.getTime()));
            employeeEntity.setBeginDate(new Date(beginDate.getTime()));
            employeeEntity.setConversionTime(new Date(conversionTime.getTime()));
            employeeEntity.setPoliticId(idByName);
            this.addEmp(employeeEntity);
        }
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

3.导入时数据校验

1.自定义注解

@Target({ ElementType.FIELD, ElementType.TYPE })
@Retention(RetentionPolicy.RUNTIME)
public @interface ExcelValid {
    String message() default "导入有为空得字段";
}
1
2
3
4
5

2.校验注解类

/**
 * @author:lzp
 * @create: 2022-08-02 10:30
 * @Description: excel字段校验类
 */

public class ExcelImportValid {
    /**
     * Excel导入字段校验
     *
     * @param object 校验的JavaBean 其属性须有自定义注解
     */
    public static void valid(Object object) throws ExceptionCustom {
        //获取当前类的所有属性
        Field[] fields = object.getClass().getDeclaredFields();
        for (Field field : fields) {
            //设置可访问
            field.setAccessible(true);
            //属性的值
            Object fieldValue = null;
            try {
                //获取到字段的值
                fieldValue = field.get(object);
            } catch (IllegalAccessException e) {
                throw new ExceptionCustom("IMPORT_PARAM_CHECK_FAIL", "导入参数检查失败");
            }
            //是否包含必填校验注解
            boolean isExcelValid = field.isAnnotationPresent(ExcelValid.class);
            //如果包含这个注解，并且获取到的值为null，抛出异常
            if (isExcelValid && Objects.isNull(fieldValue)) {
                throw new ExceptionCustom("NULL", field.getAnnotation(ExcelValid.class).message());
            }
        }
    }
}
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

3.使用方法
在需要校验的字段加上注解

在invoke中开启校验

        try {
            //通用方法数据校验
            ExcelImportValid.valid(data);
        }catch (ExceptionCustom e){
            // System.out.println(e.getMessage());
            //在easyExcel监听器中抛出业务异常 这个异常会被controller捕获，将错误信息返回给前端
            throw new ExcelAnalysisException(e.getMessage());
        }
1
2
3
4
5
6
7
8

4.ExceptionCustom 类

/**
 * @author:lzp
 * @create: 2022-08-02 10:35
 * @Description: 自定义异常
 */

@Data
@EqualsAndHashCode(callSuper = true)
public class ExceptionCustom extends RuntimeException{
    private static final long serialVersionUID = 1L;

    public ExceptionCustom()
    {
    }

    /**
     * 错误编码
     */
    private String errorCode;

    /**
     * 消息是否为属性文件中的Key
     */
    private boolean propertiesKey = true;

    /**
     * 构造一个基本异常.
     *
     * @param message
     *            信息描述
     */
    public ExceptionCustom(String message)
    {
        super(message);
    }

    /**
     * 构造一个基本异常.
     *
     * @param errorCode
     *            错误编码
     * @param message
     *            信息描述
     */
    public ExceptionCustom(String errorCode, String message)
    {
        this(errorCode, message, true);
    }

    /**
     * 构造一个基本异常.
     *
     * @param errorCode
     *            错误编码
     * @param message
     *            信息描述
     */
    public ExceptionCustom(String errorCode, String message, Throwable cause)
    {
        this(errorCode, message, cause, true);
    }

    /**
     * 构造一个基本异常.
     *
     * @param errorCode
     *            错误编码
     * @param message
     *            信息描述
     * @param propertiesKey
     *            消息是否为属性文件中的Key
     */
    private ExceptionCustom(String errorCode, String message, boolean propertiesKey)
    {
        super(message);
        this.setErrorCode(errorCode);
        this.setPropertiesKey(propertiesKey);
    }

    /**
     * 构造一个基本异常.
     *
     * @param errorCode
     *            错误编码
     * @param message
     *            信息描述
     */
    public ExceptionCustom(String errorCode, String message, Throwable cause, boolean propertiesKey)
    {
        super(message, cause);
        this.setErrorCode(errorCode);
        this.setPropertiesKey(propertiesKey);
    }

    /**
     * 构造一个基本异常.
     *
     * @param message
     *            信息描述
     * @param cause
     *            根异常类（可以存入任何异常）
     */
    public ExceptionCustom(String message, Throwable cause)
    {
        super(message, cause);
    }
}

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