使用POI导出千万数据到Excel

导出的Excel可以有两种格式：Excel 2003、Excel 2007，二者的每个sheet下的行列限制如下：

版本	行数	列数	生成文件后缀
2003	65535	256	.xls
2007	1048576	16384	.xlsx

当在POI中使用HSSF对象时，导出的Excel版本是2003版本，Excel 2003最多只允许存储65535条数据，一般用来处理较少的数据量。这时对于千万级别数据，Excel肯定容纳不了。

当在POI中使用XSSF对象时，它可以直接支持2007以上版本，因为它采用ooxml格式。这时Excel可以支持1048576条数据，单个sheet表就支持近104万条数据了，虽然这时导出100万数据能满足要求，但使用XSSF测试后发现偶尔还是会发生堆溢出并且效率很低，所以也不适合千万数据的导出。

在POI 3.8之后新增加了一个类：SXSSFWorkbook。SXSSF包是XSSF的一个扩展版本，支持流处理，在生成大数据量的电子表格且堆空间有限时使用。SXSSF通过限制内存中可访问的记录行数来实现其低内存利用，当达到限定值时，新一行数据的加入会引起老一行的数据刷新到硬盘。比如内存中限制行数为100，当行号到达101时，行号为0的记录刷新到硬盘并从内存中删除，当行号到达102时，行号为1的记录刷新到硬盘，并从内存中删除，以此类推。rowAccessWindowSize代表指定的内存中缓存记录数，默认为100，此值可以通过new SXSSFWorkbook(int rowAccessWindowSize)或SXSSFSheet.setRandomAccessWindowSize(intwindowSize)来设置。

       SXSSF在把内存数据刷新到硬盘时，是把每个SHEET生成一个临时文件，这个临时文件可能会很大，有可以会达到G级别，如果文件的过大对你来说是一个问题，你可以使用wb.setCompressTempFiles(true);方法让SXSSF来进行压缩，当然性能也会有一定的影响。

综上，我这里使用SXXFWorkBook来实现千万级别数据量的导出。我这里单表数据量为13075172。

主要代码如下：

package com.wuychn.controller;

import com.wuychn.entity.User;
import com.wuychn.service.UserService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.GetMapping;

import javax.servlet.http.HttpServletResponse;
import java.io.OutputStream;
import java.util.List;

@Controller
@Slf4j
public class ExportController {

    @Autowired
    private UserService userService;

    @GetMapping("/export")
    public void export(HttpServletResponse response) {
        log.info("开始导出.........................................");
        long startTime = System.currentTimeMillis();
        // 内存中保存10000行，超过的实时写入磁盘，保证内存消耗不会过大
        final int rowAccessWindowSize = 10000;
        try (SXSSFWorkbook workbook = new SXSSFWorkbook(rowAccessWindowSize)) {
            export(workbook);
            writeAndClose(response, workbook);
            long endTime = System.currentTimeMillis();
            log.info("导出完成，用时：" + (endTime - startTime) / 1000 + "秒");
        } catch (Exception e) {
            log.error("导出失败: ", e);
        }
    }

    public void export(SXSSFWorkbook workbook) {
        // 标题
        final String[] titleArr = {"主键", "姓名", "年龄", "性别", "地址"};
        // 结束标识。由于大量数据时count()很慢，所以不查询总数，一直往下查，直到查询的数据量小于每页数据
        boolean end = false;
        // 查询的次数
        int i = 1;
        // 每次查询数据库的条数
        final int pageSize = 100000;
        // 每个sheet的总行数,超过这个行数就会新建一个sheet
        final int sheetTotalRow = 1000000;
        // 当前的行号
        int currentRow = 0;
        // sheet的数量
        int sheetNum = 0;
        // 从数据库中查询的数据
        List<User> users = null;
        // sheet对象
        Sheet sheet = null;
        while (!end) {
            log.info("第【" + i + "】次查询数据库.............");
            users = userService.findAll((i - 1) * pageSize, pageSize);
            if (CollectionUtils.isEmpty(users)) {
                return;
            }
            if (users.size() < pageSize) {
                end = true;
            }
            i++;
            for (int j = 0; j < users.size(); j++) {
                // 每一百万行,创建一个sheet
                if (sheet == null || currentRow / sheetTotalRow > 0) {
                    currentRow = 0;
                    sheetNum++;
                    sheet = workbook.createSheet("用户清单" + sheetNum);
                    Row titleRow = sheet.createRow(currentRow);
                    for (int v = 0; v < titleArr.length; v++) {
                        Cell cell = titleRow.createCell(v);
                        cell.setCellValue(titleArr[v]);
                    }
                }
                currentRow++;
                User user = users.get(j);
                Row dataRow = sheet.createRow(currentRow);
                for (int q = 0; q < titleArr.length; q++) {
                    Cell cell = dataRow.createCell(q);
                    switch (q) {
                        case 0:
                            cell.setCellValue(user.getId());
                            break;
                        case 1:
                            cell.setCellValue(user.getName());
                            break;
                        case 2:
                            cell.setCellValue(user.getAge());
                            break;
                        case 3:
                            cell.setCellValue(user.getSex());
                            break;
                        case 4:
                            cell.setCellValue(user.getAddress());
                            break;
                    }
                }
                // 弱化引用,为了让对象尽快被回收,参考ArrayList的clear()方法
                user = null;
            }
        }
    }

    private void writeAndClose(HttpServletResponse response, SXSSFWorkbook wb) {
        String fileName = "用户.xlsx";
        try (OutputStream os = response.getOutputStream()) {
            setResponseHeader(response, fileName);
            wb.write(os);
            os.flush();
            // 此方法能够删除导出过程中生成的xml临时文件
            wb.dispose();
        } catch (Exception e) {
            log.error("导出失败:", e);
        }
    }

    public void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            response.setContentType("application/octet-stream;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + new String(fileName.getBytes("UTF-8"), "ISO-8859-1"));
            response.addHeader("Pargam", "no-cache");
            response.addHeader("Cache-Control", "no-cache");
        } catch (Exception e) {
            log.error("导出失败:", e);
        }
    }

}

导出的时候，使用jconsole查看内存、CPU的使用情况：

可见还是比较平稳，内存占用也不是太大。经过测试，导出13075172条数据耗时22分钟（包括110多次数据查询时间），导出的Excel文件大小为261MB，共14个sheet，前13个sheet每个sheet有一百万行数据，最后一个sheet有75172行数据：

问题，当后台长时间没有响应，HTTP超时怎么办？

答案是不会超时，因为后台是每10000条数据就会写入一次到临时文件中，而且每个sheet也会写入到临时文件，所以浏览器和后台没有出现长时间没有通信，所以不会超时。另外，由于是分批查询数据，并且内存中的数据行也限制在了10000行，所以也不会出现内存溢出的问题。

参考：

https://blog.csdn.net/z69183787/article/details/81102352

https://www.jianshu.com/p/6c000a9fd2bf

https://www.cnblogs.com/UncleWang001/p/10106859.html

https://blog.csdn.net/ZHOU_VIP/article/details/88222364