poi事件驱动读取大数据量excel(50W+数据)工具

一、前言

  众所周知，普通的poi读取可以操作小数据了的excel，但是如果excel数据量较大时，就会导致读取速度慢，甚至出现OOM。

这时候我们就不再使用普通的POI包中HSSFWorkbook,改为流处理,话不多少直接上代码：

1.添加依赖

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

        <!-- https://mvnrepository.com/artifact/xerces/xercesImpl -->
        <dependency>
            <groupId>xerces</groupId>
            <artifactId>xercesImpl</artifactId>
            <version>2.11.0</version>
        </dependency>

2.自定义sheet基于Sax的解析处理器

  创建SheetHandler类，用于处理单元格获取数据的操作

package com.rr;

import com.sun.istack.internal.NotNull;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.usermodel.XSSFComment;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @description: 自定义sheet基于Sax的解析处理器
 * @author: Yhq
 * @data: 2020/07/15 15:59
 * @version: V1.0.0
 * @Copyright: 2020
 */
public class SheetHandler implements XSSFSheetXMLHandler.SheetContentsHandler {
    /**
     * 封装实体对象
     */
    private Map<String, String> map;

    /**
     * 实体对象集合
     */
    private List<Map<String, String>> mapsList = new ArrayList<>(MAX_EMPLOYEE);

    /**
     * 集合最大容量
     */
    private static final int MAX_EMPLOYEE = 1024;

    private Map<String, String> keyMap;

    private Map<String, String> fieldMapping;

    /**
     * 第几次插入数据，初始值为1
     */
    private int times = 1;

    /**
     * 总数据量
     */
    private int allCount = 0;

    /**
     * 当开始解析某一行的时候出发
     *
     * @param i 行号
     */
    @Override
    public void startRow(int i) {
        if (i > 0) {
            map = new HashMap<>(16);
        } else {
            keyMap = new HashMap<>(16);
        }
    }

    /**
     * 当结束解析某一行的时候出发
     *
     * @param i 行号
     */
    @Override
    public void endRow(int i) {
        if (map != null) {
            mapsList.add(map);
            allCount++;
        }
        //当读取的数据达到最大容量时 进行添加（类似于分批插入）
        if (mapsList.size() == MAX_EMPLOYEE) {
            // 假设有一个批量插入
            System.out.println("执行第" + times + "次插入");
            times++;
            mapsList.clear();
        }
    }

    /**
     * 对行中的每一个单元格进行处理
     *
     * @param cellName    单元格名称
     * @param value       数据
     * @param xssfComment 批注
     */
    @Override
    public void cell(String cellName, String value, XSSFComment xssfComment) {
        String prefix = cellName.substring(0, cellName.length() - 1);
        if (map != null) {
            map.put(keyMap.get(prefix), value);
        } else {
            keyMap.put(prefix, fieldMapping.get(value));
        }
    }

    public List<Map<String, String>> getEmployeeList() {
        return mapsList;
    }

    public int getAllCount() {
        return allCount;
    }

    public SheetHandler(@NotNull Map<String, String> fieldMapping) throws Exception {
        if (fieldMapping == null || fieldMapping.size() == 0) {
            throw new Exception("字段中英文对应关系不能为空");
        }
        this.fieldMapping = fieldMapping;
    }
}

3.编写main方法进行测试

package com.rr;

import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.XMLReaderFactory;

import java.io.InputStream;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @description:
 * @author: Yhq
 * @data: 2020/07/16 18:09
 * @version: V1.0.0
 * @Copyright: 2020
 */
public class Test {

    static String[] fileName = {"经销商编码","经销商名称","原始产品名称","原始产品品规","原始单位","标准产品编码","标准产品名称","标准产品规格"};

    static String[] eFileName = {"dealer_code","name_of_distributor","original_product_name",
            "original_product_specification","original_unit","standard_product_code",
            "standard_product_name","standard_product_specifications"};

    public static void main(String[] args) throws Exception {
//        InputStream is = new FileInputStream(new File(""));
//        OPCPackage opcPackage = OPCPackage.open(is);

        HashMap<String, String> map = new HashMap<>(16);
        for (int i = 0; i < fileName.length; i++) {
            map.put(fileName[i],eFileName[i]);
        }
        //  第一个表格从第三行开始获取
        long l = System.nanoTime();
        // 1.根据excel报表获取OPCpackage
        String path = "H:\\data\\excel\\产品对应关系导入模板01.xlsx";
        OPCPackage opcPackage = OPCPackage.open(path, PackageAccess.READ);
        // 2.创建XSSFReader
        XSSFReader xssfReader = new XSSFReader(opcPackage);
        // 3.获取SharedStringTable对象
        SharedStringsTable sharedStringsTable = xssfReader.getSharedStringsTable();
        // 4.获取styleTable对象
        StylesTable stylesTable = xssfReader.getStylesTable();
        // 5.创建sax xmlReader对象
        XMLReader xmlReader = XMLReaderFactory.createXMLReader();
        // 6.注册事件驱动处理器
        SheetHandler sheetHandler = new SheetHandler(map);
        XSSFSheetXMLHandler xssfSheetXMLHandler = new XSSFSheetXMLHandler(stylesTable, sharedStringsTable, sheetHandler, false);
        xmlReader.setContentHandler(xssfSheetXMLHandler);
        // 7.逐行读取
        XSSFReader.SheetIterator sheetIterator = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        while (sheetIterator.hasNext()) {
            InputStream in = sheetIterator.next();
            InputSource is = new InputSource(in);
            xmlReader.parse(is);
        }
        // 剩余未插入的数据插入
        List<Map<String, String>> employeeList = sheetHandler.getEmployeeList();
        System.out.println("最后一次插入数据，本次数据量为" + employeeList.size());
        System.out.println("共插入" + sheetHandler.getAllCount() + "条数据");
        long end = System.nanoTime();
        System.out.println((end - l) / 1000000000L + "秒");
    }
}

4.准备测试数据，这里准了60W+

5.运行后查看结果

这里看到读取60W数据打印只用了5秒，速度还是可以的。

注：这个代码可以直接复制使用,项目中可以稍微修改一下，做成工具使用。

这里大数据excel读取就说到这里，详细用法请查看http://poi.apache.org/components/spreadsheet/how-to.html#xssf_sax_api