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") 
    @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(); 
    }
     
    /**  * 构建内容  
    * @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; 
    } 
    @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();         
           }     
       } 
    }

}