import java.io.InputStream;
import java.math.BigDecimal;
import java.math.MathContext;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.log4j.Logger;
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.ContentHandler;
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;
/**
*
* @author linht
* @date 创建时间：2018年2月18日
*@version 1.0
*/
public class EventModel {
protected static final Logger log =Logger.getLogger(EventModel.class);
private StylesTable stylesTable;
private List<String[]> datas = new ArrayList<>();
/**
* 获取解析器
*
* @param sst
* @return
* @throws SAXException
*/
private XMLReader fetchSheetParser(SharedStringsTable sst) throws SAXException {
XMLReader parser = XMLReaderFactory.createXMLReader("org.apache.xerces.parsers.SAXParser");
ContentHandler handler = new SheetHandler(sst);
parser.setContentHandler(handler);
return parser;
}
/**
* 自定义解析处理器 See org.xml.sax.helpers.DefaultHandler javadocs
*/
private class SheetHandler extends DefaultHandler {
private SharedStringsTable sst;
private String lastContents;
private boolean nextIsString;
private List<String> rowlist = new ArrayList<String>();
private int curRow = 0;
private int curCol = 0;
// 定义前一个元素和当前元素的位置，用来计算其中空的单元格数量，如A6和A8等
private String preRef = null, ref = null;
// 定义该文档一行最大的单元格数，用来补全一行最后可能缺失的单元格
private String maxRef = null;
private CellDataType nextDataType = CellDataType.SSTINDEX;
private final DataFormatter formatter = new DataFormatter();
private short formatIndex;
private String formatString;
// 用一个enum表示单元格可能的数据类型
private SheetHandler(SharedStringsTable sst) {
this.sst = sst;
}
/**
* 解析一个element的开始时触发事件
*/
private boolean cc = false;
public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
String r = attributes.getValue("r");
if (cc && name.equals("c")) {
if (preRef == null) {
preRef = attributes.getValue("r");
} else {
if (!ref.equals(preRef)) {
int len = countNullCell(r, preRef);
for (int i = 0; i < len; i++) {
rowlist.add(curCol, "");
curCol++;
}
}
}
cc = false;
}
// c => cell
if (name.equals("c")) {
cc = true;
// 前一个单元格的位置
if (preRef == null) {
preRef = r;
} else {
preRef = ref;
}
// 当前单元格的位置
ref = attributes.getValue("r");
this.setNextDataType(attributes);
String cellType = attributes.getValue("t");
if (cellType != null && cellType.equals("s")) {
nextIsString = true;
} else {
nextIsString = false;
}
}
// Clear contents cache
lastContents = "";
}
/**
* 根据element属性设置数据类型
*
* @param attributes
*/
public void setNextDataType(Attributes attributes) {
nextDataType = CellDataType.NUMBER;
formatIndex = -1;
formatString = null;
String cellType = attributes.getValue("t");
String cellStyleStr = attributes.getValue("s");
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 ("m/d/yy" == formatString) {
nextDataType = CellDataType.DATE;
formatString = "yyyy-MM-dd";
}
if (formatString == null) {
nextDataType = CellDataType.NULL;
formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
}
}
}
/**
* 解析一个element元素结束时触发事件
*/
public void endElement(String uri, String localName, String name) throws SAXException {
if (nextIsString) {
int idx = Integer.parseInt(lastContents);
lastContents = new XSSFRichTextString(sst.getEntryAt(idx)).toString();
nextIsString = false;
}
if (name.equals("v")) {
cc = false;
String value = this.getDataValue(lastContents.trim(), "");
// 补全单元格之间的空单元格
if (!ref.equals(preRef)) {
int len = countNullCell(ref, preRef);
for (int i = 0; i < len; i++) {
rowlist.add(curCol, "");
curCol++;
}
}
rowlist.add(curCol, value);
curCol++;
} else {
// 如果标签名称为 row，这说明已到行尾，调用 optRows() 方法
if (name.equals("row")) {
// 默认第一行为表头，以该行单元格数目为最大数目
if (curRow == 0) {
maxRef = ref;
}
// 补全一行尾部可能缺失的单元格
if (maxRef != null) {
int len = countNullCell(maxRef, ref);
for (int i = 0; i <= len; i++) {
rowlist.add(curCol, "");
curCol++;
}
}
datas.add(rowlist.toArray(new String[0]));
curRow++;
// 一行的末尾重置一些数据
rowlist.clear();
curCol = 0;
preRef = null;
ref = null;
cc = false;
}
}
}
/**
* 根据数据类型获取数据
*
* @param value
* @param thisStr
* @return
*/
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:
thisStr = value.toString();
break;
case NUMBER:
if (formatString != null) {
java.text.DecimalFormat df = new java.text.DecimalFormat("#.00");
thisStr = formatter.formatRawCellContents(Double.parseDouble(df.format(Double.parseDouble(value))),
formatIndex, formatString).trim();
} else {
if (value.contains(".")&&isDouble(value)) {
try {
BigDecimal a=new BigDecimal(value, MathContext.DECIMAL64);
thisStr=""+Double.parseDouble(a.toString());
} catch (Exception e) {
thisStr=value;
}
}else
thisStr = value;
}
thisStr = thisStr.replace("_", "").trim();
if(thisStr.contains("E")) {
BigDecimal bd = new BigDecimal(thisStr);
thisStr=bd.toPlainString();
}
break;
case DATE:
try {
thisStr = formatter.formatRawCellContents(Double.parseDouble(value), formatIndex, formatString);
} catch (NumberFormatException ex) {
thisStr = value.toString();
}
thisStr = thisStr.replace(" ", "");
break;
default:
thisStr = "";
break;
}
return thisStr;
}
/**
* 获取element的文本数据
*/
public void characters(char[] ch, int start, int length) throws SAXException {
lastContents += new String(ch, start, length);
}
public boolean isDouble(String str) {
String DOUBLE_NEGATIVE ="^(-?\\d+)(\\.\\d+)?$";
if(str!=null&&str.matches(DOUBLE_NEGATIVE))
return true;
else
return false;
}
/**
* 计算两个单元格之间的单元格数目(同一行)
*
* @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 List<String[]> readFile(String fileName)throws Exception {
try {
processOneSheet(fileName,1);
} catch (Exception e) {
throw e;
}
return this.datas;
}
/**
* 处理一个sheet
*
* @param filename
* @throws Exception
*/
private void processOneSheet(String filename,int n) throws Exception {
OPCPackage pkg = OPCPackage.open(filename);
XSSFReader r = new XSSFReader(pkg);
stylesTable = r.getStylesTable();
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
InputStream sheet2 = r.getSheet("rId"+n);
try {
InputSource sheetSource = new InputSource(sheet2);
parser.parse(sheetSource);
} catch (Exception e) {
throw e;
}finally {
sheet2.close();
}
}
/**读取多文件
* @param filename
* @return
* @throws Exception
*/
public Map<String, List<String[]>> process(String filename) throws Exception {
Map<String, List<String[]>> map=new HashMap<String, List<String[]>>();
int i=-1;
OPCPackage pkg = OPCPackage.open(filename);
log.info("读取"+filename);
XSSFReader r = new XSSFReader(pkg);
stylesTable = r.getStylesTable();
SharedStringsTable sst = r.getSharedStringsTable();
XMLReader parser = fetchSheetParser(sst);
Iterator<InputStream> sheets = r.getSheetsData();
log.info("转换完成"+filename);
InputStream sheet=null;
while (sheets.hasNext()) {
try {
i++;
sheet = sheets.next();
InputSource sheetSource = new InputSource(sheet);
parser.parse(sheetSource);
} catch (Exception e) {
throw e;
}finally {
if(sheet!=null)
sheet.close();
List<String[]> list=new ArrayList<>();
log.info("读取完成sheet"+i+filename);
list.addAll(datas);
this.datas.clear();
map.put(ti+"", list);
System.gc();
}
}
return map;
}
/*public Map<String, List<String[]>> process(String filename) throws Exception {
Map<String, List<String[]>> map=new HashMap<String, List<String[]>>();
String[] tableName=new String[] {"MR.RSRP","MR.AOA","MR.Tadv","MR.TadvRsrp","MR.TadvAoa"};
int i=-1;
while (i<5) {
try {
i++;
processOneSheet(filename,i+1);
} catch (Exception e) {
e.printStackTrace();
}finally {
map.put(tableName[i], datas);
this.datas.clear();
}
}
return map;
} */
public static void main(String[] args) throws Exception {
EventModel example = new EventModel();
String fileName = "C:\\Users\\linhr\\Desktop\\XXX.xlsx";
System.out.println("-- 程序开始 --");
long time_1 = System.currentTimeMillis();
Map<String, List<String[]>> map = example.process(fileName);
long time_2 = System.currentTimeMillis();
System.out.println("-- 程序结束 --");
System.out.println("-- 耗时 --" + (time_2 - time_1) + "ms");
for (String value :map.keySet()) {
for (String[] s : map.get(value)) {
System.out.println(Arrays.asList(s));
}
}
}
}
enum CellDataType {
BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
}
