package luye.file.reader;
|
|
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 foundation.file.data.DataLine;
|
import foundation.file.data.SheetData;
|
import foundation.file.data.SheetSet;
|
import foundation.file.processor.FileIOItem;
|
|
import java.io.InputStream;
|
import java.util.ArrayList;
|
import java.util.HashMap;
|
import java.util.Iterator;
|
import java.util.List;
|
import java.util.Map;
|
|
/**
|
* 23 * @author y 24 * @create 2018-01-18 14:28 25 * @desc
|
* POI读取excel有两种模式，一种是用户模式，一种是事件驱动模式 26 * 采用SAX事件驱动模式解决XLSX文件，可以有效解决用户模式内存溢出的问题，
|
* 27 * 该模式是POI官方推荐的读取大数据的模式， 28 * 在用户模式下，数据量较大，Sheet较多，或者是有很多无用的空行的情况下，容易出现内存溢出
|
* 29 *
|
* <p>
|
* 30 * 用于解决.xlsx2007版本大数据量问题 31
|
**/
|
public class ExcelXlsxReader extends DefaultHandler {
|
|
/**
|
* 35 * 单元格中的数据可能的数据类型 36
|
*/
|
enum CellDataType {
|
BOOL, ERROR, FORMULA, INLINESTR, SSTINDEX, NUMBER, DATE, NULL
|
}
|
|
/**
|
* 42 * 共享字符串表 43
|
*/
|
private SharedStringsTable sst;
|
|
/**
|
* 47 * 上一次的索引值 48
|
*/
|
private String lastIndex;
|
|
/**
|
* 57 * 工作表索引 58
|
*/
|
private int sheetIndex = 0;
|
|
/**
|
* 62 * sheet名 63
|
*/
|
private String sheetName = "";
|
|
/**
|
* 67 * 总行数 68
|
*/
|
private int totalRows = 0;
|
|
/**
|
* 72 * 一行内cell集合 73
|
*/
|
private List<String> cellList = new ArrayList<String>();
|
|
/**
|
* 77 * 判断整行是否为空行的标记 78
|
*/
|
private boolean flag = false;
|
|
/**
|
* 82 * 当前行 83
|
*/
|
private int curRow = 1;
|
|
/**
|
* 87 * 当前列 88
|
*/
|
private int curCol = 0;
|
|
/**
|
* 92 * T元素标识 93
|
*/
|
private boolean isTElement;
|
|
/**
|
* 97 * 异常信息，如果为空则表示没有异常 98
|
*/
|
private String exceptionMessage;
|
|
/**
|
* 102 * 单元格数据类型，默认为字符串类型 103
|
*/
|
private CellDataType nextDataType = CellDataType.SSTINDEX;
|
|
private final DataFormatter formatter = new DataFormatter();
|
|
/**
|
* 109 * 单元格日期格式的索引 110
|
*/
|
private short formatIndex;
|
|
/**
|
* 114 * 日期格式字符串 115
|
*/
|
private String formatString;
|
|
// 定义前一个元素和当前元素的位置，用来计算其中空的单元格数量，如A6和A8等
|
private String preRef = null, ref = null;
|
|
// 定义该文档一行最大的单元格数，用来补全一行最后可能缺失的单元格
|
private String maxRef = null;
|
|
/**
|
* 125 * 单元格 126
|
*/
|
private StylesTable stylesTable;
|
|
/**
|
* 130 * 遍历工作簿中所有的电子表格 131 * 并缓存在mySheetList中 132 * 133 * @param filename
|
* 134 * @throws Exception 135
|
*/
|
private SheetSet sheetSet;
|
private SheetData sheetData;
|
private Map<String, FileIOItem> fileIODic;
|
|
public ExcelXlsxReader(Map<String, FileIOItem> fileDic) {
|
sheetSet = new SheetSet();
|
fileIODic = fileDic;
|
}
|
|
public SheetSet process(String filename) throws Exception {
|
OPCPackage pkg = OPCPackage.open(filename);
|
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();
|
while (sheets.hasNext()) { // 遍历sheet
|
|
InputStream sheet = sheets.next(); // sheets.next()和sheets.getSheetName()不能换位置，否则sheetName报错
|
sheetName = sheets.getSheetName();
|
if(!fileIODic.containsKey(sheetName)){
|
continue;
|
}
|
|
curRow = 1; // 标记初始行为第一行
|
sheetIndex++;
|
sheetData = new SheetData();
|
sheetData.setName(sheetName);
|
sheetSet.add(sheetData);
|
|
InputSource sheetSource = new InputSource(sheet);
|
parser.parse(sheetSource); // 解析excel的每条记录，在这个过程中startElement()、characters()、endElement()这三个函数会依次执行
|
sheet.close();
|
}
|
pkg.close();
|
return sheetSet; // 返回该excel文件的总行数
|
}
|
|
/**
|
* 159 * 第一个执行 160 * 161 * @param uri 162 * @param localName 163 * @param
|
* name 164 * @param attributes 165 * @throws SAXException 166
|
*/
|
@Override
|
public void startElement(String uri, String localName, String name, Attributes attributes) throws SAXException {
|
// c => 单元格
|
if ("c".equals(name)) {
|
// 前一个单元格的位置
|
if (preRef == null) {
|
preRef = attributes.getValue("r");
|
} else {
|
preRef = ref;
|
}
|
|
// 当前单元格的位置
|
ref = attributes.getValue("r");
|
// 设定单元格类型
|
this.setNextDataType(attributes);
|
}
|
|
// 当元素为t时
|
if ("t".equals(name)) {
|
isTElement = true;
|
} else {
|
isTElement = false;
|
}
|
|
// 置空
|
lastIndex = "";
|
}
|
|
/**
|
* 196 * 第二个执行 197 * 得到单元格对应的索引值或是内容值 198 *
|
* 如果单元格类型是字符串、INLINESTR、数字、日期，lastIndex则是索引值 199 *
|
* 如果单元格类型是布尔值、错误、公式，lastIndex则是内容值 200 * @param ch 201 * @param start 202 * @param
|
* length 203 * @throws SAXException 204
|
*/
|
@Override
|
public void characters(char[] ch, int start, int length) throws SAXException {
|
lastIndex += new String(ch, start, length);
|
}
|
|
/**
|
* 211 * 第三个执行 212 * 213 * @param uri 214 * @param localName 215 * @param
|
* name 216 * @throws SAXException 217
|
*/
|
@Override
|
public void endElement(String uri, String localName, String name) throws SAXException {
|
|
// t元素也包含字符串
|
if (isTElement) {// 这个程序没经过
|
// 将单元格内容加入rowlist中，在这之前先去掉字符串前后的空白符
|
String value = lastIndex.trim();
|
cellList.add(curCol, value);
|
curCol++;
|
isTElement = false;
|
// 如果里面某个单元格含有值，则标识该行不为空行
|
if (value != null && !"".equals(value)) {
|
flag = true;
|
}
|
} else if ("v".equals(name)) {
|
// v => 单元格的值，如果单元格是字符串，则v标签的值为该字符串在SST中的索引
|
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 {
|
// 如果标签名称为row，这说明已到行尾，调用optRows()方法
|
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) { // 该行不为空行且该行不是第一行，则发送（第一行为列名，不需要）
|
optRows(cellList);
|
totalRows++;
|
}
|
|
cellList.clear();
|
curRow++;
|
curCol = 0;
|
preRef = null;
|
ref = null;
|
flag = false;
|
}
|
}
|
}
|
|
/**
|
* 281 * 处理数据类型 282 * 283 * @param attributes 284
|
*/
|
public void setNextDataType(Attributes attributes) {
|
nextDataType = CellDataType.NUMBER; // cellType为空，则表示该单元格类型为数字
|
formatIndex = -1;
|
formatString = null;
|
String cellType = attributes.getValue("t"); // 单元格类型
|
String cellStyleStr = attributes.getValue("s"); //
|
String columnData = attributes.getValue("r"); // 获取单元格的位置，如A1,B1
|
|
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")) {
|
nextDataType = CellDataType.DATE;
|
formatString = "yyyy-MM-dd hh:mm:ss";
|
}
|
|
if (formatString == null) {
|
nextDataType = CellDataType.NULL;
|
formatString = BuiltinFormats.getBuiltinFormat(formatIndex);
|
}
|
}
|
}
|
|
/**
|
* 324 * 对解析出来的数据进行类型处理 325 * @param value 单元格的值， 326 * value代表解析：BOOL的为0或1，
|
* ERROR的为内容值，FORMULA的为内容值，INLINESTR的为索引值需转换为内容值， 327 *
|
* SSTINDEX的为索引值需转换为内容值， NUMBER为内容值，DATE为内容值 328 * @param thisStr 一个空字符串 329
|
* * @return 330
|
*/
|
@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));// 根据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);
|
// 对日期字符串作特殊处理，去掉T
|
thisStr = thisStr.replace("T", " ");
|
break;
|
default:
|
thisStr = " ";
|
break;
|
}
|
return thisStr;
|
}
|
|
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;
|
}
|
|
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;
|
}
|
|
/**
|
* 412 * @return the exceptionMessage 41
|
*/
|
public String getExceptionMessage() {
|
return exceptionMessage;
|
}
|
|
public void optRows(List<String> rowList){
|
|
if (curRow == 1) {
|
sheetData = sheetSet.get(sheetName);
|
}
|
|
DataLine dataLine = new DataLine();
|
for(String cell : rowList)
|
{
|
dataLine.add(cell);
|
}
|
sheetData.add(dataLine);
|
}
|
|
}
|
