Java EasyExcel 导出报内存溢出如何解决 - ToB企服应用市场:ToB评测及商务社交产业平台

String fileName = "large_data.xlsx";
ExcelWriter excelWriter = EasyExcel.write(fileName).build();
WriteSheet writeSheet = EasyExcel.writerSheet("Sheet1").build();
// 假设每次写入10000条数据
int batchSize = 10000;
List<Data> dataList;
int pageIndex = 0;
do {
// 分页获取数据
dataList = getDataByPage(pageIndex++, batchSize);
excelWriter.write(dataList, writeSheet);
} while (dataList.size() == batchSize);
// 关闭资源
excelWriter.finish();

复制代码

java -Xms512M -Xmx4G -jar yourApp.jar

复制代码

EasyExcel.write(fileName)
.registerWriteHandler(new SimpleWriteHandler()) // 不使用自动列宽
.sheet("Sheet1")
.doWrite(dataList);

复制代码

try (OutputStream out = new BufferedOutputStream(new FileOutputStream(fileName))) {
ExcelWriter excelWriter = EasyExcel.write(out).build();
WriteSheet writeSheet = EasyExcel.writerSheet("Sheet1").build();
int pageIndex = 0;
List<Data> dataList;
do {
dataList = getDataByPage(pageIndex++, batchSize);
excelWriter.write(dataList, writeSheet);
} while (dataList.size() == batchSize);
excelWriter.finish();
} catch (IOException e) {
e.printStackTrace();
}

复制代码

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
public class LargeDataExportExample {
public static void main(String[] args) {
// 文件输出路径
String filePath = "vg_large_data_export.xlsx";
// 导出百万级数据
exportLargeData(filePath);
}
private static void exportLargeData(String filePath) {
// 每次写入的批次大小
final int batchSize = 10000;
// 数据总条数
final int totalRows = 1_000_000;
// 创建SXSSFWorkbook对象，内存中只保留100行，超过的部分会写入临时文件
SXSSFWorkbook workbook = new SXSSFWorkbook(100);
workbook.setCompressTempFiles(true); // 启用临时文件压缩
// 创建工作表
Sheet sheet = workbook.createSheet("Large Data");
// 创建标题行
Row headerRow = sheet.createRow(0);
String[] headers = {"ID", "Name", "Age"};
for (int i = 0; i < headers.length; i++) {
Cell cell = headerRow.createCell(i);
cell.setCellValue(headers[i]);
}
int rowNum = 1; // 数据开始的行号
try {
// 按批次写入数据
for (int i = 0; i < totalRows / batchSize; i++) {
// 模拟获取每批数据
List<Data> dataList = getDataBatch(rowNum, batchSize);
// 将数据写入到Excel中
for (Data data : dataList) {
Row row = sheet.createRow(rowNum++);
row.createCell(0).setCellValue(data.getId());
row.createCell(1).setCellValue(data.getName());
row.createCell(2).setCellValue(data.getAge());
}
// 处理完成一批数据后，可以选择清除缓存数据，防止内存溢出
((SXSSFSheet) sheet).flushRows(batchSize); // 清除已写的行缓存
}
// 将数据写入文件
try (FileOutputStream fos = new FileOutputStream(filePath)) {
workbook.write(fos);
}
System.out.println("数据导出完成：" + filePath);
} catch (IOException e) {
e.printStackTrace();
} finally {
// 关闭workbook并删除临时文件
workbook.dispose();
}
}
/**
* 模拟分页获取数据
*/
private static List<Data> getDataBatch(int startId, int batchSize) {
List<Data> dataList = new ArrayList<>(batchSize);
for (int i = 0; i < batchSize; i++) {
dataList.add(new Data(startId + i, "Name" + (startId + i), 20 + (startId + i) % 50));
}
return dataList;
}
// 数据类
static class Data {
private final int id;
private final String name;
private final int age;
public Data(int id, String name, int age) {
this.id = id;
this.name = name;
this.age = age;
}
public int getId() {
return id;
}
public String getName() {
return name;
}
public int getAge() {
return age;
}
}
}

复制代码