在项目开发中往往需要使用到数据的导入和导出,导入就是从Excel中导入到DB中,而导出就是从DB中查询数据然后使用POI写到Excel上。
1.传统POI的的版本优缺点比较
其实想到数据的导入导出,理所当然的会想到apache的poi技术,以及Excel的版本问题。
既然要做导入导出,那么我们就先来大致看一下传统poi技术的版本以及优缺点对比吧!
首先我们知道POI中我们最熟悉的莫过于WorkBook这样一个接口,我们的POI版本也在更新的同时对这个几口的实现类做了更新:
-
HSSFWorkbook :
这个实现类是我们早期使用最多的对象,它可以操作Excel2003以前(包含2003)的所有Excel版本。在2003以前Excel的版本后缀还是.xls
-
XSSFWorkbook :
这个实现类现在在很多公司都可以发现还在使用,它是操作的Excel2003--Excel2007之间的版本,Excel的扩展名是.xlsx
-
SXSSFWorkbook :
这个实现类是POI3.8之后的版本才有的,它可以操作Excel2007以后的所有版本Excel,扩展名是.xlsx
大致知道了我们在导入导出操作的时候会用到这样三个实现类以及他们可以操作的Excel版本和后缀之后,我们就要从优缺点分析他们了
HSSFWorkbook
它是POI版本中最常用的方式,不过:
-
它的缺点是 最多只能导出 65535行,也就是导出的数据函数超过这个数据就会报错;
-
它的优点是 不会报内存溢出。(因为数据量还不到7w所以内存一般都够用,首先你得明确知道这种方式是将数据先读取到内存中,然后再操作)
XSSFWorkbook
-
优点:这种形式的出现是为了突破
HSSFWorkbook
的65535行局限,是为了针对Excel2007版本的1048576行,16384列,最多可以导出104w条数据; -
缺点:伴随的问题来了,虽然导出数据行数增加了好多倍,但是随之而来的内存溢出问题也成了噩梦。因为你所创建的book,Sheet,row,cell等在写入到Excel之前,都是存放在内存中的(这还没有算Excel的一些样式格式等等),可想而知,内存不溢出就有点不科学了!!!
-
SXSSFWorkbook
-
从POI 3.8版本开始,提供了一种基于XSSF的低内存占用的SXSSF方式:
2.使用哪种方式具体得看业务场景
经过了解也知道了这三种Workbook的优点和缺点,那么具体使用哪种方式还是需要看情况的:
我一般会根据这样几种情况做分析选择:
1、当我们经常导入导出的数据不超过7w的情况下,可以使用 HSSFWorkbook
或者 XSSFWorkbook
都行;
2、当数据量查过7w并且导出的Excel中不牵扯对Excel的样式,公式,格式等操作的情况下,推荐使用SXSSFWorkbook
;
3、当数据量查过7w,并且我们需要操做Excel中的表头,样式,公式等,这时候我们可以使用 XSSFWorkbook
配合进行分批查询,分批写入Excel的方式来做;
3.百万数据导入导出
铺垫也做了不少,那么现在开始讲讲我在工作中遇到的超百万数据的导入导出解决方案:
想要解决问题,我们首先要明确自己遇到的问题是什么?
1、 我遇到的数据量超级大,使用传统的POI方式来完成导入导出很明显会内存溢出,并且效率会非常低;
2、 数据量大直接使用select * from tableName
肯定不行,一下子查出来300w条数据肯定会很慢;
3、 300w 数据导出到Excel时肯定不能都写在一个Sheet中,这样效率会非常低;估计打开都得几分钟;
4、 300w数据导出到Excel中肯定不能一行一行的导出到Excel中。频繁IO操作绝对不行;
5、 导入时300万数据存储到DB如果循环一条条插入也肯定不行;
6、导入时300w数据如果使用Mybatis的批量插入肯定不行,因为Mybatis的批量插入其实就是SQL的循环;一样很慢。
解决思路:
-
针对1 :
其实问题所在就是内存溢出,我们只要使用对上面介绍的POI方式即可,主要问题就是原生的POI解决起来相当麻烦。
经过查阅资料翻看到阿里的一款POI封装工具EasyExcel,上面问题等到解决;
-
针对2:
不能一次性查询出全部数据,我们可以分批进行查询,只不过时多查询几次的问题,况且市面上分页插件很多。此问题好解决。
-
针对3:
可以将300w条数据写到不同的Sheet中,每一个Sheet写一百万即可。
-
针对4:
不能一行一行的写入到Excel上,我们可以将分批查询的数据分批写入到Excel中。
- 针对5:
- 导入到DB时我们可以将Excel中读取的数据存储到集合中,到了一定数量,直接批量插入到DB中。