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依赖

	
		
			org.springframework.boot
			spring-boot-starter-web
		

		
		
			org.apache.poi
			poi
			5.1.0
		
		
			org.apache.poi
			poi-ooxml
			5.1.0
		

		
			com.alibaba
			easyexcel
			3.0.5
		

		
			com.alibaba
			fastjson
			1.2.78
		

		
			org.springframework.boot
			spring-boot-devtools
			runtime
			true
		
		
			org.springframework.boot
			spring-boot-starter-test
			test
		
		
			org.projectlombok
			lombok
			RELEASE
			compile
		
	

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 {

    private static final int BATCH_COUNT = 10000;
    private List 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 allData = new ArrayList();
            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 allData) {
        myTestDAO.save(allData);
    }

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

可以看到

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