共计 5348 个字符，预计需要花费 14 分钟才能阅读完成。
应用一般的 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; // 题目行，个别状况下为 0
private 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 {
@SneakyThrows
public 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;
}}
正文完
