easyexcel 简介

Java 解析、生成 Excel 比较有名的框架有 Apache POI，但存在一个严重的问题就是非常的耗内存，针对这个问题阿里出品了用来处理 Excel 的开源工具 easyexcel

GitHub 官网：https://github.com/alibaba/easyexcel
官方文档：https://alibaba-easyexcel.github.io/

EasyExcel 和 POI 的对比

• 相比于 POI，EasyExcel 简化了开发量，能够用更少的代码实现更多的功能
• 相比于 POI，EasyExcel 使用简单
• 相比于 POI，EasyExcel 能够使用更少的内存占用

SpringBoot 整合 easyexcel

Maven 依赖

主要依赖如下，其他依赖自行依赖导入

<dependency>
	<groupId>com.alibabagroupId>
	<artifactId>easyexcelartifactId>
	<version>2.2.6version>
dependency>

数据库 SQL 脚本

CREATE TABLE `excel_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `dept_id` int(11) NOT NULL COMMENT '部门ID',
  `loginName` varchar(255) NOT NULL COMMENT '登录账号\r\n',
  `phone` varchar(255) NOT NULL,
  `password` varchar(255) NOT NULL COMMENT '密码',
  `status` int(255) NOT NULL COMMENT '帐号状态',
  `createTime` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updateTime` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `index_ln` (`loginName`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

实体类

@Data
public class ExcelUser {

	// 定义表头名称和位置，0代表第一列
    @ExcelProperty(value = "编号", index = 0)
    private Integer userId;

    @ExcelProperty(value = "部门编号", index = 1)
    private Integer deptId;

    @ExcelProperty(value = "登录账号", index = 2)
    private String loginname;

    @ExcelProperty(value = "用户手机号", index = 3)
    private String phone;

    @ExcelProperty(value = "密码", index = 4)
    private String password;

    @ExcelProperty(value = "账户状态", index = 5)
    private Integer status;

    @ExcelProperty(value = "创建时间",index = 6)
    private Date createtime;

    @ExcelProperty(value = "更新时间",index = 7)
    private Date updatetime;
}

• easyexcel 中的常用注解：https://blog.csdn.net/weixin_45151960/article/details/109095332

导入导出 dao 层

mapper 接口

@Mapper
public interface ExcelUserMapper {

    List<ExcelUser> getAll();

    int insertAll(List<ExcelUserDto> excelUserDtoList);
}

mapper.xml

<select id="getAll" resultType="org.example.pojo.ExcelUser">
	SELECT
		<include refid="Base_Column_List">include>
	FROM
		excel_user
select>


<insert id="insertAll" parameterType="java.util.List">
	insert into excel_user (dept_id, loginName, phone, password, status) values
    <foreach collection="list" item="excelUserDto" index="index" separator=",">
    	(#{excelUserDto.deptId}, #{excelUserDto.loginname},
        #{excelUserDto.phone}, #{excelUserDto.password}, #{excelUserDto.status})
	foreach>
insert>

导入导出 service 层

@Slf4j
@Service
public class ExcelServiceImpl implements ExcelService {

    @Autowired
    private ExcelUserMapper excelUserMapper;

    /**
     * 导出用户信息
     *
     * @param response response
     */
    @Override
    public ResultMap excelExport(@NotNull HttpServletResponse response) {
        // 获取需要导出的数据
        List<ExcelUser> excelUserList = excelUserMapper.getAll();
        log.info("记录导出数据行数：{}", excelUserList.size());
        try {
            response.setContentType("application/json");
            response.setCharacterEncoding("UTF-8");
            String fileName = URLEncoder.encode("用户名单表", "UTF-8");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
            // 向Excel中写入数据
            ExcelWriterBuilder write = EasyExcel.write(response.getOutputStream(), ExcelUser.class);
            ExcelWriterSheetBuilder excelWriterSheetBuilder = write.sheet("用户名单表");
            excelWriterSheetBuilder.doWrite(excelUserList);
            return new ResultMap().success();
        } catch (Exception e) {
            log.error("数据导出失败！！！");
            return new ResultMap().fail();
        }
    }

    /**
     * 导入用户信息
     *
     * @param multipartFile multipartFile
     */
    @Transactional
    @Override
    public ResultMap excelImport(@NotNull MultipartFile multipartFile) {
        try {
            ExcelListener excelListener = new ExcelListener();
            // 读取数据
            ExcelReaderBuilder excelReaderBuilder = EasyExcel.read(multipartFile.getInputStream(), ExcelUser.class, excelListener);
            ExcelReaderSheetBuilder excelReaderSheetBuilder = excelReaderBuilder.sheet();
            excelReaderSheetBuilder.doRead();
            // 获取excel读取到的数据
            List<ExcelUser> list = excelListener.getList();
            // 比较loginname字段去重，数据库有唯一索引
            List<ExcelUser> excelUserList = this.removeDuplicateOrder(list);
            // 数据库表不用插入userId列，它是主键列
            List<ExcelUserDto> excelUserDtoList = new ArrayList<>();
            excelUserList.forEach(item -> {
                ExcelUserDto excelUserDto = new ExcelUserDto();
                BeanUtils.copyProperties(item, excelUserDto);
                excelUserDtoList.add(excelUserDto);
            });
            // 数据的持久化
            int i = excelUserMapper.insertAll(excelUserDtoList);
            if (i > 0) {
                return new ResultMap().success();
            }
            return new ResultMap().fail();
        } catch (IOException e) {
            log.error("数据导入失败！！！");
            return new ResultMap().fail();
        }
    }

    /**
     * 比较loginname字段去重，数据库有唯一索引
     *
     * @param list list
     * @return List
     */
    @NotNull
    private List<ExcelUser> removeDuplicateOrder(List<ExcelUser> list) {
        Set<ExcelUser> set = new TreeSet<>((a, b) -> {
            // 等于0表示重复
            int compareToResult = 1;
            if (a.getLoginname().equals(b.getLoginname())) {
                compareToResult = 0;
            }
            return compareToResult;
        });
        set.addAll(list);
        return new ArrayList<>(set);
    }
}

导入导出 controller 层

@Slf4j
@Controller
public class ExcelController {

    @Autowired
    private ExcelService excelService;

    /**
     * 导出用户信息
     *
     * @param response response
     * @return ResultMap
     */
    @GetMapping(path = "/excelExport")
    @ResponseBody
    public ResultMap excelExport(HttpServletResponse response) {
        return excelService.excelExport(response);
    }

    @GetMapping(path = "/fileUpload")
    public String uploadPage(){
        return "fileUpload";
    }

    /**
     * 导入用户信息
     *
     * @param file multipartFile
     * @return ResultMap
     */
    @PostMapping(path = "/excelImport")
    @ResponseBody
    public ResultMap excelImport(MultipartFile file) {
        return excelService.excelImport(file);
    }
}

表格事件监听器

@Slf4j
public class ExcelListener extends AnalysisEventListener<ExcelUser> {

    @Getter
    @Setter
    private List<ExcelUser> list = new ArrayList<>();

    @Override
    public void invoke(ExcelUser excelUser, AnalysisContext analysisContext) {
        log.info("--------------------------------------------");
        log.info("导入数据{}", JSON.toJSONString(excelUser));
        // 数据存储到list，供批量处理，或后续自己业务逻辑处理
        list.add(excelUser);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

    }
}

导入 excel 报表页面

DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8" />
    <title>Insert title heretitle>
head>
<body>
    <h1 th:inlines="text">文件上传h1>
    <form th:action="@{excelImport}" method="post" enctype="multipart/form-data">
        <p>选择文件: <input type="file" name="file"/>p>
        <p><input type="submit" value="提交"/>p>
    form>
body>
html>

• 此处的选择文件的 input 标签中的 name 属性值务必要与 controller 接口中的入参变量名 MultipartFile file 保持一致，否则会报错参数 MultipartFile 不能为 null

excel 报表的导出

浏览器请求接口 http://127.0.0.1:8080/excelImport，结果如下

导出 excel 中的数据

数据库表数据

excel 报表的导入

准备要导入的 excel 报表的数据如下

浏览器访问接口 http://127.0.0.1:8080/fileUpload，页面如下

选择相应的 excel 报表文件，点击提交，结果如下

数据库表新增数据如下