阅读原文：POI如何高效导出百万级Excel数据？在一个具有统计功能的系统中，导出excel功能几乎是一定的，如何导出excel？导出的数据有多少？如何高效的导出？Excel简介什么是excel就不用介绍了，这里主要说明不同版本下每个sheet下的行列限制。由上面可知 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，所以使用的是前三个。导出策略方案使用XSSF和SXSSF分别导入1w,10w,100w数据使用SXSSF，SXSSF以10w分页，SXSSF多线程以10w分页导入100w数据性能对比时间不包含网络耗时总结方案一：数据在万条时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”)@ResponseBodypublic 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();}/** * 构建内容 * @param num * @return */private String[][] buildContent(Integer num) { String[][] content = new String[num][4]; for (int i = 0; i < content.length; i++) { content[i][0] = “1”; content[i][5] = “2”; content[i][6] = “3”; content[i][7] = “4”; content[i][8] = “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;}@Overridepublic 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(); } }}}