JAVA开发_EXCEL文件解析

• EXCL文件解析

/**
 * EXCL文件解析
  */
 private Map parseExcelFile(String fileName, String tag,String oid_reguser) {
  log.info(tag + "进入xls文件解析方法");
  List<Blacklist> parseList = new ArrayList<Blacklist>();
  Map map = new HashMap();
  HSSFWorkbook book = null;
  try {
   book = new HSSFWorkbook(new FileInputStream(fileName));
  } catch (Exception e) {
   log.info("获取文件" + fileName + "异常", e);
   return null;
  }
  HSSFSheet sheet = book.getSheetAt(0);// 读取索引为0的工作表
  // 遍历除首行外的数据
  for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
   Blacklist blacklist = null;
   Blacklist tmplist = new Blacklist();
   HSSFRow rowInfo = sheet.getRow(rowNum);
   if (rowInfo == null) {
    break;
   }
   // 格式校验
   JSONObject resObj = checkLineData(rowInfo, rowNum, tag);
   if (null == resObj) {
    map.put("ret_code", "fail");
    map.put("ret_msg", "第[" + rowNum + "]笔信息解析异常");
    return map;
   }
   if ("9999".equals(resObj.get("ret_code"))) {
    log.info(tag + "第[" + rowNum + "]笔"
      + resObj.get("ret_msg"));
    map.put("ret_code", "fail");
    map.put("ret_msg", "第[" + rowNum + "]笔"
      + resObj.get("ret_msg"));
    return map;
   } else if ("1111".equals(resObj.get("ret_code"))) {
    // 特殊情况一行信息中前三组数据为空
    break;
   }
   //默认自动添加参数
   tmplist.setPartnerid(oid_reguser);
   tmplist.setCreatetime(DateUtil.getCurrentDate());
   tmplist.setState(NO_AUDIT);

   tmplist.setDimensionname((String) resObj.get("dimension"));
   tmplist.setRisktypename((String) resObj.get("risktype"));
   tmplist.setCasetimestr((String) resObj.get("casetime"));
   tmplist.setContent((String) resObj.get("content"));
   tmplist.setComment((String) resObj.get("comment"));

   parseList.add(tmplist);
  }
  map.put("parseList", parseList);
  if (((List) map.get("parseList")).size() >= Long.parseLong(SUPPORT_RISK_BLACKLIST_NO)) {
   map.put("ret_code", "fail");
   map.put("ret_msg", "批量文件总笔数超限！");
   return map;
  }
  map.put("ret_code", "succ");
  map.put("ret_msg", "解析成功");
  return map;
 }

• 解析行数据

/**
     * 解析行数据
     * @param rowInfo
     * @param rowNo
     * @param tag
     * @return
     */
    private JSONObject checkLineData(HSSFRow rowInfo, int rowNo, String tag) {
        JSONObject retObj=new JSONObject(); 
        log.info(tag + "第" + rowNo + "笔付款信息解析");
        String dimension = parseExcelCell(rowInfo.getCell(0), tag);
        String content = parseExcelCell(rowInfo.getCell(1), tag);
        String risktype = parseExcelCell(rowInfo.getCell(2), tag);
        String casetime = parseExcelCell(rowInfo.getCell(3), tag);
        String comment = parseExcelCell(rowInfo.getCell(4), tag);
        log.info(tag + "第" + rowNo + "笔付款信息，维度为[" + dimension + "]维度内容为["
                + content + "]风险类型为[" + risktype + "]案件发生时间为[" + casetime
                + "]案件原因为[" + comment + "]");
        if (FuncUtils.isNull(dimension.replace(" ", ""))
                && FuncUtils.isNull(content.replace(" ", ""))
                && FuncUtils.isNull(risktype.replace(" ", ""))) {// 特殊情况，读取文件越界时处理
            Blacklist blacklist2 = new Blacklist();
            JSONObject retObj2=new JSONObject();
            retObj2.put("ret_code", "1111");
            return retObj2;
        }
        // 非空校验
        if (FuncUtils.isNull(dimension)
                || "".equals(dimension.replace(" ", ""))) {// 维度非空校验
            log.info(tag + "第" + rowNo + "笔维度为空");
            retObj.put("ret_code", "9999");
            retObj.put("ret_msg", "维度有误，请核实后重新上传！");
            return retObj;
        }
        if (FuncUtils.isNull(content)
                ||"".equals(content.replace(" ", ""))) {// 维度内容非空校验
            log.info(tag + "第" + rowNo + "笔维度内容为空");
            retObj.put("ret_code", "9999");
            retObj.put("ret_msg", "维度内容有误，请核实后重新上传！");
            return retObj;
        }
        if (FuncUtils.isNull(risktype)
                ||"".equals(risktype.replace(" ", ""))) {
            log.info(tag + "第" + rowNo + "笔风险类型为空");
            retObj.put("ret_code", "9999");
            retObj.put("ret_msg", "风险类型有误，请核实后重新上传！");
            return retObj;
        }
        retObj.put("dimension", dimension);
        retObj.put("content", content);
        retObj.put("risktype", risktype);
        retObj.put("casetime", casetime);
        retObj.put("comment", comment);
        retObj.put("ret_code", "succ");
        retObj.put("ret_msg", "解析成功");
        return retObj;
    }

• 解析单元格数据

/**
     * 解析单元格数据
     * 
     * @param cell
     * @return
     */
    private String parseExcelCell(Cell cell, String tag) {
        log.info(tag + "进入Excel单元格解析方法");
        if (cell == null) {
            log.info(tag + "cell单元格为null,取默认''值");
            return "";
        }
        String result = new String();
        switch (cell.getCellType()) {
        case HSSFCell.CELL_TYPE_NUMERIC:// 数字类型
            log.info(tag + "cellType为数字类型");
            cell.setCellType(Cell.CELL_TYPE_STRING);
            result = cell.getStringCellValue();
            break;
        case HSSFCell.CELL_TYPE_STRING:// String类型
            log.info(tag + "cellType为String类型");
            result = cell.getRichStringCellValue().toString();
            break;
        case HSSFCell.CELL_TYPE_FORMULA:
            log.info(tag + "cellType为CELL_TYPE_FORMULA类型");
            cell.setCellType(Cell.CELL_TYPE_STRING);
            result = cell.getStringCellValue();
            break;
        default:
            log.info(tag + "cellType为非数字类型和String类型,取默认''值");
            result = "";
            break;
        }
        return result;
    }

<script type="text/javascript"> $(function () { $('pre.prettyprint code').each(function () { var lines = $(this).text().split('\n').length; var $numbering = $('<ul/>').addClass('pre-numbering').hide(); $(this).addClass('has-numbering').parent().append($numbering); for (i = 1; i <= lines; i++) { $numbering.append($('<li/>').text(i)); }; $numbering.fadeIn(1700); }); }); </script>