apache poi 百万级数据导出excel

     最近项目要求把solr中百万条数据导出到excel，用hssfworkbook  速度非常慢，最后研究发现用SXSSFWorkbook，100000条数据在1min内，废话不多，直接上代码。

public static void Excel2007AboveOperate(String filePath,int num) throws IOException {
            XSSFWorkbook workbook1 = new XSSFWorkbook(new FileInputStream(new File(filePath)));
            SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook1, 100);
            Sheet first = sxssfWorkbook.getSheetAt(0);
        for (int i = 0; i < num; i++) {
            Row row = first.createRow(60113+i);
            for (int j = 0; j < 7; j++) {
                if(i == 0) {
                    // 首行
                    row.createCell(j).setCellValue("第" + j+"列");
                } else {
                    // 数据
                    if (j == 0) {
                        CellUtil.createCell(row, j, String.valueOf(i));
                    } else
                        CellUtil.createCell(row, j, String.valueOf(Math.random())+"");
                }
            }
        }
        FileOutputStream out = new FileOutputStream(filePath);
        sxssfWorkbook.write(out);
        out.close();
    }

pom文件中添加

<dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.10-FINAL</version>
        </dependency>

针对excel2007及以上版本导出很快。