java千万数据excel导出 java导出excel百万级

Excel简介

不同版本下每个sheet下的行列限制。

版本区间	行数	列数	文件后缀
Excel 2003及以下	65535	256	xls
Excel 2007及以上	1048576	16384	xlsx

由上面可知 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，所以使用的是前三个。

导出策略

方案

1.使用XSSF和SXSSF分别导入1w,10w,100w数据
2.使用SXSSF，SXSSF以10w分页，SXSSF多线程以10w分页导入100w数据

性能对比

时间不包含网络耗时

类型	1w条/时间	10w条/时间	100w条/时间
XSSF	1331ms	10496ms	小心电脑💥
SXSSF	1568ms	600ms	5824ms

类型	100w条/时间	200w/时间
SXSSF	5824ms	超过最大行数
SXSSF Page	6040ms	12473ms
SXSSF Page Thread	3410ms	6217ms

总结

方案一

• 数据在万条时XSSF和SXSSF相差不大
• 数据上十万后SXSSF性能开始突出
• 数据到达百万时，XSSF已不适合使用

方案二

• 不进行分表时，SXSSF最多可存储1048576行
• 百万级数据分表存储时，使用多线程导出几乎是不使用多线程导出的一半时间
  最终我得出一个导出百万级数据的最高效方案：多线程分表导出

方法

//list 传入对象集合 需要在对应实体的字段上加入注解 @Excel(title = "名称")
ExcelUtil.exportExcelOutputStream(response, records, list, "项目列表" + LocalDateTime.now().format(DateTimeFormatter.ofPattern("MMddHHmmss")));

工具类

public static void exportExcelOutputStream(HttpServletResponse response, List<?> list,
                                           Class clazz, String fileName) throws Exception {
    //封装 ExcelParam
    ExcelParam excelParam = new ExcelParam(clazz,response,fileName,list);
    commonExportExcel(excelParam);
}

//定义常量sheet的大小 这面默认为1w
public static final int SHEET_LIMIT = 10000;

//excel导出方法
public static void commonExportExcel(ExcelParam excelParam) throws Exception {
    Object obj = excelParam.getClazz().newInstance();
    final int size = excelParam.getList().size();
    int pageNum = size / SHEET_LIMIT;
    int lastCount = size % SHEET_LIMIT;

    Map<String, String> map = getMap(excelParam.getClazz());
    List<String> keyList = null;
    if (StringUtils.isEmpty(excelParam.getKeyValue())) {
        keyList = getList(excelParam.getClazz());
    } else {
        keyList = getList(excelParam.getKeyValue());
    }
    SXSSFWorkbook wb = new SXSSFWorkbook();
    if (size > SHEET_LIMIT) {
        CellStyle style = wb.createCellStyle();
        int sheet = lastCount == 0 ? pageNum : pageNum + 1;
        CountDownLatch downLatch = new CountDownLatch(sheet);
        //定义线程池 按sheet设置线程池量
        Executor executor = Executors.newFixedThreadPool(sheet);
        for (int c = 0; c <= pageNum; c++) {
            int rowNum = SHEET_LIMIT;
            if (c == pageNum) {
                if (lastCount == 0) {
                    continue;
                }
                rowNum = lastCount;
            }
            List list = excelParam.getList().subList(c * rowNum, (c + 1) * rowNum);
            Sheet sxssfSheet = wb.createSheet("page" + c);
            executor.execute(new PageThreadPool(downLatch, sxssfSheet, keyList, style, rowNum, list, map, obj));
        }
        try {
            downLatch.await();
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
    } else {
        // 建立新的sheet对象（excel的表单）
        Sheet sheet = wb.createSheet("sheet1");
        // 声明样式
        CellStyle style = wb.createCellStyle();
        // 居中显示
        style.setAlignment(HorizontalAlignment.CENTER);
        // 在sheet里创建第一行为表头，参数为行索引(excel的行)
        Row rowHeader = sheet.createRow(0);
        // 创建单元格并设置单元格内容

        // 存储属性信息
        Map<String, String> attMap = new HashMap();
        int index = 0;

        for (String key : keyList) {
            rowHeader.createCell(index).setCellValue(key);
            attMap.put(Integer.toString(index), map.get(key));
            index++;
        }

        // 在sheet里创建表头下的数据
        for (int i = 0; i < excelParam.getList().size(); i++) {
            Row row = sheet.createRow(i + 1);
            for (int j = 0; j < map.size(); j++) {
                Class<?> attrType = BeanUtils.findPropertyType(attMap.get(Integer.toString(j)),
                                                               new Class[]{obj.getClass()});
                Object value = getAttrVal(excelParam.getList().get(i), attMap.get(Integer.toString(j)), attrType);
                if (null == value) {
                    value = "";
                }
                row.createCell(j).setCellValue(value.toString());
                style.setAlignment(HorizontalAlignment.CENTER);
            }
        }
    }
    SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
    String newFileName = excelParam.getFileName();
    if (StringUtils.isEmpty(newFileName)) {
        newFileName = df.format(new Date());
    }

    // 输出Excel文件
    try {
        if (excelParam.getResponse() != null) {
            OutputStream outstream = excelParam.getResponse().getOutputStream();
            excelParam.getResponse().reset();
            excelParam.getResponse().setHeader("Content-Disposition",
                                               "attachment; filename=" + new String((newFileName + ".xlsx").getBytes(), "iso-8859-1"));
            excelParam.getResponse().setContentType("application/vnd.ms-excel;charset=utf-8");
            wb.write(outstream);
            outstream.close();
        } else {
            FileOutputStream out = new FileOutputStream(excelParam.getOutFilePath());
            wb.write(out);
            out.close();
        }

    } catch (FileNotFoundException e) {
        throw new FileNotFoundException("导出失败！" + e);
    } catch (IOException e) {
        throw new IOException("导出失败！" + e);
    }

}

分表任务类

// 线程封装 sheet分页
public class PageThreadPool implements Runnable {
    private CountDownLatch countDownLatch;
    private Sheet sheet;
    private List<String> keyList;
    private CellStyle style;
    private int b;
    private List list;
    private Map<String, String> map;
    private Object obj;

    public PageThreadPool(CountDownLatch countDownLatch, Sheet sheet, List<String> keyList, CellStyle style, int b, List list, Map<String, String> map, Object obj) {
        this.countDownLatch = countDownLatch;
        this.sheet = sheet;
        this.keyList = keyList;
        this.style = style;
        this.b = b;
        this.list = list;
        this.map = map;
        this.obj = obj;
    }

    @Override
    public void run() {

        try {
            Row rowHeader = sheet.createRow(0);
            Cell cell = null;
            for (int i = 0; i < keyList.size(); i++) {
                cell = rowHeader.createCell(i);
                cell.setCellValue(keyList.get(i));
                cell.setCellStyle(style);
            }
            // 存储属性信息
            Map<String, String> attMap = new HashMap();
            int index = 0;

            for (String key : keyList) {
                rowHeader.createCell(index).setCellValue(key);
                attMap.put(Integer.toString(index), map.get(key));
                index++;
            }

            for (int i = 0; i < list.size(); i++) {
                Row row = sheet.createRow(i + 1);
                for (int j = 0; j < map.size(); j++) {
                    Class<?> attrType = BeanUtils.findPropertyType(attMap.get(Integer.toString(j)),
                            new Class[]{obj.getClass()});
                    Object value = ExcelUtilBase.getAttrVal(list.get(i), attMap.get(Integer.toString(j)), attrType);
                    if (null == value) {
                        value = "";
                    }
                    row.createCell(j).setCellValue(value.toString());
                    style.setAlignment(HorizontalAlignment.CENTER);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (countDownLatch != null) {
                countDownLatch.countDown();
            }
        }
    }
}