(Spring Boot 2.1.2)这是一个好用的解析器,个人使用中只有一个问题解决不了,解析时间稍长但是完全可以忽略
pom
<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>xerces</groupId>
<artifactId>xercesImpl</artifactId>
<version>2.12.0</version>
</dependency>
util
import java.io.File;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import org.apache.commons.lang3.StringUtils;
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.ss.usermodel.RichTextString;
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;
public class ExcelXlsxReader extends DefaultHandler {
@SuppressWarnings("unchecked")
public static void main(String[] args) throws Exception {
String path="C:\\aaa.xlsx";
List<Map<String, Object>> list = ExcelXlsxReader.readXlsxExcel(path);
Integer num = 0;
Integer number = 0;
for (Map<String, Object> map : list) {
List<List<String>> stringList = (List<List<String>>) map.get("data");
String name = (String) map.get("name");
num += stringList.size();
for (List<String> list2 : stringList) {
number++;
System.out.println("name:" + name + " | number:" + number + ">" + list2.size() + ":" +list2);
}
}
System.out.println(num);
}
enum CellDataType {
BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
}
private SharedStringsTable sst;
private String lastIndex;
@SuppressWarnings("unused")
private int sheetIndex = 0;
private String sheetName = "";
@SuppressWarnings("unused")
private int totalRows=0;
private List<String> cellList = new ArrayList<String>();
private List<List<String>> sheetList = new ArrayList<>();
private boolean flag = false;
private int curRow = 1;
private int curCol = 0;
private boolean isT;
private boolean startElementFlag = true;
private boolean endElementFlag = false;
private boolean charactersFlag = false;
private CellDataType nextDataType = CellDataType.SSTINDEX;
private final DataFormatter formatter = new DataFormatter();
private short formatIndex;
private String formatString;
@SuppressWarnings("unused")
private String prePreRef = "A", preRef = null, ref = null;
private String maxRef = null;
private StylesTable stylesTable;
public static List<Map<String, Object>> readXlsxExcel(InputStream is) throws Exception {
return new ExcelXlsxReader().process(OPCPackage.open(is));
}
public static List<Map<String, Object>> readXlsxExcel(File file) throws Exception {
return new ExcelXlsxReader().process(OPCPackage.open(file));
}
public static List<Map<String, Object>> readXlsxExcel(String filePath) throws Exception {
return new ExcelXlsxReader().process(OPCPackage.open(filePath));
}
public List<Map<String, Object>> process(OPCPackage pkg) throws Exception {
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();
List<Map<String,Object>> list = new ArrayList<>();
while (sheets.hasNext()) {
Map<String, Object> map = new HashMap<>(0);
sheetList = new ArrayList<>();
curRow = 1;
sheetIndex++;
InputStream sheet = sheets.next();
sheetName = sheets.getSheetName();
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource);
sheet.close();
map.put("name", sheetName);
map.put("data", sheetList);
list.add(map);
}
return list;
}
@Override
public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
String c = "c";
if (c.equals(name)) {
if (preRef == null) {
preRef = attributes.getValue("r");
} else {
if (endElementFlag) {
preRef = ref;
}
}
ref = attributes.getValue("r");
if (!startElementFlag && !flag){
preRef = ref;
}
this.setNextDataType(attributes);
endElementFlag = false;
charactersFlag = false;
startElementFlag = false;
}
String t = "t";
if (t.equals(name)) {
isT = true;
} else {
isT = false;
}
lastIndex = "";
}
@Override
public void characters(char[] ch, int start, int length) throws SAXException {
startElementFlag = true;
charactersFlag = true;
lastIndex += new String(ch, start, length);
}
@Override
public void endElement(String uri, String localName, String name) throws SAXException {
String v = "v";
String a = "A";
String row = "row";
if (isT) {
String value = lastIndex.trim();
cellList.add(curCol, StringUtils.isNotBlank(value) ? value.replaceAll("[\b\r\n\t]*", "").trim() : null);
endElementFlag = true;
curCol++;
isT = false;
if (StringUtils.isNotBlank(value)) {
flag = true;
}
} else if (v.equals(name)) {
String value = this.getDataValue(lastIndex.trim(), null);
if (!ref.equals(preRef)) {
int len = countNullCell(ref, preRef);
for (int i = 0; i < len; i++) {
cellList.add(curCol, null);
curCol++;
}
} else if (ref.equals(preRef) && !ref.startsWith(a)){
int len = countNullCell(ref, "A");
for (int i = 0; i <= len; i++) {
cellList.add(curCol, null);
curCol++;
}
}
cellList.add(curCol, StringUtils.isNotBlank(value) ? value.replaceAll("[\b\r\n\t]*", "").trim() : null );
curCol++;
endElementFlag = true;
if (StringUtils.isNotBlank(value)) {
flag = true;
}
} else {
if (row.equals(name)) {
if (curRow == 1) {
maxRef = ref;
}
if (maxRef != null) {
int len = -1;
if (charactersFlag){
len = countNullCell(maxRef, ref);
}else {
len = countNullCell(maxRef, preRef);
}
for (int i = 0; i <= len; i++) {
cellList.add(curCol, null);
curCol++;
}
}
List<String> temp = new ArrayList<>(cellList);
temp.removeIf(Objects::isNull);
if(temp.size() > 0) {
sheetList.add(new ArrayList<>(cellList));
}
if (flag&&curRow!=1) {
totalRows++;
}
cellList.clear();
curRow++;
curCol = 0;
preRef = null;
prePreRef = null;
ref = null;
flag=false;
}
}
}
@SuppressWarnings("unused")
public void setNextDataType(Attributes attributes) {
String b = "b";
String e = "e";
String inlineStr = "inlineStr";
String s = "s";
String str = "str";
String mdyyyy = "m/d/yyyy";
String yyyymmdd = "yyyy/mm/dd";
String yyyymm = "yyyy/mm";
String yyyymd = "yyyy/m/d";
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;
}
if (e.equals(cellType)) {
nextDataType = CellDataType.ERROR;
}
if (inlineStr.equals(cellType)) {
nextDataType = CellDataType.INLINESTR;
}
if (s.equals(cellType)) {
nextDataType = CellDataType.SSTINDEX;
}
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 == null || formatString.contains(mdyyyy) || formatString.contains(yyyymmdd) || formatString.contains(yyyymm) || formatString.contains(yyyymd)) {
nextDataType = CellDataType.DATE;
formatString = "yyyy-MM-dd";
}
if (formatString == null) {
nextDataType = CellDataType.NULL;
formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
}
}
}
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);
RichTextString rtss = sst.getItemAt(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 xfd1 = preRef == null ? "" : preRef.replaceAll("\\d+", "");
xfd = fillChar(xfd, 3, '@', true);
xfd1 = fillChar(xfd1, 3, '@', true);
char[] letter = xfd.toCharArray();
char[] letter1 = xfd1.toCharArray();
int res = (letter[0] - letter1[0]) * 26 * 26 + (letter[1] - letter1[1]) * 26 + (letter[2] - letter1[2]);
return res - 1;
}
public String fillChar(String str, int len, char let, boolean isPre) {
int len1 = str.length();
if (len1 < len) {
if (isPre) {
for (int i = 0; i < (len - len1); i++) {
str = let + str;
}
} else {
for (int i = 0; i < (len - len1); i++) {
str = str + let;
}
}
}
return str;
}
}