POI如何高效导出百万级Excel数据？

阅读原文：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();         
           }     
       } 
    }

}