使用EasyExcel对Excel导入和导出

最新推荐文章于 2024-06-11 13:24:07 发布

张某某啊哈

最新推荐文章于 2024-06-11 13:24:07 发布

阅读量1.6k

点赞数

分类专栏：项目开发实用工具项目集成实用插件与辅助工具小技巧文章标签： java EasyExcel Excel导入和导出

原文链接：https://blog.csdn.net/u013161278/article/details/81211790

版权

项目集成实用插件与辅助工具小技巧同时被 2 个专栏收录

34 篇文章 0 订阅

订阅专栏

项目开发实用工具

24 篇文章 3 订阅

订阅专栏

一提到Excel的导入和导出，我们大多数人第一反应就是POI，对我而言也是。最近正好赶上做Excel导入和导出的功能，但是这次不一样的是数据量比过去增长了不少，过去业务量是百千条记录，现在变成了上万甚至十几万条记录，于是传统的POI出现了OOM、响应时间长的问题，导入导出EXCEL的响应时间长直接影响着用户的体验，OOM则会严重影响系统的正常使用更甚则服务停滞。于是，我针对EXCEL的导入和导出做了一些探究和学习，力求寻找更快、更稳定的方法。

1.Excel结构:

①WorkBook 工作簿

②Sheet 页

③Row 行

④Cell 单元格

Excel的组成是由工作簿（WorkBook）、页（Sheet）、行（Row）、单元格（Cell），从上到下依次包含下一级，WorkBook工作簿可以认为是根节点，它可以有多个Sheet页，而Sheet页中可以拥有多个Row行，同理Row行中可以用有多个Cell单元格，Cell单元格是最小的一级，也是具体数据的存储单元。

WorkBook工作簿类型一般有三种：HSSFWorkbook、XSSFWorkbook、SXSSFWorkbook。HSSFWorkbook仅支持xls格式的导入和导出，XSSFWorkbook仅支持xlsx格式的导入和导出，SXSSFWorkbook则是为了解决生成大数据量Excel时内存溢出问题的，仅支持xlsx的导出。

可参考：https://blog.csdn.net/qq_29631809/article/details/72785338

不同版本的Excel对Row行数和Column列数（即每Row行有多少个Cell单元格）有最大限制。

类型为（.xlsx）EXCEL工作簿: Office Excel 2007 1048576行 16384列

类型为（.xls) EXCEL工作簿: Office Excel 97-2003 65536行 256列

可参考：https://blog.csdn.net/zhongguomao/article/details/77737800

2.格式规范

Excel97-2003 （后缀格式为.xls）基于OLE2 stream

Excel2007 （后缀格式为.xlsx）基于 OOXML（Office OpenXML） stream

可参考：http://hao.jobbole.com/poi/

格式规范不同，必然造成读取时解析器和生成时的生成器差异，所以，如果能有一款类库能同时兼容这两种格式规范的话，对于开发者来说，无疑是有了得利匕首。

POI基于自己的使命，它同时支持这两种格式的读取和生成，它试图提供一套高水平的、对OOXML 和 OLE2格式文件都通用的JAVA API，但是实际应用中，POI这把“匕首”还不是太够锋利，小数据量还可以挥斥方遒，一旦数据量上来了，不光时间效率上无法满足，而且经常会出现OOM这种灾难性的异常。

3.工具类库

我通过github、maven、百度和谷歌等大概搜集了下大家比较常用的一些基于Excel导入或导出的Java API类库，做了如下汇总：

3.1 POI

介绍：Apache POI项目维护的开源产品，它的使命是创造和维护可以操作各种基于Office Open XML标准或者微软OLE2格式文件的Java API。

支持格式：Excel97-2003（xls格式即OLE2文本流）、Excel2007（xlsx格式即OOXML文本流）

源码：https://github.com/apache/poi

Maven：http://mvnrepository.com/artifact/org.apache.poi/poi

可参考：http://poi.apache.org/

3.2 JXL

介绍：一款基于Java的Excel解析工具

支持格式：Excel97-2003（xls格式即OLE2文本流）

Maven：http://mvnrepository.com/artifact/net.sourceforge.jexcelapi/jxl/2.6.10

可参考：http://jxl.sourceforge.net/ 或 https://baike.baidu.com/item/jxl.jar/10116280?fr=aladdin

3.3 Excel-Streaming-Reader

介绍：基于POI做了改善，这个库作为Streaming API的包装器，同时保留了标准POI API的语法，但是仅支持xlsx格式的读取。

源码：https://github.com/monitorjbl/excel-streaming-reader

Maven：http://mvnrepository.com/artifact/com.monitorjbl/xlsx-streamer

3.4 EasyExcel

介绍：作者是Alibaba的姬朋飞，这个库也是基于POI，采用sax模式逐行解析，并将每一行的解析结果以观察者的模式通知处理，并且用反射做模型字段映射，并加入了缓存，优点是占用内存非常低，速度也很出色。

支持格式：Excel97-2003（xls格式即OLE2文本流）、Excel2007（xlsx格式即OOXML文本流）

源码：https://github.com/alibaba/easyexcel

Maven：http://mvnrepository.com/artifact/com.alibaba/easyexcel/1.0.2

4.性能测试

我在PC上进行了一些测试，由于公司是虚拟化环境，所有测试都是虚机环境下的，数据还是能分出优劣的。

环境参数：4CPU，6GB

.xlsx 均读取1048576行、17列、模拟数据共52.6M，导出均取5列生成

.xls 均读取 65535行、17列、模拟数据共16.4M，导出均取5列生成

		读取	输出
POI-HSSFWorkbook	xls（65535行）	264.947s	9.862s
	xlsx（1048576行）	不支持	不支持
POI-XSSFWorkbook	xls（65535行）	不支持	不支持
	xlsx（1048576行）	支持，数据过大会OOM	支持，数据过大会OOM
POI-SXSSFWorkbook	xls（65535行）	不支持	2.055s
	xlsx（1048576行）	不支持	15.209s
JXL	xls（65535行）	2.883 s	1.386 s
	xlsx（1048576行）	不支持	不支持
EasyExcel	xls（65535行）	1.445 s	2.202 s
	xlsx（1048576行）	44.657 s	14.374 s
Excel-Streaming-Reader	xls（65535行）	不支持	不支持
	xlsx（1048576行）	76.333 s	不支持

可参考我的示例代码：https://github.com/keycasiter/excel-learning

5.总结
1.EasyExcel在兼容性、时间效率、内存占用上一枝独秀，首推。

2.小数据量的Excel导入或导出，可以选择POI，前提是不会出现OOM，而且时间效率上可以接受；如果仅是支持xls格式，JXL也可以作为选择方案。

3.如果是大数据量的xlsx格式读取，Excel-Streaming-Reader也不失为一个好的选择，时间效率上可以接受，要远远强于POI，而且不会出现OOM，但逊色于EasyExcel。

4.单纯的大数据量导出也可以选择SXSSFWorkbook来完成，它优化了POI导出可能出现的OOM问题，时间效率也能满足需要。

6.其他
关于Excel的导入和导出，以上所讨论的内容都是基于Java API与输入流或输出流的交互，目的是如何以更快、更稳定的方式把Excel文件流读入并持久化到数据库，或者快速将数据库的数据查询出并生成Excel文本，针对这一个整体过程可能产生的性能与效率问题，总结如下：

1.读取EXCEL结束后和生成EXCEL后，数据流必须关闭以释放资源占用；生成EXCEL后的数据输出流要进行flush操作，将缓存数据刷入到硬盘上，否则生成文件为空。

2.读取EXCEL时要注意单元格数据格式的转换问题。

3.数据插入时，关闭默认的自动提交，选择批量执行SQL一次提交，能避免大量的事务存在带来的性能影响，而且由于批量提交会产生大量插入语句，SQL使用大写而不是小写，因为在数据库预编译解析时会有解析成本。

4.数据插入时，表不要建索引，以免有维护索引带来的性能消耗。

5.大量字段的查询，指定具体字段名，不要使用 * 匹配。

6.在使用Java容器接收数据时，尽量指定容器大小一次生成，避免resize带来的性能消耗；要留意数据量的大小是否超过容器所能容纳的最大值避免越界；容器使用完后的节点可以通过设置List list = null等操作触发JVM及时GC，释放宝贵的内存资源；避免使用toString这类打印操作来查看大容器的key-value这种耗费内存的操作；在效率允许的前提下，尽量保证使用线程安全的容器或通过颗粒度适当的锁来保证容器操作的安全性，避免不可预见的并发问题。

7.无论使用哪个类库，导入和导出操作都是非常占用系统资源的，包括内存、CPU、IO、数据库这些，测试时可以关注下，不要因为这个单个功能而影响整体服务的稳定性，遇见瓶颈时可以选择限制角色、时段、数据量使用，或者干脆直接单独部署，将服务分离甚至用备库做查询。

8.Apache POI提供了大量操作EXCEL的JAVA API，有能力的话也可以基于这些API根据自己的实际需要进行高层次的封装和处理，只要遵循OOXML和OLE2的解析便可以实现文本格式的兼容。
————————————————
版权声明：本文为CSDN博主「大摩羯先生」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：https://blog.csdn.net/u013161278/article/details/81211790