poi SXSSFWorkbook大数据量导出实践

最近公司做一个生成审批附件的功能，算是对老的功能的一个改造，之前使用的XSSF 和HSSF ；但是一旦数据量超过万级别了，导出的性能就差强人意了；要想快那就必须要要用到poi的SXSSFWorkbook了；

用到的依赖

<!-- List 操作相关-->
<dependency>
    <groupId>com.google.guava</groupId>
    <artifactId>guava</artifactId>
    <version>29.0-jre</version>
</dependency>
<!--poi 导入导出相关-->
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.17</version>
</dependency>
<dependency>
    <groupId>commons-fileupload</groupId>
    <artifactId>commons-fileupload</artifactId>
    <version>1.3.1</version>
</dependency>
<dependency>
    <groupId>commons-io</groupId>
    <artifactId>commons-io</artifactId>
    <version>2.5</version>
</dependency>

示范代码

public class TestPoi {

    public static void main(String[] args) throws Exception{
        Long start = System.currentTimeMillis();
        List<UserInfo> userInfoList =new ArrayList<>();
        for(int i=1;i<=20*10000;i++){
            UserInfo userInfo = new UserInfo();
            userInfo.setId(Long.getLong(Integer.toString(i)))
                    .setName("echo"+i)
                    .setPassword(UUID.randomUUID().toString());
            userInfoList.add(userInfo);
        }
        String fileUrl = "/Users/echo/Desktop/testExcel.xlsx";
        FileInputStream fs;
        FileOutputStream os = null;
        try {
            File excel=new File(fileUrl);
            fs=new FileInputStream(excel);
            XSSFWorkbook workbook = new XSSFWorkbook(fs);
            SXSSFWorkbook wb = new SXSSFWorkbook(workbook,1000);//内存中保留 1000 条数据，以免内存溢出，其余写入 硬盘
            //获得该工作区的第一个sheet 如果已有模板 和模板名字一致即可
            //Sheet sheet1 = wb.createSheet("test");
            Sheet sheet1 = wb.getSheetAt(0);
            int excelRow = 1;
//            List<String> columnList = new ArrayList<>();
//            columnList.add("id");
//            columnList.add(" 姓名");
//            columnList.add("密码");
//            //标题行
//            Row titleRow = (Row) sheet1.createRow(excelRow++);
//            for (int i = 0; i < columnList.size(); i++) {
//                Cell cell = titleRow.createCell(i);
//                cell.setCellValue(columnList.get(i));
//            }
            //明细行
            List<List<UserInfo>> reParam = Lists.partition(userInfoList, 1000);
            for (int x = 0;x<reParam.size();x++) {
                List<UserInfo> userInfos = reParam.get(x);
                for (int j = 0; j < userInfos.size(); j++) {
                    Row contentRow = sheet1.createRow(excelRow++);
                    Cell cell0 = contentRow.createCell(0);
                    cell0.setCellValue(userInfos.get(j).getPassword());

                    Cell cell1 = contentRow.createCell(1);
                    cell1.setCellValue(userInfos.get(j).getName());
                    Cell cell2 = contentRow.createCell(2);
                    cell2.setCellValue(userInfos.get(j).getPassword());
                }
            }
            os = new FileOutputStream(fileUrl);
            wb.write(os);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (os != null) {
                    os.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            } // 关闭输出流
        }
        System.out.println("导20万数据耗时："+(System.currentTimeMillis()-start));
    }
}

总结

SXSSFWorkbook 有个缓存区，所以上面定义为1000，后面的数据就需要按1000拆分写到excel中，如果不设定此值有可能OOM,按1000拆分后，注意创建行的行号就行了！