前言

最近公司有一个云服务涉及到了将设备数据导出到excel并从浏览器弹出下载的需求，项目原先使用的是poi-3.17版本的HSSFWorkbook 实现导出的。但是最近数据量增大频频出问题，高于5W行的导出几乎必然报错。领导交给我来跟踪，以前也了解过但一知半解，这次好好整理总结一下，有问题欢迎提出，共同进步。

一. POI处理excel简单介绍

首先，apache-poi官方提供了HSSF、XSSF、SXSSF三种方式来操作Excel。
HSSF：操作 excel97-2003版本，扩展名为 .xls ;
XSSF：操作 excel2007及更高版本，扩展名为 .xlsx。
SXSSF：是在XSSF的基础上，从poi-3.8版本后开始出现的处理内存占用的一种高效导出excel方式。

另外，了解以下知识点：
① 支持的行数、列数

excel2003版本，一个shee最大行数为65536，最大列数256。
excel2007版本开始，一个sheet最大行数1048576，最大列数16384。

② 文件大小

.xlsx格式的文件比 .xls格式的压缩率高，即数据量一样得情况下，.xlsx的文件更小。

③ 兼容性

excel2003版本不支持打开 .xlsx文件；
excel2007开始的版本向下兼容，支持打开 .xls文件。

④ 三者各自对应的类
HSSF对应： > HSSFWorkbook、HSSFSheet、HSSFRow、HSSFCell……
XSSF对应：> XSSFWorkbook、XSSFSheet、XSSFRow、XSSFCell……
SXSSF对应：> SXSSFWorkbook、SXSSFSheet、SXSSFRow、SXSSFCell……

二. poi导出excel的代码

这里直接放上 SXSSF导出的代码：

// 新的导出方法（使用SXSSFWorkbook导出）
    public void excelExport() {
        // 导出前设置session中exportedFlag任意值，导出后清空
        super.getSession().setAttribute("exportedFlag", "false");

        SXSSFWorkbook workbook = null;
        workbook = new SXSSFWorkbook(100);//默认SXSSFWorkbook只会保留100条数据在内存中，避免内存溢出,旧的会刷新到磁盘中
        workbook.setCompressTempFiles(true); //压缩临时文件，很重要，否则磁盘很快就会被写满
        SXSSFSheet sheet = workbook.createSheet("设备信息");
        try {
            String genDir = getRequest().getSession().getServletContext().getRealPath("/excel_gen");
            logger.info("genDir: " + genDir);

            String filename = UUID.randomUUID().toString() + ".xlsx";
               新建行头标题
            SXSSFRow headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue("id");
            headerRow.createCell(1).setCellValue("companyName ");
            headerRow.createCell(2).setCellValue("Device_Model");//设备型号
            headerRow.createCell(3).setCellValue("System_version");
            headerRow.createCell(4).setCellValue("FirmWare_version");
            ...
            
            List<String> deviceIds = new ArrayList<String>();  // 循环写入数据
            int count = 0;  // 统计每次循环写入行数 ， 累加创建Row
            for(int k=0;;k++) { // 此处k无边界条件，因为未知数据量，当查询rows为空时break退出循环
                logger.info("进入分页查询for循环：k = "+k);
                DeviceForm form = (DeviceForm) session.get("_device_query_form");
                form.setPage(k+1);
                form.setRows(10000);//
                String strTimeZone = getForm().getTimeZone();
                int timezone = Integer.parseInt(strTimeZone);
                long startTime1 = System.currentTimeMillis();
                JSONObject jsonObject = JSONObject.parseObject(getService().query(form)); // 字符串转对象
                long endTime1 = System.currentTimeMillis();

                JSONArray rows = jsonObject.getJSONArray("rows");

                if (rows.isEmpty()) {
                    logger.info("查询rows为空，结束分页查询for循环——>excelExport k=" +  k );
                    break;
                }else{
                    String companyName = "";
                    String deviceModel = "";
                    String operatingSystemInfo = "";
                    String firmwareVersion = "";
                    String id = "";
                    
                    for (int i = 0; i < rows.size(); i++) {
                        JSONObject row = rows.getJSONObject(i);
                        try {

                            SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
                            companyName = row.containsKey("companyName") ? row.getString("companyName") : "";
                            deviceModel = row.containsKey("deviceModel") ? row.getString("deviceModel") : "";
                            firmwareVersion = row.containsKey("firmwareVersion") ? row.getString("firmwareVersion") : "";
                            id = row.getString("id");
                            operatingSystemInfo = row.containsKey("operatingSystemInfo") ? row.getString("operatingSystemInfo") : "";

                        } catch (Exception e) {              
                            log.error(e);
                        }

                        SXSSFRow excelRow = sheet.createRow(i + 1 + count);
                        excelRow.createCell(0).setCellValue(id);
                        excelRow.createCell(1).setCellValue(companyName );
                        excelRow.createCell(2).setCellValue(deviceModel);
                        excelRow.createCell(3).setCellValue(operatingSystemInfo);
                        excelRow.createCell(4).setCellValue(firmwareVersion);  // 固件版本
                        
                        deviceIds.add(id);
                    }
                    count += rows.size();
                }
                logger.info("当前页循环结束，after generate excel data , 数据总行数为: "+ count);

            }
            // 设置响应头为——>下载文件
            HttpServletResponse response = ServletActionContext.getResponse(); // Content-Disposition文件下载的方式；text/plain输出到浏览器
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(filename.getBytes(StandardCharsets.UTF_8)));
            response.addHeader("Pragma", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
            response.setContentType("application/octet-stream;charset=UTF-8");
            ServletOutputStream out = response.getOutputStream(); // 表格对象注入Stream流中
            try {
                logger.info("一次性写入文件...");
                workbook.write(out);  // 同一时刻workbook只有100行数据在内存，超过就将旧的写到磁盘里减少内存占用
                out.flush();  // 强制清空缓存
                logger.info("after write excel file..." + filename);
            } catch (IOException e) {
                e.printStackTrace();
                logger.error("workbook write out error: ", e);
                log.error(e.getMessage(), e);
            } finally {
                if (out != null) {
                    try {
                        out.close(); // 流关闭
                    } catch (IOException e) {
                        logger.error("out stream close error：", e);
                    }
                }
                workbook.dispose();// 释放workbook所占用的所有windows资源
            }
//            try {
//                renderAttachment(filename, "application/octet-stream", FileUtils.readFileToByteArray(genFile));
//            } catch (IOException e) {
//                log.error(e);
//            }
            logger.info("after return to browser...");
            super.getSession().removeAttribute("exportedFlag");
        } catch (Exception e) {
            e.printStackTrace();
            log.error(e.getMessage(), e);
        }
    }

注：里面部分涉及到项目相关的处理逻辑，注意辨别。
① 开始时设置的导出前session中exportedFlag任意值，导出后清空，是为了web端获取导出是否成功信息，与poi本身导出逻辑无关；
② 文件名、路径自定义；excel列命名、导出字段自定义；
③ 考虑到数据量大sql查询会比较耗时，故采用了for循环分批查询，每次查询1W数据，这里也与poi导出逻辑无关；
④ getService.query(form)，指向的方法，同样是项目自己定义的方法及实现逻辑，这里就不贴出，无非就是根据form、sql去调用底层的jdbctemplate.query()方法查询返回结果集。