如何将几十万的数据高效的导出到excel中

参考文章：

excel 大量数据导出：
POI之SXSSFWorkbook大量数据导出至excel 
https://blog.csdn.net/k_520_w/article/details/84404652
https://blog.csdn.net/z69183787/article/details/52045844
https://www.jianshu.com/p/aeb578844e5c?open_source=weibo_search

java导出excel弹出下载框
http://www.360doc.com/content/17/0625/18/29229598_666467187.shtml

思路：

一、同步导出

1、分页查询数据

2、将数据通过poi SXSSFWorkbook类写入excel，并可持久化到磁盘，防止内存溢出

3、多线程 TODO

4、导出时，通知用户，导出到哪一行了 TODO

      a、可通过websocket通知

      b、算法: (当前值/总大小值) *(100/100)

      c、下载文件设置显示进度：https://blog.csdn.net/mynamepg/article/details/79801413

5、提示用户导出所需的大概时间（每毫秒所需时间=总时间（毫秒）/总导出记录数）

6、导出数量的控制（比如1w条）

二、异步导出 TODO

三、注意点：

1、poi 3.8 SXSSFWorkbook没有删除本地缓存文件方法

2、excel文件写性能优化

3、同步导出时，nginx超时时间设置：

  location /xxxx/ {
                proxy_pass http://localhost:11111/xxxx/xxxx/;
                proxy_redirect              off;
                proxy_set_header            Host $host;
                proxy_set_header            X-real-ip $remote_addr;
                proxy_set_header            X-Forwarded-For $proxy_add_x_forwarded_for;
                proxy_send_timeout 1200;
                proxy_read_timeout 1200;
        }

四、源码 链接: https://pan.baidu.com/s/1FS6gY_Ybh5aBGJT_KrDbkg 密码: 69t7

1、实现代码,

 

   /**
     * 自定义字段的导出
     */
    @RequestMapping("/xxxx")
    @ResponseBody
    public void xxxx(HttpServletRequest request, HttpServletResponse response) {
        Result result = new Result();
        OutputStream ouputStream = null;
        InputStream inputStream = null;
        XSSFWorkbook xssfWorkbook = null;
        SXSSFWorkbook sxssfWorkbook = null;
        long startTime=System.currentTimeMillis();
        try {
            Map<String, String> paramMap = this.getParamMap(request);
            logger.warn("导出excel原始请求参数:" + paramMap);
            String ip = getIpAddr(request);
            String sessionId = getCookieValue(request);
            
            inputStream = this.getClass().getResourceAsStream("/xxx.xlsx");
            xssfWorkbook = new XSSFWorkbook(inputStream);//既可读、也可写
            XSSFSheet xssfSheet = xssfWorkbook.getSheetAt(0);
            //设置表头
            XSSFRow row9 = xssfSheet.getRow(9);
            XSSFCell cell9_1Style = row9.getCell(1);
            CellStyle cellStyle = cell9_1Style.getCellStyle();
            //设置内容样式
            CellStyle style = xssfWorkbook.createCellStyle();
            Font font = xssfWorkbook.createFont();
            font.setFontHeightInPoints((short) 10);
            font.setFontName("微软雅黑");
            style.setFont(font);

            CellStyle style2 = xssfWorkbook.createCellStyle();
            style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
            style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
            style2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
            style2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框
            style2.setTopBorderColor(HSSFColor.WHITE.index);
            style2.setBottomBorderColor(HSSFColor.WHITE.index);
            style2.setLeftBorderColor(HSSFColor.WHITE.index);
            style2.setRightBorderColor(HSSFColor.WHITE.index);
            // 设置账号和时间
            XSSFRow row6 = xssfSheet.getRow(6);
            XSSFCell cell6_12 = row6.createCell(12);
            cell6_12.setCellValue("导出账号:" + mobile);
            cell6_12.setCellStyle(style2);
            //设置时间
            XSSFRow row7 = xssfSheet.getRow(7);
            XSSFCell cell7_12 = row7.createCell(12);
            String date = DateUtil.Date2String(new Date(), DateUtil.DATE_FORMAT);
            cell7_12.setCellValue("导出日期:" + date);
            cell7_12.setCellStyle(style2);

            String[] keyArr = paramMap.get("myMap").split(",");
            xssfSheet.removeRow(row9);
            row9 = xssfSheet.createRow(9);
            int x = 1;
            XSSFCell cell9_0 = row9.createCell(0);
            cell9_0.setCellValue("序号");
            cell9_0.setCellStyle(cellStyle);
            for (String s : keyArr) {
                XSSFCell cell9_n = row9.createCell(x);
                cell9_n.setCellStyle(cellStyle);
                cell9_n.setCellValue(xxx.get(s));
                x++;
            }
            for (int y = x; y < 31; y++) {
                XSSFCell cell9_y = row9.createCell(y);
                cell9_y.setCellStyle(cellStyle);
                cell9_y.setCellValue("");
            }
            sxssfWorkbook = new SXSSFWorkbook(xssfWorkbook);//SXSSFWorkbook用来写数据的
            sxssfWorkbook.setCompressTempFiles(true);
            SXSSFSheet sxssfSheet = sxssfWorkbook.getSheetAt(0);
            paramMap.remove("myMap");
            paramMap.remove("pageNum");
            paramMap.put("xxx","xxxx");
            Pager<DataVo> firstPager = this.searchApi.list(paramMap,1);
            int totalPage = firstPager.getTotalPage();
            //设置内容
            int i = 10;
            int m = 1;
            logger.warn("请求总页数:"+totalPage);
            for (int currPage = 1; currPage <= totalPage; currPage++) {
                logger.warn("请求第"+currPage+"页");
                List<xxx> xxxx= null;
                if(currPage==1){
                    xxxx= firstPager.getRows();
                }else{
                   xxxx= this.searchApi.list(paramMap,currPage).getRows();
                }
                if(CollectionUtils.isEmpty(dataVos)){
                    logger.warn("第"+currPage+"页无数据");
                    continue;
                }
                List<Product> list;

                for (xxx xxx: list) {
                    sxssfSheet.createRow(i);
                    SXSSFRow row = sxssfSheet.getRow(i);
                    SXSSFCell cell0 = row.createCell(0);
                    cell0.setCellValue(m);
                    cell0.setCellStyle(style);
                    int y = 1;
                    for (String key : keyArr) {
                        SXSSFCell cell = row.createCell(y);
  
                        y++;
                    }
                    i++;
                    m++;
                }
            }
        } catch (Exception e) {
            logger.error("导出数据异常1", e);
            sxssfWorkbook.removeSheetAt(0);
            sxssfWorkbook.createSheet().createRow(0).createCell(0).setCellValue("导出数据失败，请稍后再试");
        }
        try {
            response.setContentType("application/vnd.ms-excel;");
            response.setHeader("Content-disposition",
                    "attachment;filename=" + new String("批量导出.xlsx".getBytes("GB2312"), "ISO8859_1"));// 设定输出文件头
            ouputStream = response.getOutputStream();
            sxssfWorkbook.write(ouputStream);
            ouputStream.flush();
        } catch (Exception e) {
            logger.error("导出数据异常2", e);
        } finally {
            if (sxssfWorkbook != null) {
                sxssfWorkbook.dispose();
            }
            closeIOs(ouputStream, inputStream, sxssfWorkbook, xssfWorkbook);
        }
        logger.warn("当前程序耗时："+(System.currentTimeMillis()-startTime)/1000+"s");
    }

    private void closeIOs(Closeable... closeables) {
        for (Closeable closeable : closeables) {
            if (closeable != null) {
                try {
                    closeable.close();
                } catch (IOException e) {
                    logger.error("io关闭异常", e);
                }
            }
        }
    }