【Java读写大数据量Excel】

在这里插入图片描述

主页传送门:💁 传送

1 问题描述

1.1 Excel读写时候内存溢出

       虽然POI是目前使用最多的用来做excel解析的框架,但这个框架并不那么完美。大部分使用POI都是使用他的userModel模式。userModel的好处是上手容易使用简单,随便拷贝个代码跑一下,剩下就是写业务转换了,虽然转换也要写上百行代码,相对比较好理解。然而userModel模式最大的问题是在于非常大的内存消耗,一个几兆的文件解析要用掉上百兆的内存。现在很多应用采用这种模式,之所以还正常在跑一定是并发不大,并发上来后一定会OOM或者频繁的full gc。

1.2 其他开源框架使用复杂

       对POI有过深入了解的估计才知道原来POI还有SAX模式。但SAX模式相对比较复杂,excel有03和07两种版本,两个版本数据存储方式截然不同,sax解析方式也各不一样。想要了解清楚这两种解析方式,才去写代码测试,估计两天时间是需要的。再加上即使解析完,要转换到自己业务模型还要很多繁琐的代码。总体下来感觉至少需要三天,由于代码复杂,后续维护成本巨大。

2 解决方案

2.1 Hutool-poi

2.1.1 快速开始

引入hutool依赖

<dependency>
    <groupId>cn.hutool</groupId>
    <artifactId>hutool-all</artifactId>   
    <version>${hutool.version}</version>
</dependency>

读取大数据量的Excel

private RowHandler createRowHandler() {
    return new RowHandler() {
        @Override
        public void handle(int sheetIndex, int rowIndex, List<Object> rowlist) {
            Console.log("[{}] [{}] {}", sheetIndex, rowIndex, rowlist);
        }
    };
}

ExcelUtil.readBySax("aaa.xlsx", 0, createRowHandler());

写大数量量的Excel

List<?> row1 = CollUtil.newArrayList("aa", "bb", "cc", "dd", DateUtil.date(), 3.22676575765);
List<?> row2 = CollUtil.newArrayList("aa1", "bb1", "cc1", "dd1", DateUtil.date(), 250.7676);
List<?> row3 = CollUtil.newArrayList("aa2", "bb2", "cc2", "dd2", DateUtil.date(), 0.111);
List<?> row4 = CollUtil.newArrayList("aa3", "bb3", "cc3", "dd3", DateUtil.date(), 35);
List<?> row5 = CollUtil.newArrayList("aa4", "bb4", "cc4", "dd4", DateUtil.date(), 28.00);

List<List<?>> rows = CollUtil.newArrayList(row1, row2, row3, row4, row5);

BigExcelWriter writer=ExcelUtil.getBigWriter("e:/xxx.xlsx");
// 一次性写出内容,使用默认样式 
writer.write(rows);
// 关闭writer,释放内存
writer.close();

2.1.2 核心原理

       读excel:底层采用poi的sax模式一行一行解析,并将一行的解析结果以观察者的模式通知处理。抛弃不重要的数据,大大降低内存使用,比如样式,字体,宽度,这些在读取数据的时候并不关心。
       写excel:底层基于SXSSFWorkBook的流式写入,当内存中的数据达到一个极限的数量的时候就flush到磁盘中,保证占用内存可控。

2.1.3 优缺点

  • 优点:
    1. 使用简单,官方文档详细,社区活跃,版本稳定
    2. 读写速度快,节省内存
  • 缺点:
    1. 相对EasyExcel功能支持和写入模式较少
    2. 不支持读取单元格样式和值类型
    3. 很多高级功能不支持,如单文件并发读写,图片,图表读写,宏

2.1.4 官方文档

Hutool帮助文档

2.2 EasyExcel

2.2.1 快速开始

引入EasyExcel依赖

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>easyexcel</artifactId>
    <version>${easyexcel.version}</version>
</dependency>
  • 读Excel
    对象
@Getter
@Setter
@EqualsAndHashCode
public class DemoData {
    private String string;
    private Date date;
    private Double doubleData;
}

监听器

// 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
@Slf4j
public class DemoDataListener implements ReadListener<DemoData> {

    /**
     * 每隔5条存储数据库,实际使用中可以100条,然后清理list ,方便内存回收
     */
    private static final int BATCH_COUNT = 100;
    /**
     * 缓存的数据
     */
    private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    /**
     * 假设这个是一个DAO,当然有业务逻辑这个也可以是一个service。当然如果不用存储这个对象没用。
     */
    private DemoDAO demoDAO;

    public DemoDataListener() {
        // 这里是demo,所以随便new一个。实际使用如果到了spring,请使用下面的有参构造函数
        demoDAO = new DemoDAO();
    }

    /**
     * 如果使用了spring,请使用这个构造方法。每次创建Listener的时候需要把spring管理的类传进来
     *
     * @param demoDAO
     */
    public DemoDataListener(DemoDAO demoDAO) {
        this.demoDAO = demoDAO;
    }

    /**
     * 这个每一条数据解析都会来调用
     *
     * @param data    one row value. Is is same as {@link AnalysisContext#readRowHolder()}
     * @param context
     */
    @Override
    public void invoke(DemoData data, AnalysisContext context) {
        log.info("解析到一条数据:{}", JSON.toJSONString(data));
        cachedDataList.add(data);
        // 达到BATCH_COUNT了,需要去存储一次数据库,防止数据几万条数据在内存,容易OOM
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            // 存储完成清理 list
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    /**
     * 所有数据解析完成了 都会来调用
     *
     * @param context
     */
    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        // 这里也要保存数据,确保最后遗留的数据也存储到数据库
        saveData();
        log.info("所有数据解析完成!");
    }

    /**
     * 加上存储数据库
     */
    private void saveData() {
        log.info("{}条数据,开始存储数据库!", cachedDataList.size());
        demoDAO.save(cachedDataList);
        log.info("存储数据库成功!");
    }
}

持久层

/**
 * 假设这个是你的DAO存储。当然还要这个类让spring管理,当然你不用需要存储,也不需要这个类。
 **/
public class DemoDAO {
    public void save(List<DemoData> list) {
        // 如果是mybatis,尽量别直接调用多次insert,自己写一个mapper里面新增一个方法batchInsert,所有数据一次性插入
    }
}

关键代码

 /**
     * 最简单的读
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link DemoData}
     * <p>
     * 2. 由于默认一行行的读取excel,所以需要创建excel一行一行的回调监听器,参照{@link DemoDataListener}
     * <p>
     * 3. 直接读即可
     */
    @Test
    public void simpleRead() {
        // 写法1:JDK8+ ,不用额外写一个DemoDataListener
        // since: 3.0.0-beta1
        String fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
        // 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
        // 这里每次会读取3000条数据 然后返回过来 直接调用使用数据就行
        EasyExcel.read(fileName, DemoData.class, new PageReadListener<DemoData>(dataList -> {
            for (DemoData demoData : dataList) {
                log.info("读取到一条数据{}", JSON.toJSONString(demoData));
            }
        })).sheet().doRead();

        // 写法2:
        // 匿名内部类 不用额外写一个DemoDataListener
        fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
        // 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, DemoData.class, new ReadListener<DemoData>() {
            /**
             * 单次缓存的数据量
             */
            public static final int BATCH_COUNT = 100;
            /**
             *临时存储
             */
            private List<DemoData> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);

            @Override
            public void invoke(DemoData data, AnalysisContext context) {
                cachedDataList.add(data);
                if (cachedDataList.size() >= BATCH_COUNT) {
                    saveData();
                    // 存储完成清理 list
                    cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
                }
            }

            @Override
            public void doAfterAllAnalysed(AnalysisContext context) {
                saveData();
            }

            /**
             * 加上存储数据库
             */
            private void saveData() {
                log.info("{}条数据,开始存储数据库!", cachedDataList.size());
                log.info("存储数据库成功!");
            }
        }).sheet().doRead();

        // 有个很重要的点 DemoDataListener 不能被spring管理,要每次读取excel都要new,然后里面用到spring可以构造方法传进去
        // 写法3:
        fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
        // 这里 需要指定读用哪个class去读,然后读取第一个sheet 文件流会自动关闭
        EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).sheet().doRead();

        // 写法4:
        fileName = TestFileUtil.getPath() + "demo" + File.separator + "demo.xlsx";
        // 一个文件一个reader
        ExcelReader excelReader = null;
        try {
            excelReader = EasyExcel.read(fileName, DemoData.class, new DemoDataListener()).build();
            // 构建一个sheet 这里可以指定名字或者no
            ReadSheet readSheet = EasyExcel.readSheet(0).build();
            // 读取一个sheet
            excelReader.read(readSheet);
        } finally {
            if (excelReader != null) {
                // 这里千万别忘记关闭,读的时候会创建临时文件,到时磁盘会崩的
                excelReader.finish();
            }
        }
    }
  • 写excel
    对象
@Getter
@Setter
@EqualsAndHashCode
public class DemoData {
    @ExcelProperty("字符串标题")
    private String string;
    @ExcelProperty("日期标题")
    private Date date;
    @ExcelProperty("数字标题")
    private Double doubleData;
    /**
     * 忽略这个字段
     */
    @ExcelIgnore
    private String ignore;
}

关键代码

  /**
     * 最简单的写
     * <p>
     * 1. 创建excel对应的实体对象 参照{@link DemoData}
     * <p>
     * 2. 直接写即可
     */
    @Test
    public void simpleWrite() {
        // 注意 simpleWrite在数据量不大的情况下可以使用(5000以内,具体也要看实际情况),数据量大参照 重复多次写入
        
        // 写法1 JDK8+
        // since: 3.0.0-beta1
        String fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, DemoData.class)
            .sheet("模板")
            .doWrite(() -> {
                // 分页查询数据
                return data();
            });

        // 写法2
        fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去写,然后写到第一个sheet,名字为模板 然后文件流会自动关闭
        // 如果这里想使用03 则 传入excelType参数即可
        EasyExcel.write(fileName, DemoData.class).sheet("模板").doWrite(data());

        // 写法3
        fileName = TestFileUtil.getPath() + "simpleWrite" + System.currentTimeMillis() + ".xlsx";
        // 这里 需要指定写用哪个class去写
        ExcelWriter excelWriter = null;
        try {
            excelWriter = EasyExcel.write(fileName, DemoData.class).build();
            WriteSheet writeSheet = EasyExcel.writerSheet("模板").build();
            excelWriter.write(data(), writeSheet);
        } finally {
            // 千万别忘记finish 会帮忙关闭流
            if (excelWriter != null) {
                excelWriter.finish();
            }
        }
    }
  • 填充Excel
    对象
@Getter
@Setter
@EqualsAndHashCode
public class FillData {
    private String name;
    private double number;
    private Date date;
}

关键代码

  /**
     * 最简单的填充
     *
     * @since 2.1.1
     */
    @Test
    public void simpleFill() {
        // 模板注意 用{} 来表示你要用的变量 如果本来就有"{","}" 特殊字符 用"\{","\}"代替
        String templateFileName =
            TestFileUtil.getPath() + "demo" + File.separator + "fill" + File.separator + "simple.xlsx";

        // 方案1 根据对象填充
        String fileName = TestFileUtil.getPath() + "simpleFill" + System.currentTimeMillis() + ".xlsx";
        // 这里 会填充到第一个sheet, 然后文件流会自动关闭
        FillData fillData = new FillData();
        fillData.setName("张三");
        fillData.setNumber(5.2);
        EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(fillData);

        // 方案2 根据Map填充
        fileName = TestFileUtil.getPath() + "simpleFill" + System.currentTimeMillis() + ".xlsx";
        // 这里 会填充到第一个sheet, 然后文件流会自动关闭
        Map<String, Object> map = new HashMap<String, Object>();
        map.put("name", "张三");
        map.put("number", 5.2);
        EasyExcel.write(fileName).withTemplate(templateFileName).sheet().doFill(map);
    }

2.2.2 核心原理

       读excel:底层采用poi的sax模式一行一行解析,并将一行的解析结果以观察者的模式通知处理。抛弃不重要的数据,大大降低内存使用,比如样式,字体,宽度,这些在读取数据的时候并不关心。
       写excel:底层基于SXSSFWorkBook的流式写入,当内存中的数据达到一个极限的数量的时候就flush到磁盘中,保证占用内存可控。

2.2.3 优缺点

  • 优点:
    1. 使用简单,官方文档详细,社区活跃,版本稳定
    2. 读写速度快,节省内存
    3. 支持场景较多,包括很多复杂表格
  • 缺点:
    1. 不支持读取单元格样式和值类型
    2. 很多高级功能不支持,如单文件并发读写,图片,图表读写,宏

2.2.4 官方文档

EasyExcel帮助文档

如果喜欢的话,欢迎 🤞关注 👍点赞 💬评论 🤝收藏 🙌一起讨论
你的支持就是我✍️创作的动力! 💞💞💞

  • 2
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 3
    评论
EasyExcel是一种Java开源的大数据读写工具,它提供了一种简单易用的方式来处理Excel文件。在处理大数据的情况下,传统的Excel读写方式往往效率较低、消耗内存较大,而EasyExcel则能够更好地解决这一问题。 EasyExcel在处理大数据时的优势主要体现在以下几个方面: 1. 低内存消耗:EasyExcel使用流式读写的方式处理Excel文件,只读取部分数据到内存中,因此可以有效降低内存消耗。在处理大数据时,这一特性尤为重要,可大大提高程序的性能和效率。 2. 大数据读写性能优异:EasyExcel采用了多线程和缓存技术,可以同时读写多个Excel文件,并利用缓存提高读写效率。这使得EasyExcel能够在处理大规模数据时表现出良好的性能表现,大大减少了读写时间。 3. 丰富的读写功能:EasyExcel支持常见的Excel读写操作,包括单元格数据读取、写入、合并单元格、样式设置等功能。同时,EasyExcel还支持各种数据类型的读写,包括日期、数字、字符串、布尔值等,非常灵活方便。 通过EasyExcel,我们可以轻松地读取、处理和导出大数据,特别在需要将Excel数据导入到数据库或从数据库导出到Excel时,EasyExcel展现出了以下几点优势: 1. 提高数据导入导出的效率:EasyExcel的高效读写方式和性能优势,可以大幅提高数据导入导出的效率,省去了手动逐行读写的繁琐过程。 2. 简化代码编写:EasyExcel提供了简洁易用的API,可以通过少的代码完成复杂的Excel读写操作,大大简化了编码工作。 3. 支持数据格式的灵活处理:EasyExcel丰富的读写功能可以满足各种复杂数据处理需求,包括日期格式化、数值类型转换、文本处理等,可根据需求自由定制。 总之,EasyExcel是一款非常实用的大数据读写工具,通过优化性能和提供丰富的功能,能够极大地提高Excel处理的效率和便捷性,为开发人员带来了很大的便利。在CSDN平台上,有关EasyExcel的相关教程和案例也很丰富,可以方便地学习和使用。

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

码农桶子哥

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值