导入数据 org.apache.poi.hssf.usermodel.HSSFWorkbook

HSSFWorkbook book = null; try { book = new HSSFWorkbook(file.getInputStream()); } catch (IOException e) { log.error("导入的文件存在异常，无法转换为excel文件对象！",e); return 1; } HSSFSheet sheet = book.getSheetAt(0); int lastRowNum = sheet.getLastRowNum(); log.info("sheet name:"+ sheet.getSheetName()+", lastRowNum:"+lastRowNum); if(lastRowNum < 1){ return 2; } String username = UserSession.getUser().getUserName(); List<DisableCustomer> disableCustomerList = new LinkedList<DisableCustomer>(); for(int i=1;i<lastRowNum+1;i++){ HSSFRow row = sheet.getRow(i); String cardNo = Common.getCellValue(row.getCell(0)); if(StringUtils.isEmail(cardNo)){ log.info("第【"+i+"】行月结卡号为空,结束读取操作,开始插入数据库."); break; } if(!Common.isCardNo(cardNo)){ log.error("非法月结卡号:【" + cardNo+"】"); return 3; } String reason = Common.getCellValue(row.getCell(1)); if(StringUtils.isEmail(reason)){ log.error("导入永久取消权限中数据中存在空值！"); continue; } log.info("cardNo:"+cardNo+"reason:"+reason); disableCustomerList.add(new DisableCustomer(StringUtils.getUUID(32), cardNo, reason, username)); } cancelPermissionsMapper.saveBatchDisableCustomer(disableCustomerList); log.info("list size:" + disableCustomerList.size()); return 0;

 

/** * 获取表格的值（全部转换为String类型,长度超出100会截断） * @param cell * @return * @throws UnsupportedEncodingException */ public static String getCellValue(Cell cell) { String value; if(cell == null) return ""; switch (cell.getCellType()) { case Cell.CELL_TYPE_NUMERIC: //数字 if(HSSFDateUtil.isCellDateFormatted(cell)){//是否为时间类型 Date d = cell.getDateCellValue(); DateFormat formater = new SimpleDateFormat("yyyy-MM-dd"); value = formater.format(d); }else{ value = new DecimalFormat("#").format(cell.getNumericCellValue()); } break; case Cell.CELL_TYPE_STRING: //字符串 value = cell.getStringCellValue().trim(); break; case Cell.CELL_TYPE_BOOLEAN: //Boolean value = String.valueOf(cell.getBooleanCellValue()); break; case Cell.CELL_TYPE_FORMULA: //公式 value = cell.getCellFormula(); break; case Cell.CELL_TYPE_BLANK: //空值 value = ""; break; case Cell.CELL_TYPE_ERROR: //故障 value = ""; break; default: value = ""; break; } return value; }