EasyPOI

Excel 版本的区别

03 版本: 最多行数支持:65536，并且后缀为.xls
07 版本: 理论上没有限制,但实际支持行数为:1068576,并且后缀为:.xlsx

EasyPOI

文档

官网

https://easypoi.mydoc.io/
特点
1. 设计精巧,使用简单
2. 接口丰富,扩展简单
3. 默认值多write less do more 4.spring mvc 支持,web 导出可以简单明了

基本使用

添加依赖

<dependencies>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.11</version>
        <scope>test</scope>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-base</artifactId>
        <version>3.2.0</version>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-web</artifactId>
        <version>3.2.0</version>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-annotation</artifactId>
        <version>3.2.0</version>
    </dependency>
</dependencies>

注解介绍

easypoi 起因就是Excel 的导入导出，最初的模板时实体和Excel 的对应，model-row,filed-col 这样利用注解我们可以很容易做到Excel
1. @Excel 作用到field 上面,是对Excel 一列的一个描述
2. @ExcelCollection 表示一个集合，主要针对一对多的导出,比如一个老师对应多个科目,科目就可以用集合表示
3. @ExcelEntity 表示一个继续深入导出的实体,但她没有太多的实际意义,只是告诉这个系统这个对象里面同样有导出的字段
4. @ExcelIgnore 和名字一样,表示这个字段被忽略跳过这个导出
5. @ExcelTarget 这个是作用与最外层的对象，描述这个对象的id，以便支持一个对象可以针对不同带出做出不同处理

@ExcelTarget

说明
- 用在实体类上标识是一个可以通过EasyPOI 导入导出的实体类
相关属性
- value:[String][定义 id 唯一标识,不能重复(常用)]
- height:[Double][定义单元格高度]
- fontSize:[short][定义单元格字体大小]

@Excel

说明
- 用在field(实体类字段上),是对Excel一列的一个描述
- 常用属性
  - name:[string] [生成Excel表格中的列名]
  - needMerge:[boolean][是否需要纵向上合并单元格(用于含有 list中,单个的单元格,合并 list创建的多个 row)]
  - orderNum[string][指定生成Excel中列顺序,按照数字自然顺序排序]
  - savePathp[string][指定导出 Excel中图片的保存路径]
  - type[string][导出类型 1:文本 2:图片 3:函数 10:数字默认是文本]
  - width[Double][指定导出Excel时的列宽]
  - isImportField[boolean][是否是导入字段,如果没有说明是错误的 Excel]
  - exportFormat[string][导出 Excel的时间格式]
  - format[string][相当于设置了 exportFormat和importFormat]
  - imagetype[int][导出类型 1:从file读取 2:从数据库中读取默认是文件,同样导入也是一样的]
  - suffix[string][文字后缀,如 % 90变成 90%]

数据导出

序列化

注意: 导出 Excel 的对象必须实现对象的序列化接口

解析实体类

实体与`Excel` 的对应关系

导出实现

package com.coderitl.exsel.dto;

public class TestExcelUserDTO {
    // 模拟查询
    public List<UserDTO> getUser() {
        List<UserDTO> users = new ArrayList<>();
        for (int i = 0; i < 5; i++) {
            UserDTO user = new UserDTO();
            user.setId(String.valueOf(i));
            user.setName("coder-itl_" + i);
            user.setAge(10 + i);
            user.setBirthday(new Date());
            users.add(user);
        }
        return users;
    }

    // 导出 excel
    @Test
    public void textExport() throws Exception {
        List<UserDTO> userDTOS = getUser();
        /**
         * 导出 excel
         * new ExportParams() 导出配置对象
         * UserDTO.class 导出的类型
         * userDTOS 导出数据集合
         */
        Workbook workbook = ExcelExportUtil.exportExcel(
                new ExportParams("用户信息列表", "用户信息"),
                UserDTO.class, userDTOS);
        // 将 excel 写入指定位置
        FileOutputStream outputStream = new FileOutputStream("F:\\bb.xls");
        workbook.write(outputStream);
        outputStream.close();
        workbook.close();
    }
}

对比

导出 List 类型处理

添加字段

1 2	@Excel(name = "喜好", width = 20.0, orderNum = "5") private List<String> hobbys;

if (i % 2 == 0) {
    user.setHobbys(Arrays.asList("打篮球", "看书", "听音乐"));
} else {
    user.setHobbys(Arrays.asList("Java", "SpringBoot", "SSM"));
}

`List` 默认的导出格式

自定义格式

@ExcelIgnore
private List<String> hobbys;
@Excel(name = "喜好", width = 40.0, orderNum = "5")
private String hobbyStr;
public String getHobbyStr() {
    StringBuilder sb = new StringBuilder();
    hobbys.forEach(e -> {
        sb.append(e).append("、");
    });
    return sb.toString();
}

渲染为自定义格式

引用类型处理

card


@Data
@ExcelTarget("CardDTO")
public class CardDTO implements Serializable {
    @Excel(name = "身份证号", orderNum = "6", width = 30.0)
    private String cardId;
    @Excel(name = "籍贯", orderNum = "7", width = 40.0)
    private String address;
}

package com.coderitl.exsel.dto;

@Data
// value 的值仅仅是一个标识
@ExcelTarget(value = "UserDTO")
public class UserDTO implements Serializable {
    ...
    // 标识一一对应
    @ExcelEntity
    private CardDTO cardDTO;

}

CardDTO cardDTO = new CardDTO();
cardDTO.setCardId("123456904796874859");
cardDTO.setAddress("aaa");
user.setCardDTO(cardDTO);

引用类型处理

一对多关系处理

注解:@ExcelCollection
- 说明
  - 一对多的集合注解，用以标记集合是否被数据以及集合的整体排序
  - 常用属性
    - name:[string][定义集合列名]
    - orderNum:[int][用来指定带出 excel集合内列的顺序]
    - type:[CLass<?>][用来指定导出是创建对象的类型]

定义

@Data
@AllArgsConstructor
@ExcelTarget("OrderDTO")
public class OrderDTO implements Serializable {
    @Excel(name = "订单编号", orderNum = "8")
    private String orderId;
    @Excel(name = "订单名称", orderNum = "9", width = 15.0)
    private String orderName;
}

1 2	@ExcelCollection(name = "订单列表") private List<OrderDTO> orderDTOS;

// 订单赋值
List<OrderDTO> orderDTOS = new ArrayList<>();
orderDTOS.add(new OrderDTO("12", "313"));
user.setOrderDTOS(orderDTOS);

一对多

导出图片

定义

1 2	@Excel(name = "图片", type = 2, width = 20, height = 30, imageType = 1) private String photo;

1 2	// 设置图片路径(绝对路径) user.setPhoto("C:\\Users\\coderitl\\Desktop\\h5\\表格.png");

图片

大数据量导出

说明

大数据导出是当我们的导出数据量在几万，到上百万的数据时,一次从数据库查询到这么多数据加载到内存然后就写入会对我们的内存和CPU 都产生压力，这个时候需要我们像分页一样处理导出分段写入Excel 缓解Excel 压力

注意: 最好大量数据进行分页处理,每次导出数据最好不要超过1W 条记录

1
2
3

Workbook workbook = ExcelExportUtil.exportBigExcel(new ExportParams("title", "sheetName"), UserDTO.class, getUser());
workbook.write(FileOutputStream("C:\\aa.xsl"));
ExcelExportUtil.closeExportBigExcel();

导入基本数据

生成测试数据

package com.coderitl.exsel.dto;

@Data
// value 的值仅仅是一个标识
@ExcelTarget(value = "UserDTO")
public class UserDTO implements Serializable {
    @Excel(name = "编号", orderNum = "1")
    private String id;

    @Excel(name = "姓名", orderNum = "2")
    private String name;

    @Excel(name = "年龄", orderNum = "4")
    private Integer age;

    @Excel(name = "生日", orderNum = "3", width = 40.0)
    private Date birthday;

    @Excel(name = "状态", orderNum = "5", width = 20.0, replace = {"激活_1", "锁定_0"})
    private String status;

}

// 赋值

public List<UserDTO> getUser() {
    List<UserDTO> users = new ArrayList<>();
    for (int i = 0; i < 5; i++) {
        UserDTO user = new UserDTO();
        user.setId(String.valueOf(i));
        user.setName("coder-itl_" + i);
        user.setAge(10 + i);
        user.setBirthday(new Date());
        if (i % 2 == 0) {
            user.setStatus("激活");
        } else {
            user.setStatus("锁定");
        }
        users.add(user);
    }

    return users;
}

// 导出 excel
@Test
public void textExport() throws Exception {
    List<UserDTO> userDTOS = getUser();
    /**
         * 导出 excel
         * new ExportParams() 导出配置对象
         * UserDTO.class 导出的类型
         * userDTOS 导出数据集合
         */
    Workbook workbook = ExcelExportUtil.exportExcel(
        new ExportParams("用户信息列表", "用户信息"),
        UserDTO.class, userDTOS);
    // 将 excel 写入指定位置
    FileOutputStream outputStream = new FileOutputStream("F:\\bb.xls");
    workbook.write(outputStream);
    outputStream.close();
    workbook.close();
}

导入的数据模型

导入

package com.coderitl.exsel.entity;

@Data
@ExcelTarget("UserInfo")
public class UserInfo implements Serializable {
    @Excel(name = "编号")
    private Integer userId;
    @Excel(name = "姓名")
    private String userName;
    @Excel(name = "年龄")
    private Integer age;
    @Excel(name = "生日")
    private Date bir;
    @Excel(name = "状态", replace = {"激活_1", "未激活_0"})
    private String status;
}

// 导入
@Test
public void testImport() throws Exception {
    ImportParams params = new ImportParams();
    // 标题列占几行
    params.setTitleRows(1);
    // header列占几行
    params.setHeadRows(1);
    // 导入小技巧 params.setStartSheetIndex(2)
    List<UserInfo> userInfos = ExcelImportUtil.importExcel(new FileInputStream("F://bb.xls"), UserInfo.class, params);
    userInfos.forEach(System.out::println);
}

读取效果

注意

`title、header`

导入小技巧

读取指定sheet

比如要读取上传得第二个sheet，那么就需要把startSheetIndex=1
读取几个sheet

比如读取前两个,那么sheetNum=2
读取第二个到第五个sheet

设置startSheetIndex=1,然后 sheetNum=4
读取全部的sheet

sheetNum 设置大点就可以了

判断一个Excel 是不是合法的Excel

importFields 设置下值，就是表示表头必须至少包含字段，如果缺一个就是不合法的excel，不导入

数组类型

整合 SpringBoot

添加依赖

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </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>

    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>2.2.2</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
    </dependency>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.2.11</version>
    </dependency>

    <!-- easypoi -->
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-base</artifactId>
        <version>4.1.2</version>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-web</artifactId>
        <version>4.1.2</version>
    </dependency>
    <dependency>
        <groupId>cn.afterturn</groupId>
        <artifactId>easypoi-annotation</artifactId>
        <version>4.1.2</version>
    </dependency>
    <!-- thymeleaf -->
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-thymeleaf</artifactId>
    </dependency>
</dependencies>

excel 数据

数据

配置

server:
  port: 8080

spring:
  application:
    name: easypoi
  thymeleaf:
    cache: false
  datasource:
    druid:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://localhost:3306/easypoi?useUnicode=true&serverTimezone=GMT&characterEncoding=UTF-8&useSSL=false
      username: root
      password: root
      initial-size: 1
      min-idle: 1
      max-active: 20
  # 静态资源
  web:
    resources:
      static-locations: classpath:/static/,file:${upload.dir}

upload:
  dir:  E:\code\easy-poi\src\main\resources\static\images


mybatis:
  mapper-locations: classpath:com/example/easypoi/mapper/*.xml
  type-aliases-package: com.example.easypoi.entity

logging:
  level:
    com.example.easypoi: debug

基础模板

<!DOCTYPE html>
<html lang="en" xmlns:th="http://www.thymeleaf.org">
<head>
    <meta charset="UTF-8">
    <title>EasyPOI</title>
    <style>
        .container {
            width: 600px;
            margin: 50px auto;
        }

        table {
            text-align: center;
            /* 边框合并 */
            border-collapse: collapse;
        }

        /* 添加边框 */
        th,
        td {
            border: 1px solid #ccc;
            padding: 8px 16px;
        }

        caption {
            padding: 15px;
        }
    </style>
</head>
<body>
<div class="container">
    <form th:action="@{/user/import}" method="post" enctype="multipart/form-data">
        <input type="file" name="excelFile">
        <input type="submit" value="导入数据">
    </form>
    <div class="table-container">

        <table>
            <!-- 标题 -->
            <caption>用户信息列表</caption>

            <tr>
                <th>编号</th>
                <th>姓名</th>
                <th>年龄</th>
                <th>生日</th>
                <th>状态</th>
                <th>图像</th>
            </tr>
            <tr th:each="user:${users}">
                <td th:text="${user.id}"></td>
                <td th:text="${user.name}"></td>
                <td th:text="${user.age}"></td>
                <td th:text="${#dates.format(user.birthday,'yyyy-MM-dd HH:mm:ss')}"></td>
                <td th:text="${user.status}"></td>
                <td>
                    <img th:src="${'/'+user.logo}" alt="">
                </td>
            </tr>
        </table>
    </div>
    <hr>
    <a th:href="@{/user/export}">导出excel</a>
</div>
</body>
</html>

模板渲染

根据excel 建立表信息

create table t_user
(
    id       int(4) not null primary key comment '编号',
    name     varchar(80) comment '姓名',
    birthday timestamp comment '生日',
    age      int comment '年龄',
    status   varchar(10) comment '状态',
    logo     varchar(120) comment '图像'
);

实体类

package com.example.easypoi.entity;

@Data
@ExcelTarget("User")
public class User implements Serializable {
    @Excel(name = "编号")
    private Integer id;
    @Excel(name = "姓名")
    private String name;
    // 时间格式化需要与 Excel 时间格式一致
    @Excel(name = "生日", format = "yyyy-MM-dd HH:mm:ss")
    private Date birthday;
    @Excel(name = "年龄")
    private Integer age;
    @Excel(name = "状态")
    private String status;
    // 图片的存储路径使用绝对路径(推荐)
    @Excel(name = "图片",type = 2,savePath = "E:\\code\\easy-poi\\src\\main\\resources\\static\\images")
    private String logo;
}

业务层

package com.example.easypoi.service.impl;

import com.example.easypoi.mapper.UserDao;
import com.example.easypoi.entity.User;
import com.example.easypoi.service.UserService;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.util.List;

@Service
@Transactional
public class UserServiceImpl implements UserService {
    @Resource
    private UserDao userDao;

    @Override
    @Transactional(propagation = Propagation.SUPPORTS)
    public List<User> findAll() {
        List<User> users = userDao.findAll();
        if (users != null) {
            return users;
        }
        return null;
    }

    @Override
    public void saveAll(List<User> users) {
        users.forEach(user -> {
            // 只保存图片文件名
            String fileName = user.getLogo().substring(user.getLogo().lastIndexOf("\\") + 1);
            user.setLogo(fileName);
            userDao.saveExcel(user);
        });
    }
}

持久层

<?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.example.easypoi.mapper.UserDao">

    <!-- List<User> findAll(); -->
    <select id="findAll" resultType="user">
        select *
        from t_user
    </select>

    <!--    void saveExcel(User user);-->
    <insert id="saveExcel" parameterType="user">
        insert into t_user
        values (#{id},
                #{name},
                #{birthday},
                #{age},
                #{status},
                #{logo});
    </insert>
</mapper>

控制器

package com.example.easypoi.controller;

@Slf4j
@Controller
@RequestMapping("/user")
public class UserController {
    @Resource
    private UserService userService;

    @Value("${upload.dir}")
    private String realPath;

    // 查询所有
    @RequestMapping("/findAll")
    public String findAll(Model model) {
        List<User> users = userService.findAll();
        model.addAttribute("users", users);

        return "easypoi";
    }

    // 导入 excel 文件
    @RequestMapping("/import")
    public String importExcel(MultipartFile excelFile) throws Exception {
        log.info("文件名: [{}]", excelFile.getOriginalFilename());
        // excel 导入
        ImportParams params = new ImportParams();
        params.setTitleRows(1);
        params.setHeadRows(1);
        List<User> users = ExcelImportUtil.importExcel(excelFile.getInputStream(), User.class, params);
        // 存储到数据库
        userService.saveAll(users);
        // 上传完成后 跳转到查询所有信息的路径
        return "redirect:/user/findAll";
    }

    @RequestMapping("/export")
    public void exportExcel(HttpServletResponse response) throws Exception {
        // 查询所有数据
        List<User> users = userService.findAll();
        users.forEach(user -> {
            user.setLogo(realPath + "/" + user.getLogo());
        });
        log.info("导出 Excel,当前数据库数据总数为: [{}]", users.size());
        // 生成 excel
        Workbook workbook = ExcelExportUtil.exportExcel(new ExportParams("用户列表信息", "用户信息"), User.class, users);
        // 设置响应头
        response.setHeader("content-disposition", "attachment;fileName=" + URLEncoder.encode("用户列表.xls", "UTF-8"));
        ServletOutputStream outputStream = response.getOutputStream();
        workbook.write(outputStream);
        outputStream.close();
        workbook.close();

    }
}

// 访问 thymleaf
@Controller
public class EasyPOIController {
    @RequestMapping("/easypoi")
    public String easypoi() {
        return "easypoi";
    }
}

EasyExcel

创建项目

添加依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>3.1.1</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.24</version>
</dependency>
<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>fastjson</artifactId>
    <version>1.2.29</version>
</dependency>

实体类

@Data
@EqualsAndHashCode
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}

xlsx 实现写(推荐)

package org.example;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.util.ListUtils;
import org.example.entity.DemoData;

import java.util.Date;
import java.util.List;

/**
 * Hello world!
 */
public class App {
    public static void main(String[] args) {
        // 通过后缀区分版本
        String fileName = "./CODERITL.xlsx";
        /**
         * fileName: 需要生成的文件名 xlsx
         * DemoData.class: 实体类
         */
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());
        
    }
    // 注意泛型(实体类)
    private static List<DemoData> data() {
        List<DemoData> list = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            DemoData data = new DemoData();
            data.setString("字符串" + i);
            data.setDate(new Date());
            data.setDoubleData(0.56);
            list.add(data);
        }
        return list;
    }
}

简单写入

xls 写操作

1 2	// 在写的时候需要指定类型 EasyExcel.write(fileName, DemoData.class).excelType(ExcelTypeEnum.XLS).sheet("模板").doWrite(data());

读操作

实现监听器

package org.example.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import lombok.extern.slf4j.Slf4j;
import org.example.entity.DemoData;

/**
 * 不能交给 spring 管理
 * log.info 未创建配置文件时,非 springboot 项目无法输出
 */
@Slf4j
public class DemoDataListerner extends AnalysisEventListener<DemoData> {
    /**
     * 这个每一条数据解析都会来调用
     *
     * @param demoData
     * @param analysisContext
     */
    @Override
    public void invoke(DemoData demoData, AnalysisContext analysisContext) {
        log.info("数据: {}", demoData);
        System.out.println(demoData.toString());
    }
    
    /**
     * 所有数据解析完成了,都会来调用
     *
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        // 最后执行,进行收尾处理
        log.info("收尾处理......................");
        System.out.println("收尾处理......................");
    }
}

测试

@Test
public void testExcelRead() {
    String fileName = "C://Users//coderitl//Desktop//easy-excel//CODERITL.xlsx";
    EasyExcel.read(fileName, DemoData.class, new DemoDataListerner()).sheet().doRead();
}

输出

文件上传-Excel

创建项目(springboot)

配置

server:
  port: 8080

# druid 数据源配置
spring:
  datasource:
    druid:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: jdbc:mysql://localhost:3306/security_authorize?autoReconnect=true&useSSL=false&characterEncoding=utf-8
      username: root
      password: root
      initial-size: 1
      min-idle: 1
      max-active: 20
  # 全局日期输出格式化
  jackson:
    date-format: yyyy-MM-dd HH:mm:ss
    time-zone: GMT+8

mybatis:
  type-aliases-package: com.example.file.upload.excel.entity
  mapper-locations: com/example/file/upload/excel/mapper/*.Mapper
  configuration:
    map-underscore-to-camel-case: true

logging:
  level:
    com.example.file.upload.excel.mapper: debug

添加依赖

<dependencies>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>easyexcel</artifactId>
        <version>3.1.1</version>
    </dependency>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </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>
    <dependency>
        <groupId>com.alibaba</groupId>
        <artifactId>druid-spring-boot-starter</artifactId>
        <version>1.2.15</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.30</version>
    </dependency>
    <dependency>
        <groupId>org.mybatis.spring.boot</groupId>
        <artifactId>mybatis-spring-boot-starter</artifactId>
        <version>3.0.0</version>
    </dependency>
</dependencies>

创建监听器

注意点

注意实现的方法(x)

应该被实现的方法


@Slf4j
public class UserInfoDTOListener extends AnalysisEventListener<UserInfoDTO> {
    
    // TODO: 应该被实现的方法
    @Override
    public void invoke(UserInfoDTO userInfoDTO, AnalysisContext analysisContext) {
    
    }
    // TODO: 应该被实现的方法
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
    
    }
}

正确实现

创建数据库表

创建数据库

1	create database `easy-excel`;

创建表

CREATE TABLE `userinfo`
(
    `uid`      int         NOT NULL,
    `username` varchar(30) NOT NULL,
    `age`      int      DEFAULT NULL,
    `birthday` datetime DEFAULT now(),
    PRIMARY KEY (`uid`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4
  COLLATE = utf8mb4_unicode_ci

创建实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
public class UserInfoDTO {
    @ExcelProperty("用户ID")
    private Integer uid;
    @ExcelProperty("用户名")
    private String username;
    @ExcelProperty("年龄")
    private Integer age;
    @DateTimeFormat(fallbackPatterns = "yyyy-MM-dd HH:mm:ss")
    @ExcelProperty("生日")
    private Date birthday;
}

读接口定义

// 数据导入(通过前端页面读取)
package com.example.file.upload.excel;

import java.io.InputStream;

public interface UserInfoService {
    void importExcelData(InputStream inputStream);
}

package com.example.file.upload.excel.impl;

import com.alibaba.excel.EasyExcel;
import com.example.file.upload.excel.UserInfoService;
import com.example.file.upload.excel.entity.dto.UserInfoDTO;
import com.example.file.upload.excel.listener.UserInfoDTOListener;
import lombok.extern.slf4j.Slf4j;
import org.springframework.transaction.annotation.Transactional;

import java.io.InputStream;

@Slf4j
public class UserInfoServiceImpl implements UserInfoService {
    // 出现所有错误进行回滚
    @Transactional(rollbackFor = Exception.class)
    @Override
    public void importExcelData(InputStream inputStream) {
        log.info("read excel data............");
        // 通过流读入
        EasyExcel.read(inputStream, UserInfoDTO.class, new UserInfoDTOListener()).sheet().doRead();
    }
}

读控制器定义


@Slf4j
@CrossOrigin
@RestController
@RequestMapping("/file/excel")
public class ReadExcelDataController {
    @Autowired
    private UserInfoService userInfoService;
    
    /**
     * 文件批量导入
     *
     * @param file 前端上传组件中的 name 属性
     * @return
     */
    @PostMapping("/read")
    public void batchImport(@RequestParam("file") MultipartFile file) {
        try {
            userInfoService.importExcelData(file.getInputStream());
            log.info("数据批量导入成功.......................");
        } catch (IOException e) {
            // TODO: 抛出自定义异常
            log.error("TODO: 请更改为抛出自定义异常");
        }
    }
}

测试

创建测试数据


@SpringBootTest
class FileUploadExcelApplicationTests {
    // 注意泛型(实体类)
    private static List<UserInfoDTO> data() {
        List<UserInfoDTO> list = ListUtils.newArrayList();
        for (int i = 0; i < 10; i++) {
            UserInfoDTO data = new UserInfoDTO();
            data.setUid(i + 1);
            data.setUsername("abc" + i);
            data.setAge(i + 1);
            data.setBirthday(new Date());
            list.add(data);
        }
        return list;
    }
    
    @Test
    void contextLoads() {
        EasyExcel.write("./userinfo.xlsx", UserInfoDTO.class).sheet("模板").doWrite(data());
    }
}

测试

接口测试	读取效果

批量添加

实现批量添加	数据库查看

实现细节

mapper

@Mapper
public interface UserInfoMapper {
    // 批量添加
    void saveBatche(List<UserInfoDTO> list);
}

<?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.example.file.upload.excel.mapper.UserInfoMapper">
    <!-- void saveBatche(List<UserInfoDTO> list); -->
    <insert id="saveBatche">
        insert into userinfo values
        <foreach collection="list" item="item" index="index" separator=",">
            (
            #{item.uid},
            #{item.username},
            #{item.age},
            #{item.birthday}
            )
        </foreach>
    </insert>
</mapper>

service


@Slf4j
@Service
public class UserInfoServiceImpl implements UserInfoService {
    @Autowired
    private UserInfoMapper userInfoMapper;
    
    @Transactional(rollbackFor = Exception.class)
    @Override
    public void importExcelData(InputStream inputStream) {
        log.info("read excel data............");
        // 通过流读入
        EasyExcel.read(inputStream, UserInfoDTO.class, new UserInfoDTOListener(userInfoMapper)).sheet().doRead();
    }
}

监听器

// 通过监听器的有参构造注入 userInfoMapper
package com.example.file.upload.excel.listener;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.example.file.upload.excel.entity.dto.UserInfoDTO;
import com.example.file.upload.excel.mapper.UserInfoMapper;
import lombok.NoArgsConstructor;
import lombok.extern.slf4j.Slf4j;

import java.util.ArrayList;
import java.util.List;

@Slf4j
@NoArgsConstructor
public class UserInfoDTOListener extends AnalysisEventListener<UserInfoDTO> {
    // 临界值: 每隔 3 条存储一次
    private final int BATCH_COUNT = 3;
    List<UserInfoDTO> list = new ArrayList<>();
    private UserInfoMapper userInfoMapper;
    
    // 构造注入
    public UserInfoDTOListener(UserInfoMapper userInfoMapper) {
        this.userInfoMapper = userInfoMapper;
    }
    
    @Override
    public void invoke(UserInfoDTO data, AnalysisContext analysisContext) {
        log.info("userInfoDTO: {}", data);
        list.add(data);
        // 优化: 进行批量保存
        if (list.size() >= BATCH_COUNT) {
            saveData(list);
            list.clear();
        }
    }
    
    /**
     * 批量保存
     *
     * @param list
     */
    private void saveData(List<UserInfoDTO> list) {
        log.info("{} 条数据存储到数据库!", list.size());
        // 调用 mapper 层的批量保存方法
        userInfoMapper.saveBatche(list);
    }
    
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("收尾处理..............");
        // 将存储临界值不到的数据再次存储
        saveData(list);
    }
}