java 百万数据快速导入Excel，

pom文件依赖：

        <!-- poi 相关 -->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.0.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml-schemas</artifactId>
            <version>4.0.1</version>
        </dependency>


        <!-- mysql 驱动 -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.47</version>
        </dependency>
        <!--mybatis plus-->
        <dependency>
            <!--包米豆-->
            <groupId>com.baomidou</groupId>
            <artifactId>mybatis-plus-boot-starter</artifactId>
            <version>3.5.2</version>
        </dependency>
        <!-- mybatis -->
        <!--<dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.4.5</version>
        </dependency>-->
        <!-- 连接池 -->
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.4</version>
        </dependency>

实体类：

package com.bean.domain;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import java.io.Serializable;
import java.util.List;

import lombok.Data;

/**
 * 人员信息表Me
 * @TableName a_personnel_info
 */
@TableName(value ="a_personnel_info")
@Data
public class APersonnelInfo implements Serializable {
    /**
     * 
     */
    @TableId(type = IdType.AUTO)
    private Long id;

    /**
     * 姓名
     */
    private String name;

    /**
     * 性别
     */
    private String gender;

    /**
     * 年龄
     */
    private Integer age;

    /**
     * 地址
     */
    private String address;

    /**
     * 手机号
     */
    private String phone;

    /**
     * 唯一编码
     */
    private String uuidnum;

    @TableField(exist = false)
    private static final long serialVersionUID = 1L;

    public APersonnelInfo() {
    }

    public APersonnelInfo(Long id, String name, String gender, Integer age, String address, String phone, String uuidnum) {
        this.id = id;
        this.name = name;
        this.gender = gender;
        this.age = age;
        this.address = address;
        this.phone = phone;
        this.uuidnum = uuidnum;
    }
}

mapper:

package com.bean.mapper;

import com.bean.domain.APersonnelInfo;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;

import java.util.List;

/**
* @author y1851
* @description 针对表【a_personnel_info(人员信息表Me)】的数据库操作Mapper
* @createDate 2024-03-11 19:35:17
* @Entity com.bean.domain.APersonnelInfo
*/
@Mapper
public interface APersonnelInfoMapper extends BaseMapper<APersonnelInfo> {



   List<APersonnelInfo> selectAPersonnelInfoListThree();
}

mapper.xml:

<?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.bean.mapper.APersonnelInfoMapper">

    <resultMap id="BaseResultMap" type="com.bean.domain.APersonnelInfo">
            <id property="id" column="id" jdbcType="BIGINT"/>
            <result property="name" column="name" jdbcType="VARCHAR"/>
            <result property="gender" column="gender" jdbcType="VARCHAR"/>
            <result property="age" column="age" jdbcType="INTEGER"/>
            <result property="address" column="address" jdbcType="VARCHAR"/>
            <result property="phone" column="phone" jdbcType="VARCHAR"/>
            <result property="uuidnum" column="uuidNum" jdbcType="VARCHAR"/>
    </resultMap>

    <sql id="Base_Column_List">
        id,name,gender,
        age,address,phone,
        uuidNum
    </sql>


    <select id="selectAPersonnelInfoListThree" resultType="com.bean.domain.APersonnelInfo">
        SELECT  *  FROM  a_personnel_info
    </select>


</mapper>

application.yml:

server:
  port: 1003
---

spring:
  application:
    name: ExcelService-service

---
spring:
  redis:
    host: localhost
    port: 6379
  datasource:
    url: jdbc:mysql://localhost:3306/blockchain?serverTimezone=Asia/Shanghai&useTimezone=true
    driver-class-name: com.mysql.jdbc.Driver
    username: root
    password: root
mybatis-plus:
  configuration:
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

测试类数据：

package com.bean;


import com.bean.domain.APersonnelInfo;
import com.bean.mapper.APersonnelInfoMapper;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
import org.springframework.test.context.junit4.SpringRunner;


import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

/**
 * Unit test for simple App.
 */
@SpringBootTest(classes = AppExcelService.class)
@RunWith(SpringRunner.class)
public class AppTest {

    @Autowired
    APersonnelInfoMapper aPersonnelInfoMapper;

    @Test
    public  void  test1() throws IOException {
        // 创建工作簿对象
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        // 创建工作表对象
        SXSSFSheet sheet = workbook.createSheet("Sheet1");
        // 写入表头
        Row header = sheet.createRow(0);
        header.createCell(0).setCellValue("id");
        header.createCell(1).setCellValue("姓名");
        header.createCell(2).setCellValue("性别");
        header.createCell(3).setCellValue("年龄");
        header.createCell(4).setCellValue("地址");
        header.createCell(5).setCellValue("手机号");
        header.createCell(6).setCellValue("唯一编码");
        List<APersonnelInfo> aPersonnelInfos = aPersonnelInfoMapper.selectAPersonnelInfoListThree();
        for (int i = 0; i < aPersonnelInfos.size(); i++) {
            Row row = sheet.createRow(i + 1);
            row.createCell(0).setCellValue(aPersonnelInfos.get(i).getId());
            row.createCell(1).setCellValue(aPersonnelInfos.get(i).getName());
            row.createCell(2).setCellValue(aPersonnelInfos.get(i).getGender());
            row.createCell(3).setCellValue(aPersonnelInfos.get(i).getAge());
            row.createCell(4).setCellValue(aPersonnelInfos.get(i).getAddress());
            row.createCell(5).setCellValue(aPersonnelInfos.get(i).getPhone());
            row.createCell(6).setCellValue(aPersonnelInfos.get(i).getUuidnum());
        }


        // 写入到文件中
        FileOutputStream outputStream = new FileOutputStream("test.xlsx");
        workbook.write(outputStream);
        outputStream.close();
        // 关闭工作簿对象
        workbook.close();
    }
}

成果：