public ReadStatus readSheet(InputStream is, int sheetNo, Map<String, Object> beans) {
ReadStatus readStatus = new DefaultReadStatus();
readStatus.setStatus(ReadStatus.STATUS_SUCCESS);
OgnlStack stack = new OgnlStack(beans);
try {
Workbook wb = WorkbookFactory.create(is);
readSheet(wb, sheetNo, definition.getExcelSheets().iterator().next(), stack, readStatus);
} catch (IOException e) {
readStatus.setStatus(ReadStatus.STATUS_READ_FILE_ERROR);
} catch (InvalidFormatException e) {
readStatus.setStatus(ReadStatus.STATUS_READ_FILE_ERROR);
}
return readStatus;
}
public ReadStatus readAllPerSheet(InputStream is, Map<String, Object> beans) {
ReadStatus readStatus = new DefaultReadStatus();
readStatus.setStatus(ReadStatus.STATUS_SUCCESS);
try {
Workbook wb = WorkbookFactory.create(is);
if (definition.getExcelSheets().size() == 0) {
readStatus.setStatus(ReadStatus.STATUS_SETTING_ERROR);
readStatus.setMessage("No sheet definition found");
} else {
// Only first ExcelSheet Definition will be used
ExcelSheet sheetDefinition = definition.getExcelSheets().iterator().next();
Map<String, List<Object>> cacheMap = new HashMap<String, List<Object>>();
for (String key : beans.keySet()) {
if (beans.get(key) != null) cacheMap.put(key, new ArrayList<Object>());
}
for (int i = 0; i < wb.getNumberOfSheets(); i++) {
Map<String, Object> clonedBeans = cloneMap(beans);
readSheet(wb, i, sheetDefinition, new OgnlStack(clonedBeans), readStatus);
for (String key : clonedBeans.keySet()) cacheMap.get(key).add(clonedBeans.get(key));
}
for (String key : beans.keySet()) {
if (cacheMap.containsKey(key)) {
beans.put(key, cacheMap.get(key));
} else {
beans.put(key, null);
}
}
}
} catch (IOException e) {
readStatus.setStatus(ReadStatus.STATUS_READ_FILE_ERROR);
} catch (InvalidFormatException e) {
readStatus.setStatus(ReadStatus.STATUS_READ_FILE_ERROR);
} catch (InstantiationException e) {
e.printStackTrace();
readStatus.setStatus(ReadStatus.STATUS_SYSTEM_ERROR);
readStatus.setMessage("New Instance Error");
} catch (IllegalAccessException e) {
e.printStackTrace();
readStatus.setStatus(ReadStatus.STATUS_SYSTEM_ERROR);
readStatus.setMessage("New Instance Error");
}
return readStatus;
}
private void readSheet(
Workbook wb,
int sheetNo,
ExcelSheet sheetDefinition,
OgnlStack stack,
ReadStatus readStatus) {
// In Read Operation only the first loopBlock will be read
int loopBlock = 0;
for (ExcelBlock blockDefinition : sheetDefinition.getExcelBlocks()) {
if (((skipErrors && readStatus.getStatus() == ReadStatus.STATUS_DATA_COLLECTION_ERROR)
|| readStatus.getStatus() == ReadStatus.STATUS_SUCCESS)
&& (loopBlock < 1 || !blockDefinition.isLoop())) {
if (blockDefinition.isLoop()) {
loopBlock++;
readLoopBlock(wb, sheetNo, blockDefinition, stack, readStatus);
} else readSimpleBlock(wb, sheetNo, blockDefinition, stack, readStatus);
}
}
}
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());
}
}
}
@SuppressWarnings({"unchecked", "rawtypes"})
private void readLoopBlock(
Workbook wb,
int sheetNo,
ExcelBlock blockDefinition,
OgnlStack stack,
ReadStatus readStatus) {
// Loop Block will only care about row loop
String dataName = blockDefinition.getDataName();
if (dataName == null || dataName.length() == 0) {
readStatus.setStatus(ReadStatus.STATUS_SETTING_ERROR);
readStatus.setMessage("dataName for block[" + blockDefinition.toString() + "] is not set");
return;
}
try {
Object obj = stack.getValue(dataName);
Collection dataList;
if (obj == null) {
dataList = new ArrayList();
stack.setValue(dataName, dataList);
} else if (!(obj instanceof Collection)) {
readStatus.setStatus(ReadStatus.STATUS_SETTING_ERROR);
readStatus.setMessage("Property " + dataName + " is not a Collection");
return;
} else {
dataList = (Collection) obj;
}
int startRow = blockDefinition.getStartRow();
int step = blockDefinition.getEndRow() - blockDefinition.getStartRow() + 1;
while (!checkBreak(
wb.getSheetAt(sheetNo),
startRow,
blockDefinition.getStartCol(),
blockDefinition.getBreakCondition())) {
Object value = readBlock(wb, sheetNo, blockDefinition, startRow, readStatus);
dataList.add(value);
startRow += step;
}
} catch (Exception e) {
e.printStackTrace();
readStatus.setStatus(ReadStatus.STATUS_SYSTEM_ERROR);
readStatus.setMessage(e.getMessage());
}
}
public ReadStatus readAll(InputStream is, Map<String, Object> beans) {
ReadStatus readStatus = new DefaultReadStatus();
readStatus.setStatus(ReadStatus.STATUS_SUCCESS);
try {
Workbook wb = WorkbookFactory.create(is);
if (definition.getExcelSheets().size() == 0
|| wb.getNumberOfSheets() < definition.getExcelSheets().size()) {
readStatus.setStatus(ReadStatus.STATUS_SETTING_ERROR);
readStatus.setMessage(
"No sheet definition found or Sheet Number in definition is more than number in file.");
} else {
OgnlStack stack = new OgnlStack(beans);
for (int i = 0; i < definition.getExcelSheets().size(); i++) {
readSheet(wb, i, definition.getExcelSheets().get(i), stack, readStatus);
}
}
} catch (IOException e) {
readStatus.setStatus(ReadStatus.STATUS_READ_FILE_ERROR);
} catch (InvalidFormatException e) {
readStatus.setStatus(ReadStatus.STATUS_READ_FILE_ERROR);
}
return readStatus;
}
/**
* 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;
}
}