由于通过new XSSFWorkbook 这种方式导入导致生产环境端口宕机、通过dump文件和javacore文件分析是导入功能导致的。
解决办法：自己通过网上写的工具类，不知道是否存在bug。
package com.yygx.impexptemplate.utils;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.openxml4j.exceptions.OpenXML4JException;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFReader.SheetIterator;
import org.apache.poi.xssf.model.SharedStringsTable;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.springframework.beans.factory.annotation.Autowired;
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 ExcelReaderParse extends DefaultHandler {
private List<String> rowData = new ArrayList<String>();
private List<String[]> sheetData = new ArrayList<String[]>();
private Map<Integer, Object> map = new HashMap<Integer, Object>();
private String lastContents;
private SharedStringsTable sst;
private boolean nextIsString;
private Integer limit = 0;
// 定义前一个元素和当前元素的位置，用来计算其中空的单元格数量，如A6和A8等
private String preRef = null, ref = null;
// 定义该文档一行最大的单元格数，用来补全一行最后可能缺失的单元格
private String maxRef = null;
private int curRow = 0;
private int maxlimit = 0;
private List<String[]> sheetNames ;
private int titleRow = 2;
public int getSheetRow() {
return titleRow;
}
public void setSheetRow(int sheetRow) {
this.titleRow = sheetRow;
}
public List<String[]> getSheetNames() {
return sheetNames;
}
public void setSheetNames(List<String[]> sheetNames) {
this.sheetNames = sheetNames;
}
public Map<Integer, Object> getMap() {
return map;
}
public void setMap(Map<Integer, Object> map) {
this.map = map;
}
/**
* 读取所有工作簿的入口方法
*
* @param path
* @throws Exception
*/
@Autowired
public void process(InputStream inputStream) {
OPCPackage pkg = null;
InputStream sheet = null;
try {
pkg = OPCPackage.open(inputStream);
XSSFReader r = new XSSFReader(pkg);
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
// Iterator<InputStream> sheets = r.getSheetsData();
SheetIterator sheets = (SheetIterator) r.getSheetsData();
String sheetName = null;
int sheetNum = 0;
while (sheets.hasNext()) {
sheet = sheets.next();
if(sheetNum == 0){
sheetName = sheets.getSheetName();
}
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource);
sheet.close();
map.put(sheetNum, this.sheetData);
this.sheetData = new ArrayList<String[]>();
sheetNum++;
curRow = 0;
}
List<String[]> list = new ArrayList<String[]>();
list.add(new String[]{sheetName});
this.setSheetNames(list);
} catch (InvalidFormatException e) {
throw new RuntimeException(e.getMessage());
} catch (IOException e) {
throw new RuntimeException(e.getMessage());
} catch (OpenXML4JException e) {
throw new RuntimeException(e.getMessage());
} catch (SAXException e) {
throw new RuntimeException(e.getMessage());
} finally {
try {
pkg.close();
sheet.close();
} catch (IOException e) {
// TODO Auto-generated catch block
throw new RuntimeException(e.getMessage());
}
}
}
/**
* 读取第一个工作簿的入口方法
*
* @param path
* @throws RuntimeException
*/
private void readOneSheet(String path) throws RuntimeException {
// TODO Auto-generated method stub
OPCPackage pkg = null;
InputStream sheet = null;
try {
pkg = OPCPackage.open(path);
XSSFReader r = new XSSFReader(pkg);
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
sheet = r.getSheet("rId1");
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource);
map.put(0, this.sheetData);
this.sheetData.clear();
} catch (InvalidFormatException e) {
throw new RuntimeException(e.getMessage());
} catch (IOException e) {
throw new RuntimeException(e.getMessage());
} catch (OpenXML4JException e) {
throw new RuntimeException(e.getMessage());
} catch (SAXException e) {
throw new RuntimeException(e.getMessage());
} finally {
try {
pkg.close();
sheet.close();
} catch (IOException e) {
// TODO Auto-generated catch block
throw new RuntimeException(e.getMessage());
}
}
}
private XMLReader fetchSheetParser(SharedStringsTable sst)
throws RuntimeException {
// TODO Auto-generated method stub
XMLReader parser;
try {
parser = XMLReaderFactory
.createXMLReader("org.apache.xerces.parsers.SAXParser");
this.sst = sst;
parser.setContentHandler(this);
return parser;
} catch (SAXException e) {
// TODO Auto-generated catch block
throw new RuntimeException(e.getMessage());
}
}
public void startElement(String uri, String localName, String name,
Attributes attributes) throws SAXException {
// c => 单元格
if (name.equals("c")) {
// 前一个单元格的位置
if (preRef == null) {
preRef = attributes.getValue("r");
} else {
preRef = ref;
}
// 当前单元格的位置
ref = attributes.getValue("r");
// 如果下一个元素是 SST 的索引，则将nextIsString标记为true
String cellType = attributes.getValue("t");
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();
nextIsString = false;
} catch (Exception e) {
e.printStackTrace();
}
}
// v => 单元格的值，如果单元格是字符串则v标签的值为该字符串在SST中的索引
// 将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
if (name.equals("v")) {
String value = lastContents.trim();
// if (value.equals("销售与客服支撑")) {
// System.out.println();
// }
// 补全单元格之间的空单元格
if (!ref.equals(preRef)) {
int len = countNullCell(ref, preRef);
for (int i = 0; i < len; i++) {
rowData.add(limit, "");
limit++;
}
} else if (ref.equals(preRef) && limit == 0 && !ref.contains("A")) {
int len = letterToNum(ref);
for(int i=0;i<len-1;i++){
rowData.add(limit,"");
limit++;
}
}
rowData.add(limit, value);
limit++;
} else if (name.equals("row")) {
// 如果标签名称为 row ，这说明已到行尾，调用 optRows() 方法
// System.out.println(rowData);
if(curRow == titleRow) {
maxlimit = limit;
}else if(curRow == 0) {
maxlimit = limit;
}
if (limit != maxlimit) {
int len = maxlimit - limit;
for (int i = 0; i < len; i++) {
rowData.add(limit, "");
limit++;
}
}
sheetData.add(rowData.toArray(new String[limit]));
rowData.clear();
limit = 0;
curRow++;
preRef = null;
ref = null;
}
}
/**
* 计算两个单元格之间的单元格数目(同一行)
*
* @param ref
* @param preRef
* @return
*/
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;
}
/**
* 字符串的填充
*
* @param str
* @param len
* @param let
* @param isPre
* @return
*/
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;
}
public void characters(char[] ch, int start, int length)
throws SAXException {
// 得到单元格内容的值
lastContents += new String(ch, start, length);
}
// 将字母转换成数字
public int letterToNum(String input) {
StringBuffer sb = new StringBuffer();
for (int i = 0; i < input.length(); i++) {
char c = input.charAt(i);
if ((c <= 'z' && c >= 'a') || (c <= 'Z' && c >= 'A')) {
sb.append(c);
}
}
StringBuilder builder = new StringBuilder();
for (byte b : sb.toString().toLowerCase().getBytes()) {
builder.append(b - 96);
}
return Integer.valueOf(builder.toString());
}
}