解决：从excel读取数据保存到数据库时NullPointerException

业务背景：

通过导入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;
	}