联系管理员

开通文章发布权限

扫码 添加微信
微信图片
电话：18888888888 QQ：88888
  1. 文章博客
  2. java后端

评论

收藏

点赞

分享

easyExcel(导入excel和导出excel)的使用

java后端 更新：2025-08-11 浏览：49 收藏：0 点赞：0
easyExcel(导入excel和导出excel)的使用

easyExcel的使用

点击进入easyExcel官网,查看官网如何使用easyExcel
!!注意: jdbc的url后面需要加上:useUnicode=true&characterEncoding=utf-8&useSSL=false&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8&rewriteBatchedStatements=true&useServerPrepStmts=true&allowMultiQueries=true&useCursorFetch=true&defaultFetchSize=100 方便于大数据导入

1.在pom.xml文件中引入依赖

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

2.读取excel文件

2.1.简单读取excel文件(导入excel)

@Data
public class StockEntity {
    private Long id;
    @ExcelProperty(value="序号")
    private Long productId;
    @ExcelProperty(value="商品编码")
    private String productCode;
    @ExcelProperty(value="类型")
    private String productType;
    @ExcelProperty(value="品牌")
    private String productBrand;
    @ExcelProperty(value="实物库存")
    private Long productNum;
    @ExcelProperty(value="成本")
    private BigDecimal costPrice;
}

2.1.1.方案一:分页读取,默认100

@Override
public void excelSimpleReader(MultipartFile file) {
    try {
        InputStream inputStream = file.getInputStream();
        //这里默认每次会读取100条数据,可在构造函数设置
        EasyExcel.read(inputStream, StockEntity.class,new PageReadListener<StockEntity>(dataList->{
                    for (StockEntity stockEntity : dataList) {
                        log.info("从excel读取到的信息:{}",stockEntity);
                    }
                }))
                .sheet()  //默认第一个sheet页
                .headRowNumber(2) // 跳过2行表头,看情况（无关数据几条)
                .doRead();
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}

2.1.2.方案二:通过自定义监听器(完成业务逻辑)读取excel

注意:监听器其他方法的实现参考官网

@Slf4j
public class SimpleReadListener implements ReadListener<StockEntity> {
    /**
     * 每隔5条存储数据库，实际使用中可以100条，然后清理list ，方便内存回收
     */
    private static final int BATCH_COUNT = 50;
    /**
     * 缓存的数据
     */
    private List<StockEntity> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    private StockEntity stockEntity;
    public SimpleReadListener() {
    }
    public SimpleReadListener(StockEntity stockEntity) {
        this.stockEntity = stockEntity;
    }
    public void saveData(){
        log.info("{}条数据，开始存储数据库！", cachedDataList.size());
        //此处用来将数据存储到数据库
        log.info("存储数据库成功！");
    }
    @Override
    public void invoke(StockEntity stockEntity, AnalysisContext analysisContext) {
        log.info("解析到一条数据:{}",stockEntity);
        cachedDataList.add(stockEntity);
        // 达到BATCH_COUNT了，需要去存储一次数据库，防止数据几万条数据在内存，容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据，确保最后遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析完成！");
    }
}

@Override
public void excelCustomListenerRead(MultipartFile file){
    try {
        InputStream inputStream = file.getInputStream();
        EasyExcel.read(inputStream,StockEntity.class,new SimpleReadListener())
                .sheet()   //默认第一个sheet页
                .headRowNumber(2) // 跳过2行表头,看情况（无关数据几条)
                .doRead();
    } catch (IOException e) {
        throw new RuntimeException(e);
    }

}

2.2.读取多个sheet页(全部和多个)

注意:上面的都是第一个sheet页

2.2.1.读取所有的sheet页

只需要将.sheet().doRead()换成.doReadAll()即可

@Override
public void excelAllSheetRead(MultipartFile file){
    try {
        InputStream inputStream = file.getInputStream();
        EasyExcel.read(inputStream, StockEntity.class,new SimpleReadListener())
                .headRowNumber(1)    //此处用1，我是把测试文件修改了，去掉了第一行库存数据
                .doReadAll();
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}

2.2.2.读取部分的sheet页

说明:可以通过ExcelReader.read(ReadSheet...)来指定sheet页进行读取，并且每个sheet页的监听器都可以自定义，跳过几行表头等

@Override
    public void excelMultiSheetRead(MultipartFile file) {
        try {
            InputStream inputStream = file.getInputStream();
            ExcelReader excelReader = EasyExcel.read(inputStream).build();
            ReadSheet readSheet1 =
                    EasyExcel.readSheet(0).head(StockEntity.class).registerReadListener(new SimpleReadListener()).headRowNumber(2).build();
            ReadSheet readSheet2 =
                    EasyExcel.readSheet(2).head(UserEntity.class).registerReadListener(new SimpleReadListener2()).headRowNumber(1).build();
            excelReader.read(readSheet1, readSheet2);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

2.2.多行头读取

说明:就是跳过前几行标题进行读取,由于上面均使用，我就简单表述写法
.headRowNumber(1)    //虽然默认为1,但是最好自己写一下

2.3.读写的时候使用类型转换器

作用:可以指定格式,比如对于日期类型的表格，我转化为String类型，可指定格式，其他类型转化器也是如此

说明:下面我列举了日期类型转化器，自定义类型转化器
@Data
public class MyUser {
    private Long id;
    @ExcelProperty(value="姓名")
    private String name;
    @ExcelProperty(value="性别",converter = SexConverter.class)
    private Integer sex;
    @ExcelProperty(value="出生日期")
    @DateTimeFormat("yyyy年MM月dd日HH时mm分ss秒")   //使用日期转换器,alibaba包下
    private String  birthday;
}

public class SexConverter implements Converter<Integer> {
    /**
     *读取excel的时候调用
     */
    @Override
    public Integer convertToJavaData(ReadCellData<?> cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
        //获取表格数据
        String sex = cellData.getStringValue();
        switch (sex){
            case "男":
                return 0;
            case "女":
                return 1;
            case "保密":
                return 2;
            default:
                throw new RuntimeException("性别错误");
        }
    }
}

@Override
public void excelDataFormatRead(MultipartFile file) {
    try {
        InputStream inputStream = file.getInputStream();
        EasyExcel.read(inputStream, MyUser.class,new PageReadListener<MyUser>(dataList->{
                    for (MyUser myUser : dataList) {
                        log.info("从excel读取到的信息:{}",myUser);
                    }})).sheet(2)
                .headRowNumber(1).doRead();
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}

2.4.百万数据导入

前提:jdbc的url加上了:useUnicode=true&characterEncoding=utf-8&useSSL=false&zeroDateTimeBehavior=convertToNull&serverTimezone=GMT%2B8&rewriteBatchedStatements=true&useServerPrepStmts=true&allowMultiQueries=true&useCursorFetch=true&defaultFetchSize=100
@Override
public void multiReadExcel(MultipartFile file) {
    try {
        // 多线程读取
        EasyExcel.read(file.getInputStream(),TbExcelVO.class,new MultiReadListener(tbExcelMapper)).sheet().doRead();
    } catch (IOException e) {
        throw new RuntimeException(e);
    }
}

@Slf4j
public class MultiReadListener implements ReadListener<TbExcelVO> {

    private TbExcelMapper tbExcelMapper;

    public MultiReadListener(TbExcelMapper tbExcelMapper) {
        this.tbExcelMapper = tbExcelMapper;
    }

    /**
     * 使用线程安全集合
     */
    private List<TbExcelVO> list= 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(TbExcelVO tbExcelVO, AnalysisContext analysisContext) {
        if(list!=null){
            list.add(tbExcelVO);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext analysisContext) {

        //创建线程池
        ExecutorService executor = new ThreadPoolExecutor(CORE_POOL_SIZE,
                MAX_POOL_SIZE,
                KEEP_ALIVE_TIME,
                TimeUnit.SECONDS,
                new ArrayBlockingQueue<>(QUEUE_CAPACITY),
                new ThreadPoolExecutor.CallerRunsPolicy());

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

@Slf4j
public class DeadMainThread implements Runnable {

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

    public DeadMainThread() {
    }

    public DeadMainThread(CountDownLatch count, TbExcelMapper tbExcelMapper, List<TbExcelVO> list, int startPosition, int endPosition) {
        this.startPosition = startPosition;
        this.endPosition = endPosition;
        this.tbExcelMapper = tbExcelMapper;
        this.list = list;
        this.count = count;
    }

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

3.导出excel文件

前置条件:

//用于和数据建库映射
@Data
@TableName("tb_excel")
@NoArgsConstructor
public class TbExcel {
    @TableId(type = IdType.ASSIGN_ID)
    private Long id;
    private String strCol;
    private BigDecimal decCol;
    private LocalDateTime datetimeCol;
    public TbExcel(String strCol, BigDecimal decCol, LocalDateTime datetimeCol) {
        this.strCol = strCol;
        this.decCol = decCol;
        this.datetimeCol = datetimeCol;
    }
}

//用于和excel映射，作为导出的bean
@Data
@AllArgsConstructor
@NoArgsConstructor
public class TbExcelVO {
    // 每两行合并
//    @ContentLoopMerge(eachRow = 2)
    @ExcelProperty(value = "ID", index = 0,converter = LongStringConverter.class)
    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;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}

//获取数据库里面的数据生成extity,并把entity映射成vo(用于导出)
@Autowired
private TbExcelMaqpper tbExcelMaqpper;
public List<TbExcelVO> data(){
    //查询数据中所有数据
    List<TbExcel> tbExcels = tbExcelMaqpper.selectList(null);
    return tbExcels.stream().map(tbExcel -> {
        TbExcelVO tbExcelVO = new TbExcelVO();
        tbExcelVO.setId(tbExcel.getId());
        tbExcelVO.setStrCol(tbExcel.getStrCol());
        tbExcelVO.setDecCol(tbExcel.getDecCol());
        tbExcelVO.setDatetimeCol(tbExcel.getDatetimeCol());
        return tbExcelVO;
    }).toList();
}

3.1.简单导出excel文件

3.1.1.简单导出excel文件供页面下载

@Override
    public void simpleExportExcel(HttpServletResponse response) {
        try {
            //设置响应头
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("数据.xlsx", "UTF-8"));
            //从数据库中获取数据
            List<TbExcelVO> tbExcelVOS = data();
            OutputStream outputStream = response.getOutputStream();
            EasyExcel.write(outputStream,TbExcelVO.class)
                    .sheet("模板")
                    .doWrite(tbExcelVOS);
        } catch (IOException e) {
            throw new RuntimeException("tb_exdcel文件导出失败~~",e);
        }
    }

3.1.2.导出excel文件由多次集合写入一个sheet

@Override
public void multiListExportExcel(HttpServletResponse response) {
    try{
        //设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("多集合导入数据.xlsx", "UTF-8"));
        //从数据库中获取数据
        List<TbExcelVO> tbExcelVOS = data();
        OutputStream outputStream = response.getOutputStream();
        try(ExcelWriter excelWriter = EasyExcel.write(outputStream,TbExcelVO.class).build()){  //excelWriter在try代码块执行完毕后自动调用 close() 方法
            WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
            for(int i=1;i<=5;i++){
                excelWriter.write(tbExcelVOS, writeSheet);
            }
        }
    } catch (IOException e) {
        throw new RuntimeException("tb_exdcel文件导出失败~~",e);
    }
}

3.1.3.导出excel文件由多次集合写入多个sheet

@Override
    public void multiListAndSheetExportExcel(HttpServletResponse response) {
        try{
            //设置响应头
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
            response.setCharacterEncoding("utf-8");
            response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("多集合导入数据.xlsx", "UTF-8"));
            OutputStream outputStream = response.getOutputStream();
            try(ExcelWriter excelWriter = EasyExcel.write(outputStream,TbExcelVO.class).build()){  //excelWriter在try代码块执行完毕后自动调用 close() 方法
                for(int i=0;i<5;i++){
                    WriteSheet writeSheet = EasyExcel.writerSheet(i,"模板"+i).build();
                    //从数据库中获取数据
                    List<TbExcelVO> tbExcelVOS = data();
                    excelWriter.write(tbExcelVOS, writeSheet);
                }
            }
        } catch (IOException e) {
            throw new RuntimeException("tb_exdcel文件导出失败~~",e);
        }
    }

3.1.4.通过模板xlxs文件导出excel文件

说明: 模板xlxs文件中,{date}代表map存放的1，{.ID}代表list集合要遍历的列
可查看模板文件，即可知道模板如何编写:[点击下载模板文件]()

@Override
public void writeTemplateExportExcel(HttpServletResponse response) {
    OutputStream outputStream= null;
    try{
        //设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("依据模板导出数据.xlsx", "UTF-8"));
        outputStream = response.getOutputStream();
        // 模板文件路径
        String templateFilePath = this.getClass().getClassLoader().getResource("模板.xlsx").getPath(); // 假设模板存放在 resources目录下
        templateFilePath = URLDecoder.decode(templateFilePath, "UTF-8");
        try(ExcelWriter excelWriter = EasyExcel.write(outputStream,TbExcelVO.class).withTemplate(templateFilePath).build()){
            // 创建写入的工作表
            WriteSheet writeSheet = EasyExcel.writerSheet().build();

            //从数据库中获取数据
            List<TbExcelVO> tbExcelVOS = data();
            // 计算 decCol 字段的总和 sum
            BigDecimal sum = tbExcelVOS.stream()
                    .map(TbExcelVO::getDecCol)  // 提取 decCol 字段
                    .reduce(BigDecimal.ZERO, BigDecimal::add);

            // 准备数据填充，使用map传递数据
            Map<String, Object> map = new HashMap<>();
            map.put("date", LocalDate.now());
            map.put("sum", sum);

            FillConfig fillConfig= FillConfig.builder().forceNewRow(Boolean.TRUE).build();
            excelWriter.fill(tbExcelVOS, fillConfig,writeSheet);
            excelWriter.fill(map, writeSheet);
        }
    } catch (IOException e) {
        throw new RuntimeException("tb_exdcel文件导出失败~~",e);
    }finally {
        if(outputStream!=null){
            try {
                outputStream.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
}

3.2.解决精度丢失问题和出现##问题

说明:精度丢失是由于数字类型默认使用科学计数法；出现##，是因为默认宽度不够（上述前置vo已经使用解决了)
解决:

  1. 精度丢失:@ExcelProperty(value = "ID", index = 0,converter = LongStringConverter.class),使用转换器，将long类型转换成字符串类型

  2. 出现##:@ColumnWidth(12),设置列宽，可根据情况来定

3.3.实现百万数据导出

3.3.1.透过过线程池将数据导出Excel中的多个sheet页中(可自己调节参数)

此案例数据我测试:3136160条数据,导出耗时:61341ms
技术:线程池，计数器，mybatis-plus分页查询,锁
说明:我们可以修改代码，实现写入一个sheet页中，但是一个excel页好像最多100万条数据 

@Override
public void exportMillionExcel(HttpServletResponse response) {
    long start0=System.currentTimeMillis();

    //定义每个sheet页导出的数量
    Integer pageNum=100000;
    // 线程池大小跟cpu有关，一般是cpu数量 * 2 + 1
    Integer threadPoolSize = 12;
    //查询数据总数
    Long dataTotal = tbExcelMaqpper.selectCount(null);
    if(dataTotal==null||dataTotal==0){
        log.info("没有数据需要进行导出");
        return;
    }


    //获取sheet页的页数
    int sheetPages = (int)Math.ceil((double) dataTotal / pageNum);

    //本质就是一个计数器,用来等待所有线程执行完毕
    //1.countDown() 每调用一次，计数器减1
    //2.await() 调用此方法的线程会被阻塞，直到计数器变为0
    final CountDownLatch latch = new CountDownLatch(sheetPages);

    //创建线程池
    ExecutorService executorService = Executors.newFixedThreadPool(threadPoolSize);

    try {
        //设置响应头
        response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
        response.setCharacterEncoding("utf-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode("百万数据导出.xlsx", "UTF-8"));
        OutputStream outputStream= response.getOutputStream();


        ExcelWriter excelWriter = EasyExcel.write(outputStream,TbExcelVO.class).build();

        for( int i=1;i<=sheetPages;i++){
            //定义当前的sheet页数
            final int currentSheetPage = i;  //因为lambda表达式不能改变变量的值，所以这里定义一个final变量，用来保存当前的sheet页数

            executorService.execute(()->{
                //分页查询数据
                Page<TbExcel> page = new Page<>(currentSheetPage, pageNum);
                long start1 = System.currentTimeMillis();
                Page<TbExcel> tbExcelPage = tbExcelMaqpper.selectPage(page, null);
                log.info("第"+currentSheetPage+"页数据查询耗时："+(System.currentTimeMillis()-start1)+"ms");
                List<TbExcel> tbExcels = tbExcelPage.getRecords();
                List<TbExcelVO> tbExcelVOS = tbExcels.stream()
                        .map(tbExcel -> new TbExcelVO(tbExcel.getId(), tbExcel.getStrCol(), tbExcel.getDecCol(), tbExcel.getDatetimeCol()))
                        .toList();

                WriteSheet writeSheet = EasyExcel.writerSheet(currentSheetPage,"第"+currentSheetPage+"页").head(TbExcelVO.class).build();

                synchronized (excelWriter) {  //确保只有一个线程可以写入Excel文件
                    long start2=System.currentTimeMillis();
                    excelWriter.write(tbExcelVOS,writeSheet);
                    log.info("第"+currentSheetPage+"页数据写入Excel文件耗时："+(System.currentTimeMillis()-start2)+"ms");
                }

                latch.countDown();
            });
        }

        latch.await();
        excelWriter.finish();
        outputStream.flush();
        outputStream.close();
        executorService.shutdown(); // 关闭线程池

        log.info("excel文件导出完毕,总耗时:{}ms",(System.currentTimeMillis()-start0));
    } catch (IOException | InterruptedException e) {
        throw new RuntimeException(e);
    }


}

3.3.2.通过虚拟线程池实现将数据导出Excel中的多个sheet页中(可自己调节参数)

条件:jdk21+(才有虚拟线程)，这里不做编写，拷贝别人的代码

 @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);
    }

}

 

相关文章

欢迎使用WhaleCMS-鲸鱼博客CMS系统，这是您的第一篇初始文章
欢迎使用WhaleCMS-鲸鱼博客CMS系统，本系统所有功能全部免费开放，后续会持续更新更多功能，敬请期待。
jdk原生Timer和spring提供的SpringTask的用法
单体的定时任务
virtualbox配置虚拟机网路和配置yum,dnf镜像，配置epel仓库
virtual配置虚拟机网络和配置yum,dnf镜像
常见软件安装和卸载,如:docker
常见软件安装和卸载,如:docker

评论

快捷导航

把好文章收藏到微信

打开微信，扫码查看

关闭
×
  • 微信支付
  • 支付宝支付
微信支付二维码

￥{{ payArticleData.total_amount }}

请使用手机微信扫一扫
扫描二维码支付

支付宝支付二维码

￥{{ payArticleData.total_amount }}

请使用手机支付宝扫一扫
扫描二维码支付

请先勾选协议再支付哦

还没有账号？立即注册

扫一扫，添加微信