Excel大数据量解析优化方案
点击上方“技术支持联盟”,选择“置顶公众号”
第一时间了解程序员大小事儿
作者:龙遨星海,80后程序员,热爱编程,喜欢解决技术问题,体验编码的喜悦。
摘要:大数据量Excel导入数据到数据库时,经常会导致内存溢出、报错或者耗费时间过长,本方案可以很好的解决此问题。
问题背景
在常规企业应用系统中,大部分会用Excel进行批量导入或导出操作,通过使用Apache POI组件实现对Excel的读写操作,但是在读取Excel时,一次性把Excel加载到内存,对于大数据量的Excel解析处理,会导致内存占用过高或溢出。因此,我们可以通过限制读入内存的Excel条数并优化解析过程,实现高效率、大批量的Excel解析。
02解决方案
1、优化Excel解析方式,使用Apache xerces 替换POI内置XML解析器。
2、采用多线程中生产者和消费者模式,通过一边解析数据,一边保存数据,从而提高吞吐量,加快处理速度。
组件介绍
Excel 版本介绍Excel2003版单sheet中最多存储65536行记录,因此欲在单sheet中导出超过这个行数数据需要导出的Excel文件格式应是Excel2007及以上版本文件(.xlsx格式),否则需要按行数拆分sheet导出数据。Excel2007及以上版本单sheet也支持最多1048576(104万)行数据,单页大于此阈值也需要分sheet页存储。
Apache POI介绍1.简介
Apache POI用于Java处理Excel的主要组件,该组件是由Java编写,专门用于处理MS Word、PPT、Excel等产品的开源组件,为Java提供API用于操作Microsoft Office格式的读和写的功能。
2.WorkBook的不同实现
1)HSSFWorkbook:该workbook实现的是操作Excel2003以前(包括2003)的版本,扩展名为.xls,该实现单sheet页最大行数为65536行。
2)XSSFWorkbook:该workbook实现的是操作Excel2007及以上的版本,扩展名为.xlsx,单sheet页最大行数为1048576行。
3)SXSSFWorkbook:为解决XSSFWorkbook实现中当导出行数较多时,所有数据都在内存中导致的OOM(内存溢出)问题而出现的阶段性持久化内存数据的一种实现方案,通过多次IO操作换来对内存的固定值消耗,如设定内存中最大保持1000行数据,及超出设定值时SXSSFWorkbook会将内存中的1000行记录持久化到文件中,周而复始操作可导出海量数据到Excel。
Apache xerces介绍1.简介
Apache xerces是由Apache组织所推动的一项XML文档解析开源项目, 是一个开放源代码的XML语法分析器。
2、SAXParser解析器
该SAXParser解析器解析XML时, 并不需要读入整个XML文档,而文档的读入过程也就是SAXParser的解析过程。它是事件驱动的,所谓事件驱动,是指一种基于回调(callback)机制的程序运行方法,解析开始之前,需要向XMLReader注册一ContentHandler,也就是相当于一个事件监听器,实现自定义处理,它逐行扫描文档,一边扫描一边解析,从而能够有效的降低解析Excel所占用内存过高的问题,提高解析速度。
SAXParser优点1、占用内存小。该解析器,解析XML文档,并不会一次性加载全部数据到内存。
2、解析速度快。对于大量数据的Excel 解析,采用边解析边处理的方式,使之能够高效的进行大数据量Excel的解析。
数据解析-生产者和消费者模式
在进行Excel解析过程中将数据保存到数据库时,我们可以通过使用多线程编程模式中的生产者和消费者模式,我们可以把解析Excel获取数据的操作当作生产者,把解析后的数据保存到数据库的操作看作是消费者,同时执行,互不干涉,从而来提高系统吞吐量和并发数。
模式简介
生产者和消费者模式为多线程中的一种重要设计模式,数据的提供方可形象的称为数据的生产者,而数据的加工方则可以称为消费者,为了避免生产者生产数据的速率比消费者快,我们在生产者和消费者中间引入一个缓冲区(Channel,或者队列),对二者进行解耦,生产者将其“生产”的数据放入通道,消费者从相应的通道中消费数据,生产者和消费者各自运行在各自的线程中。模型图如下:
优点
1、模块解耦,可以把Excel 的解析和Excel 数据的处理并行执行。
2、提高吞吐率,对于用户同时进行Excel上传解析,能够更好的支持并发。
3、有效利用系统资源,提高执行任务处理效率。
角色介绍
生产者:Excel不同版本对应的解析工具类,XlsxExcelParse.java (Excel 2007+版本解析工具类 ) 和XlsExcelParse.java (Excel 2003版本解析工具类)
消费者:ImportDBConsumer.java 导入数据库消费者类
缓冲区:BlockCacheQueue.java为有界阻塞缓冲队列
该方案中,生产者生产数据放入缓冲区队列,消费者从缓冲区队列消费数据,由于处理的数据量较大,此处,该方案中将消费的数据放入临时集合中,当数据量达到默认事务提交数量后,进行事务提交,保存数据入库。
03实现方式
Excel 2007+版解析工具实现
XlsxExcelParse.java为Excel2007+版本解析的工具类,实现过程如下:
1、读取文件
OPCPackage pkg = OPCPackage.open(filePath);
XSSFReader r = new XSSFReader( pkg );
2、替换POI内置的XML文档解析器为SAXParser
XMLReader parser =
XMLReaderFactory.createXMLReader(
"org.apache.xerces.parsers.SAXParser"
);
ContentHandler handler = new PagingHandler(sst);
parser.setContentHandler(handler);
3、通过继承DefaultHandler类,实现自己处理XML文档标签的逻辑
/**
* 实现自己的处理逻辑
* See org.xml.sax.helpers.DefaultHandler javadocs
*/
private class PagingHandler extends DefaultHandler {
@Override
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
// ...
}
/**
* 每个单元格结束时的处理
*/
@Override
public void endElement(String uri, String localName, String name)
throws SAXException {
// ...
}
}
Excel 2003版解析工具实现
Excel2003ParserUtil为Excel2003版本解析的工具类,实现过程如下:
1、读取文件
FileInputStream fileInputStream = new FileInputStream(new File(fileAbsolutePath));
return new HSSFWorkbook(fileInputStream);
2、解析上传的Excel文件
for (int sheetNum = 0; sheetNum < totalSheetNum ; sheetNum++) {
this.currentSheetPage = sheetNum;
HSSFSheet sheet = workbook.getSheetAt(sheetNum);
parseSheet(sheet);
}
生产者的实现
通过继承AbstractProducer.java 抽象类,将上述的Excel解析工具类方法调用传入即可,则表示该工具类为生产者
/**
* 开始执行生产者任务
*/
public void task() {
if (CommonUtil.isXlsExcel(filePath)){
// 调用Excel 2003版本解析工具类方法
XlsExcelParse xlsExcelParse = new XlsExcelParse(cacheQueue);
xlsExcelParse.XlsExcelParser(filePath);
}else if (CommonUtil.isXlsxExcel(filePath)){
// 调用Excel 2007+版本解析工具类方法
XlsxExcelParse xlsxExcelParse = new XlsxExcelParse(cacheQueue);
xlsxExcelParse.parseExcel(filePath);
}
}
消费者的实现
通过继承AbstractConsumer.java抽象类,重写该接口中的方法即可
/**
* 消费者的任务
*/
public void task() {
// 消费数据
RowProperties rowProperties = cacheQueue.consume();
isTerminated = rowProperties.isTerminated();
// 处理消费者的业务逻辑
handleConsumerData(rowProperties);
}
// 判断所有任务是否执行完毕
public boolean isLastTask() {
return isTerminated;
}
缓冲区队列的实现
通过实现CacheQueue 接口,该方案采用有界阻塞队列来作为缓冲区队列
public class BlockCacheQueue implements CacheQueue {
// …
}
生产者消费者线程启动的实现
通过ThreadExecutor.java 类中方法executeTask() 进行产生 生产者线程、消费者线程、缓冲区队列的进程,控制整个线程的生命周期,默认为一个生产者和一个消费者。
/**
* 产生生产者和消费者执行任务
*/
public void executeTask(String fileAbsolutePath) throws FileNotFoundException {
if (!CommonUtil.existFile(fileAbsolutePath)){
throw new FileNotFoundException("文件不存在!");
}
CacheQueue cacheQueue = new BlockCacheQueue(new LinkedBlockingQueue());
// 线程管理器
ExecutorService executorService = Executors.newFixedThreadPool(CommonUtil.getDefaultThreadNum());
// 一个生产者
ExcelProducer producerThread = new ExcelProducer(cacheQueue,fileAbsolutePath);
// 一个消费者
ImportDBConsumer importDBConsumer = new ImportDBConsumer(cacheQueue);
// 分别提交执行任务
executorService.submit(producerThread);
executorService.submit(importDBConsumer);
// 结束执行任务
executorService.shutdown();
}
04测试结果
Excel2003版解析测试
测试环境:
Excel版本:Excel2003版 文件类型:xls
Excel数据量:65533 Excel列数:35列
Excel 文件大小:32.5M
测试方法:
采用Jdbc预处理方式进行提交,每次提交事务数据量为20000
测试结果:解析该测试excel文件共花费时间约:8秒。
Excel2007版解析测试
测试环境:
Excel版本:Excel2007版 文件类型:xlsx
Excel数据量:56万多 Excel列数:35列
Excel 文件大小:69.6M
测试方法:
采用Jdbc预处理方式进行提交,每次提交事务数据量为20000
测试结果:解析该测试excel文件共花费时间约:53秒,解析过程中内存消耗并无明显变化。
![56be029cc51aeb5c41bdbc9213d2037b.png](https://i-blog.csdnimg.cn/blog_migrate/7ad4bafec623ea4bce091c5f1f635a34.png)