导出大数据量excel文件——poi的SXSSFWorkbook对象使用

一、适用场景

       使用poi所供的api可以实现excel文件的导入导出，其中org.apache.poi.ss.usermodel.Workbook对象可以满足工作簿的创建以及通过excel模板创建工作簿。但是这个对象及相关api的实现原理是一次性将数据读取到内存中，然后写入excel文件，这样当数据量大时，会出现内存溢出。在这种情况下可以使用org.apache.poi.xssf.streaming.SXSSFWorkbook对象来完成工作簿的创建。

 

二、实现原理

       使用SXSSFWorkbook wb = new SXSSFWorkbook(100)创建的工作簿在读取数据时，会根据所传入的阈值（此处是100）。当内存中的对象达到这个阈值时，生成一个临时文件，以临时文件进行存储，来实现分段读取与写入。

 

三、代码

       以下两部分代码分别从控制层和业务层提取，在业务层完成了SXSSFWorkbook对象的创建，并读取通过for循环构造的测试数据完成了sheet和cell部分的赋值，最后在控制层调用业务层的方法，获取返回的对象SXSSFWorkbook，并通过io流完成excel文件的导出。

controller：

    /**
     * 导出大批量数据的excel
     * @param response HttpServletResponse
     */
    @RequestMapping(value = {"/exportBigDataFile.action"}, method = RequestMethod.GET)
    public void exportBigDataFile(HttpServletResponse response) {
        try {
            // 獲取工作表
            Workbook workbook = exportExcelService.exportBigDataExcel();
            // 完成下載
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            workbook.write(os);

            downFile(os, response);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 抽取下载部分的公共代码
     * @param os ByteArrayOutputStream
     * @param response HttpServletResponse
     * @throws IOException
     */
    public void downFile(ByteArrayOutputStream os, HttpServletResponse response) throws IOException {
        response.setContentType("application/octet-stream");
        response.setCharacterEncoding("utf-8");
        response.addHeader("Content-Disposition", "attachment;filename=" + "test.xlsx");
        ServletOutputStream outputStream = response.getOutputStream();
        os.writeTo(outputStream);
        os.close();
        outputStream.flush();
    }

service:

    /**
     * 导出大数据量excel文件(该方式不能使用模板进行excel导出)
     * @return SXSSFWorkbook
     */
    @Override
    public SXSSFWorkbook exportBigDataExcel() {
        // 1.获取数据
        List<Employee> employeeList = getEmployeeList(999999);
        // 2.创建工作簿
        // 阈值，内存中的对象数量最大值，超过这个值会生成一个临时文件存放到硬盘中
        SXSSFWorkbook wb = new SXSSFWorkbook(100);
        Sheet sheet = wb.createSheet();
        // 标题
        String[] titles = {"编号", "名字", "性别", "部门", "职位", "直系领导", "月薪", "入职日期", "年假天数"};
        Row titleRow = sheet.createRow(0);
        for (int i = 0; i < titles.length; i++) {
            Cell cell = titleRow.createCell(i);
            cell.setCellValue(titles[i]);
        }
        // 3.从集合中取数据并赋值
        for (int i = 0; i < employeeList.size(); i++) {
            Employee employee = employeeList.get(i);
            Row row = sheet.createRow(i+1);
            row.createCell(0).setCellValue(employee.getCode());
            row.createCell(1).setCellValue(employee.getName());
            row.createCell(2).setCellValue(employee.getSex());
            row.createCell(3).setCellValue(employee.getDept());
            row.createCell(4).setCellValue(employee.getPosition());
            row.createCell(5).setCellValue(employee.getLeaderName());
            row.createCell(6).setCellValue(employee.getSalary());
            row.createCell(7).setCellValue(employee.getInDateStr());
            row.createCell(8).setCellValue(employee.getHolidayCount());
        }
        return wb;
    }

    /**
     * 获取模板数据
     * @return List
     * @param row 生成多少行数据
     */
    public List<Employee> getEmployeeList(int row) {
        List<Employee> employeeList = new ArrayList<>();
        for (int i = 0; i < row; i++) {
            Employee employee = new Employee();
            employee.setCode(100 + i);
            employee.setName("名字" + i);
            employee.setDept("牛棚");
            employee.setHolidayCount(2 + i);
            employee.setInDateStr("2018-01-02");
            employee.setLeaderName("张四");
            employee.setPosition("工程师");
            employee.setSalary(3000.0);
            employee.setSex("男");
            employeeList.add(employee);
        }
        return employeeList;
    }

实体类：

public class Employee {
    /**
     * 编号
     */
    private Integer code;

    /**
     * 姓名
     */
    private String name;

    /**
     * 性别
     */
    private String sex;

    /**
     * 部门
     */
    private String dept;

    /**
     * 职位
     */
    private String position;

    /**
     * 直系领导姓名
     */
    private String leaderName;

    /**
     * 月薪
     */
    private Double salary;

    /**
     * 入职日期
     */
    private String inDateStr;

    /**
     * 年假天数
     */
    private Integer holidayCount;
}