JAVA笔记-如何将百万级数据高效的导出到Excel表单

本文链接：https://blog.csdn.net/jack_shuai/article/details/97812079

1》场景

　　项目中需要从数据库中导出100万行数据，以excel形式下载并且只要一张sheet（打开这么大文件有多慢另说，呵呵）。

　　ps：xlsx最大容纳1048576行，csv最大容纳1048576行，xls最大容纳65536行，但是存放相同的数据量文件大小排序：xls>csv>xlsx ；xls是biff8的二进制文件，就是个B+树而xlsx是 xml的zip压缩文件。

2》常规做法

　　按照平常的做法，先到数据库中取数然后循环组装成一个list，然后用excel工具（我用的是POI）生成excel。

3》遇到的问题

　　1' 内存经常溢出。

　　2' 组装list,生成excel慢，50万的数据花了一个小时都没见完成。

4》解决方法

　　1' POI 改用 SXSSFWorkbook 参看比如SXSSFWorkbook wb = new SXSSFWorkbook(100);在内存中只保留100行记录,超过100就将之前的存储到磁盘里,

2' 调整JVM 相关的参数 -Xmx....

　 3' 循环中减少使用new，尽量复用；String改为StringBuffer就不说了，重点是在组装一行数据时，一直比较喜欢用map来拼装，但是在我功能上发现还是耗内存的，后来的GC时间太长，造成严重拖累组装数据的效率，后来发现由HashMap改为用StringBuffer拼接行数据效率直接就上去了，当然指定合理的StringBuffer的起始容量效率就更好了。

　　ps:StringBuffer 的构造器会创建一个默认大小（通常是16）的字符数组。在使用中，如果超出这个大小，就会重新分配内存，创建一个更大的数组，并将原先的数组复制过来，再丢弃旧的数组。在大多数情况下，你可以在创建 StringBuffer的时候指定大小，这样就避免了在容量不够的时候自动增长，以提高性能。

4' 下载任务由同步改为异步，用户提交了后只要等待邮件通知即可，我用了quartz。

5》效果

100万数据组装以及生成excel大概要10分钟，平均下来1分钟10万条，我的小黑腰不酸腿不疼了。

6》代码实现

public class PoiExcelUtil {

    private static final Logger logger = LoggerFactory.getLogger(PoiExcelUtil.class);



    public void userList2Excel(SXSSFWorkbook wb, List<UserBaseLighting> userList, int page) throws Exception {
        //创建一个webbook，对应一个Excel文件
        //在webbook中添加一个sheet,对应Excel文件中的sheet
        String sheetName = "sheet"+page;
        SXSSFSheet userSheet = wb.createSheet(sheetName);
        //在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short
        Row titleRow = userSheet.createRow(0);
        userSheet.setDefaultColumnWidth(20);
        //创建单元格，并设置值表头 设置表头居中
        CellStyle style = wb.createCellStyle();
        Cell cell = null;
        //标题行抽出字段
        String[] title = {"序号","流水号", "平台会员编号", "手机号（选填）", "身份证号", "会员姓名", "卡号(选填)", "用户角色","授权类型列表(选填枚举类型地址符号隔开)","证件类型(选填默认PRC_ID)"};
        //把已经写好的标题行写入excel文件中
        for (int i = 0; i < title.length; i++) {
            cell = titleRow.createCell(i);
            cell.setCellValue(title[i]);
            cell.setCellStyle(style);
        }

        //把从数据库中取得的数据一一写入excel文件中
        Row row = null;
        Iterator<UserBaseLighting> it = userList.iterator();
        int i=0;
        while(it.hasNext()){

        //for (int i = 0; i < userList.size(); i++) {
            //创建list.size()行数据
            row = userSheet.createRow(i + 1);
            //把值一一写进单元格里
            //设置第一列为自动递增的序号
            row.createCell(0).setCellValue(i + 1);
            UserBaseLighting user=it.next();
            row.createCell(1).setCellValue(user.getCreateTime());
            row.createCell(2).setCellValue(user.getGid());
            row.createCell(3).setCellValue(user.getMobile());
            row.createCell(4).setCellValue(user.getIdCard());
            row.createCell(5).setCellValue(user.getName());
            row.createCell(6).setCellValue("");//卡号
            if (user.getUserSource() == 0) {
                row.createCell(7).setCellValue("INVESTOR");
            } else {
                row.createCell(7).setCellValue("BORROWER");
            }
            row.createCell(8).setCellValue("CREDIT_ASSIGNMENT&TENDER");
            row.createCell(9).setCellValue("PRC_ID");
            logger.info("##create cell No={}",i);
            it.remove();
            i++;
        }

        //设置单元格宽度自适应，在此基础上把宽度调至1.5倍
        userSheet.trackAllColumnsForAutoSizing();
        for (int j = 0; j< title.length; j++) {
            userSheet.autoSizeColumn(j, true);
            userSheet.setColumnWidth(j, userSheet.getColumnWidth(i) * 15 / 10);
        }

    }

 @Override
    @Async
    public void multiSheetExportData2Excel() {
        ClassPathResource cl = new ClassPathResource("export");
        String savePath = cl.getPath();
        //创建文件保存的目录
        File folder = new File(savePath);
        //如果文件夹不存在创建对应的文件夹
        if (!folder.exists()) {
            folder.mkdirs();
        } else {
            File[] filePaths = folder.listFiles();
            for (File f : filePaths) {
                if (f.isFile()) {
                    f.delete();
                }
            }
        }
        List<UserBaseLighting> userBaseList = new ArrayList<>();
        int count = userBaseDao.countAllUserBase();
        int pageSize = 1000000;
        int totalPage = count / pageSize;
        SXSSFWorkbook workbook = new SXSSFWorkbook(10000);
        PoiExcelUtil excelUtil = new PoiExcelUtil();
        OutputStream fileOut = null;
        try {
            if (totalPage <= 1) {
                userBaseList = userBaseDao.getAllUserBase(null, null);
                excelUtil.userList2Excel(workbook, userBaseList, 1);
            } else {
               for (int page = 1; page <= totalPage + 1; page++) {
                    userBaseList = userBaseDao.getAllUserBase((page - 1) * pageSize, pageSize);
                    excelUtil.userList2Excel(workbook, userBaseList, page);
                    userBaseList=null;
                }
            }
            savePath = cl.getPath() + File.separator + "存量用户.xlsx";
            fileOut = new FileOutputStream(savePath);
            workbook.write(fileOut);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (fileOut != null) {
                try {
                    fileOut.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }

            }
        }
    }