大量数据导出
问题
如果数据量较大,那么使用 Apache POI 导出数据可能会导致OOM或者到处时间较长。
解决方案
使用多线程同时导出多个excel,缩短接口响应时间
代码示例
public void test () {
List<Test> tests = testMapper.selectAll(); // 查询数据
// 每个excel 5w 条数据
int num = 50000;
int size = tests.size();
int yushu = size % num;
int zhengshu = size / num;
if (yushu != 0) {
zhengshu++;
}
// 创建线程池
ThreadPoolExecutor threadPool = new ThreadPoolExecutor(5,
5,
10,
TimeUnit.SECONDS,
new LinkedBlockingQueue<>(zhengshu),
new ThreadPoolExecutor.AbortPolicy());
CountDownLatch latch = new CountDownLatch(zhengshu);
// 向线程池提交任务
for (int i = 0; i < zhengshu; i++) {
int finalSheetnum = i;
threadPool.execute(() -> {
try {
// 导出
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = wb.createSheet("数据:" + finalSheetnum);
for (int j = 0; j < num; j++) {
XSSFRow row = sheet.createRow(j);
int index = finalSheetnum * num + j;
if (index < size) {
// 填充数据
Test test = tests.get(index);
XSSFCell cell0 = row.createCell(0);
cell0.setCellValue(test.getId());
XSSFCell cell1 = row.createCell(1);
cell1.setCellValue(test.getName());
XSSFCell cell2 = row.createCell(2);
cell2.setCellValue(test.getEmail());
XSSFCell cell3 = row.createCell(3);
cell3.setCellValue(test.getPhone());
XSSFCell cell4 = row.createCell(4);
cell4.setCellValue(test.getGender());
XSSFCell cell5 = row.createCell(5);
cell5.setCellValue(test.getPassword());
XSSFCell cell6 = row.createCell(6);
cell6.setCellValue(test.getAge());
XSSFCell cell7 = row.createCell(7);
cell7.setCellValue(test.getCreateTime());
XSSFCell cell8 = row.createCell(8);
cell8.setCellValue(test.getUpdateTime());
}
}
FileOutputStream fileOutputStream = new FileOutputStream("D:/桌面/测试/test" + finalSheetnum + ".xlsx");
wb.write(fileOutputStream);
fileOutputStream.close();
wb.close();
System.out.println("File test" + finalSheetnum + ".xlsx has been created.");
latch.countDown();
} catch (Exception e) {
e.printStackTrace();
}
});
}
try {
latch.await();
} catch (InterruptedException e) {
e.printStackTrace();
}
threadPool.shutdown();
System.out.println("All threadPool have been closed.");
}