大数据导出是当我们的导出数量在几万,到上百万的数据时,一次从数据库查询这么多数据加载到内存然后写入会对我们的内存和CPU都产生压力,这个时候需要我们像分页一样处理导出分段写入Excel缓解Excel的压力 EasyPoi提供的是两个方法 *强制使用 xssf版本的Excel *

  /**
     * @param entity
     *            表格标题属性
     * @param pojoClass
     *            Excel对象Class
     * @param dataSet
     *            Excel对象数据List
     */
    public static Workbook exportBigExcel(ExportParams entity, Class<?> pojoClass,
                                          Collection<?> dataSet) {
        ExcelBatchExportServer batachServer = ExcelBatchExportServer
            .getExcelBatchExportServer(entity, pojoClass);
        return batachServer.appendData(dataSet);
    }

    public static void closeExportBigExcel() {
        ExcelBatchExportServer batachServer = ExcelBatchExportServer.getExcelBatchExportServer(null,
            null);
        batachServer.closeExportBigExcel();
    }

添加数据和关闭服务,关闭服务不是必须的,可以调也可以不掉
我们只需要for循环写入Excel就可以了

@Test
    public void bigDataExport() throws Exception {

        List<MsgClient> list = new ArrayList<MsgClient>();
        Workbook workbook = null;
        Date start = new Date();
        ExportParams params = new ExportParams("大数据测试", "测试");
        for (int i = 0; i < 1000000; i++) {  //一百万数据量
            MsgClient client = new MsgClient();
            client.setBirthday(new Date());
            client.setClientName("小明" + i);
            client.setClientPhone("18797" + i);
            client.setCreateBy("JueYue");
            client.setId("1" + i);
            client.setRemark("测试" + i);
            MsgClientGroup group = new MsgClientGroup();
            group.setGroupName("测试" + i);
            client.setGroup(group);
            list.add(client);
            if(list.size() == 10000){
                workbook = ExcelExportUtil.exportBigExcel(params, MsgClient.class, list);
                list.clear();
            }
        }
        ExcelExportUtil.closeExportBigExcel();
        System.out.println(new Date().getTime() - start.getTime());
        File savefile = new File("D:/excel/");
        if (!savefile.exists()) {
            savefile.mkdirs();
        }
        FileOutputStream fos = new FileOutputStream("D:/excel/ExcelExportBigData.bigDataExport.xlsx");
        workbook.write(fos);
        fos.close();
    }

生成的Excel数据

但是上面可能不太好写代码,所以提供了另外一个服务,接口多次调用获取多次数据,方便大家查询数据库

    /**
     * 大数据量导出
     *
     * @param entity      表格标题属性
     * @param pojoClass   Excel对象Class
     * @param server      查询数据的接口
     * @param queryParams 查询数据的参数
     */
    public static Workbook exportBigExcel(ExportParams entity, Class<?> pojoClass,
                                          IExcelExportServer server, Object queryParams) {
        ExcelBatchExportService batchServer = new ExcelBatchExportService();
        batchServer.init(entity, pojoClass);
        return batchServer.exportBigExcel(server, queryParams);
    }

@Test
public void bigDataExport() throws Exception {

    Workbook     workbook = null;
    Date         start    = new Date();
    ExportParams params   = new ExportParams("大数据测试", "测试");
    workbook = ExcelExportUtil.exportBigExcel(params, MsgClient.class, new IExcelExportServer() {

        @Override
        public List<Object> selectListForExcelExport(Object obj, int page) {
            if (((int) obj) == page) {
                return null;
            }
            List<Object> list = new ArrayList<Object>();
            for (int i = 0; i < 10000; i++) {
                MsgClient client = new MsgClient();
                client.setBirthday(new Date());
                client.setClientName("小明" + i);
                client.setClientPhone("18797" + i);
                client.setCreateBy("JueYue");
                client.setId("1" + i);
                client.setRemark("测试" + i);
                MsgClientGroup group = new MsgClientGroup();
                group.setGroupName("测试" + i);
                client.setGroup(group);
                list.add(client);
            }
            return list;
        }
    }, 10);

    System.out.println(new Date().getTime() - start.getTime());
    File savefile = new File("D:/home/excel/");
    if (!savefile.exists()) {
        savefile.mkdirs();
    }
    FileOutputStream fos = new FileOutputStream("D:/home/excel/ExcelExportBigData.bigDataExport.xlsx");
    workbook.write(fos);
    fos.close();
}

`
Cpu和内存

多次测试用时统计,速度还是可以接受的,^^

数据量 用时 文件大小 列数
100W 16.4s 24.3MB 5
100W 15.9s 24.3MB 5
200W 29.5s 48.5MB 5
100W 30.8s 37.8MB 10
200W 58.7s 76.1MB 10
作者:悟耘信息  创建时间:2020-04-09 15:14
最后编辑:悟耘信息  更新时间:2024-11-15 17:27