cellList = new ArrayList();
/**
* 判断整行是否为空行的标记
*/
private boolean flag = false;
//定义列名的行索引
private int headerIncex = 1;
//定义数据的行索引
private int dataIndex = 2;
private String sheetName;
/**
* 根据文件流解析
*
* @param inputStream
* @param headerIncex
* @param dataIndex
* @return
* @throws Exception
*/
public int process(InputStream inputStream, int headerIncex, int dataIndex)
throws Exception {
this.fs = new POIFSFileSystem(inputStream);
return init(headerIncex, dataIndex);
}
/**
* 根据文件路径解析
* @param path
* @param headerIncex
* @param dataIndex
* @return
* @throws Exception
*/
public int process(String path, int headerIncex, int dataIndex)
throws Exception {
this.fs = new POIFSFileSystem(new FileInputStream(path));
return init(headerIncex, dataIndex);
}
/**
* 遍历excel下所有的sheet
*
* @param headerIncex
* @param dataIndex
* @return
* @throws Exception
*/
private int init(int headerIncex, int dataIndex)
throws Exception {
MissingRecordAwareHSSFListener listener = new MissingRecordAwareHSSFListener(this);
formatListener = new FormatTrackingHSSFListener(listener);
HSSFEventFactory factory = new HSSFEventFactory();
HSSFRequest request = new HSSFRequest();
if (outputFormulaValues) {
request.addListenerForAllRecords(formatListener);
}
else {
workbookBuildingListener = new EventWorkbookBuilder.SheetRecordCollectingListener(formatListener);
request.addListenerForAllRecords(workbookBuildingListener);
}
if (headerIncex > 0)
//设置表头行
this.headerIncex = headerIncex;
if (dataIndex > 0)
//设置数据行
this.dataIndex = dataIndex;
factory.processWorkbookEvents(request, fs);
//返回该excel文件的总行数，不包括首列和空行
return totalRows;
}
/**
* HSSFListener 监听方法，处理Record
* 处理每个单元格
*/
@SuppressWarnings("unchecked")
public void processRecord(Record record) {
int thisRow = -1;
int thisColumn = -1;
String thisStr = null;
String value = null;
switch (record.getSid()) {
case BoundSheetRecord.sid:
boundSheetRecords.add(record);
break;
case BOFRecord.sid:
//开始处理每个sheet
BOFRecord br = (BOFRecord)record;
if (br.getType() == BOFRecord.TYPE_WORKSHEET) {
//如果有需要，则建立子工作簿
if (workbookBuildingListener != null && stubWorkbook == null) {
stubWorkbook = workbookBuildingListener.getStubHSSFWorkbook();
}
if (orderedBSRs == null) {
orderedBSRs = BoundSheetRecord.orderByBofPosition(boundSheetRecords);
}
sheetName = orderedBSRs[sheetIndex].getSheetname();
sheetIndex++;
}
break;
case SSTRecord.sid:
sstRecord = (SSTRecord)record;
break;
case BlankRecord.sid:
//单元格为空白
BlankRecord brec = (BlankRecord)record;
thisRow = brec.getRow();
thisColumn = brec.getColumn();
thisStr = "";
cellList.add(thisColumn, thisStr);
break;
case BoolErrRecord.sid:
//单元格为布尔类型
BoolErrRecord berec = (BoolErrRecord)record;
thisRow = berec.getRow();
thisColumn = berec.getColumn();
thisStr = berec.getBooleanValue() + "";
cellList.add(thisColumn, thisStr);
//如果里面某个单元格含有值，则标识该行不为空行
checkRowIsNull(thisStr);
break;
case FormulaRecord.sid:
//单元格为公式类型
FormulaRecord frec = (FormulaRecord)record;
thisRow = frec.getRow();
thisColumn = frec.getColumn();
if (outputFormulaValues) {
if (Double.isNaN(frec.getValue())) {
outputNextStringRecord = true;
nextRow = frec.getRow();
nextColumn = frec.getColumn();
}
else {
thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression()) + '"';
}
}
else {
thisStr = '"' + HSSFFormulaParser.toFormulaString(stubWorkbook, frec.getParsedExpression()) + '"';
}
cellList.add(thisColumn, thisStr);
//如果里面某个单元格含有值，则标识该行不为空行
checkRowIsNull(thisStr);
break;
case StringRecord.sid:
//单元格中公式的字符串
if (outputNextStringRecord) {
StringRecord srec = (StringRecord)record;
thisStr = srec.getString();
thisRow = nextRow;
thisColumn = nextColumn;
outputNextStringRecord = false;
}
break;
case LabelRecord.sid:
LabelRecord lrec = (LabelRecord)record;
curRow = thisRow = lrec.getRow();
thisColumn = lrec.getColumn();
value = lrec.getValue().trim();
value = value.equals("") ? "" : value;
cellList.add(thisColumn, value.replaceAll("
", ""));
//如果里面某个单元格含有值，则标识该行不为空行
checkRowIsNull(value);
break;
case LabelSSTRecord.sid:
//单元格为字符串类型
LabelSSTRecord lsrec = (LabelSSTRecord)record;
curRow = thisRow = lsrec.getRow();
thisColumn = lsrec.getColumn();
if (sstRecord == null) {
cellList.add(thisColumn, "");
}
else {
value = sstRecord.getString(lsrec.getSSTIndex()).toString().trim();
value = value.equals("") ? "" : value;
cellList.add(thisColumn, value.replaceAll("
", ""));
//如果里面某个单元格含有值，则标识该行不为空行
checkRowIsNull(value);
}
break;
case NumberRecord.sid:
//单元格为数字类型
NumberRecord numrec = (NumberRecord)record;
curRow = thisRow = numrec.getRow();
thisColumn = numrec.getColumn();
//第一种方式 这个被写死，采用的m/d/yy h:mm格式，不符合要求
//value = formatListener.formatNumberDateCell(numrec).trim();
//第二种方式，参照formatNumberDateCell里面的实现方法编写
Double valueDouble = ((NumberRecord)numrec).getValue();
String formatString = formatListener.getFormatString(numrec);
/*if (formatString.contains("m/d/yy")) {
formatString = "yyyy-MM-dd hh:mm:ss";
}*/
int formatIndex = formatListener.getFormatIndex(numrec);
value = formatter.formatRawCellContents(valueDouble, formatIndex, formatString).trim();
value = value.equals("") ? "" : value;
//向容器加入列值
cellList.add(thisColumn, value.replaceAll("
", ""));
//如果里面某个单元格含有值，则标识该行不为空行
checkRowIsNull(value);
break;
default:
break;
}
//遇到新行的操作
if (thisRow != -1 && thisRow != lastRowNumber) {
lastColumnNumber = -1;
}
//空值的操作
if (record instanceof MissingCellDummyRecord) {
MissingCellDummyRecord mc = (MissingCellDummyRecord)record;
curRow = thisRow = mc.getRow();
thisColumn = mc.getColumn();
cellList.add(thisColumn, "");
}
//更新行和列的值
if (thisRow > -1)
lastRowNumber = thisRow;
if (thisColumn > -1)
lastColumnNumber = thisColumn;
//行结束时的操作
if (record instanceof LastCellOfRowDummyRecord) {
if (minColums > 0) {
//列值重新置空
if (lastColumnNumber == -1) {
lastColumnNumber = 0;
}
}
lastColumnNumber = -1;
if (curRow == headerIncex - 1) {
//表头行结束时，调用sendHeaderRows()方法
sendHeaderRows(filePath, sheetName, sheetIndex, curRow + 1, cellList);
}
//该行不为空行且该行为数据行
if (flag && curRow >= dataIndex - 1) {
//每行数据结束时，调用sendDataRows()方法
sendDataRows(filePath, sheetName, sheetIndex, curRow + 1, cellList);
totalRows++;
}
//清空容器
cellList.clear();
flag = false;
}
}
/**
* 如果里面某个单元格含有值，则标识该行不为空行
* @param value
*/
public void checkRowIsNull(String value) {
if (value != null && !"".equals(value)) {
flag = true;
}
}
/**
* 表头数据
* @param filePath
* @param sheetName
* @param sheetIndex
* @param curRow
* @param cellList
*/
public void sendHeaderRows(String filePath, String sheetName, int sheetIndex, int curRow, List
-
网站地图 |
-
- 声明：登载此文出于传递更多信息之目的，并不意味着赞同其观点或证实其描述。文章内容仅供参考，不做权威认证，如若验证其真实性，请咨询相关权威专业人士。
