@Override
public void processRecord(final Record record) {
switch (record.getSid()) {
case BOFRecord.sid:
// 开始解析到workboot sheet 等
BOFRecord bof = (BOFRecord) record;
if (bof.getType() == bof.TYPE_WORKBOOK) {
// workbook
} else if (bof.getType() == bof.TYPE_WORKSHEET) {
// sheet
}
break;
case BoundSheetRecord.sid:
// 开始解析BundleSheet
BoundSheetRecord bsr = (BoundSheetRecord) record;
// bsr.getSheetname() 得到sheet name
break;
case RowRecord.sid:
// 开始解析行
RowRecord rowrec = (RowRecord) record;
break;
case NumberRecord.sid:
// 解析一个Number类型的单元格值
NumberRecord numrec = (NumberRecord) record;
// numrec.getRow() numrec.getColumn() numrec.getValue()
// 非第一行 第一列
if (numrec.getRow() > 0 && numrec.getColumn() == 0) {
current = new ExcelData();
current.setId(Double.valueOf(numrec.getValue()).longValue());
}
break;
case SSTRecord.sid:
// SSTRecords存储了在Excel中使用的所有唯一String的数组
sstrec = (SSTRecord) record;
break;
case LabelSSTRecord.sid:
// 解析一个String类型的单元格值（存储在SSTRecord）
LabelSSTRecord lrec = (LabelSSTRecord) record;
if (lrec.getRow() > 0 && lrec.getColumn() == 1) {
current.setContent(sstrec.getString(lrec.getSSTIndex()).getString());
dataList.add(current);
totalSize++;
// 最后一个单元格时 判断是否该写了
if (totalSize % batchSize == 0) {
doBatchSave(dataList);
dataList.clear();
}
}
break;
}
}
/**
* This method listens for incoming records and handles them as required.
*
* @param record The record that was found while reading.
*/
public void processRecord(Record record) {
switch (record.getSid()) {
// the BOFRecord can represent either the beginning of a sheet or the
// workbook
case BOFRecord.sid:
BOFRecord bof = (BOFRecord) record;
if (bof.getType() == bof.TYPE_WORKBOOK) {
System.out.println("Encountered workbook");
// assigned to the class level member
} else if (bof.getType() == bof.TYPE_WORKSHEET) {
System.out.println("Encountered sheet reference");
}
break;
case BoundSheetRecord.sid:
BoundSheetRecord bsr = (BoundSheetRecord) record;
System.out.println("New sheet named: " + bsr.getSheetname());
break;
case RowRecord.sid:
RowRecord rowrec = (RowRecord) record;
// System.out.println("Row found, first column at " +
// rowrec.getFirstCol() + " last column at " + rowrec.getLastCol());
break;
case NumberRecord.sid:
NumberRecord numrec = (NumberRecord) record;
// System.out.println("Cell found with value " + numrec.getValue() +
// " at row " + numrec.getRow() + " and column "
// + numrec.getColumn());
break;
// SSTRecords store a array of unique strings used in Excel.
case SSTRecord.sid:
sstrec = (SSTRecord) record;
/*
* for (int k = 0; k < sstrec.getNumUniqueStrings(); k++) {
* System.out.println("String table value " + k + " = " +
* sstrec.getString(k)); }
*/
break;
case LabelSSTRecord.sid:
LabelSSTRecord lrec = (LabelSSTRecord) record;
col = lrec.getColumn();
service = sstrec.getString(lrec.getSSTIndex()).toString().trim().toUpperCase();
System.out.println(service);
// svckey += 1;
if (service != null && !service.equals("")) {
for (int i = 1; i <= 22; i++) {
sqlstr =
"INSERT INTO SVC (SVC_NAME, SITE_LOCN_KEY, STAT_IND, REC_EFF_DT, REC_END_DT)"
+ " VALUES (?, ?, ?, ?, ?)";
try {
stmt = con.prepareStatement(sqlstr);
// stmt.setInt(1, svckey);
stmt.setString(1, service.toString().trim());
stmt.setInt(2, i);
stmt.setString(3, "A");
stmt.setString(4, "2009-03-04");
stmt.setString(5, "2999-12-31");
rows = stmt.executeUpdate();
System.out.println(rows + " inserted");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
System.out.println();
// System.out.println("String cell found with value " +
// sstrec.getString(lrec.getSSTIndex()));
break;
}
}
@Override
public void processRecord(Record record) {
switch (record.getSid()) {
case BoundSheetRecord.sid:
BoundSheetRecord boundSheetRecord = (BoundSheetRecord) record;
parsedDataMap.put(boundSheetRecord.getSheetname(), new ArrayList<List<Object>>());
boundSheetRecords.add(boundSheetRecord);
missingRows.put(boundSheetRecord.getSheetname(), new ArrayList<Integer>());
missingCells.put(boundSheetRecord.getSheetname(), new ArrayList<int[]>());
break;
case BOFRecord.sid:
BOFRecord bofRecord = (BOFRecord) record;
bofRecordType = bofRecord.getType();
switch (bofRecordType) {
case BOFRecord.TYPE_WORKBOOK:
LOG.trace("loading excel data information.");
break;
case BOFRecord.TYPE_WORKSHEET:
sheetIndex += 1;
sheetName = boundSheetRecords.get(sheetIndex).getSheetname();
currentSheet = parsedDataMap.get(sheetName);
originalRowIndex = 0;
maxColumnlength = -1;
currentRow = new ArrayList<Object>();
break;
}
break;
case RowRecord.sid:
RowRecord rowRecord = (RowRecord) record;
int firstColumn = rowRecord.getFirstCol();
int lastColumn = rowRecord.getLastCol();
int length = lastColumn - firstColumn;
if (length > maxColumnlength) {
maxColumnlength = length;
}
break;
case BlankRecord.sid:
BlankRecord blankRecord = (BlankRecord) record;
missingCells.get(sheetName).add(new int[] {blankRecord.getColumn(), blankRecord.getRow()});
currentRow.add(null);
break;
case BoolErrRecord.sid:
BoolErrRecord boolErrRecord = (BoolErrRecord) record;
if (boolErrRecord.isBoolean()) {
currentRow.add(boolErrRecord.getErrorValue() == BOOLEAN_CELL_TRUE_FLAG);
} else if (boolErrRecord.isError()) {
LOG.warn(boolErrRecord);
}
break;
case FormulaRecord.sid:
FormulaRecord formulaRecord = (FormulaRecord) record;
LOG.trace(
"formulaRecord:[" + formulaRecord.getColumn() + "," + formulaRecord.getRow() + "]");
break;
case StringRecord.sid:
StringRecord stringRecord = (StringRecord) record;
currentRow.add(stringRecord.getString());
break;
case LabelRecord.sid:
LabelRecord labelRecord = (LabelRecord) record;
currentRow.add(labelRecord.getValue());
break;
case LabelSSTRecord.sid:
LabelSSTRecord labelSSTRecord = (LabelSSTRecord) record;
currentRow.add(
sheetRecordCollectingListener
.getSSTRecord()
.getString(labelSSTRecord.getSSTIndex())
.getString());
break;
case NoteRecord.sid:
NoteRecord notegRecord = (NoteRecord) record;
LOG.trace("formulaRecord:[" + notegRecord.getColumn() + "," + notegRecord.getRow() + "]");
break;
case NumberRecord.sid:
NumberRecord numberRecord = (NumberRecord) record;
double numberValue = numberRecord.getValue();
int formatIndex = formatTrackingHSSFListener.getFormatIndex(numberRecord);
if (HSSFDateUtil.isInternalDateFormat(formatIndex)) {
currentRow.add(HSSFDateUtil.getJavaDate(numberValue));
} else {
currentRow.add(numberValue);
}
break;
case RKRecord.sid:
RKRecord pkRecord = (RKRecord) record;
currentRow.add(pkRecord.getRKNumber());
break;
case EOFRecord.sid:
switch (bofRecordType) {
case BOFRecord.TYPE_WORKBOOK:
LOG.trace("loading excel data information complete.");
break;
case BOFRecord.TYPE_VB_MODULE:
break;
case BOFRecord.TYPE_WORKSHEET:
int size = currentSheet.size();
if (size > 0) {
if (currentSheet.get(size - 1).isEmpty()) {
currentSheet.remove(size - 1);
}
}
break;
case BOFRecord.TYPE_CHART:
break;
case BOFRecord.TYPE_EXCEL_4_MACRO:
break;
case BOFRecord.TYPE_WORKSPACE_FILE:
break;
}
break;
default:
if (record instanceof LastCellOfRowDummyRecord) {
// remove empty row.
if (!currentRow.isEmpty()) {
int nullSize = 0;
for (Object obj : currentRow) {
if (obj == null) {
nullSize += 1;
}
}
// remove the row of all member is null
if (nullSize == currentRow.size()) {
currentRow.clear();
missingRows.get(sheetName).add(originalRowIndex);
} else {
LastCellOfRowDummyRecord lastCellOfRowDummyRecord = (LastCellOfRowDummyRecord) record;
if (lastCellOfRowDummyRecord.getLastColumnNumber() > -1) {
for (int i = currentRow.size(); i < maxColumnlength; i += 1) {
missingCells.get(sheetName).add(new int[] {i, lastCellOfRowDummyRecord.getRow()});
currentRow.add(null);
}
}
currentSheet.add(currentRow);
currentRow = new ArrayList<Object>();
}
}
originalRowIndex += 1;
} else if (record instanceof MissingCellDummyRecord) {
MissingCellDummyRecord missingCellDummyRecord = (MissingCellDummyRecord) record;
currentRow.add(null);
missingCells
.get(sheetName)
.add(new int[] {missingCellDummyRecord.getColumn(), missingCellDummyRecord.getRow()});
} else if (record instanceof MissingRowDummyRecord) {
MissingRowDummyRecord missingRowDummyRecord = (MissingRowDummyRecord) record;
missingRows.get(sheetName).add(missingRowDummyRecord.getRowNumber());
}
break;
}
}
/*
* (non-Javadoc)
*
* @see
* org.apache.poi.hssf.eventusermodel.HSSFListener#processRecord(org
* .apache.poi.hssf.record.Record)
*/
public void processRecord(Record record) {
int row = -1;
int column = -1;
int fType = Types.VARCHAR;
Object cellValue = null;
boolean isNewValue = false;
TypedKeyValue<Integer, Number> typeAndValue;
if (BOFRecord.sid == record.getSid()) {
BOFRecord bof = (BOFRecord) record;
if (bof.getType() == BOFRecord.TYPE_WORKSHEET) {
_currentSheetIndex++;
}
}
boolean isFound =
_currentSheetIndex >= 0 && _currentSheetIndex == _sheetNames.indexOf(_sheetName);
_sheetFound = _sheetFound || isFound;
if (_currentSheetIndex >= 0 && !isFound) {
if (!_sheetFound) return;
throw new RuntimeException(ExcelConnectorParams.SHEET_ALREADY_EXTRACTED_EXCEPTION);
}
switch (record.getSid()) {
case BoundSheetRecord.sid:
BoundSheetRecord bsr = (BoundSheetRecord) record;
_sheetNames.add(bsr.getSheetname());
break;
case SSTRecord.sid:
_sstRecord = (SSTRecord) record;
break;
case BlankRecord.sid:
BlankRecord brec = (BlankRecord) record;
row = brec.getRow();
column = brec.getColumn();
cellValue = null;
fType = Types.VARCHAR;
isNewValue = true;
break;
case BoolErrRecord.sid:
BoolErrRecord berec = (BoolErrRecord) record;
row = berec.getRow();
column = berec.getColumn();
cellValue = berec.getBooleanValue();
isNewValue = true;
fType = Types.BOOLEAN;
break;
case FormulaRecord.sid:
FormulaRecord frec = (FormulaRecord) record;
row = frec.getRow();
column = frec.getColumn();
if (Double.isNaN(frec.getValue())) {
// Formula result is a string
// This is stored in the next record
_outputNextStringRecord = true;
_nextRow = frec.getRow();
_nextColumn = frec.getColumn();
} else {
cellValue = Utils.str2Number(_formatListener.formatNumberDateCell(frec), null);
fType = Types.NUMERIC;
isNewValue = true;
}
break;
case StringRecord.sid:
if (_outputNextStringRecord) {
// String for formula
StringRecord srec = (StringRecord) record;
cellValue = srec.getString();
row = _nextRow;
column = _nextColumn;
_outputNextStringRecord = false;
fType = Types.VARCHAR;
isNewValue = true;
}
break;
case LabelRecord.sid:
LabelRecord lrec = (LabelRecord) record;
row = lrec.getRow();
column = lrec.getColumn();
cellValue = lrec.getValue();
fType = Types.VARCHAR;
isNewValue = true;
break;
case LabelSSTRecord.sid:
LabelSSTRecord lsrec = (LabelSSTRecord) record;
if (_sstRecord == null) break;
row = lsrec.getRow();
column = lsrec.getColumn();
fType = Types.VARCHAR;
cellValue = _sstRecord.getString(lsrec.getSSTIndex()).toString();
typeAndValue = SqlUtils.getNumberTypeAndValue((String) cellValue);
if (typeAndValue != null) {
fType = typeAndValue.getKey();
cellValue = typeAndValue.getValue();
}
isNewValue = true;
break;
case NoteRecord.sid:
break;
case NumberRecord.sid:
NumberRecord numrec = (NumberRecord) record;
row = numrec.getRow();
column = numrec.getColumn();
int fIndex = numrec.getXFIndex();
String formatString = _formatListener.getFormatString(numrec);
if (_params.isDateTimeFormat(formatString)) {
cellValue =
Utils.str2Date(
Utils.date2Str(
DateUtil.getJavaDate(numrec.getValue()), _params.getDateTimeFormat()),
null,
_params.getDateTimeFormat());
fType = Types.TIMESTAMP;
} else if (_params.isDateFormat(formatString)) {
cellValue =
Utils.str2Date(
Utils.date2Str(
DateUtil.getJavaDate(numrec.getValue()), _params.getDateFormat()),
null,
_params.getDateFormat());
fType = Types.DATE;
} else if (_params.isTimeFormat(formatString)) {
cellValue =
Utils.str2Date(
Utils.date2Str(
DateUtil.getJavaDate(numrec.getValue()), _params.getTimeFormat()),
null,
_params.getTimeFormat());
fType = Types.TIME;
} else if (DateUtil.isADateFormat(fIndex, formatString)) {
cellValue = DateUtil.getJavaDate(numrec.getValue());
if (cellValue instanceof Date
&& (Utils.getDate((Date) cellValue, Calendar.YEAR, null) != 1900))
fType = Types.TIMESTAMP;
else {
typeAndValue =
SqlUtils.getNumberTypeAndValue(_formatListener.formatNumberDateCell(numrec));
if (typeAndValue == null) {
fType = Types.NUMERIC;
cellValue = null;
} else {
fType = typeAndValue.getKey();
cellValue = typeAndValue.getValue();
}
}
} else {
typeAndValue =
SqlUtils.getNumberTypeAndValue(_formatListener.formatNumberDateCell(numrec));
if (typeAndValue == null) {
fType = Types.NUMERIC;
cellValue = null;
} else {
fType = typeAndValue.getKey();
cellValue = typeAndValue.getValue();
}
}
isNewValue = true;
break;
case RKRecord.sid:
break;
default:
break;
}
// Handle new row
if (row > 0 && row != _lastRowNumber) {
try {
if (row == 1 && _params.getBeforeCallback() != null)
_params.getBeforeCallback().onBefore(_dataSet, _driver);
} catch (Exception ex) {
new RuntimeException(ex);
}
_dataSetRecord = new DataSetRecord();
if (!_params.isSilent() && _params.getLogStep() > 0 && (_index % _params.getLogStep()) == 0)
Logger.log(
Logger.INFO,
EtlLogger.class,
_dataSet.getName() + ": " + _index + EtlResource.READING_DATASET_MSG.getValue());
_index++;
}
// Handle missing column
if (record instanceof MissingCellDummyRecord) {
MissingCellDummyRecord mc = (MissingCellDummyRecord) record;
row = mc.getRow();
column = mc.getColumn();
cellValue = "";
fType = Types.VARCHAR;
isNewValue = true;
}
// If we got something to add, do so
if (isNewValue && row >= 0 && column >= 0) {
FieldDef fieldDef = null;
// fields defs
if (row == 0) {
fieldDef = new FieldDef();
fieldDef.setName(cellValue != null ? cellValue.toString() : "field" + column);
_dataSet.addField(fieldDef);
} else if (_dataSet.getFieldCount() > column) {
fieldDef = _dataSet.getFieldDef(column);
if (fieldDef != null) {
if (!Utils.isEmpty(cellValue)) {
int type = fieldDef.getSqlDataType();
fType = SqlUtils.getFieldType(fType, type, _types.containsKey(column));
fieldDef.setSqlDataType(fType);
fieldDef.setNativeDataType(
_driver.getType(new FieldDef(fType, "VARCHAR"), null, null));
_types.put(column, true);
} else cellValue = null;
if (_dataSetRecord != null) {
try {
if (_params.getAddFieldValueCallback() != null)
_params
.getAddFieldValueCallback()
.onAddFieldValue(_dataSet, _driver, _dataSetRecord, fieldDef);
} catch (Exception ex) {
new RuntimeException(ex);
}
addValue(cellValue, _dataSetRecord, _dataSet);
}
}
}
}
// Update column and row count
if (row > 0) _lastRowNumber = row;
// Handle end of row
if (record instanceof LastCellOfRowDummyRecord) {
// We're onto a new row
if (_dataSetRecord != null) {
if (_params.getMaxRows() >= 0 && _dataSet.getRecordCount() >= _params.getMaxRows()) {
throw new RuntimeException(DataSetConnectorParams.MAX_ROWS_EXCEEDED_EXCEPTION);
}
boolean added = _dataSet.addRecord(_dataSetRecord);
try {
if (added && _params.getAddRecordCallback() != null)
_params
.getAddRecordCallback()
.onAddRecord(_dataSet, _driver, _dataSetRecord, _index - 1);
} catch (Exception ex) {
new RuntimeException(ex);
}
}
}
}
@Override
public void processRecord(Record record) {
int curCol = -1;
double curNum = Double.NaN;
ValueString curStr = null;
switch (record.getSid()) {
case BoundSheetRecord.sid:
case BOFRecord.sid:
// we just run together multiple sheets
break;
case SSTRecord.sid:
_sstRecord = (SSTRecord) record;
break;
case BlankRecord.sid:
BlankRecord brec = (BlankRecord) record;
curCol = brec.getColumn();
curStr = _str.setTo("");
break;
case BoolErrRecord.sid:
BoolErrRecord berec = (BoolErrRecord) record;
curCol = berec.getColumn();
curStr = _str.setTo("");
break;
case FormulaRecord.sid:
FormulaRecord frec = (FormulaRecord) record;
curCol = frec.getColumn();
curNum = frec.getValue();
if (Double.isNaN(curNum)) {
// Formula result is a string
// This is stored in the next record
_outputNextStringRecord = true;
_nextCol = frec.getColumn();
}
break;
case StringRecord.sid:
if (_outputNextStringRecord) {
// String for formula
StringRecord srec = (StringRecord) record;
curStr = _str.setTo(srec.getString());
curCol = _nextCol;
_outputNextStringRecord = false;
}
break;
case LabelRecord.sid:
LabelRecord lrec = (LabelRecord) record;
curCol = lrec.getColumn();
curStr = _str.setTo(lrec.getValue());
break;
case LabelSSTRecord.sid:
LabelSSTRecord lsrec = (LabelSSTRecord) record;
if (_sstRecord == null) {
System.err.println("[ExcelParser] Missing SST record");
} else {
curCol = lsrec.getColumn();
curStr = _str.setTo(_sstRecord.getString(lsrec.getSSTIndex()).toString());
}
break;
case NoteRecord.sid:
System.err.println("[ExcelParser] Warning cell notes are unsupported");
break;
case NumberRecord.sid:
NumberRecord numrec = (NumberRecord) record;
curCol = numrec.getColumn();
curNum = numrec.getValue();
break;
case RKRecord.sid:
System.err.println("[ExcelParser] Warning RK records are unsupported");
break;
default:
break;
}
// Handle missing column
if (record instanceof MissingCellDummyRecord) {
MissingCellDummyRecord mc = (MissingCellDummyRecord) record;
curCol = mc.getColumn();
curNum = Double.NaN;
}
// Handle end of row
if (record instanceof LastCellOfRowDummyRecord) {
if (_firstRow) {
_firstRow = false;
String[] arr = new String[_columnNames.size()];
arr = _columnNames.toArray(arr);
_callback.setColumnNames(arr);
}
_callback.newLine();
}
if (curCol == -1) return;
if (_firstRow) {
_columnNames.add(curStr == null ? "" : curStr.toString());
} else {
if (curStr == null)
if (Double.isNaN(curNum)) _callback.addInvalidCol(curCol);
else _callback.addCol(curCol, curNum);
else _callback.addStrCol(curCol, curStr);
}
}
