业务背景:
通过导入excel的方式实现批量插入/更新数据。
loadExcelToList()是将excel中的数据读取到list中,import2Database()则将list中的数据逐条转换为我们保存到数据库的对象,save到数据库表后调用存储结构。
出现的问题:
Excel表里有5行,只有2行是有数据的,但是我在读Excel表的时候它连没有数据的行也读进来了。导致list中有5个对象(3个对象属性为null后面save就会报NullPointerException);
空的行也会放入list中继续向后面的行读数据,这也影响了程序的性能。
public void import2Database(String sessionId, String templateFilePath) throws Exception {
List items = loadExcelToList(templateFilePath);
String curUserLoginId = SecurityUtils.getCurrentUser();
User curUser = userService.getUserByLoginId(curUserLoginId);
if (items != null && items.size() > 0) {
// import data into db
SalesProfileUpload upload = null;
for (Iterator it = items.iterator(); it.hasNext();) {
SalesProfileTemplate item = (SalesProfileTemplate) it.next();
upload = transSalesProfileTemplate2SalesProfileUpload(item);
if (upload != null) {
upload.setCreator(curUser.getId());
salesProfileUploadDao.save(upload);
}
}
// call store procedure
salesProfileUploadDao.callSalesProfileProcedure(sessionId);
}
}
private List loadExcelToList(String templateFilePath) throws Exception {
HSSFWorkbook wb = null;
POIFSFileSystem fs = null;
try {
fs = new POIFSFileSystem(new FileInputStream(templateFilePath));
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
logger.error("failed to open excel file", e);
}
List list = new ArrayList();
HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(0);
for (Iterator it = sheet.iterator(); it.hasNext();) {
HSSFRow curRow = (HSSFRow) it.next();
if (curRow.getRowNum() > 0) {
SalesProfileTemplate item = new SalesProfileTemplate();
for (Iterator cellIt = curRow.cellIterator(); cellIt.hasNext();) {
HSSFCell curCell = (HSSFCell) cellIt.next();
if (curCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(curCell)) {
Date dateValue = curCell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
SalesProfileExcelUtil.setValueByCellIndex(item, curCell.getColumnIndex(),
sdf.format(dateValue));
} else {
Double value = new Double(curCell.getNumericCellValue());
SalesProfileExcelUtil.setValueByCellIndex(item, curCell.getColumnIndex(), value);
}
} else if (curCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
String cellValue = curCell.getRichStringCellValue().getString();
SalesProfileExcelUtil.setValueByCellIndex(item, curCell.getColumnIndex(), cellValue);
} else {
continue;
}
}
list.add(item);
}
}
return list;
}
解决方案:
优化loadExcelToList(),在读取单元格数据add到list之前对行数据row作非空判断,优化后的loadExcelToList():
private List loadExcelToList(String templateFilePath) throws Exception {
HSSFWorkbook wb = null;
POIFSFileSystem fs = null;
try {
fs = new POIFSFileSystem(new FileInputStream(templateFilePath));
wb = new HSSFWorkbook(fs);
} catch (IOException e) {
logger.error("failed to open excel file", e);
}
List list = new ArrayList();
HSSFSheet sheet = (HSSFSheet) wb.getSheetAt(0);
for (Iterator it = sheet.iterator(); it.hasNext();) {
HSSFRow curRow = (HSSFRow) it.next();
if (ExcelUtil.isRowEmpty(curRow)) {
continue;
}
if (curRow.getRowNum() > 0) {
SalesProfileTemplate item = new SalesProfileTemplate();
for (Iterator cellIt = curRow.cellIterator(); cellIt.hasNext();) {
HSSFCell curCell = (HSSFCell) cellIt.next();
if (curCell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
if (HSSFDateUtil.isCellDateFormatted(curCell)) {
Date dateValue = curCell.getDateCellValue();
SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");
SalesProfileExcelUtil.setValueByCellIndex(item, curCell.getColumnIndex(),
sdf.format(dateValue));
} else {
Double value = new Double(curCell.getNumericCellValue());
SalesProfileExcelUtil.setValueByCellIndex(item, curCell.getColumnIndex(), value);
}
} else if (curCell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
String cellValue = curCell.getRichStringCellValue().getString();
SalesProfileExcelUtil.setValueByCellIndex(item, curCell.getColumnIndex(), cellValue);
} else {
continue;
}
}
list.add(item);
}
}
return list;
}
其中非空判断方法isRowEmpty(Row row)具体代码为:
public static boolean isRowEmpty(Row row) {
for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
Cell cell = row.getCell(c);
if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
return false;
}
}
return true;
}