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

你的名字 2022-01-27 04:21 933阅读 0赞

**目录**

1 适用场景

2 实现原理

3 代码

3.1 Controller

3.2 Service

3.3 实体类

--------------------

## 1 适用场景 ##

使用POI提供的API可以实现Excel文件的导入导出，其中org.apache.poi.ss.usermodel.Workbook对象可以满足工作簿的创建以及通过excel模板创建工作簿。但是这个对象及相关API的实现原理是一次性将数据写入内存，再生成Excel文件。这样当数据量大时，会出现内存溢出。在这种情况下不推荐修改JVM的配置，可以使用org.apache.poi.xssf.streaming.SXSSFWorkbook对象来完成工作簿的创建。

## 2 实现原理 ##

SXSSFWorkbook类的构造函数参数为一个int类型，在这里表示一个阈值。比如设置阈值为100，通过创建的对象读取数据，每当内存中的对象数量达到100这个阈值，就会生成一个临时文件，以临时文件存储，实现分段读写。

## 3 代码 ##

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

### 3.1 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();
        }

### 3.2 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;
        }

### 3.3 实体类 ###

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;
    }