EasyExcelDemo初体验

官网：https://easyexcel.opensource.alibaba.com/

demo代码：

github：ishuaige/easyExcelDemo: easyExcel的Demo (github.com)

gitee：easyExcelDemo: easyExcel的小demo (gitee.com)

个人博客：dogbin.vip

一.准备工作

1. 导入数据库文件
   1. 我们做一个类似积分系统，数据库包含用户名，上月积分和当前积分

• 数据均为随机生成的哈

DROP TABLE IF EXISTS `user`;
CREATE TABLE `user`  (
  `id` int(0) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL COMMENT '用户名',
  `oldScore` int(0) NOT NULL COMMENT '旧分数',
  `currentScore` int(0) NULL DEFAULT NULL COMMENT '当前分数',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES (1, '覃瑾瑜', 2, 815562);
INSERT INTO `user` VALUES (2, '高炎彬', 8907860, 42442602);
INSERT INTO `user` VALUES (3, '石嘉熙', 665342, 86753);
INSERT INTO `user` VALUES (4, '赵越彬', 426, 999);
INSERT INTO `user` VALUES (5, '廖智渊', 733840, 6529367);
INSERT INTO `user` VALUES (6, '傅明辉', 5, 57647299);
INSERT INTO `user` VALUES (7, '龚越彬', 43, 78204464);
INSERT INTO `user` VALUES (8, '陆烨霖', 1681509, 3);
INSERT INTO `user` VALUES (9, '黎晋鹏', 2, 134809752);
INSERT INTO `user` VALUES (10, '雷昊焱', 20, 3489470);
INSERT INTO `user` VALUES (11, '唐子骞', 62, 20720);
INSERT INTO `user` VALUES (12, '杜健雄', 2594, 3);
INSERT INTO `user` VALUES (13, '刘烨霖', 25, 120);
INSERT INTO `user` VALUES (14, '吕远航', 1, 59870);

SET FOREIGN_KEY_CHECKS = 1;

2. 创建一个springboot工程
   1. 依赖 springboot 2.7.7

    <dependencies>
        <!--fastjson序列化-->
        <dependency>
            <groupId>com.alibaba.fastjson2</groupId>
            <artifactId>fastjson2</artifactId>
            <version>2.0.23</version>
        </dependency>
        <!--easyexcel操作excel-->
        <dependency>
            <groupId>com.alibaba</groupId>
            <artifactId>easyexcel</artifactId>
            <version>3.2.0</version>
        </dependency>
        <!-- mybatis-plus -->
        <dependency>
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.1</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <scope>runtime</scope>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.3.0</version>
        </dependency>
      <!-- 接口文档 -->
        <dependency>
            <groupId>com.github.xiaoymin</groupId>
            <artifactId>knife4j-spring-boot-starter</artifactId>
            <version>3.0.3</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <scope>runtime</scope>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

spring:
  application:
    name: easyExcelDemo
  # Mysql config
  datasource:
    driver-class-name: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://localhost:3306/easyexcel
    username: root
    password: 123456
  mvc:
    pathmatch:
      matching-strategy: ANT_PATH_MATCHER
mybatis-plus:
  configuration:
    map-underscore-to-camel-case: false
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
  global-config:
    db-config:
      logic-delete-field: isDelete
      logic-delete-value: 1
      logic-not-delete-value: 0

3. 创建好操作数据的model、mapper、service
   1. 使用mybatis X插件创建

这里不赘述以上技术的玩法哈

二.简单的写

主要api: EasyExcel.write()

1. 导入easyExcel和fastjson的依赖，上面的依赖中已有
2. 定义excel的表头
   1. 我们在这里自己算出增长积分
   2. @ExcelProperty(“姓名”) 声明表头，在代码中变量不好取中文，就可以使用注解声明

package com.niuma.easyexceldemo.excel.model;

import com.alibaba.excel.annotation.ExcelProperty;
import lombok.Data;

/**
 * @author niumazlb
 * @create 2023-01-19 20:35
 */
@Data
public class ExcelUserData {
    @ExcelProperty("姓名")
    private String name;
    @ExcelProperty("上月积分")
    private Integer oldScore;
    @ExcelProperty("总积分")
    private Integer currentScore;
    @ExcelProperty("增长积分")
    private Integer growScore;

}

3. 编写测试类

@SpringBootTest
public class easyExcelTest {
    @Resource
    UserService userService;

    public static final String PATH = "D:\\code\\IDEA\\easyExcelDemo\\";
    public static final String fileName = "testEasyExcelWrite.xlsx";
    public static final String pathName = PATH + fileName;

    @Test
    public void testEasyExcelWrite() {
        List<User> userList = userService.list();
        List<ExcelUserData> excelDataList = userList.stream().map(user -> {
            ExcelUserData excelData = new ExcelUserData();
            excelData.setName(user.getName());
            excelData.setOldScore(user.getOldScore());
            excelData.setCurrentScore(user.getCurrentScore());
            excelData.setGrowScore(user.getCurrentScore() - user.getOldScore());
            return excelData;
        }).sorted(Comparator.comparingInt(ExcelUserData::getGrowScore)).collect(Collectors.toList());
    	//参数声明路径名和表头
        EasyExcel.write(pathName,ExcelData.class).sheet().doWrite(excelDataList);
    }
}

• 其实就是调用EasyExcel.write()这个api，参数是路径名，表头
  • 链式调用 sheet()，可以声明页信息，如页号，页名
  • dowrite()，执行写操作，参数填入数据集

4. 校验结果
   1. 在对应的路径下就可以看到导出的excel表

三.简单的读

在导入依赖和定义了表头后开始读操作的编写测试

大概意思：服务端要接收到excel，我们需要知道

• excel文件：通过网络或本地路径（对谁做？）

• 表头字段：转换成对象（什么样子？面向对象）

• 监听器：定义读取数据后的处理方式（做什么？）

1. 定义Listeners监听器，注意这里处理数据的方法，防止OOM
   1. 实现ReadListener<DataType>接口，泛型自然就是表头的字段
   2. 实现2个主要方法
      1. invoke(ExcelUserData excelUserData, AnalysisContext analysisContext)：每读到一条数据都会调用这个函数，可以在这里对数据的预处理，比如将读到的ExcelUserData转换成数据库的User
      2. doAfterAllAnalysed(AnalysisContext analysisContext)：所有数据解析完成了 都会来调用 做收尾工作，确保最后遗留的数据也持久化（存储到数据库）

package com.niuma.easyexceldemo.excel.listeners;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.alibaba.fastjson2.JSON;
import com.niuma.easyexceldemo.excel.model.ExcelUserData;
import com.niuma.easyexceldemo.service.UserService;
import lombok.extern.slf4j.Slf4j;

import java.util.List;

/**
 * ExcelModelListener 不能被spring管理，要每次读取 excel 都要 new,然后里面用到 spring 可以构造方法传进去
 *
 * @author niumazlb
 * @create 2023-01-19 20:59
 */
@Slf4j
public class UserDataListener implements ReadListener<ExcelUserData> {


    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理 list ，方便内存回收，避免 OOM
     */
    private static final int BATCH_COUNT = 100;
    /**
     * 缓存的数据，在 invoke 函数中存储每次读到的数据，这里的泛型虽业务变化而变化，存储的可以是excel表数据处理后的数据
     * 假如我要存入数据库中就需要将 ExcelUserData 转换成 User 那么这里的泛型就是User，在 invoke 中处理后添加
     */
    private List<ExcelUserData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

    /**
     * 这个是一个DAO，当然有业务逻辑这个也可以是一个service。可以用来解析数据后操作数据库
     */
    private UserService userService;

    /**
     * 每读到一条数据都会调用这个函数，可以在这里对数据的预处理
     *
     * @param excelUserData
     * @param analysisContext
     */
    @Override
    public void invoke(ExcelUserData excelUserData, AnalysisContext analysisContext) {
        log.info("解析到一条数据:{}", JSON.toJSONString(excelUserData));
        cachedDataList.add(excelUserData);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            log.info("已达到BATCH_COUNT，共{}条数据", cachedDataList.size());

            // 调用储存数据函数
            saveData();

            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    /**
     * 所有数据解析完成了 都会来调用 做收尾工作，确保最后遗留的数据也持久化（存储到数据库）
     *
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();

        log.info("所有数据解析完成！");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据，开始存储数据库！", cachedDataList.size());

        //  TODO 数据存储,使用批处理操作防止多次连接数据库，例如 userService.saveBatch();

        log.info("存储数据库成功！");
    }
}

2. 测试

    @Test
    public void testRead(){
      EasyExcel.read(
          pathName,
          ExcelUserData.class,
          new UserDataListener()
      ).sheet().doRead();
    }

• 主要调用api：EasyExcel.read() 参数就是前面我们需要知道的三个东西。链式调用sheet同写

3. 检验结果

四.web接口调用

① 上传 excel 并解析

更多可参考：spring boot 中使用easyExcel 实现Excel文件读取_夜半子时的博客

• 解析逻辑在 Listener 里，这里演示只做日志打印

/**
 * @author niumazlb
 */
@RestController
@RequestMapping("/user")
public class UserController {
    @Resource
    private UserService userService;

    /**
     * 上传 excel 并解析，解析逻辑在 Listener 里
     *
     * @param file
     * @return
     */
    @PostMapping("/upload")
    public BaseResponse<Boolean> uploadDataByExcel(@RequestBody MultipartFile file) {
        try {
            //判断文件类型是否正确
            String originalFilename = file.getOriginalFilename();
            String fileType = originalFilename.substring(originalFilename.lastIndexOf("."));
            if (!".xls".equalsIgnoreCase(fileType) && !".xlsx".equalsIgnoreCase(fileType)) {
                throw new BusinessException(ErrorCode.PARAMS_ERROR, "文件格式错误");
            }

            EasyExcel.read(file.getInputStream(), ExcelUserData.class, new UserDataListener()).sheet().doRead();
            return ResultUtils.success(true);
        } catch (IOException e) {
            throw new BusinessException(ErrorCode.SYSTEM_ERROR);
        }
    }

}

② 下载文件

• 用swagger不好使，直接在浏览器输入地址就好了
• 这里应该将大段逻辑放到service中，只不过作演示方便

    /**
     * 下载文件，通过 easyExcel 写 excel 再传输到前端
     *
     * @param response
     * @throws IOException
     */
    @GetMapping("/download")
    public void download(HttpServletResponse response) {
        try {
            // 这里注意 有同学反应使用 swagger 会导致各种问题，请直接用浏览器或者用postman
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            // 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系
            String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            List<User> userList = userService.list();
            List<ExcelUserData> excelDataList = userList.stream().map(user -> {
                ExcelUserData excelData = new ExcelUserData();
                excelData.setName(user.getName());
                excelData.setOldScore(user.getOldScore());
                excelData.setCurrentScore(user.getCurrentScore());
                excelData.setGrowScore(user.getCurrentScore() - user.getOldScore());
                return excelData;
            }).sorted(Comparator.comparingInt(ExcelUserData::getGrowScore)).collect(Collectors.toList());
            EasyExcel.write(response.getOutputStream(), ExcelUserData.class).sheet().doWrite(excelDataList);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }