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 {// 數據庫Idprivate 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*/@Overridepublic void invoke(StockEntity stockEntity, AnalysisContext analysisContext) {log.info("讀取到一條數據：{}", stockEntity);}/*** 讀取完成之后，會調用doAfterAllAnalysed方法，做一些數據清理的操作* @param analysisContext*/@Overridepublic 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 自己使用功能必須不同的ListenerReadSheet 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> {/*** 這里讀的時候會調用*/@Overridepublic 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> {@Getterprivate List<MyUser> data;public UserReadListener() {this.data = new ArrayList<>();}@Overridepublic void invoke(MyUser myUser, AnalysisContext analysisContext) {data.add(myUser);}@Overridepublic 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轉換為ExcelVOList<ExcelVO> excelVOS = BeanUtil.copyToList(tbExcels, ExcelVO.class);// 提供給前端下載，需要使用到HttpServletResponse對象// 這里注意 有同學反應使用swagger 會導致各種問題，請直接用瀏覽器或者用postmanresponse.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 會導致各種問題，請直接用瀏覽器或者用postmanresponse.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;//存活時間@Overridepublic void invoke(ExcelVO data, AnalysisContext context) {if (dataList != null) {dataList.add(data);}}@Overridepublic 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;}@Overridepublic 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 + 1Integer 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);}}

---