apache poi 寫excel 大資料_POI如何高效導出百萬級Excel資料？

在一个具有统计功能的系统中，导出excel功能几乎是一定的，如何导出excel？导出的数据有多少？如何高效的导出？

Excel简介

什么是excel就不用介绍了，这里主要说明不同版本下每个sheet下的行列限制。

版本区间	行数	列数	文件后缀
Excel 2003及以下	65535	256	xls
Excel 2007及以上	1048576	16384	xlsx

由上面可知 Excel 2003及以下是无法实现单sheet百万级的数据。

Apache POI

简介

Apache POI 是用Java编写的免费开源的跨平台的 Java API，Apache POI提供API给Java程式对Microsoft Office(Excel、WORD、PowerPoint、Visio等)格式档案读和写的功能。POI为“Poor Obfuscation Implementation”的首字母缩写，意为“可怜的模糊实现"。

常用类

• HSSF － 提供读写Microsoft Excel XLS格式档案的功能。
• XSSF － 提供读写Microsoft Excel OOXML XLSX格式档案的功能。
• SXSSF － 一种基于XSSF的低内存占用的API(3.8版本开始出现)。
• HWPF － 提供读写Microsoft Word DOC97格式档案的功能。
• XWPF － 提供读写Microsoft Word DOC2003格式档案的功能。
• HSLF － 提供读写Microsoft PowerPoint格式档案的功能。
• HDGF － 提供读Microsoft Visio格式档案的功能。
• HPBF － 提供读Microsoft Publisher格式档案的功能。
• HSMF － 提供读Microsoft Outlook格式档案的功能。

我们这里是导出Excel，所以使用的是前三个。

导出策略

方案

1.使用XSSF和SXSSF分别导入1w,10w,100w数据

2.使用SXSSF，SXSSF以10w分页，SXSSF多线程以10w分页导入100w数据

性能对比

时间不包含网络耗时

类型	1w条/时间	10w条/时间	100w条/时间
XSSF	1331ms	10496ms	小心电脑?
SXSSF	1568ms	600ms	5824ms

类型	100w条/时间	200w/时间
SXSSF	5824ms	è¶ è¿‡æœ€å¤§è¡Œæ•°
SXSSF Page	6040ms	12473ms
SXSSF Page Thread	3410ms	6217ms

总结

方案一

• 数据在万条时XSSF和SXSSF相差不大
• 数据上十万后SXSSF性能开始突出
• 数据到达百万时，XSSF已不适合使用

方案二

• 不进行分表时，SXSSF最多可存储1048576行
• 百万级数据分表存储时，使用多线程导出几乎是不使用多线程导出的一半时间

最终我得出一个导出百万级数据的最高效方案：多线程分表导出

实战

• controller层

@RestController

@RequestMapping("export")

public class ReportController {

public static final String[] TITLE = new String[]{"第1列", "第2列", "第3列", "第4列", "第5列"};

public static final String SHEET_NAME = "page1";

@RequestMapping(value = "/sxssf/page/thread")

@ResponseBody

public void exportSXSSFWorkbookByPageThread(HttpServletResponse response, Integer num) throws Exception {

//excel文件名

String fileName = System.currentTimeMillis() + ".xlsx";

//sheet名

if (Objects.isNull(num)) {

num = 65536;

}

String[][] content = buildContent(num);

long start = System.currentTimeMillis();

SXSSFWorkbook wb = ExcelUtil.getSXSSFWorkbookByPageThread(TITLE, content, null);

long millis = System.currentTimeMillis() - start;

long second = millis / 1000;

System.out.println("SXSSF Page Thread 导出" + num + "条数据，花费：" + second + "s/ " + millis + "ms");

writeAndClose(response, fileName, wb);

wb.dispose();

}

private String[][] buildContent(Integer num) {

String[][] content = new String[num][5];

for (int i = 0; i < content.length; i++) {

content[i][0] = "1";

content[i][1] = "2";

content[i][2] = "3";

content[i][3] = "4";

content[i][4] = "5";

}

return content;

}

private void writeAndClose(HttpServletResponse response, String fileName, Workbook wb) {

try {

this.setResponseHeader(response, fileName);

OutputStream os = response.getOutputStream();

wb.write(os);

os.flush();

os.close();

} catch (Exception e) {

e.printStackTrace();

}

}

public void setResponseHeader(HttpServletResponse response, String fileName) {

try {

try {

fileName = new String(fileName.getBytes(), "UTF-8");

} catch (UnsupportedEncodingException e) {

e.printStackTrace();

}

response.setContentType("application/octet-stream;charset=ISO8859-1");

response.setHeader("Content-Disposition", "attachment;filename=" + fileName);

response.addHeader("Pargam", "no-cache");

response.addHeader("Cache-Control", "no-cache");

} catch (Exception ex) {

ex.printStackTrace();

}

}

}

• 工具类

public class ExcelUtil {

public static final int PER_SHEET_LIMIT = 500000;

public static SXSSFWorkbook getSXSSFWorkbookByPageThread(String[] title, String[][] values) {

SXSSFWorkbook wb = new SXSSFWorkbook();

int pageNum = values.length / PER_SHEET_LIMIT;

int lastCount = values.length % PER_SHEET_LIMIT;

if (values.length > PER_SHEET_LIMIT) {

CellStyle style = wb.createCellStyle();

int sheet = lastCount == 0 ? pageNum : pageNum + 1;

CountDownLatch downLatch = new CountDownLatch(sheet);

Executor executor = Executors.newFixedThreadPool(sheet);

for (int c = 0; c <= pageNum; c++) {

int rowNum = PER_SHEET_LIMIT;

if (c == pageNum) {

if (lastCount == 0) {

continue;

}

rowNum = lastCount;

}

Sheet sheet = wb.createSheet("page" + c);

executor.execute(new PageTask(downLatch, sheet, title, style, rowNum, values));

}

try {

downLatch.await();

} catch (InterruptedException e) {

e.printStackTrace();

}

}

return wb;

}

}

• 分表任务类

public class PageTask implements Runnable {

private CountDownLatch countDownLatch;

private Sheet sheet;

private String[] title;

private CellStyle style;

private int b;

private String[][] values;

public PageTask(CountDownLatch countDownLatch, Sheet sheet, String[] title, CellStyle style, int b, String[][] values) {

this.countDownLatch = countDownLatch;

this.sheet = sheet;

this.title = title;

this.style = style;

this.b = b;

this.values = values;

}

@Override

public void run() {

try {

Row row = sheet.createRow(0);

Cell cell = null;

for (int i = 0; i < title.length; i++) {

cell = row.createCell(i);

cell.setCellValue(title[i]);

cell.setCellStyle(style);

}

for (int i = 0; i < b; i++) {

row = sheet.createRow(i + 1);

for (int j = 0; j < values[i].length; j++) {

row.createCell(j).setCellValue(values[i][j]);

}

}

} catch (Exception e) {

e.printStackTrace();

} finally {

if (countDownLatch != null) {

countDownLatch.countDown();

}

}

}

}

献给所有的程序媛，女王节快乐

海内存知己,天涯若比邻，喜欢就关注吧！