private Object getCellValue(Cell cell, FormulaEvaluator evaluator)
throws ExcelManipulateException {
if (cell == null) return null;
// log.debug("Read Value for: " + ExcelUtil.getCellIndex(cell.getRowIndex(),
// cell.getColumnIndex()));
Object value = null;
CellValue cellValue = evaluator.evaluate(cell);
if (cellValue == null) {
logger.debug("{}: null", ExcelUtil.getCellIndex(cell.getRowIndex(), cell.getColumnIndex()));
return null;
}
switch (cellValue.getCellType()) {
case Cell.CELL_TYPE_BLANK:
case Cell.CELL_TYPE_ERROR:
break;
case Cell.CELL_TYPE_BOOLEAN:
value = cellValue.getBooleanValue();
break;
case Cell.CELL_TYPE_NUMERIC:
if (DateUtil.isCellDateFormatted(cell)) {
value = DateUtil.getJavaDate(cellValue.getNumberValue());
} else value = cellValue.getNumberValue();
break;
case Cell.CELL_TYPE_STRING:
value = cellValue.getStringValue();
}
logger.debug(
"{}: {}", ExcelUtil.getCellIndex(cell.getRowIndex(), cell.getColumnIndex()), value);
return value;
}
private void readSimpleBlock(
Workbook wb,
int sheetNo,
ExcelBlock blockDefinition,
OgnlStack stack,
ReadStatus readStatus) {
// Simple Block will only care about cells in these Block
Sheet sheet = wb.getSheetAt(sheetNo);
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
for (ExcelCell cellDefinition : blockDefinition.getCells()) {
Row row = sheet.getRow(cellDefinition.getRow());
Cell cell = row == null ? null : row.getCell(cellDefinition.getCol());
try {
Object value = getCellValue(cell, evaluator);
value =
checkValue(
sheetNo,
ExcelUtil.getCellIndex(cellDefinition.getRow(), cellDefinition.getCol()),
value,
cellDefinition,
getPropertyType(stack.peek(), cellDefinition));
logger.debug(
"{}[Checked]:{}",
ExcelUtil.getCellIndex(cellDefinition.getRow(), cellDefinition.getCol()),
value);
stack.setValue(cellDefinition.getDataName(), value);
} catch (ExcelManipulateException e) {
if (readStatus.getStatus() == ReadStatus.STATUS_SUCCESS)
readStatus.setStatus(ReadStatus.STATUS_DATA_COLLECTION_ERROR);
readStatus.addException(e);
} catch (Exception e) {
e.printStackTrace();
readStatus.setStatus(ReadStatus.STATUS_SYSTEM_ERROR);
readStatus.setMessage(e.getMessage());
}
}
}
/**
* Read Block in loop condition
*
* @param <T>
* @param wb
* @param sheetNo
* @param blockDefinition
* @param startRow
* @param needCreate
* @param readStatus
* @return
* @throws Exception
*/
private Object readBlock(
Workbook wb, int sheetNo, ExcelBlock blockDefinition, int startRow, ReadStatus readStatus)
throws Exception {
Sheet sheet = wb.getSheetAt(sheetNo);
FormulaEvaluator evaluator = wb.getCreationHelper().createFormulaEvaluator();
if (blockDefinition.getLoopClass() == null) {
Map<String, Object> result = new HashMap<String, Object>();
for (ExcelCell cellDefinition : blockDefinition.getCells()) {
int rowOffSet = cellDefinition.getRow() - blockDefinition.getStartRow();
Row row = sheet.getRow(startRow + rowOffSet);
Cell cell = row == null ? null : row.getCell(cellDefinition.getCol());
try {
Object value = getCellValue(cell, evaluator);
value =
checkValue(
sheetNo,
ExcelUtil.getCellIndex(startRow + rowOffSet, cellDefinition.getCol()),
value,
cellDefinition,
getPropertyType(result, cellDefinition));
logger.debug(
"{}[Checked]:{}",
ExcelUtil.getCellIndex(startRow + rowOffSet, cellDefinition.getCol()),
value);
result.put(cellDefinition.getDataName(), value);
} catch (ExcelManipulateException e) {
if (readStatus.getStatus() == ReadStatus.STATUS_SUCCESS)
readStatus.setStatus(ReadStatus.STATUS_DATA_COLLECTION_ERROR);
readStatus.addException(e);
}
}
return result;
} else {
Object result = blockDefinition.getLoopClass().newInstance();
OgnlStack ognlStack = new OgnlStack(result);
for (ExcelCell cellDefinition : blockDefinition.getCells()) {
int rowOffSet = cellDefinition.getRow() - blockDefinition.getStartRow();
Row row = sheet.getRow(startRow + rowOffSet);
Cell cell = row == null ? null : row.getCell(cellDefinition.getCol());
try {
Object value = getCellValue(cell, evaluator);
value =
checkValue(
sheetNo,
ExcelUtil.getCellIndex(startRow + rowOffSet, cellDefinition.getCol()),
value,
cellDefinition,
getPropertyType(result, cellDefinition));
logger.debug(
"{}[Checked]:{}",
ExcelUtil.getCellIndex(startRow + rowOffSet, cellDefinition.getCol()),
value);
ognlStack.setValue(cellDefinition.getDataName(), value);
} catch (ExcelManipulateException e) {
if (readStatus.getStatus() == ReadStatus.STATUS_SUCCESS)
readStatus.setStatus(ReadStatus.STATUS_DATA_COLLECTION_ERROR);
readStatus.addException(e);
}
}
return result;
}
}