EasyExcel 4.X 读写数据

EasyExcel是Ailibaba团队提供的一个基于Java的、快速、简洁、解决大文件内存溢出的Excel处理工具。
在不用考虑性能、内存等因素下，快速完成Excel的读、写等功能。

官方地址：https://easyexcel.opensource.alibaba.com/

EasyExcel与SpringBoot集成

创建SpringBoot项目后引入EasyExcel依赖即可，其他依赖可按须引入。思考：这玩意为什么不是一个stater呢？

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

读数据

读取数据就是将excel中的数据读到程序中，可以进行逻辑处理之后将数据根据业务需求存储到数据库、写到另一个文件中或者其他的处理均可。数据读取依据监听器实现，可通过匿名内部类的形式，也可以新建类创建监听器。问：两者的最佳应用场景是什么？

读取数据的流程

• 需要有一个excel，判断excel中的数据格式和excel的sheet页
• 创建对应的实体类，对应类型的字段，存储读取的数据
• 选用不同的读取方式即可

定义实体类

@Data
public class StockEntity {

    // 数据库Id
    private Long id;
    // 商品序号
    @ExcelProperty(value = "序号")
    private Integer productNo;
    // 商品编码
    @ExcelProperty(value = "商品编码")
    private String productCode;
    // 商品类型
    @ExcelProperty(value = "类型")
    private String productType;
    // 商品品牌
    @ExcelProperty(value = "品牌")
    private String productBrand;
    // 实物库存
    @ExcelProperty(value = "实物库存")
    private String productStock;
    // 成本 浮点类型
    @ExcelProperty(value = "成本")
    private BigDecimal costPrice;

}

简单读取

public void readSimpleExcel(MultipartFile file) {
    try {
        InputStream inputStream = file.getInputStream();
        EasyExcel.read(inputStream, StockEntity.class,new PageReadListener<StockEntity>(dataList -> {
            for (StockEntity stockEntity : dataList) {
                log.info("读取到一条数据:{}", stockEntity);
            }
        })).sheet().doRead();
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}

自定义监听器

新建类实现ReadListener接口，泛型指定要将数据存到到哪个实体中

package com.stt.listener.read;

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.stt.entity.StockEntity;
import lombok.extern.slf4j.Slf4j;

/**
 * @author Jshuai
 * @description
 * @date 2024-10-29 22:14
 * 自定义简单数据读取的监听器
 */
@Slf4j
public class SimpleReadListener implements ReadListener<StockEntity> {


    /**
     * 每读取一条数据，就会调用invoke方法
     * @param stockEntity
     * @param analysisContext
     */
    @Override
    public void invoke(StockEntity stockEntity, AnalysisContext analysisContext) {
        log.info("读取到一条数据：{}", stockEntity);
    }

    /**
     * 读取完成之后，会调用doAfterAllAnalysed方法，做一些数据清理的操作
     * @param analysisContext
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("读取完成！");
    }
}

读取指定sheet和所有sheet

public void readMultiSheetExcel(MultipartFile file) {
    // 读取所有的
    // EasyExcel.read(file.getInputStream(), StockEntity.class,new SimpleReadListener()).doReadAll();

    // 读取指定的sheet页
    try (ExcelReader excelReader = EasyExcel.read(file.getInputStream()).build()) {
        // 这里为了简单 所以注册了 同样的head 和Listener 自己使用功能必须不同的Listener
        ReadSheet readSheet1 =
                EasyExcel.readSheet(0).head(StockEntity.class).registerReadListener(new SimpleReadListener()).build();
        ReadSheet readSheet2 =
                EasyExcel.readSheet(1).head(StockEntity.class).registerReadListener(new SimpleReadListener()).build();
        // 这里注意 一定要把sheet1 sheet2 一起传进去，不然有个问题就是03版的excel 会读取多次，浪费性能
        excelReader.read(readSheet1, readSheet2);
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}

多行头读取

public void readMultiHeadExcel(MultipartFile file) {
    try {
        EasyExcel.read(file.getInputStream(), StockEntity.class, new SimpleReadListener()).sheet(0)
                // 这里可以设置1，因为头就是一行。如果多行头，可以设置其他值。不传入也可以，因为默认会根据DemoData 来解析，他没有指定头，也就是默认1行
                .headRowNumber(2).doRead();
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}

数据格式转换

部分数据读写场景涉及到数据格式问题，需要转换，比如性别，数据库中存储的一般是数字类型【0表示男，1表示女】，时间在不同系统中也有固定的格式，EasyExcel实现类型转换有两种方式：

• 通过@DateTimeFormat和@NumberFormat对日期时间和数字格式转换
• 通过实现Converter接口实现读写数据的格式转换

以员工数据为例：

列表数据

姓名	性别	出生日期
加油鸭	男	2024/10/10
酱香鸭	女	2024/10/10
果木鸭	保密	2024/10/10

实体类

其中birthday通过easyexcel提供的注解设置格式，性别通过自定义的GenderConverter实现

public class MyUser {
    @ExcelProperty(value = "姓名",index = 0)
    private String name;
    @ExcelProperty(value = "性别",index = 1,converter = GenderConverter.class)
    private Integer gender;
    @ExcelProperty(value = "出生日期",index = 2)
    @DateTimeFormat("yyyy-MM-dd")
    private LocalDate birthday;
}

自定义转换器

@Slf4j
public class GenderConverter implements Converter<Integer> {

    /**
     * 这里读的时候会调用
     */
    @Override
    public Integer convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        // 获取读取的字符串
        String gender = cellData.getStringValue();
        log.info("gender===》{}",gender);
        // 转换成对应的数字
        return GenderEnum.getCodeByLabel(gender);
    }
}

自定义监听器

不同的数据实体需要不同的监听器，此处定义一个读取员工数据的监听器，泛型修改，并且提供list来存储读取到的数据，外部可以通过get方法获取集合数据

@Slf4j
public class UserReadListener implements ReadListener<MyUser> {

    @Getter
    private List<MyUser> data;

    public UserReadListener() {
        this.data = new ArrayList<>();
    }

    @Override
    public void invoke(MyUser myUser, AnalysisContext analysisContext) {
        data.add(myUser);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {
        log.info("所有数据解析完成！");
    }

}

数据读取

public void readByConverterData(MultipartFile file) {
    try {
        // 创建监听器
        UserReadListener readListener = new UserReadListener();
        
        EasyExcel.read(file.getInputStream(), MyUser.class, readListener).sheet(2).doRead();
        // 获取读取到的数据
        List<MyUser> data = readListener.getData();
        data.forEach(System.out::println);
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}

写数据

写数据就是将系统内的数据写到excel中，可以将文件写到磁盘或者提供给前端下载，本案例基于MySQL +Mybatis实现从数据库中读取数据写出到excel中

简单数据写出

存储到磁盘

public void demo1(HttpServletResponse response) {
    List<TbExcel> tbExcels = excelMapper.selectList(null);
    List<ExcelVO> excelVOS = BeanUtil.copyToList(tbExcels, ExcelVO.class);

    EasyExcel.write("C:\\demo1.xlsx", ExcelVO.class)
            .sheet("sheet1")
            .doWrite(excelVOS);
}

返回前端下载

public void demo1(HttpServletResponse response) {
    // 查询数据库数据，查询所有的
    List<TbExcel> tbExcels = excelMapper.selectList(null);
    // 将TbExcel转换为ExcelVO
    List<ExcelVO> excelVOS = BeanUtil.copyToList(tbExcels, ExcelVO.class);
    // 提供给前端下载，需要使用到HttpServletResponse对象
    // 这里注意 有同学反应使用swagger 会导致各种问题，请直接用浏览器或者用postman
    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    // 强制调起下载
//        response.setContentType("application/octet-stream;charset=UTF-8");
    response.setCharacterEncoding("utf-8");
    try {
        String fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), ExcelVO.class).sheet("模板").doWrite(excelVOS);
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}

写出指定列宽，和数值精度丢失问题

设置列宽

通过@ColumnWidth注解设置

public class ExcelVO {
    @ExcelProperty(value = "ID", index = 0)
    private Long id;
    @ExcelProperty(value = "字符串", index = 1)
    private String strCol;
    @ExcelProperty(value = "浮点数字", index = 2)
    @ColumnWidth(12)
    private BigDecimal decCol;
    @ExcelProperty(value = "日期时间", index = 3)
    @ColumnWidth(18)
    private LocalDateTime datetimeCol;
}

精度丢失问题

通过设置转换器实现，将Long转为String导出

EasyExcel.write(response.getOutputStream(), ExcelVO.class)
                    .registerConverter(new LongStringConverter())
                    .sheet("模板").doWrite(excelVOS);

同一sheet写多次

public void demo2(HttpServletResponse response) {
    List<TbExcel> tbExcels = excelMapper.selectList(null);
    List<ExcelVO> excelVOS = BeanUtil.copyToList(tbExcels, ExcelVO.class);
    try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), ExcelVO.class).build()) {
        // 这里注意 有伙伴反应使用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");
        // 这里注意 如果同一个sheet只要创建一次
        WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
        // 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来
        for (int i = 0; i < 5; i++) {
            excelWriter.write(excelVOS, writeSheet);
        }
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}

写出多个sheet

根据不同的维度将数据分别存到不同的sheet表中，比如根据日期分类

public void demo3(HttpServletResponse response) {
    List<TbExcel> tbExcels = excelMapper.selectList(null);
    List<ExcelVO> excelVOS = BeanUtil.copyToList(tbExcels, ExcelVO.class);

    try (ExcelWriter excelWriter = EasyExcel.write(response.getOutputStream(), ExcelVO.class).build()) {
        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");
        // 去调用写入,这里我调用了五次，实际使用时根据数据库分页的总的页数来。这里最终会写到5个sheet里面
        Map<LocalDateTime, List<ExcelVO>> collect = excelVOS.stream().collect(Collectors.groupingBy(ExcelVO::getDatetimeCol));
        int index = 0;
        for (LocalDateTime localDateTime : collect.keySet()) {
            WriteSheet writeSheet = EasyExcel.writerSheet(index, localDateTime.format(DateTimeFormatter.ofPattern("yyyy-MM-dd"))).build();
            // 分页去数据库查询数据 这里可以去数据库查询每一页的数据
            excelWriter.write(collect.get(localDateTime), writeSheet);
            index++;
        }
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}

自定义样式

easyexcel导出的表格有默认样式，我们还可以自定义头，表格内容和字体样式

public void demo4(HttpServletResponse response) {
    // 头的策略
    WriteCellStyle headWriteCellStyle = new WriteCellStyle();
    // 背景设置为红色
    headWriteCellStyle.setFillForegroundColor(IndexedColors.RED.getIndex());
    WriteFont headWriteFont = new WriteFont();
    headWriteFont.setFontHeightInPoints((short)20);
    headWriteCellStyle.setWriteFont(headWriteFont);
    // 内容的策略
    WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
    // 这里需要指定 FillPatternType 为FillPatternType.SOLID_FOREGROUND 不然无法显示背景颜色.头默认了 FillPatternType所以可以不指定
    contentWriteCellStyle.setFillPatternType(FillPatternType.SOLID_FOREGROUND);
    // 背景绿色
    contentWriteCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
    WriteFont contentWriteFont = new WriteFont();
    // 字体大小
    contentWriteFont.setFontHeightInPoints((short)20);
    contentWriteCellStyle.setWriteFont(contentWriteFont);
    // 这个策略是 头是头的样式 内容是内容的样式 其他的策略可以自己实现
    HorizontalCellStyleStrategy horizontalCellStyleStrategy =
            new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);

    List<TbExcel> tbExcels = excelMapper.selectList(null);
    List<ExcelVO> excelVOS = BeanUtil.copyToList(tbExcels, ExcelVO.class);

    response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
    response.setCharacterEncoding("utf-8");
    String fileName = null;
    try {
        fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        EasyExcel.write(response.getOutputStream(), ExcelVO.class)
                .registerWriteHandler(horizontalCellStyleStrategy)
                .sheet("模板")
                .doWrite(excelVOS);
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}

合并单元格

通过注解方式合并

@Data
@AllArgsConstructor
@NoArgsConstructor
// 将第6-7行的2-3列合并成一个单元格
// @OnceAbsoluteMerge(firstRowIndex = 5, lastRowIndex = 6, firstColumnIndex = 1, lastColumnIndex = 2)
public class ExcelVO {
    // 每两行合并
    @ContentLoopMerge(eachRow = 2)
    @ExcelProperty(value = "ID", index = 0)
    private Long id;
    @ExcelProperty(value = "字符串", index = 1)
    private String strCol;
    @ExcelProperty(value = "浮点数字", index = 2)
    private BigDecimal decCol;
    @ExcelProperty(value = "日期时间", index = 3)
    private LocalDateTime datetimeCol;
}

自定义合并

public void demo5(HttpServletResponse response) {
        List<ExcelVO> excelVOS = new ArrayList<>();
        excelVOS.add(new ExcelVO(1L,"字符串1",BigDecimal.ONE, LocalDateTime.now()));
        excelVOS.add(new ExcelVO(1L,"字符串2",BigDecimal.ONE, LocalDateTime.now()));
        excelVOS.add(new ExcelVO(2L,"字符串3",BigDecimal.ONE, LocalDateTime.now()));
        excelVOS.add(new ExcelVO(2L,"字符串4",BigDecimal.ONE, LocalDateTime.now()));

        // 相加
        BigDecimal sum = excelVOS.stream().map(ExcelVO::getDecCol).reduce(BigDecimal.ZERO, BigDecimal::add);
        // 将合计追加到集合中
        excelVOS.add(new ExcelVO(null,"合计", sum , null));
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        String fileName = null;
        try {
            fileName = URLEncoder.encode("测试", "UTF-8").replaceAll("\\+", "%20");
            response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
            // 设置合并规则
            LoopMergeStrategy loopMergeStrategy = new LoopMergeStrategy(2, 0);
            EasyExcel.write(response.getOutputStream(), ExcelVO.class)
                    .sheet("模板")
                    .registerWriteHandler(loopMergeStrategy)
                    .doWrite(excelVOS);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

百万级别数据读写

多线程读取

• 思路：

  • 分析瓶颈在哪里？
  • 数据读取的性能：读取可以使用多线程【重点】
  • 数据库中插入数据，这个性能是比较不错的，可以使用批量插入【使用数据库连接池】

• 多线程读取数据的问题：

  • 需要避免重复读取，每个线程需要设置读取哪个区间的数据

public void importExcel(MultipartFile file) {

    try {
        // 多线程读取
        EasyExcel.read(file.getInputStream(),ExcelVO.class,new MutliReadListener(excelMapper)).sheet().doRead();
    } catch (IOException e) {
        throw new RuntimeException(e);
    }

}

多线程读取监听器

@Service
@Slf4j
@NoArgsConstructor
public class MutliReadListener extends AnalysisEventListener<ExcelVO> {

    private ExcelMapper excelMapper;

    public MutliReadListener(ExcelMapper excelMapper) {
        this.excelMapper = excelMapper;
    }

    /**
     * 使用线程安全集合
     */
    private List<ExcelVO> dataList = Collections.synchronizedList(new ArrayList<>());

    /**
     * 创建线程池必要参数
     */
    private static final int CORE_POOL_SIZE = 5;//核心线程数
    private static final int MAX_POOL_SIZE = 10;//最大线程数
    private static final int QUEUE_CAPACITY = 100;//队列大小
    private static final Long KEEP_ALIVE_TIME = 1L;//存活时间

    @Override
    public void invoke(ExcelVO data, AnalysisContext context) {
        if (dataList != null) {
            dataList.add(data);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {

        //创建线程池
        ExecutorService executor = new ThreadPoolExecutor(CORE_POOL_SIZE,
                MAX_POOL_SIZE,
                KEEP_ALIVE_TIME,
                TimeUnit.SECONDS,
                new ArrayBlockingQueue<>(QUEUE_CAPACITY),
                new ThreadPoolExecutor.CallerRunsPolicy());
        //指定每个线程需要处理的导入数量，假设每个线程处理1000条
        int singleThreadDealCount = 100000;
        //根据假设每个线程需要处理的数量以及总数，计算需要提交到线程池的线程数量
        int threadsize = (dataList.size() / singleThreadDealCount) + 1;
        log.info("threadsize===>{}",threadsize);
        //计算需要导入的数据总数，用于拆分时线程需要处理数据时使用
        int rowsize = dataList.size() + 1;
        //测试开始时间
        long startTime = System.currentTimeMillis();
        //申明该线程需要处理数据的开始位置
        int startPosition = 0;
        //申明该线程需要处理数据的结束位置
        int endPosition = 0;
        //为了让每个线程执行完后回到当前线程，使用CountDownLatch,值为线程数，每次线程执行完就会执行countDown方法减1，为0后回到主
        CountDownLatch count = new CountDownLatch(threadsize);
        //计算每个线程要处理的数据
        for (int i = 0; i < threadsize; i++) {
            //如果是最后一个线程，为保证程序不发生空指针异常，特殊判断结束位置
            if ((i + 1) == threadsize) {
                //计算开始位置
                startPosition = (i * singleThreadDealCount);
                //当前线程为划分的最后一个线程，则取总数据的最后为此线程的结束位置
                endPosition = rowsize - 1;
            } else {
                //计算开始位置
                startPosition = (i * singleThreadDealCount);
                //计算结束位置
                endPosition = (i + 1) * singleThreadDealCount;
            }
            log.info("线程开启====》count:{},startPosition:{},endPosition:{}",count,startPosition,endPosition);
            DeadMainThread deadMainThread = new DeadMainThread(count, excelMapper, dataList, startPosition, endPosition);
            executor.execute(deadMainThread);
        }
        try {
            count.await();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
        //逻辑处理完，关闭线程池
        executor.shutdown();
        long endTime = System.currentTimeMillis();
        System.out.println("总耗时：" + (endTime - startTime));
    }
}

线程任务

@Component
@Slf4j
public class DeadMainThread implements Runnable {

    /**
     * 当前线程需要处理的总数据中的开始位置
     */
    private int startPosition;
    /**
     * 当前线程需要处理的，总数据中的结束位置
     */
    private int endPosition;
    /**
     * 需要处理的拆分之前的全部数据
     */
    private List<ExcelVO> list = Collections.synchronizedList(new ArrayList<>());
    private CountDownLatch count;
    private ExcelMapper excelMapper;

    public DeadMainThread() {
    }

    public DeadMainThread(CountDownLatch count, ExcelMapper excelMapper, List<ExcelVO> list, int startPosition, int endPosition) {
        this.startPosition = startPosition;
        this.endPosition = endPosition;
        this.excelMapper = excelMapper;
        this.list = list;
        this.count = count;
    }

    @Override
    public void run() {
        try {
            List<ExcelVO> newList = list.subList(startPosition, endPosition);
            //批量新增
            excelMapper.insertBatch(BeanUtil.copyToList(newList, TbExcel.class));
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            //当一个线程执行完了计数要减一不然这个线程会被一直挂起
            count.countDown();
            log.info("减一===》{}",count.getCount());
        }
    }
}

多线程写出

造数据

public void init() {

    LocalDateTime now = LocalDateTime.now();
    // 通过计数器，信号量
    CountDownLatch countDownLatch = new CountDownLatch(100);

    for (int i = 0; i < 100; i++) {
        // 开启虚拟线程
        Thread.ofVirtual().start(() -> {
            List<TbExcel> list = new ArrayList<>();
            for (int j = 0; j < 10000; j++) {
                list.add(new TbExcel("字符串" + j, new BigDecimal(j), now));
            }
            excelMapper.insertBatch(list);
            // 计数器减一
            countDownLatch.countDown();
            log.info("线程{}执行完毕", Thread.currentThread().getName());
        });
    }

    // 是否结束
    try {
        countDownLatch.await();
        log.info("初始化完成");
    } catch (InterruptedException e) {
        throw new RuntimeException(e);
    }
}

实现分析

• 百万级别的数据考虑两个核心问题

  • 内存不能爆掉，不能产生OOM
  • 速度，性能

• 实现逻辑就是多线程，导出数据瓶颈在：

  • 数据查询，一次性查询多少数据
  • 内存不能溢出

至于数据写到excel中，这个就是easyexcel的表现了。

public void exportData(HttpServletResponse response) {
    long start1 = System.currentTimeMillis();
    // 每次查询10万条，考虑性能，内存
    Integer pageSize = 100000;
    // 线程池大小跟cpu有关，一般是cpu数量 * 2 + 1
    Integer poolSize = 10;
    // 随机文件名
    String fileName = String.valueOf(UUID.randomUUID());
    // 查询数据总数
    Long totalCount = excelMapper.selectCount(null);
    if (totalCount == 0) {
        log.info("没有数据需要导出");
        return; // 如果没有数据，直接返回
    }

    int loopCount = (int) Math.ceil((double) totalCount / pageSize);  // 使用 Math.ceil 计算循环次数
    // 设置
    final CountDownLatch latch = new CountDownLatch(loopCount);
    log.info("要查询的次数===>{}", loopCount);
    ExecutorService executorService = Executors.newFixedThreadPool(poolSize);
    OutputStream outputStream = null;
    try {
        outputStream = response.getOutputStream();
        // 创建写对象
        ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
        for (int i = 0; i < loopCount; i++) {
            final int pageNum = i + 1; // 改为从1开始，直接使用 i + 1 作为页码
            executorService.execute(() -> {
                long start = System.currentTimeMillis();
                // 查询数据
                IPage<TbExcel> data = excelMapper.selectPage(new Page<>(pageNum, pageSize), null);
                List<TbExcel> records = data.getRecords();

                log.info("第{}页,查询耗时===>{}", pageNum,System.currentTimeMillis() - start);

                WriteSheet writeSheet = EasyExcel.writerSheet(pageNum ,"第" + pageNum + "页")
                        .head(ExcelVO.class)
                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                        .build();
                synchronized (excelWriter) {
                    long start2 = System.currentTimeMillis();
                    excelWriter.write(BeanUtil.copyToList(records, ExcelVO.class), writeSheet);
                    log.info("数据写出耗时===》{}",System.currentTimeMillis() - start2);
                }

                latch.countDown();
            });
        }
        latch.await();
        response.setContentType("application/octet-stream");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        excelWriter.finish();
        outputStream.flush();
        executorService.shutdown(); // 关闭线程池
        outputStream.close();
        log.info("总耗时====》{}",System.currentTimeMillis() - start1);
    } catch (IOException | InterruptedException e) {
        throw new RuntimeException(e);
    }

}

/**
 * 虚拟线程
 * @param response
 */
@Override
public void exportData2(HttpServletResponse response) {
    long start1 = System.currentTimeMillis();
    Integer pageSize = 100000;
    Integer poolSize = 10;
    String fileName = String.valueOf(UUID.randomUUID());
    // 查询数据总数
    Long totalCount = excelMapper.selectCount(null);
    if (totalCount == 0) {
        log.info("没有数据需要导出");
        return; // 如果没有数据，直接返回
    }

    int loopCount = (int) Math.ceil((double) totalCount / pageSize);  // 使用 Math.ceil 计算循环次数
    // 设置
    final CountDownLatch latch = new CountDownLatch(loopCount);
    log.info("要查询的次数===>{}", loopCount);
    ExecutorService executorService = Executors.newFixedThreadPool(poolSize);
    OutputStream outputStream = null;
    try {
        outputStream = response.getOutputStream();
        // 创建写对象
        ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
        for (int i = 0; i < loopCount; i++) {
            final int pageNum = i + 1; // 改为从1开始，直接使用 i + 1 作为页码
            Thread.ofVirtual().start(() -> {
                long start = System.currentTimeMillis();
                // 查询数据
                IPage<TbExcel> data = excelMapper.selectPage(new Page<>(pageNum, pageSize), null);
                List<TbExcel> records = data.getRecords();
                log.info("第{}页,查询耗时===>{}", pageNum,System.currentTimeMillis() - start);

                WriteSheet writeSheet = EasyExcel.writerSheet(pageNum ,"第" + pageNum + "页")
                        .head(ExcelVO.class)
                        .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                        .build();
                synchronized (excelWriter) {
                    long start2 = System.currentTimeMillis();
                    excelWriter.write(BeanUtil.copyToList(records, ExcelVO.class), writeSheet);
                    log.info("数据写出耗时===》{}",System.currentTimeMillis() - start2);
                }

                latch.countDown();
            });
        }
        latch.await();
        response.setContentType("application/octet-stream");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        excelWriter.finish();
        outputStream.flush();
        executorService.shutdown(); // 关闭线程池
        outputStream.close();
        log.info("总耗时====》{}",System.currentTimeMillis() - start1);
    } catch (IOException | InterruptedException e) {
        throw new RuntimeException(e);
    }

}

/**
 * 单sheet页超过100万条数据就写不进去了
 * @param response
 */
@Override
public void exportData3(HttpServletResponse response) {
    long start1 = System.currentTimeMillis();
    Integer pageSize = 100000;
    Integer poolSize = 10;
    String fileName = String.valueOf(UUID.randomUUID());
    // 查询数据总数
    Long totalCount = excelMapper.selectCount(null);
    if (totalCount == 0) {
        log.info("没有数据需要导出");
        return; // 如果没有数据，直接返回
    }

    int loopCount = (int) Math.ceil((double) totalCount / pageSize);  // 使用 Math.ceil 计算循环次数
    // 设置
    final CountDownLatch latch = new CountDownLatch(loopCount);
    log.info("要查询的次数===>{}", loopCount);
    ExecutorService executorService = Executors.newFixedThreadPool(poolSize);
    OutputStream outputStream = null;
    try {
        outputStream = response.getOutputStream();
        // 创建写对象
        ExcelWriter excelWriter = EasyExcel.write(outputStream).build();
        WriteSheet writeSheet = EasyExcel.writerSheet(1 ,"第" + 1 + "页")
                .head(ExcelVO.class)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .registerConverter(new LongStringConverter())
                .build();
        for (int i = 0; i < loopCount; i++) {
            final int pageNum = i + 1; // 改为从1开始，直接使用 i + 1 作为页码
            Thread.ofVirtual().start(() -> {
                long start = System.currentTimeMillis();
                // 查询数据
                IPage<TbExcel> data = excelMapper.selectPage(new Page<>(pageNum, pageSize), null);
                List<TbExcel> records = data.getRecords();
                log.info("第{}页,查询耗时===>{}", pageNum,System.currentTimeMillis() - start);

                synchronized (excelWriter) {
                    long start2 = System.currentTimeMillis();
                    excelWriter.write(BeanUtil.copyToList(records, ExcelVO.class), writeSheet);
                    log.info("数据写出耗时===》{}",System.currentTimeMillis() - start2);
                }
                latch.countDown();
            });
        }
        latch.await();
        response.setContentType("application/octet-stream");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        excelWriter.finish();
        outputStream.flush();
        executorService.shutdown(); // 关闭线程池
        outputStream.close();
        log.info("总耗时====》{}",System.currentTimeMillis() - start1);
    } catch (IOException | InterruptedException e) {
        throw new RuntimeException(e);
    }

}

---