一. 创建好一个SpringBoot项目

关键依赖

	<dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.1.0</version>
        </dependency>

1
2
3
4
5
6

二.关键代码

2.1 关闭权限

2.2 配置Excel的解析监听器

package com.huang.vhr.framework.excel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.huang.vhr.framework.web.entity.Employeeremove;
import com.huang.vhr.framework.web.service.EmployeeremoveService;
import java.util.ArrayList;
import java.util.List;

public class EmployeeremoveListener implements ReadListener<Employeeremove> {

    private List<Employeeremove> employeeremoves = new ArrayList<>();
    private EmployeeremoveService employeeremoveService;

    public EmployeeremoveListener(EmployeeremoveService employeeremoveService) {
        this.employeeremoveService = employeeremoveService;
    }


    /**
     * Excel 文件是被 EasyExcel 自动解析的，每解析一行数据，就通过反射生成一个 Position 对象
     * @param
     * @param analysisContext
     */
    @Override
    public void invoke(Employeeremove employeeremove, AnalysisContext analysisContext) {
        employeeremove.setId(null);
        employeeremoves.add(employeeremove);
    }


    /**
     * 整个 Excel 文件解析完成时，会触发整个方法
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        employeeremoveService.saveBatch(employeeremoves);
    }
}

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

2.3Controller中写入文件上传的接口

这里使用的是Mybatis-plus 框架 ，开启了批处理

 @PostMapping("/import")
    //参数名为file，即我们airpost里要给他file一个对应的名字file
    public ResponseCode importPositionData(MultipartFile file) {
        return employeeremoveService.importEmployeeremoveData(file);
    }
1
2
3
4
5

2.4 yaml配置的开启批处理

注意：不开启批处理会十分慢

1.&rewriteBatchedStatements=true 开启批处理
2.
mybatis:
configuration:
map-underscore-to-camel-case: false
关闭mybatis的驼峰命名识别
3.mybatis-plus:
configuration:
map-underscore-to-camel-case: false
关闭mybatis-plus的驼峰命名识别

spring:
  datasource:
    username: root
    password: 1234
    url: jdbc:mysql:///vhr?serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
server:
  port: 8081


mybatis:
  configuration:
    map-underscore-to-camel-case: false

mybatis-plus:
  configuration:
    map-underscore-to-camel-case: false
#pagehelper: helper-dialect=mysql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

2.5 实体类

package com.huang.vhr.framework.web.entity;

import java.io.Serializable;
import java.util.Date;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableId;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@ToString
@NoArgsConstructor
public class Employeeremove implements Serializable {
    private static final long serialVersionUID = 1L;
    /**
     *
     */
    @TableId(value = "id", type = IdType.AUTO)
    @ExcelProperty("调动编号")
    private Integer id;

    /**
     *
     */

    @ExcelProperty("调动与主表关联")
    private Integer eid;

    /**
     * 调动后部门
     */
    @ExcelProperty("调动后部门")
    private Integer afterDepId;

    /**
     * 调动后职位
     */
    @ExcelProperty("调动后职位")
    private Integer afterJobId;

    /**
     * 调动日期
     */
    @JsonFormat(pattern="yyyy-MM-dd HH:mm:ss",timezone = "Asia/Shanghai")
    @ExcelProperty("调动日期")
    private Date removeDate;

    /**
     * 调动原因
     */
    @ExcelProperty("调动原因")
    private String reason;

    /**
     *
     */
    @ExcelProperty("调动评语")
    private String remark;
}


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

postman测试