apache poi 写excel 大数据_Excel大数据量解析优化方案

Excel大数据量解析优化方案

点击上方“技术支持联盟”，选择“置顶公众号”

第一时间了解程序员大小事儿

作者:龙遨星海，80后程序员，热爱编程，喜欢解决技术问题，体验编码的喜悦。

摘要:大数据量Excel导入数据到数据库时，经常会导致内存溢出、报错或者耗费时间过长，本方案可以很好的解决此问题。

01

问题背景

在常规企业应用系统中，大部分会用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秒,解析过程中内存消耗并无明显变化。

END