Java解决Excel导出大批量数据（附上测试代码）

记录一次项目中使用POI导出Excel报错的修改方案。
参考了作者：happyljw的文章 JAVA使用POI如何导出百万级别数据，对代码进行封装扩展。由于项目时间太紧，并未对写的代码进行严格测试，遇到问题的朋友可以一起探讨。
修改结果：100万条数据导出花费58s。比之前效率高多了。

package star;

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.junit.Test;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

/**
 * @author :jinxin_itmaster@163.com
 * @date :2018年3月2日 下午4:24:53
 */
public class TestExcelImport {

/**
     * 测试导出执行器
     *
     * @param <T>
     */
    static class ExcelExportExecutor<T> {

        /**
         * 工作簿
         */
        private SXSSFWorkbook wb = new SXSSFWorkbook(100);
        /**
         * 表格
         */
        private Sheet sheet;
        /**
         * 原始数据list
         */
        private List<T> data;
        /**
         * 分批次写入数据，分页条数
         */
        private int pageSize = 5;
        /**
         * 当前输出的列
         */
        private int currentRow = 0;
        /**
         * 数据行起始行数
         */
        private int dataRowStart = 1;
        /**
         * 表头
         */
        private String[] rowHeader;
        /**
         * 是否开启row状态监听
         */
        private boolean watcherRowStatus;
        /**
         * 导入row状态监听器
         */
        private ExcelExportRowStatusListener rowStatusListener;


        ExcelExportExecutor(String[] rowHeader, List<T> data) {
            this();
            this.rowHeader = rowHeader;
            this.data = data;
        }

        ExcelExportExecutor(String[] rowHeader, List<T> data, DataSheetExecute<T> executorListener) {
            this(rowHeader, data);
            this.executorListener = executorListener;
        }

        ExcelExportExecutor(String[] rowHeader
                , List<T> data
                , DataSheetExecute<T> executorListener
                , boolean watcherRowStatus
                , ExcelExportRowStatusListener rowStatusListener) {
            this(rowHeader, data, executorListener);
            this.watcherRowStatus = watcherRowStatus;
            this.rowStatusListener = rowStatusListener;
        }


        ExcelExportExecutor() {
            this.sheet = this.wb.createSheet();
        }

        /**
         * 数据切割时的数据设置执行器
         */
        private DataSheetExecute<T> executorListener;

        public void setExecutorListener(DataSheetExecute<T> executorListener) {
            this.executorListener = executorListener;
        }


        void execute() {
            List<T> data = this.data;
            /**
             * 处理表头
             */
            if (this.executorListener == null) {
                throw new RuntimeException("未设置执行处理器");
            }
            /**
             * 设置表头
             */
            Row row = this.sheet.createRow(this.dataRowStart - 1);
            for (int i = 0; i < rowHeader.length; i++) {
                row.createCell(i).setCellValue(rowHeader[i]);
            }
            if (data == null) {
                throw new RuntimeException("无效的数据");
            }

            int size = data.size();
            /**
             * 导出总条数小于分页条数时，则直接导入，不用分页导入
             */
            if (size < pageSize) {
                Row tmpRow;
                for (int i = 0; i < size; i++) {
                    tmpRow = sheet.createRow(this.dataRowStart + i);
                    this.executorListener.execute(tmpRow, data.get(i));
                }
                data.clear();
            } else if (pageSize > 0 && size >= pageSize) {
                /*
                 * 数据总条数
                 */
                int listSize = data.size();
                /*
                 * 分批次导入数据次数
                 */
                int batchSize = listSize / pageSize;
                /*
                 * 分批次后，剩余的记录条数
                 */
                int remain = listSize % pageSize;
                List<T> tmp;

                for (int i = 0; i < batchSize; i++) {
                    /*
                     * 此处快被搞疯了，list.subList(from,to)方法太坑，截取到的是list的视图。
                     * 如果tmp list在后边clear的话，就会导致data数据丢失，data的长度会逐渐减少pageSize个。
                     * 导致suList方法会报异常。于是就将代码改为subList(0,pageSize)。
                     */

                    /*
                     * 将原始list数据按照pageSize分批截取
                     */
                    tmp = data.subList(0, pageSize);
                    int len = tmp.size();
                    Row tmpRow;
                    for (int j = 0; j < len; j++) {
                        /*
                         * 记录当前执行到哪一行了
                         */
                        currentRow = i * pageSize + j + 1;
                         /*
                         * 创建一个row
                         */
                        tmpRow = sheet.createRow(currentRow);
                        if (watcherRowStatus && rowStatusListener != null) {
                            rowStatusListener.listen(tmpRow, currentRow);
                        }
                        /*
                         * 将list中的数据对象设置到指定的row中
                         */
                        this.executorListener.execute(tmpRow, tmp.get(j));
                    }
                    /**
                     * 清除缓存list，优化内存
                     */
                    tmp.clear();
                }
                if (remain > 0) {
                    tmp = data.subList(0, remain);
                    int len = tmp.size();
                    Row tmpRow;
                    for (int j = 0; j < len; j++) {
                        tmpRow = sheet.createRow(currentRow + j + 1);
                        this.executorListener.execute(tmpRow, tmp.get(j));
                    }
                    /**
                     * 清除缓存list，优化内存
                     */
                    tmp.clear();
                }
            }
            OutputStream outputStream = null;
            try {
                executorListener.writeExcel(this.wb, outputStream);
            } catch (Exception e) {
                e.printStackTrace();
            } finally {
                /*
                 * 确保自定义保存后忘记调用dispose
                 */
                if (this.wb != null) {
                    this.wb.dispose();
                }
                if (outputStream != null) {
                    try {
                        outputStream.close();
                    } catch (IOException e) {
                        System.out.println("关闭输出流失败");
                    }

                }
            }
        }
    }

    /**
     * 数据导出
     *
     * @param <T>
     */

    interface DataSheetExecute<T> {
        /**
         * 导出数据时，将泛型T设置到row中的每个cell内
         *
         * @param row 当前row对象
         * @param t   泛型T对象
         */
        void execute(Row row, T t);

        /**
         * 输出Excel，自定义实现输出位置（保存到本地，还是上传到其他位置）
         *
         * @param workbook 生成好的excel文档薄
         */
        void writeExcel(SXSSFWorkbook workbook, OutputStream outputStream) throws Exception;
    }

    /**
     * 导入记录条数状态监听，也可以对row对象进行格式化操作。
     */
    interface ExcelExportRowStatusListener {
        void listen(Row row, int rows);
    }

    /**
     * 测试bean
     */
    class PersonUser {

        private String name;
        private String address;
        private String sex;
        private String phone;
        private String group;
        private String id;
        private String area;
        private String school;
        private String stat;
        private String score;

        public PersonUser(String name, String address, String sex, String phone, String group, String id, String area, String school, String stat, String score) {
            this.name = name;
            this.address = address;
            this.sex = sex;
            this.phone = phone;
            this.group = group;
            this.id = id;
            this.area = area;
            this.school = school;
            this.stat = stat;
            this.score = score;
        }

        public String getPhone() {
            return phone;
        }

        public void setPhone(String phone) {
            this.phone = phone;
        }

        public String getGroup() {
            return group;
        }

        public void setGroup(String group) {
            this.group = group;
        }

        public String getId() {
            return id;
        }

        public void setId(String id) {
            this.id = id;
        }

        public String getArea() {
            return area;
        }

        public void setArea(String area) {
            this.area = area;
        }

        public String getSchool() {
            return school;
        }

        public void setSchool(String school) {
            this.school = school;
        }

        public String getStat() {
            return stat;
        }

        public void setStat(String stat) {
            this.stat = stat;
        }

        public String getScore() {
            return score;
        }

        public void setScore(String score) {
            this.score = score;
        }

        public String getName() {
            return name;
        }

        public void setName(String name) {
            this.name = name;
        }

        public String getAddress() {
            return address;
        }

        public void setAddress(String address) {
            this.address = address;
        }

        public String getSex() {
            return sex;
        }

        public void setSex(String sex) {
            this.sex = sex;
        }

       @Override
        public String toString() {
            return "PersonUser{" +
                    "name='" + name + '\'' +
                    ", address='" + address + '\'' +
                    ", sex='" + sex + '\'' +
                    ", phone='" + phone + '\'' +
                    ", group='" + group + '\'' +
                    ", id='" + id + '\'' +
                    ", area='" + area + '\'' +
                    ", school='" + school + '\'' +
                    ", stat='" + stat + '\'' +
                    ", score='" + score + '\'' +
                    '}';
        }
    }

    /**
     * 测试导出方法
     */
    @Test
    public void testExport() {

        /*
          制造数据
         */
        List<PersonUser> data = new ArrayList<PersonUser>();
        for (int i = 0; i < 2000; i++) {
            data.add(new PersonUser("金XX:" + i
                    , "北京市朝阳区:" + i
                    , "男:" + i
                    , "137****2152:" + i
                    , "开发组：" + i
                    , "" + i + 1
                    , "华北地区：" + i
                    , "北京大学：" + i
                    , "已晋级：" + i
                    , "1021:" + i));
        }
        /*
         * 文件输出到磁盘的位置
         */
        final String filePath = "E:\\3.xlsx";

        new ExcelExportExecutor<PersonUser>(
                new String[]{"姓名", "地址", "性别", "手机号", "组别", "身份证号", "地区", "学校/机构", "晋级状态", "测评成绩"}
                , data, new DataSheetExecute<PersonUser>() {

            public void execute(Row row, PersonUser personUser) {
                row.createCell(0).setCellValue(personUser.getName());
                row.createCell(1).setCellValue(personUser.getAddress());
                row.createCell(2).setCellValue(personUser.getSex());
                row.createCell(3).setCellValue(personUser.getPhone());
                row.createCell(4).setCellValue(personUser.getGroup());
                row.createCell(5).setCellValue(personUser.getId());
                row.createCell(6).setCellValue(personUser.getArea());
                row.createCell(7).setCellValue(personUser.getSchool());
                row.createCell(8).setCellValue(personUser.getStat());
                row.createCell(9).setCellValue(personUser.getScore());
            }

            public void writeExcel(SXSSFWorkbook workbook, OutputStream outputStream) throws Exception {
                outputStream = new FileOutputStream(filePath);
                workbook.write(outputStream);
            }
        }, true, new ExcelExportRowStatusListener() {
            public void listen(Row row, int rows) {
                /*
                 * 可以对row对象进行设置格式
                 */
                System.out.println("执行到了：<" + rows + "> 这一行");
            }
        }).execute();
    }

}