JAVA处理Excel(alibaba/easyexcel 和 ApachePOI)(一)

我们的项目处理Excel用的都是POI但是后面觉得POI实在是太占用内存了，后面搜到了easyexcel这个是基于POI做了优化然后解决了内存占用过大的问题，接下来比较下这2个jar看看easyexcel的内存到底优化了多少

使用到的工具和框架：

 Intellij IDEA 

 Sprintboot

 alibaba/EasyExcel

 apache/POI

1. 首先使用Spring Initializr初始化一个简单的Springboot web项目出来方便以后进行扩展和调试

然后引入easyExcel 和POI的maven依赖

	<dependencies>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-web</artifactId>
		</dependency>

		<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi</artifactId>
			<version>5.1.0</version>
		</dependency>
		<dependency>
			<groupId>org.apache.poi</groupId>
			<artifactId>poi-ooxml</artifactId>
			<version>5.1.0</version>
		</dependency>

		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>easyexcel</artifactId>
			<version>3.0.5</version>
		</dependency>

		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>fastjson</artifactId>
			<version>1.2.78</version>
		</dependency>

		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-devtools</artifactId>
			<scope>runtime</scope>
			<optional>true</optional>
		</dependency>
		<dependency>
			<groupId>org.springframework.boot</groupId>
			<artifactId>spring-boot-starter-test</artifactId>
			<scope>test</scope>
		</dependency>
		<dependency>
			<groupId>org.projectlombok</groupId>
			<artifactId>lombok</artifactId>
			<version>RELEASE</version>
			<scope>compile</scope>
		</dependency>
	</dependencies>

2. 接下来编写easyExcel的读取excel的方法

    我们这边要构造一个com.alibaba.excel.read.listener.ReadListener;  easyExcel主要使用这个类来进行excel的读取

    public void easyExcelRead() {
        String fileName = basePath + "/Book3.xlsx";
        EasyExcel.read(fileName, BusinessBean.class, new DemoDataListener(myTestDAO, System.currentTimeMillis())).sheet(0).doRead();
    }

  我们自己新建的类DemoDataListener继承ReadListener，里面主要实现2个方法

   public void invoke(BusinessBean data, AnalysisContext context) 

   这个方法是每行调用一次可以处理当前行的数据

   public void doAfterAllAnalysed(AnalysisContext context) 

   doAfterAllAnalysed这个方法只调用一次就是数据全部处理完最后调用

package com.kyle.excel.service.easyExcel;

import com.kyle.excel.bean.BusinessBean;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.read.listener.ReadListener;
import com.alibaba.excel.util.ListUtils;
import com.kyle.excel.dao.MyTestDAO;
import lombok.extern.slf4j.Slf4j;

import java.util.List;

@Slf4j
public class DemoDataListener implements ReadListener<BusinessBean> {

    private static final int BATCH_COUNT = 10000;
    private List<BusinessBean> cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
    private int totalCount = 0;
    private MyTestDAO myTestDAO;
    private Long start = System.currentTimeMillis();

    public DemoDataListener() {
        myTestDAO = new MyTestDAO();
    }

    public DemoDataListener(MyTestDAO myTestDAO, Long start) {
        this.myTestDAO = myTestDAO;
        this.start = start;
    }


    @Override
    public void invoke(BusinessBean data, AnalysisContext context) {
        totalCount++;
        cachedDataList.add(data);
        if (cachedDataList.size() >= BATCH_COUNT) {
            saveData();
            cachedDataList = ListUtils.newArrayListWithExpectedSize(BATCH_COUNT);
        }
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {
        saveData();
        log.info("total record number: {} and cost time: {}", totalCount, (System.currentTimeMillis() - start));
    }

    private void saveData() {
        myTestDAO.save(cachedDataList);
    }
}

3. 接下来写POI调用测试

    POI需要先根据文件流构造XSSFWorkBook或者HSSFWorkBook(根据excel版本来选取xlsx,xls),然后workbook对象里面可以拿到Sheet然后继续可以拿到row还有cell

public void readFile() {
        File file = new File(basePath + "/Book3.xlsx");
        try {
            long start = System.currentTimeMillis();
            FileInputStream fileInputStream = new FileInputStream(file);
            //Get the workbook instance for XLSX file
            XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
            List<BusinessBean> allData = new ArrayList<BusinessBean>();
            int totalNumber = 0;
            if (!file.isFile() || !file.exists()) {
                log.info("Error to open open workbook.xlsx file.");
                return;
            }
            log.info("open workbook.xlsx file open successfully.");
            Sheet sheet = workbook.getSheetAt(0);
            for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
                BusinessBean data = new BusinessBean();
                data.setName(sheet.getRow(i).getCell(0).getStringCellValue());
                data.setDate(sheet.getRow(i).getCell(1).getDateCellValue());
                data.setDoubleData(sheet.getRow(i).getCell(2).getNumericCellValue());
                data.setDesc(sheet.getRow(i).getCell(3).getStringCellValue());
                data.setCreate(sheet.getRow(i).getCell(4).getDateCellValue());
                data.setAge(sheet.getRow(i).getCell(5).getNumericCellValue());
                if (allData.size() > batchCount) {
                    saveData(allData);
                    allData.clear();
                }
                allData.add(data);
                totalNumber++;
            }
            saveData(allData);
            log.info("total record number is: {} and cost time: {}", totalNumber, (System.currentTimeMillis() - start));

        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    private void saveData(List<BusinessBean> allData) {
        myTestDAO.save(allData);
    }

4. 接下来然后运行一下然后看结果，我这的源文件是一共1.8M大小，然后有44625多条数据

可以看到

结果

	内存消耗(m)	花费时间(ms)
POI	449	1700
EasyExcel	18	507
	25倍	3.4倍

所以目前来看easyExcel和POI相比性能和内存这方面还是有很大的提升的