应用一般的POI或者其余的技术形式解决Excel文件会呈现IDEA内存溢出问题的，本办法实现50万条数据的轻松实现，大略须要300多秒的解决工夫左右，实现对手机号码或其余数据格式的信息读取解决，须要写出文件操作能够另行写一个办法写出，本办法只是实现读取Excel文件！
一、增加所需依赖：
<dependency> <groupId>com.monitorjbl</groupId> <artifactId>xlsx-streamer</artifactId> <version>1.2.0</version> </dependency> <dependency> <groupId>org.eclipse.birt.runtime.3_7_1</groupId> <artifactId>org.apache.xerces</artifactId> <version>2.9.0</version> </dependency> <dependency> 二、新建工具类
package com.cy.exceldata.util;
import java.io.InputStream;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.junit.jupiter.api.Test;
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;
/**
- XSSF and SAX (Event API)
- 能防止内存溢出问题的大数据Excel文件数据处理
*/
public abstract class BigDataParseExcel extends DefaultHandler {
private SharedStringsTable sst;private String lastContents;private boolean nextIsString;private int sheetIndex = -1;private List<String> rowlist = new ArrayList<String>();private int curRow = 0; //以后行private int curCol = 0; //当前列索引private int preCol = 0; //上一列列索引private int titleRow = 0; //题目行，个别状况下为0private int rowsize = 0; //列数//excel记录行操作方法，以sheet索引，行索引和行元素列表为参数，对sheet的一行元素进行操作，元素为String类型public abstract void optRows(int sheetIndex, int curRow, List<String> rowlist) throws SQLException;//只遍历一个sheet，其中sheetId为要遍历的sheet索引，从1开始，1-3/** * @param filename * @param sheetId sheetId为要遍历的sheet索引，从1开始，1-3 * @throws Exception */public void processOneSheet(String filename, int sheetId) throws Exception { OPCPackage pkg = OPCPackage.open(filename); XSSFReader r = new XSSFReader(pkg); SharedStringsTable sst = r.getSharedStringsTable(); XMLReader parser = fetchSheetParser(sst); // rId2 found by processing the Workbook // 依据 rId# 或 rSheet# 查找sheet InputStream sheet2 = r.getSheet("rId" + sheetId); sheetIndex++; InputSource sheetSource = new InputSource(sheet2); parser.parse(sheetSource); sheet2.close();}/** * 遍历 excel 文件 */public void process(String filename) throws Exception { OPCPackage pkg = OPCPackage.open(filename); XSSFReader r = new XSSFReader(pkg); SharedStringsTable sst = r.getSharedStringsTable(); XMLReader parser = fetchSheetParser(sst); Iterator<InputStream> sheets = r.getSheetsData(); while (sheets.hasNext()) { curRow = 0; sheetIndex++; InputStream sheet = sheets.next(); InputSource sheetSource = new InputSource(sheet); parser.parse(sheetSource); sheet.close(); }}public XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException { XMLReader parser = XMLReaderFactory.createXMLReader(); //.createXMLReader("org.apache.xerces.parsers.SAXParser"); this.sst = sst; parser.setContentHandler(this); return parser;}public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException { // c => 单元格 if (name.equals("c")) { // 如果下一个元素是 SST 的索引，则将nextIsString标记为true String cellType = attributes.getValue("t"); String rowStr = attributes.getValue("r"); curCol = this.getRowIndex(rowStr); if (cellType != null && cellType.equals("s")) { nextIsString = true; } else { nextIsString = false; } } // 置空 lastContents = "";}public void endElement(String uri, String localName, String name) throws SAXException { // 依据SST的索引值的到单元格的真正要存储的字符串 // 这时characters()办法可能会被调用屡次 if (nextIsString) { try { int idx = Integer.parseInt(lastContents); lastContents = new XSSFRichTextString(sst.getEntryAt(idx)) .toString(); } catch (Exception e) { } } // v => 单元格的值，如果单元格是字符串则v标签的值为该字符串在SST中的索引 // 将单元格内容退出rowlist中，在这之前先去掉字符串前后的空白符 if (name.equals("v")) { String value = lastContents.trim(); value = value.equals("") ? " " : value; int cols = curCol - preCol; if (cols > 1) { for (int i = 0; i < cols - 1; i++) { rowlist.add(preCol, ""); } } preCol = curCol; rowlist.add(curCol - 1, value); } else { //如果标签名称为 row ，这阐明已到行尾，调用 optRows() 办法 if (name.equals("row")) { int tmpCols = rowlist.size(); if (curRow > this.titleRow && tmpCols < this.rowsize) { for (int i = 0; i < this.rowsize - tmpCols; i++) { rowlist.add(rowlist.size(), ""); } } try { optRows(sheetIndex, curRow, rowlist); } catch (SQLException e) { e.printStackTrace(); } if (curRow == this.titleRow) { this.rowsize = rowlist.size(); } rowlist.clear(); curRow++; curCol = 0; preCol = 0; } }}public void characters(char[] ch, int start, int length) throws SAXException {//失去单元格内容的值 lastContents += new String(ch, start, length);}//失去列索引，每一列c元素的r属性形成为字母加数字的模式，字母组合为列索引，数字组合为行索引，//如AB45,示意为第（A-A+1）*26+（B-A+1）*26列，45行public int getRowIndex(String rowStr) { rowStr = rowStr.replaceAll("[^A-Z]", ""); byte[] rowAbc = rowStr.getBytes(); int len = rowAbc.length; float num = 0; for (int i = 0; i < len; i++) { num += (rowAbc[i] - 'A' + 1) * Math.pow(26, len - i - 1); } return (int) num;}public int getTitleRow() { return titleRow;}public void setTitleRow(int titleRow) { this.titleRow = titleRow;}}
三、测试(这里实现的是提取列表中符合条件的手机号信息，可依据需要扭转)
public class POITest {
@SneakyThrowspublic static void main(String[] args) { long start = System.currentTimeMillis(); BigDataParseExcel xlx = new BigDataParseExcel(){ @Override public void optRows(int sheetIndex, int curRow, List<String> rowlist) throws SQLException { for (int i = 0; i < rowlist.size(); i++) { if(isMobile( rowlist.get(i))){ System.out.println(rowlist.get(i)); } } } }; xlx.process("C:/Users/Administrator/Desktop/data/test/DATA552410.xlsx"); long end = System.currentTimeMillis(); System.out.println((end-start)/1000);}public static boolean isMobile(final String str) { Pattern p = null; Matcher m = null; boolean b = false; p = Pattern.compile("^[1][3,4,5,7,8][0-9]{9}$"); // 验证手机号 m = p.matcher(str); b = m.matches(); return b;}}