LayUI导入Excel表（Apache POI）

LayUI导入Excel表（Apache POI）

前端框架LayUI ，将excel表导入到数据库，并展示在页面。
首先要明白，excel表整体构造。

一整个 excel表是一个工作簿(workbook)，而其中又有很多工作表( sheet)，然后一行一行(row)是一条完整数据，最后根据第几列(cell) 来确定其中一个数据。

1.导入依赖

<dependencies>
        <!--xls(03)-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.9</version>
        </dependency>

        <!--xlsx(07)-->
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.9</version>
        </dependency>
 </dependencies>

2. 前端 html

<button type="button" class="layui-btn" id="import" style="width: auto"><i class="layui-icon"></i>导入</button>

3.js代码

// 导入按钮
upload.render({
    elem: '#import',
    url: Hussar.ctxPath + "/blackList/uploadExcel", // 方法地址
    accept: 'file', //普通文件
    multiple : false,
    exts: 'xlsx|xls', // 限制文件类型为 xlsx或xls
    done: function(data){  // 成功的回调函数
        if (data.code === 200) {
            $(".layui-laypage-btn")[0].click();
            Hussar.success(data.message);
            table.reload('tableId', { // 刷新表格
                page: {
                    page: 1
                }
            })
        } else {
            Hussar.error(data.message);
        }
    }
    ,error: function(index, upload){  // 失败的回调函数
        Hussar.error('导入失败');
    }
});

4.Controller层代码

 //导入黑名单
 @RequestMapping("/uploadExcel")
 @ResponseBody
 public Tip uploadExcel(@RequestPart("file") MultipartFile file) {
     if (!file.isEmpty()) {
         String fileName = file.getOriginalFilename();
         //检查文件名是否包含xss脚本
         String xss = XssCheckerUtils.checkXss(fileName);
         if (!StringUtils.isEmpty(xss)) {
             throw new ApiException(HttpCode.BAD_REQUEST.value(), "invalid request , xss code:" + xss);
         }
         // 获取文件后缀
         String prefix = fileName.substring(fileName.lastIndexOf("."));
         //对导入的文件类型进行白名单后台验证 (其实在前js已经做了限制)
         if (!".xls".equalsIgnoreCase(prefix) && !".xlsx".equalsIgnoreCase(prefix)) {
             throw new HussarException(BizExceptionEnum.UPLOAD_ERROR);
         }
         // 调service层方法，把excel导入到数据库
         Tip tip = iBlackListService.importBlackList(fileName, file);
         return tip;
     } else {
     	 // 有一说一，这一步显得突兀又多余 hhhh
         return new ErrorTip(HttpCode.CONFLICT.value(), "文件为空!");
     }
 }

5.Service 层方法

  /**
  * 导入黑名单
  * @param filename   文件名(全名，例 blackList.xlsx)
  * @param file  前端传来的文件
  * @return
  */
  @Override
  public Tip importBlackList(String filename, MultipartFile file) {
      List<BlackListVO> list = new ArrayList<>();
      boolean isExcel2003 = true;
      String flag = "";
      if (filename.matches("^.+\\.(?i)(xlsx)$")) { // 检测文件名后缀是否是.xlsx
          isExcel2003 = false;
      }
      Workbook wb = null;
      InputStream is = null;
      Cell cell = null;
      try {
          is = file.getInputStream(); // 文件转换成输入流
          if (isExcel2003) {  // excel表 ，03版的用HSSF 07版的用XSSF
              wb = new HSSFWorkbook(is);  // 把流文件放入工作簿中
          } else {
              wb = new XSSFWorkbook(is);
          }
      } catch (IOException e) {
          e.printStackTrace();
      }
      Sheet sheet = wb.getSheetAt(0);  // 获取工作簿中第一个工作表(通过下标来获取，也可以通过名字)
      int rowCount = sheet.getPhysicalNumberOfRows();  // 获取 工作表中数据的高度，即行的数量
      boolean b2 = true;
      for (int rowNum = 1; rowNum < rowCount; rowNum++) { // 从第二行开始读取(业务需要)
          Row row = sheet.getRow(rowNum);  // 获取这一行的所有数据
          int cellCount = row.getPhysicalNumberOfCells();  // 获取这一行的长度，即列的数量
          String value = "";
          BlackListVO blackListVO = new BlackListVO();  // 业务需要，将得到的数据放到对象中
          for(int cellNum = 0; cellNum < cellCount; cellNum++){ // 从第一列开始读取
              cell = row.getCell(cellNum);  // 得到列(cell)
              value = getExcelValue(cell);  // 内部方法，对cell进行验证，得到String字符串
              String strDateFormat = "yyyy-MM-dd";
              SimpleDateFormat simpleDateFormat=new SimpleDateFormat(strDateFormat);  // 将String类型转化成 Date类型
              Date data = null;
              switch(cellNum){  // 第几个就把对应的数据放到对象中
                  case 0 :
                      blackListVO.setSupName(value);
                      break;
                  case 1 :
                      blackListVO.setUnitCode(value);
                      break;
                  case 2 :
                      if (isDate(value,strDateFormat)){  // isDate 内部方法，对比value是否是指定的格式，该处是"yyyy-MM-dd"
                          try {
                              data = simpleDateFormat.parse(value); // 是的话，就转成date数据
                          } catch (ParseException e) {
                              e.printStackTrace();
                          }
                          blackListVO.setLockTime(data);
                          break;
                      }else {
                          return new ErrorTip(HttpCode.CONFLICT.value(),"存在日期格式不符合规范，导入失败！");
                      }
                  case 3 :
                      if (isDate(value,strDateFormat)){
                          try {
                              data = simpleDateFormat.parse(value);
                          } catch (ParseException e) {
                              e.printStackTrace();
                          }
                          blackListVO.setExpiredTime(data);
                          break;
                      }else {
                          return new ErrorTip(HttpCode.CONFLICT.value(),"存在日期格式不符合规范，导入失败！");
                      }
                  case 4 :
                      blackListVO.setLockReson(value);
                      break;
                  case 5 :
                      if ("暂停合作".equals(value)){
                          blackListVO.setType("04");
                      }else {
                          blackListVO.setType("05");
                      }
                      break;
                  default:   // 1-5执行完，就弹出switch循环
                      break;
              }
          }  // 这一行数据就是 blackListVO 对象的一部分，也是要放到数据库中的，接下来就是完善对象，入库操作
          if (blackListVO.getType()==null){
              blackListVO.setType("05");
          }
          blackListVO.setAccountStatus("0");
          blackListVO.setInsUser(ShiroKit.getUser().getId());
          blackListVO.setInsTime(new Date(System.currentTimeMillis()));
          blackListVO.setTaskNameOther("未启动");
          String s = blackListMapper.querySysUsers(blackListVO.getSupName(), blackListVO.getUnitCode());
          String id = blackListMapper.queryBlackListByOne(blackListVO);
          boolean b = false;
          boolean b1 = false;
          if (s==null){
              if (id==null){
                  blackListVO.setBlackListInfoId(UUID.randomUUID().toString().replaceAll("-", ""));
                  b = blackListMapper.insertBlackListInfo(blackListVO);
              }else {
                  blackListVO.setBlackListInfoId(id);
                  b = blackListMapper.updateBlackListInfo(blackListVO);
              }
              b1 = true;
          }else {
              blackListVO.setSupId(s);
              if (id==null){
                  blackListVO.setBlackListInfoId(UUID.randomUUID().toString().replaceAll("-", ""));
                  b= blackListMapper.insertBlackListInfo(blackListVO);
              }else {
                  blackListVO.setBlackListInfoId(id);
                  b = blackListMapper.updateBlackListInfo(blackListVO);
              }
              b1 = blackListMapper.updateSysUsers(blackListVO);
          }
          if (!b&&b1){
              b2 =false;
          }
      }  // (rowCount-1)个对象入库执行完，返回给前端结果(Tip)
      if (b2){
          Tip tip = new SuccessTip();
          tip.setMessage("导入成功！");
          return tip;
      }else {
          return new ErrorTip(HttpCode.CONFLICT.value(),"导入失败！");
      }
  }




 /**
  * 导入时数据验证,将cell数转成String
  * @param cell  
  * @return
  */
 public static String getExcelValue(Cell cell){
     String value = "";
     if (cell != null) {
         switch (cell.getCellType()) {
             case Cell.CELL_TYPE_STRING:
                 value = cell.getStringCellValue();
                 break;
             case Cell.CELL_TYPE_NUMERIC:
                 value = new BigDecimal(cell.getNumericCellValue()).toString();
                 if (HSSFDateUtil.isCellDateFormatted(cell)) {//日期
                     Date date = cell.getDateCellValue();
                     value = new DateTime(date).toString("yyyy-MM-dd");
                 }else if (value.indexOf(".") != -1) {
                     DecimalFormat df = new DecimalFormat("0.000");
                     value = df.format(cell.getNumericCellValue());
                 } else {
                     DecimalFormat df = new DecimalFormat("0");
                     value = df.format(cell.getNumericCellValue());
                 }
                 break;
             case Cell.CELL_TYPE_BLANK:
                 value = "";
                 break;
             case Cell.CELL_TYPE_ERROR:
                 value = "";
                 break;
             case Cell.CELL_TYPE_BOOLEAN:
                 value = (cell.getBooleanCellValue() == true ? "Y" : "N");
                 break;
             default:
                 value = "";
         }
     }
     return value;
 }


 /**
  * 判断是否是指定日期格式
  * @param dat 需对比的Sting字符串,如 2000-11-30
  * @param fmt 指定的日期格式,如 "yyyy-MM-dd"
  * @return
  */
 private static boolean isDate(String dat, String fmt) {
      DateFormat formatter = new SimpleDateFormat(fmt);
      ParsePosition pos = new ParsePosition(0);
      formatter.setLenient(false);
      Date result = formatter.parse(dat, pos);
      return !(pos.getIndex() == 0) && dat.equals(formatter.format(result));
  }

有一说一，对于时间类型的来回处理，显得臃肿。但 getExcelValue 又是统一方法，不得已而为之。