優(yōu)化前：優(yōu)化大量數(shù)據(jù)導(dǎo)出到Excel的內(nèi)存消耗_大文件異步導(dǎo)出 內(nèi)存占用高-CSDN博客

寫Excel文件報錯：Invalid row number (1048576) outside allowable range (0..1048575)

寫入Excel時遇到`IllegalArgumentException`，原因是超出允許的最大行數(shù)。文章提供了解決方案，即使用多個sheet并將數(shù)據(jù)分批寫入以避免此問題。

數(shù)據(jù)導(dǎo)出優(yōu)化：如果數(shù)據(jù)超出Excel單表上限，Excel單表最多可以存儲1048576條數(shù)據(jù)（1024的平方，2的20次方），如果數(shù)據(jù)超出Excel單表上限，則進行分表。

 public void writeExcel(OutputStream os, String sheetName, Map<String, String> header, List<Map<String, Object>> datas) {logger.info("導(dǎo)入數(shù)據(jù)到excel==========> 開始");long startTime = System.currentTimeMillis();  // 記錄開始時間int rowAccessWindowSize = 100;  // 設(shè)置適當(dāng)?shù)男性L問窗口大小SXSSFWorkbook wb = new SXSSFWorkbook(rowAccessWindowSize);wb.setCompressTempFiles(true);  // 啟用臨時文件壓縮以提高性能int sheetIndex = 0; // 新增變量，用于追蹤當(dāng)前的sheet索引int maxRowsPerSheet = 1048576; // 單個sheet的最大行數(shù)int totalRecords = ObjectKit.isNotEmpty(datas) ? datas.size() : 0;  // 總共導(dǎo)出記錄數(shù)logger.info("即將導(dǎo)出記錄總數(shù): " + totalRecords);Map<String, CellStyle> cellStyles = initStyles(wb); // 優(yōu)化：樣式創(chuàng)建移到循環(huán)外部while (!datas.isEmpty()) {String currentSheetName = sheetName + "_" + sheetIndex;Sheet sheet = wb.createSheet(currentSheetName);int rowNum = 0;Row row = sheet.createRow(rowNum);
//            Map<String, CellStyle> cellStyles = initStyles(wb);int cellNum = 0;// 寫入表頭for (Map.Entry<String, String> entry : header.entrySet()) {String fieldDesc = entry.getValue();Cell cell = row.createCell(cellNum);cell.setCellValue(fieldDesc);logger.info("導(dǎo)入數(shù)據(jù)到excel==========> 表頭" + entry.getKey());cellNum++;}// 計算本次循環(huán)需要處理的數(shù)據(jù)量int recordsToProcess = Math.min(maxRowsPerSheet - 1, datas.size()); // 減去1是因為第一行是表頭for (int i = 0; i < recordsToProcess; i++) {Map<String, Object> map = datas.remove(0); // 從列表頭部移除已處理的數(shù)據(jù)rowNum++;row = sheet.createRow(rowNum);cellNum = 0;for (Map.Entry<String, String> entry : header.entrySet()) {String fieldName = entry.getKey();Object data = map.get(fieldName.toUpperCase());String dataString = null == data ? "" : data.toString();if (data instanceof BigDecimal) {Cell cell = row.createCell(cellNum);cell.setCellValue(((BigDecimal) data).toPlainString());cell.setCellStyle(cellStyles.get("Number"));} else {if (data instanceof Date || data instanceof Timestamp) {if (data.toString().contains(".")) {dataString = null == data ? "" : data.toString().substring(0, data.toString().indexOf("."));} else {dataString = null == data ? "" : data.toString();}}row.createCell(cellNum).setCellValue(null == data ? "" : dataString);}cellNum++;}// 當(dāng)前已導(dǎo)出記錄數(shù)及進度if(rowNum % 10000 == 0){logger.info("當(dāng)前sheet已導(dǎo)出記錄數(shù): " + rowNum + ", 進度: " + ((float) rowNum / recordsToProcess) * 100 + "%");}}sheetIndex++; // 切換到下一個sheet}logger.info("導(dǎo)入數(shù)據(jù)到excel==========> 結(jié)束");long endTime = System.currentTimeMillis();  // 記錄結(jié)束時間long elapsedTime = endTime - startTime;  // 計算耗時時間logger.info("總共導(dǎo)出記錄數(shù): " + totalRecords);long elapsedTimeInSeconds = elapsedTime / 1000;  // 將毫秒轉(zhuǎn)換為秒logger.info("耗時時間: " + elapsedTimeInSeconds + " 秒 " );try {wb.write(os);} catch (IOException e) {throw new ImpException(ImpError.APP_ERR_20_04_10, e);} finally {try {if (null != wb) {wb.close();}} catch (IOException e) {logger.error(e.getMessage(), e);}}}

這一優(yōu)化措施確保了當(dāng)數(shù)據(jù)量超過Excel單表上限時，數(shù)據(jù)能夠被有效地分散到多個工作表中，從而支持更大規(guī)模的數(shù)據(jù)導(dǎo)出。
目前測試了導(dǎo)出三百萬數(shù)據(jù)正常則進行分表。