java通过POI快速读取excel大量数据的方式

1.在pom.xml中引入poi相关依赖

<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-base</artifactId>
    <version>4.4.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-web</artifactId>
    <version>4.4.0</version>
</dependency>
<dependency>
    <groupId>cn.afterturn</groupId>
    <artifactId>easypoi-annotation</artifactId>
    <version>4.4.0</version>
</dependency>

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

2.添加工具类

 
import cn.afterturn.easypoi.excel.annotation.Excel;
import com.alibaba.fastjson.JSON;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.ss.usermodel.BuiltinFormats;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.XMLReader;
import org.xml.sax.helpers.DefaultHandler;
import org.xml.sax.helpers.XMLReaderFactory;
 
import java.io.InputStream;
import java.lang.reflect.Field;
import java.util.*;
 
 
/**
 * POI读取excel有两种模式，一种是用户模式，一种是事件驱动模式
 * 采用SAX事件驱动模式解决XLSX文件，可以有效解决用户模式内存溢出的问题，
 * 该模式是POI官方推荐的读取大数据的模式，
 * 在用户模式下，数据量较大，Sheet较多，或者是有很多无用的空行的情况下，容易出现内存溢出
 * <p>
 * 解决版本大数据量问题
 **/
public class ExcelXlsxReader extends DefaultHandler {
 
    //存储所有数据
    private List<List<String>> dataList = new ArrayList<>();
 
    //存储所有数据Map
    private Map<Integer, List<List<String>>> dataListMap = new HashMap<>();
 
    /**
     * 单元格中的数据可能的数据类型
     */
    enum CellDataType {
        BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
    }
 
    public ExcelXlsxReader() {
 
    }
 
    public ExcelXlsxReader(InputStream in) throws Exception {
        process(in);
    }
 
    /**
     * 共享字符串表
     */
    private SharedStringsTable sst;
 
    /**
     * 上一次的索引值
     */
    private String lastIndex;
 
    /**
     * 文件的绝对路径
     */
    private String filePath = "";
 
    /**
     * 工作表索引
     */
    private int sheetIndex = 0;
 
    /**
     * sheet名
     */
    private String sheetName = "";
 
    /**
     * 总行数
     */
    private int totalRows = 0;
 
    /**
     * 一行内cell集合
     */
    private List<String> cellList = new ArrayList<String>();
 
    /**
     * 判断整行是否为空行的标记
     */
    private boolean flag = false;
 
    /**
     * 当前行
     */
    private int curRow = 1;
 
    /**
     * 当前列
     */
    private int curCol = 0;
 
    /**
     * T元素标识
     */
    private boolean isTElement;
 
    /**
     * 判断上一单元格是否为文本空单元格
     */
    private boolean startElementFlag = true;
    private boolean endElementFlag = false;
    private boolean charactersFlag = false;
 
    /**
     * 异常信息，如果为空则表示没有异常
     */
    private String exceptionMessage;
 
    /**
     * 单元格数据类型，默认为字符串类型
     */
    private CellDataType nextDataType = CellDataType.SSTINDEX;
 
    private final DataFormatter formatter = new DataFormatter();
 
    /**
     * 单元格日期格式的索引
     */
    private short formatIndex;
 
    /**
     * 日期格式字符串
     */
    private String formatString;
 
    //定义前一个元素和当前元素的位置，用来计算其中空的单元格数量，如A6和A8等
    private String prePreRef = "A", preRef = null, ref = null;
 
    //定义该文档一行最大的单元格数，用来补全一行最后可能缺失的单元格
    private String maxRef = null;
 
    /**
     * 单元格
     */
    private StylesTable stylesTable;
 
    /**
     * 遍历工作簿中所有的电子表格
     * 并缓存在mySheetList中
     *
     * @param filename
     * @throws Exception
     */
    public int process(String filename) throws Exception {
        filePath = filename;
        OPCPackage pkg = OPCPackage.open(filename);
        XSSFReader xssfReader = new XSSFReader(pkg);
        stylesTable = xssfReader.getStylesTable();
        SharedStringsTable sst = xssfReader.getSharedStringsTable();
        XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
        this.sst = sst;
        parser.setContentHandler(this);
        XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        while (sheets.hasNext()) { //遍历sheet
            curRow = 1; //标记初始行为第一行
            sheetIndex++;
            InputStream sheet = sheets.next(); //sheets.next()和sheets.getSheetName()不能换位置，否则sheetName报错
            sheetName = sheets.getSheetName();
            InputSource sheetSource = new InputSource(sheet);
            parser.parse(sheetSource); //解析excel的每条记录，在这个过程中startElement()、characters()、endElement()这三个函数会依次执行
            sheet.close();
        }
        return totalRows; //返回该excel文件的总行数，不包括首列和空行
    }
 
    public Map<Integer, List<List<String>>> process(InputStream in) throws Exception {
        //filePath = filename;
        OPCPackage pkg = OPCPackage.open(in);
        XSSFReader xssfReader = new XSSFReader(pkg);
        stylesTable = xssfReader.getStylesTable();
        SharedStringsTable sst = xssfReader.getSharedStringsTable();
        //XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
        XMLReader parser = XMLReaderFactory.createXMLReader();
        this.sst = sst;
        parser.setContentHandler(this);
        XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
        while (sheets.hasNext()) { //遍历sheet
            curRow = 1; //标记初始行为第一行
            InputStream sheet = sheets.next(); //sheets.next()和sheets.getSheetName()不能换位置，否则sheetName报错
            sheetName = sheets.getSheetName();
            InputSource sheetSource = new InputSource(sheet);
            parser.parse(sheetSource); //解析excel的每条记录，在这个过程中startElement()、characters()、endElement()这三个函数会依次执行
            //每次都新创建一个数组对象对应一个sheet页
            dataList = new ArrayList<>();
            sheet.close();
            sheetIndex++;
        }
        return dataListMap;
    }
 
    /**
     * 获取文件解析信息
     *
     * @param in        文件流
     * @param sheetNo   sheet索引
     * @param headNo    表头位置 从表头开始遍历数据
     * @param pojoClass 类对象
     */
    public <T> List<T> process(InputStream in, int sheetNo, int headNo, Class<T> pojoClass) throws Exception {
        Map<Integer, List<List<String>>> process = process(in);
        List<List<String>> dataList = process.get(sheetNo);
        if (dataList == null) {
            return null;
        }
 
 
        List<T> result = new ArrayList<>();
        Map<String, String> fieldName = getFieldName(pojoClass);
        Map<String, Integer> headMap = new HashMap<>();
        Map<String, Integer> allFieldMap = new HashMap<>();
        for (int i = headNo; i < dataList.size(); i++) {
            List<String> data = dataList.get(i);
            if (i == headNo) {
                for (int j = 0; j < data.size(); j++) {
                    String value = data.get(j);
                    headMap.put(value, j);
                }
                for (String key : fieldName.keySet()) {
                    String var1 = fieldName.get(key); // 字段名
                    Integer var2 = headMap.get(key);  // 字段索引位置
                    if ((var1 != null && var1.trim().length() > 0) && var2 != null) {
                        allFieldMap.put(var1, var2);
                    }
                }
            } else {
                T o = pojoClass.newInstance();
                setValue(o, data, allFieldMap);
                result.add(o);
            }
        }
        return result;
    }
 
    private <T> void setValue(T o, List<String> data, Map<String, Integer> allFieldMap) throws IllegalAccessException {
        List<Field> allFields = getAllFields(o.getClass());
        for (Field field : allFields) {
            field.setAccessible(true);
            String name = field.getName();
            Integer integer = allFieldMap.get(name);
            if (integer != null) {
                Class<?> type = field.getType();
                field.set(o, JSON.parseObject(JSON.toJSONString(data.get(integer)), type));
            }
        }
    }
 
    private List<Field> getAllFields(Class<?> aClass) {
        List<Field> fieldList = new ArrayList<>();
        while (aClass != null && !aClass.getName().equalsIgnoreCase("java.lang.object")) {
            fieldList.addAll(Arrays.asList(aClass.getDeclaredFields()));
            aClass = aClass.getSuperclass(); //得到父类,然后赋给自己
        }
        return fieldList;
    }
 
    private <T> Map<String, String> getFieldName(Class<T> pojoClass) {
        Map<String, String> result = new HashMap<>();
        List<Field> allFields = getAllFields(pojoClass);
        for (Field field : allFields) {
            field.setAccessible(true);
            String name = field.getName();
            Excel excel = field.getAnnotation(Excel.class);
            if (excel != null) {
                result.put(excel.name(), name);
            }
        }
        return result;
    }
 
    /**
     * 第一个执行
     *
     * @param uri
     * @param localName
     * @param name
     * @param attributes
     * @throws SAXException
     */
    @Override
    public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
        //c => 单元格
        if ("c".equals(name)) {
 
            //前一个单元格的位置
            if (preRef == null) {
                preRef = attributes.getValue("r");
 
            } else {
                //中部文本空单元格标识 ‘endElementFlag’ 判断前一次是否为文本空字符串，true则表明不是文本空字符串，false表明是文本空字符串跳过把空字符串的位置赋予preRef
                if (endElementFlag) {
                    preRef = ref;
                }
            }
 
            //当前单元格的位置
            ref = attributes.getValue("r");
            //首部文本空单元格标识 ‘startElementFlag’ 判断前一次，即首部是否为文本空字符串，true则表明不是文本空字符串，false表明是文本空字符串, 且已知当前格，即第二格带“B”标志，则ref赋予preRef
            if (!startElementFlag && !flag) { //上一个单元格为文本空单元格，执行下面的，使ref=preRef；flag为true表明该单元格之前有数据值，即该单元格不是首部空单元格，则跳过
                // 这里只有上一个单元格为文本空单元格，且之前的几个单元格都没有值才会执行
                preRef = ref;
            }
 
            //设定单元格类型
            this.setNextDataType(attributes);
            endElementFlag = false;
            charactersFlag = false;
            startElementFlag = false;
        }
 
        //当元素为t时
        if ("t".equals(name)) {
            isTElement = true;
        } else {
            isTElement = false;
        }
 
        //置空
        lastIndex = "";
    }
 
 
    /**
     * 第二个执行
     * 得到单元格对应的索引值或是内容值
     * 如果单元格类型是字符串、INLINESTR、数字、日期，lastIndex则是索引值
     * 如果单元格类型是布尔值、错误、公式，lastIndex则是内容值
     *
     * @param ch
     * @param start
     * @param length
     * @throws SAXException
     */
    @Override
    public void characters(char[] ch, int start, int length) throws SAXException {
        startElementFlag = true;
        charactersFlag = true;
        lastIndex += new String(ch, start, length);
    }
 
    /**
     * 第三个执行
     * 个人理解：基本上的数据填充都是在这一步了，而cellList则是作为一个数组，里面记录的是每一行数据的内容
　　　* 至于cellList.clear();为什么会改为用 cellList = new ArrayList<String>(); 
     * 因为引用的关系，虽然塞入到了dataListMap里面，但是由于cellList的引用还是指向那块内存，
　　　* 所以如果clear()的话那块内存的内容也会被清空，导致dataListMap里面的内容也被清空。
     * 所以如果要用cellList.clear()的话，对于dataList.add(cellList);应该改为dataList.add(new ArrayList<>(cellList));本质上是开辟另一块内存存储这块的数据
     * @param uri
     * @param localName
     * @param name
     * @throws SAXException
     */
    @Override
    public void endElement(String uri, String localName, String name) throws SAXException {
        //t元素也包含字符串
        if (isTElement) {
            //将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
            String value = lastIndex.trim();
            cellList.add(curCol, value);
            endElementFlag = true;
            curCol++;
            isTElement = false;
            //如果里面某个单元格含有值，则标识该行不为空行
            if (!"".equals(value)) {
                flag = true;
            }
        } else if ("v".equals(name)) {
            //v => 单元格的值，如果单元格是字符串，则v标签的值为该字符串在SST中的索引
            String value = this.getDataValue(lastIndex.trim(), "");//根据索引值获取对应的单元格值
 
            //补全单元格之间的空单元格
            if (!ref.equals(preRef)) {
                int len = countNullCell(ref, preRef);
                for (int i = 0; i < len; i++) {
                    cellList.add(curCol, "");
                    curCol++;
                }
            } else if (!ref.startsWith("A")) { //ref等于preRef，且以B或者C...开头，表明首部为空格
                int len = countNullCell(ref, "A");
                for (int i = 0; i <= len; i++) {
                    cellList.add(curCol, "");
                    curCol++;
                }
            }
            cellList.add(curCol, value);
            curCol++;
            endElementFlag = true;
            //如果里面某个单元格含有值，则标识该行不为空行
            if (value != null && !"".equals(value)) {
                flag = true;
            }
        } else {
            //如果标签名称为row，这说明已到行尾，调用optRows()方法
            if ("row".equals(name)) {
                //默认第一行为表头，以该行单元格数目为最大数目
                if (curRow == 1) {
                    maxRef = ref;
                }
                //补全一行尾部可能缺失的单元格
                if (maxRef != null) {
                    int len = -1;
                    //前一单元格，true则不是文本空字符串，false则是文本空字符串
                    if (charactersFlag) {
                        len = countNullCell(maxRef, ref);
                    } else {
                        len = countNullCell(maxRef, preRef);
                    }
                    for (int i = 0; i <= len; i++) {
                        cellList.add(curCol, "");
                        curCol++;
                    }
                }
 
                if (flag) { //该行不为空行则发送（第一行为列名）
                    //ExcelReaderUtil.sendRows(filePath, sheetName, sheetIndex, curRow, cellList);
                    totalRows++;
                    //添加到数据集合中
 
                    dataList.add(cellList);
                }
                //将当前sheet页的数据存为map，这样支持多个sheet页的海量数据
                dataListMap.put(sheetIndex, dataList);
                //清空容器
                cellList = new ArrayList<String>();
                //cellList.clear();
                curRow++;
                curCol = 0;
                preRef = null;
                prePreRef = null;
                ref = null;
                flag = false;
            }
        }
    }
 
    /**
     * 处理数据类型
     *
     * @param attributes
     */
    public void setNextDataType(Attributes attributes) {
        nextDataType = CellDataType.NUMBER; //cellType为空，则表示该单元格类型为数字
        formatIndex = -1;
        formatString = null;
        String cellType = attributes.getValue("t"); //单元格类型
        String cellStyleStr = attributes.getValue("s"); //
        String columnData = attributes.getValue("r"); //获取单元格的位置，如A1,B1
 
        if ("b".equals(cellType)) { //处理布尔值
            nextDataType = CellDataType.BOOL;
        } else if ("e".equals(cellType)) {  //处理错误
            nextDataType = CellDataType.ERROR;
        } else if ("inlineStr".equals(cellType)) {
            nextDataType = CellDataType.INLINESTR;
        } else if ("s".equals(cellType)) { //处理字符串
            nextDataType = CellDataType.SSTINDEX;
        } else if ("str".equals(cellType)) {
            nextDataType = CellDataType.FORMULA;
        }
 
        if (cellStyleStr != null) { //处理日期
            int styleIndex = Integer.parseInt(cellStyleStr);
            XSSFCellStyle style = stylesTable.getStyleAt(styleIndex);
            formatIndex = style.getDataFormat();
            formatString = style.getDataFormatString();
            if (formatString.contains("m/d/yyyy") || formatString.contains("yyyy/mm/dd") || formatString.contains("yyyy/m/d")) {
                nextDataType = CellDataType.DATE;
                formatString = "yyyy-MM-dd hh:mm:ss";
            }
 
            if (formatString == null) {
                nextDataType = CellDataType.NULL;
                formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
            }
        }
    }
 
    /**
     * 对解析出来的数据进行类型处理
     *
     * @param value   单元格的值，
     *                value代表解析：BOOL的为0或1， ERROR的为内容值，FORMULA的为内容值，INLINESTR的为索引值需转换为内容值，
     *                SSTINDEX的为索引值需转换为内容值， NUMBER为内容值，DATE为内容值
     * @param thisStr 一个空字符串
     * @return
     */
    @SuppressWarnings("deprecation")
    public String getDataValue(String value, String thisStr) {
        switch (nextDataType) {
            // 这几个的顺序不能随便交换，交换了很可能会导致数据错误
            case BOOL: //布尔值
                char first = value.charAt(0);
                thisStr = first == '0' ? "FALSE" : "TRUE";
                break;
            case ERROR: //错误
                thisStr = "\"ERROR:" + value.toString() + '"';
                break;
            case FORMULA: //公式
                thisStr = '"' + value.toString() + '"';
                break;
            case INLINESTR:
                XSSFRichTextString rtsi = new XSSFRichTextString(value.toString());
                thisStr = rtsi.toString();
                rtsi = null;
                break;
            case SSTINDEX: //字符串
                String sstIndex = value.toString();
                try {
                    int idx = Integer.parseInt(sstIndex);
                    XSSFRichTextString rtss = new XSSFRichTextString(sst.getEntryAt(idx));//根据idx索引值获取内容值
                    thisStr = rtss.toString();
                    //System.out.println(thisStr);
                    //有些字符串是文本格式的，但内容却是日期
 
                    rtss = null;
                } catch (NumberFormatException ex) {
                    thisStr = value.toString();
                }
                break;
            case NUMBER: //数字
                if (formatString != null) {
                    thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString).trim();
                } else {
                    thisStr = value;
                }
                thisStr = thisStr.replace("_", "").trim();
                break;
            case DATE: //日期
                thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString);
                // 对日期字符串作特殊处理，去掉T
                thisStr = thisStr.replace("T", " ");
                break;
            default:
                thisStr = " ";
                break;
        }
        return thisStr;
    }
 
    public int countNullCell(String ref, String preRef) {
        //excel2007最大行数是1048576，最大列数是16384，最后一列列名是XFD
        String xfd = ref.replaceAll("\\d+", "");
        String xfd_1 = preRef.replaceAll("\\d+", "");
 
        xfd = fillChar(xfd, 3, '@', true);
        xfd_1 = fillChar(xfd_1, 3, '@', true);
 
        char[] letter = xfd.toCharArray();
        char[] letter_1 = xfd_1.toCharArray();
        int res = (letter[0] - letter_1[0]) * 26 * 26 + (letter[1] - letter_1[1]) * 26 + (letter[2] - letter_1[2]);
        return res - 1;
    }
 
    public String fillChar(String str, int len, char let, boolean isPre) {
        int len_1 = str.length();
        if (len_1 < len) {
            StringBuilder strBuilder = new StringBuilder(str);
            if (isPre) {
                for (int i = 0; i < (len - len_1); i++) {
                    strBuilder.insert(0, let);
                }
            } else {
                for (int i = 0; i < (len - len_1); i++) {
                    strBuilder.append(let);
                }
            }
            str = strBuilder.toString();
        }
        return str;
    }
 
    /**
     * @return the exceptionMessage
     */
    public String getExceptionMessage() {
        return exceptionMessage;
    }
}

3.根据下载地址获取流文件

/**
 * 根据url下载文件流
 * @param urlStr
 * @return
 */
public static InputStream getInputStreamFromUrl(String urlStr) {
    InputStream inputStream=null;
    try {
        //url解码
        URL url = new URL(java.net.URLDecoder.decode(urlStr, "UTF-8"));
        HttpURLConnection conn = (HttpURLConnection) url.openConnection();
        //设置超时间为3秒
        conn.setConnectTimeout(3 * 1000);
        //防止屏蔽程序抓取而返回403错误
        conn.setRequestProperty("User-Agent", "Mozilla/4.0 (compatible; MSIE 5.0; Windows NT; DigExt)");
        //得到输入流
        inputStream = conn.getInputStream();
    } catch (IOException e) {
        e.printStackTrace();
    }
    return inputStream;
}

 

4.使用方法

//文件流
InputStream inputStream = getInputStreamFromUrl("http://localhost:8080/file/xxxx.xlsx");
//进行解析
ExcelXlsxReader xlsxReader = new ExcelXlsxReader();
List<UserInfo> excList = xlsxReader.process(inputStream,1,1,UserInfo.class);

备注：

本文内容仅是个人笔记记录，内容转自  https://blog.csdn.net/u011414736/article/details/128231706 ；

至于cellList.clear();为什么会改为用 cellList = new ArrayList<String>(); 因为引用的关系，虽然塞入到了dataListMap里面，但是由于cellList的引用还是指向那块内存，所以如果clear()的话那块内存的内容也会被清空，导致dataListMap里面的内容也被清空。所以如果要用cellList.clear()的话，对于dataList.add(cellList);应该改为dataList.add(new ArrayList<>(cellList));本质上是开辟另一块内存存储这块的数据