一、导入PIO依赖
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>4.1.2</version>
</dependency>
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml-schemas</artifactId>
<version>4.1.2</version>
</dependency>
二、代码演示
1、常量类(定义一些约定值)
package com.abin.boot.sax;
public class ExcelConstant {
public static final String EXCEL07_EXTENSION = ".xlsx";
public static final Integer PER_SHEET_ROW_COUNT = 1000000;
public static final Integer PER_WRITE_ROW_COUNT = 200000;
public static final Integer PER_SHEET_WRITE_COUNT = PER_SHEET_ROW_COUNT / PER_WRITE_ROW_COUNT;
public static final Integer PER_READ_INSERT_BATCH_COUNT = 10000;
}
2、Excel读取类
package com.abin.boot.sax;
import com.fasterxml.jackson.databind.ser.std.StdKeySerializers;
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.util.ArrayList;
import java.util.List;
public class ExcelXlsxReaderWithDefaultHandler extends DefaultHandler {
private ExcelReadDataDelegated excelReadDataDelegated;
public ExcelReadDataDelegated getExcelReadDataDelegated() {
return excelReadDataDelegated;
}
public void setExcelReadDataDelegated(ExcelReadDataDelegated excelReadDataDelegated) {
this.excelReadDataDelegated = excelReadDataDelegated;
}
public ExcelXlsxReaderWithDefaultHandler(ExcelReadDataDelegated excelReadDataDelegated) {
this.excelReadDataDelegated = excelReadDataDelegated;
}
enum CellDataType {
BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
}
private SharedStringsTable sst;
private String lastIndex;
private String filePath = "";
private int sheetIndex = 0;
private String sheetName = "";
private int totalRows = 0;
private List<String> cellList = new ArrayList<String>();
private boolean flag = false;
private int curRow = 1;
private int curCol = 0;
private boolean isTElement;
private String exceptionMessage;
private CellDataType nextDataType = CellDataType.SSTINDEX;
private final DataFormatter formatter = new DataFormatter();
private short formatIndex;
private String formatString;
private String preRef = null, ref = null;
private String maxRef = null;
private StylesTable stylesTable;
private Integer totalRowCount;
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("com.sun.org.apache.xerces.internal.parsers.SAXParser");
this.sst = sst;
parser.setContentHandler(this);
XSSFReader.SheetIterator sheets = (XSSFReader.SheetIterator) xssfReader.getSheetsData();
while (sheets.hasNext()) {
curRow = 1;
sheetIndex++;
InputStream sheet = sheets.next();
sheetName = sheets.getSheetName();
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource);
sheet.close();
}
return totalRows;
}
@Override
public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
if("dimension".equals(name)) {
String dimensionStr = attributes.getValue("ref");
totalRowCount = Integer.parseInt(dimensionStr.substring(dimensionStr.indexOf(":") + 2)) - 1;
}
if ("c".equals(name)) {
if (preRef == null) {
preRef = attributes.getValue("r");
} else {
preRef = ref;
}
ref = attributes.getValue("r");
this.setNextDataType(attributes);
}
if ("t".equals(name)) {
isTElement = true;
} else {
isTElement = false;
}
lastIndex = "";
}
@Override
public void characters(char[] ch, int start, int length) throws SAXException {
lastIndex += new String(ch, start, length);
}
@Override
public void endElement(String uri, String localName, String name) throws SAXException {
if (isTElement) {
String value = lastIndex.trim();
cellList.add(curCol, value);
curCol++;
isTElement = false;
if (value != null && !"".equals(value)) {
flag = true;
}
} else if ("v".equals(name)) {
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++;
}
}
cellList.add(curCol, value);
curCol++;
if (value != null && !"".equals(value)) {
flag = true;
}
} else {
if ("row".equals(name)) {
if (curRow == 1) {
maxRef = ref;
}
if (maxRef != null) {
int len = countNullCell(maxRef, ref);
for (int i = 0; i <= len; i++) {
cellList.add(curCol, "");
curCol++;
}
}
if (flag && curRow != 1) {
excelReadDataDelegated.readExcelDate(sheetIndex, totalRowCount, curRow, cellList);
totalRows++;
}
cellList.clear();
curRow++;
curCol = 0;
preRef = null;
ref = null;
flag = false;
}
}
}
public void setNextDataType(Attributes attributes) {
nextDataType = CellDataType.NUMBER;
formatIndex = -1;
formatString = null;
String cellType = attributes.getValue("t");
String cellStyleStr = attributes.getValue("s");
String columnData = attributes.getValue("r");
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/yy") || 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);
}
}
}
@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));
thisStr = rtss.toString();
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);
thisStr = thisStr.replace("T", " ");
break;
default:
thisStr = " ";
break;
}
return thisStr;
}
public int countNullCell(String ref, String preRef) {
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) {
if (isPre) {
for (int i = 0; i < (len - len_1); i++) {
str = let + str;
}
} else {
for (int i = 0; i < (len - len_1); i++) {
str = str + let;
}
}
}
return str;
}
}
3、输出类(每读取一行,输出一次)
package com.abin.boot.sax;
import java.util.List;
public interface ExcelReadDataDelegated {
public abstract void readExcelDate(int sheetIndex, int totalRowCount, int curRow, List<String> cellList);
}
4、封装为工具类(主方法为测试)
package com.abin.boot.sax;
import java.util.List;
public class ExcelReaderUtil {
public static void readExcel(String filePath, ExcelReadDataDelegated excelReadDataDelegated) throws Exception {
int totalRows = 0;
if (filePath.endsWith(ExcelConstant.EXCEL07_EXTENSION)) {
ExcelXlsxReaderWithDefaultHandler excelXlsxReader = new ExcelXlsxReaderWithDefaultHandler(excelReadDataDelegated);
totalRows = excelXlsxReader.process(filePath);
} else {
throw new Exception("文件格式错误,fileName的扩展名只能是xlsx!");
}
System.out.println("读取的数据总行数:" + totalRows);
}
public static void main(String[] args) throws Exception {
String path = "F:\\test\\bigData07.xlsx";
ExcelReaderUtil.readExcel(path, new ExcelReadDataDelegated() {
@Override
public void readExcelDate(int sheetIndex, int totalRowCount, int curRow, List<String> cellList) {
System.out.println("总行数为:" + totalRowCount + " 行号为:" + curRow + " 数据:" + cellList);
}
});
}
}