Excel导入导出设计

daiwei-dave

已于 2024-03-15 16:47:25 修改

阅读量538

点赞数

分类专栏：架构设计文章标签： excel

于 2021-07-27 16:35:26 首次发布

本文链接：https://blog.csdn.net/sinat_34814635/article/details/119147284

版权

架构设计专栏收录该内容

11 篇文章 1 订阅

订阅专栏

一、poi

有三种解析方式：
1、eventmodel方式，基于事件驱动,SAX的方式解析excel（.xlsx是基于OOXML的），CPU和内存消耗非常低，但是只能读不能写，塔式应用程序一边读取数据，一边处理数据。

2、usermodel，就是我们一般使用的方式，这种方式可以读可以写，但是CPU和内存消耗非常大

3、SXSSF，POI3.8以后开始支持，这种方式只能写excel

@see http://www.cnblogs.com/tootwo2/p/6683143.html

HSSF －提供读写Microsoft Excel XLS格式档案的功能。
XSSF －提供读写Microsoft Excel OOXML XLSX格式档案的功能

1.HSSF
HSSF 是Horrible SpreadSheet Format的缩写，通过HSSF，你可以用纯Java代码来读取、写入、修改Excel文件。
HSSF 为读取操作提供了两类API：usermodel和eventusermodel，即“用户模型”和“事件-用户模型”。

SAX，全称Simple API for XML，既是一种接口，也是一种软件包。它是一种XML解析的替代方法。SAX不同于DOM解析，它逐行扫描文档，一边扫描一边解析。
由于应用程序只是在读取数据时检查数据，因此不需要将数据存储在内存中，这对于大型文档的解析是个巨大优势。
https://baike.baidu.com/item/sax/3006235?fr=aladdin

SAX模型最大的优点是内存消耗小，因为整个文档无需一次加载到内存中，这使SAX解析器可以解析大于系统内存的文档。

SAX的工作原理简单地说就是对文档进行顺序扫描，当扫描到文档（document）开始与结束、元素（element）开始与结束、文档（document）结束等地方时通知事件处理函数，
由事件处理函数做相应动作，然后继续同样的扫描，直至文档结束

思考:

1.usermodel和eventmodel模型的区别?

1.usermodel是将整个文件一次性加载到内存中进行解析，适合较小的文件。而eventmodel一边读取一边解析，无需加载到内存中，适合较大的文件。

2.eventmodel只能读不能写，usermodel这种方式可以读可以写，但是CPU和内存消耗非常大

2.HSSFWorkbook和XSSFWorkbook，SXSSFWorkbook的区别

HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls；

XSSFWorkbook:是操作Excel2007后的版本，扩展名是.xlsx；

SXSSFWorkbook:是操作Excel2007后的版本，扩展名是.xlsx；

第一种：HSSFWorkbook

poi导出excel最常用的方式；但是此种方式的局限就是导出的行数至多为65535行，超出65536条后系统就会报错。此方式因为行数不足七万行所以一般不会发生内存不足的情况（OOM）。

第二种：XSSFWorkbook

这种形式的出现是为了突破HSSFWorkbook的65535行局限。其对应的是excel2007(1048576行，16384列)扩展名为“.xlsx”，最多可以导出104万行，不过这样就伴随着一个问题---OOM内存溢出，原因是你所创建的book sheet row cell等此时是存在内存的并没有持久化。

第三种：SXSSFWorkbook

从POI 3.8版本开始，提供了一种基于XSSF的低内存占用的SXSSF方式。对于大型excel文件的创建，一个关键问题就是，要确保不会内存溢出。其实，就算生成很小的excel（比如几Mb），它用掉的内存是远大于excel文件实际的size的。如果单元格还有各种格式（比如，加粗，背景标红之类的），那它占用的内存就更多了。对于大型excel的创建且不会内存溢出的，就只有SXSSFWorkbook了。它的原理很简单，用硬盘空间换内存（就像hash map用空间换时间一样）。

SXSSFWorkbook是streaming版本的XSSFWorkbook,它只会保存最新的excel rows在内存里供查看，在此之前的excel rows都会被写入到硬盘里（Windows电脑的话，是写入到C盘根目录下的temp文件夹）。被写入到硬盘里的rows是不可见的/不可访问的。只有还保存在内存里的才可以被访问到。

SXSSF与XSSF的对比：

a. 在一个时间点上，只可以访问一定数量的数据

b. 不再支持Sheet.clone()

c. 不再支持公式的求值

d. 在使用Excel模板下载数据时将不能动态改变表头，因为这种方式已经提前把excel写到硬盘的了就不能再改了

当数据量超出65536条后，在使用HSSFWorkbook或XSSFWorkbook，程序会报OutOfMemoryError：Javaheap space;内存溢出错误。这时应该用SXSSFworkbook。

二、easyexcel

1.防止内存溢出

EasyExcel 是一种开源的 Java Excel 处理框架，它被广泛应用于读写 Excel 文件的操作中。相比于其他 Excel 处理框架，EasyExcel 在处理大数据量时不容易出现内存溢出的问题。这主要有以下几个原因：

EasyExcel 使用流式读写的方式处理 Excel 文件。在读取 Excel 文件时，它将文件分为多个小块进行读取，而不是一次性将整个文件加载到内存中。这种分块读取的方式减少了内存的占用，提高了读取大文件的性能。在写入 Excel 文件时，EasyExcel 也是采用类似的方式，将数据分块写入，避免了一次性将所有数据加载到内存导致的内存溢出问题。

EasyExcel 内部采用了对象缓存和集合缓存的机制。当大量数据需要写入 Excel 文件时，EasyExcel 会根据内存的使用情况自动调整缓存的大小，避免内存溢出。同时，EasyExcel 也提供了手动设置缓存大小的接口，用户可以根据实际情况进行调整。

此外，EasyExcel 还提供了对 Excel 格式的优化处理。它对 Excel 文件的读取和写入进行了性能的优化，减少了不必要的内存开销。而且，EasyExcel 支持多种数据类型的读取和写入，包括基本类型、日期类型、图片等，方便用户处理各种复杂的 Excel 数据。

EasyExcel 通过流式读写、缓存机制和格式优化等方式有效地减少了内存的占用，避免了内存溢出的问题。这使得开发人员可以更加方便地处理大数据量的 Excel 文件，提供了更好的用户体验。

而在代码实现上可参考下面的代码

 @Override
    public void exportI18n(ExcelI18nQueryParam excelQuery, HttpServletRequest request, HttpServletResponse response) {
        ExcelWriter excelWriter = null;
        try {
            EasyExcelUtil.responseHeader(response, "国际化配置导出");
            excelWriter = EasyExcel.write(response.getOutputStream(), SysI18nExcelModel.class).build();
            WriteSheet writeSheet = EasyExcel.writerSheet("国际化配置").build();
            List<String> businessTypes = excelQuery.getBusinessTypes();
            int pageNum = 1;
            int pageSize = 1000;
            while (true) {
                Page<SysI18n> pageParam = new Page<SysI18n>(pageNum, pageSize);

                LambdaQueryWrapper<SysI18n> lambdaQueryWrapper = new LambdaQueryWrapper<SysI18n>()
                        .in(CollectionUtil.isNotEmpty(businessTypes), SysI18n::getBusinessType, businessTypes);
                List<SysI18n> list = baseMapper.selectPage(pageParam, lambdaQueryWrapper).getRecords();
                if (list.isEmpty()) {
                    break;
                }
                excelWriter.write(BeanUtils.copy(list,SysI18nExcelModel.class), writeSheet);
                if (list.size() < pageSize) {
                    break;
                }
                pageNum++;
            }
        } catch (Exception e) {
            log.error("国际化配置导出异常！", e);
        } finally {
            if (Objects.nonNull(excelWriter)) {
                excelWriter.finish();
            }
        }
    }

上面代码实现了一边解析数据一边往excel输出的方式，防止一次性加载大量数据而产生OOM。