Java解析Excel内存占用更低的解析方式,EasyExcel

Java解析Excel内存占用更低的解析方式,EasyExcel

Java解析以及生成Excel有许多工具,Like(POI,JXL),但是他们的内存占用都比较严重,今天给大家介绍一款来自Alibaba的框架:EasyExcel。经过我不太严谨的初步测试(哈哈),能够使内存占用减少80%左右(注意啊不严谨的测试,数据仅供参考,具体可以自己测试一下哈),接下来进入正题。

---

EasyExcel解析

EasyExcel的GitHub

Jar包

<!-- https://mvnrepository.com/artifact/com.alibaba/easyexcel -->
<dependency>
	<groupId>com.alibaba</groupId>
	<artifactId>easyexcel</artifactId>
	<version>1.1.2-beta4</version>
</dependency>

代码如下
 

import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;

import java.util.ArrayList;
import java.util.List;

public class ExcelListener extends AnalysisEventListener {


    private List<Object> data = new ArrayList<Object>();

    @Override
    public void invoke(Object object, AnalysisContext context) {
       data.add(object);
    }

    @Override
    public void doAfterAllAnalysed(AnalysisContext context) {

    }
}

import com.alibaba.excel.EasyExcelFactory;
import com.alibaba.excel.metadata.Sheet;

import java.io.IOException;
import java.io.InputStream;

public class ExcelUtils {

    public static ExcelListener saxReadListStringV2007(String filePath) {
        InputStream inputStream = null;
        ExcelListener excelListener = null;
        try {
            inputStream = Thread.currentThread().getContextClassLoader().getResourceAsStream(filePath);
            excelListener = new ExcelListener();
            EasyExcelFactory.readBySax(inputStream, new Sheet(1, 1), excelListener);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(inputStream != null) {
                    inputStream.close();
                }
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return excelListener;
    }

    public static void main(String[] args) {
        System.out.println("解析Excel前内存:"+(Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory())/(1024)+"KB");
        ExcelListener excelListener = ExcelUtils.saxReadListStringV2007("test.xls");
        System.out.println("解析Excel后内存:"+(Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory())/(1024)+"KB");
        System.out.println();

    }
}

注意:传入saxReadListStringV2007的FilePath是classpath相对路径哦。

测试结果

约占内存(21120-3972)/1024=16.7MB

 

 

POI解析(用于与EasyExcel对比测试)

注:此部分代码懒得写了,网上找了一个工具类

 

Jar包

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>4.0.0</version>
</dependency>

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>4.0.0</version>
</dependency>

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml-schemas</artifactId>
	<version>4.0.0</version>
</dependency>

代码如下

import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class POIExcel {

    private int totalRows = 0;// 总行数
    private int totalCells = 0;// 总列数

    public Map<String, List<List<String>>> read(String fileName) {
        Map<String, List<List<String>>> maps = new HashMap<String, List<List<String>>>();
        if (fileName == null || !fileName.matches("^.+\\.(?i)((xls)|(xlsx))$"))
            return maps;
        File file = new File(fileName);
        if (file == null || !file.exists())
            return maps;
        try {
            Workbook wb = WorkbookFactory.create(new FileInputStream(file));
            maps = read(wb);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return maps;
    }

    public int getTotalRows() {
        return totalRows;
    }

    public int getTotalCells() {
        return totalCells;
    }

    private Map<String, List<List<String>>> read(Workbook wb) {
        Map<String, List<List<String>>> maps = new HashMap<String, List<List<String>>>();
        int number = wb.getNumberOfSheets();
        if (number > 0) {
            for (int i = 0; i < number; i++) { // 循环每个工作表
                List<List<String>> list = new ArrayList<List<String>>();
                int delnumber = 0;// 第一页去除行数
                Sheet sheet = wb.getSheetAt(i);
                this.totalRows = sheet.getPhysicalNumberOfRows() - delnumber; // 获取工作表中行数
                if (this.totalRows >= 1 && sheet.getRow(delnumber) != null) {
                    this.totalCells = sheet.getRow(0)
                            .getPhysicalNumberOfCells(); // 得到当前行的所有单元格
                    for (int j = 0; j < totalRows; j++) {
                        List<String> rowLst = new ArrayList<String>();
                        for (int f = 0; f < totalCells; f++) {
                            if (totalCells > 0) {
                                String value = getCell(sheet.getRow(j).getCell(f));
                                rowLst.add(value);
                            }
                        }
                        list.add(rowLst);
                    }
                }
                maps.put(sheet.getSheetName(), list);
            }
        }
        return maps;
    }

    public String getCell(Cell cell) {
        String cellValue = null;
        HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
        cellValue = hSSFDataFormatter.formatCellValue(cell); // 使用EXCEL原来格式的方式取得值
        return cellValue;
    }

    public static void main(String[] args) {
        try {
            System.out.println("解析Excel前内存:"+(Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory())/(1024)+"KB");

            Map<String, List<List<String>>> map = new POIExcel()
                    .read("I:\\test.xls");
            System.out.println("解析Excel后内存:"+(Runtime.getRuntime().totalMemory() - Runtime.getRuntime().freeMemory())/(1024)+"KB");

            System.out.println(map);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

测试结果

约占内存(121144-3989)/1024=114.4MB