POI导入Excel并处理数据

过程：

在网页中导入一个Excel表格，批量处理这些数据，符合形式的存入数据库，不符合的放到session中，并记录不符合原因。

代码：

html中：

<input type="file" id="input_file" name="btn_file" accept="application/vnd.ms-excel" style="display:none" οnchange="file_change(this.value)">

js中：

<script>
function file_change(name){
                      $("#file_name").val(name);
                  };

                  $("#upload").click(function () {
                      //alert(1);
                    var formData = new FormData();
                    var name = $("#file_name").val;
                    var file = $("#input_file")[0].files[0];
                    formData.append("file", $("#input_file")[0].files[0]);
                    formData.append("name",name);
                    $.ajax({
                      url: '/merchant/input',
                      type: 'POST',
                      data: formData,
                      processData: false,
                      contentType: false,
                      success : function(result) {


                          if(result.falseCount == 0){
                            $("#tip").html("处理成功，匹配成功"+result.seccessCount+"条，失败"+result.falseCount+"

条。");
                          }else{
                            $("#tip").html("处理成功，匹配成功"+result.seccessCount+"条，失败"+result.falseCount+"

条。<a href='/merchant/loadFalse'>点击下载失败文件</a>");
                          }
                        falseList =JSON.stringify(result.falseList);

                      },
                      error : function(responseStr) {
                        console.log("error");
                      }
                    })
                  });
</script>

controller中：

@ResponseBody
    @RequestMapping(value="/input")
    public Map<String,Object> batchadd(HttpServletRequest request,HttpSession session){
        Integer siteId = Integer.parseInt(request.getSession().getAttribute("siteId").toString());
        MultipartHttpServletRequest multipartRequest  =  (MultipartHttpServletRequest) request;
        MultipartFile fileInput = multipartRequest.getFile("file");
        Map<String,Object> map = null;
        try {

            map = membersService.batchadd(fileInput,siteId);


        } catch (IOException e) {
            LOGGER.error("",e);
            e.printStackTrace();
        }
        session.setAttribute("falseList",map.get("falseList"));
        return map;
    }

service中：

public Map<String,Object> batchadd(MultipartFile fileInput, Integer siteId) throws IOException {
        InputStream inputStream = fileInput.getInputStream();
        HSSFWorkbook workbook = new HSSFWorkbook(inputStream); //创建excel
        HSSFSheet sheet = workbook.getSheetAt(0); //得到第一个sheet
        int row_num = sheet.getPhysicalNumberOfRows(); //sheet中实际物理行
        HSSFRow head = sheet.getRow(0);
        List falseList = new ArrayList<>();

        int seccessCount = 0; //成功条数
        int falseCount= 0; //失败条数
        //遍历每一行
        for (int i = 1; i < row_num; i++) {
            HSSFRow row = sheet.getRow(i);
            if(row.getCell(0)!=null && row.getCell(0).getStringCellValue()!="") { //填写了mobile

                BMember member = new BMember();
                BMemberInfo memberInfo = new BMemberInfo();
                Integer status;
                Integer seccess = 0;


                if (row.getCell(0) != null)
                    member.setMobile(row.getCell(0).getStringCellValue());
                if (row.getCell(1) != null)
                    member.setPasswd(row.getCell(1).getStringCellValue());
                if (row.getCell(2) != null && row.getCell(2).getStringCellValue()!="")
                    member.setRegister_stores(Integer.parseInt(row.getCell(2).getStringCellValue()));
                if (row.getCell(3) != null && row.getCell(3).getStringCellValue()!="")
                    member.setRegister_clerks(Long.parseLong(row.getCell(3).getStringCellValue()));
                if (row.getCell(4) != null && row.getCell(4).getStringCellValue()!="")
                    member.setIntegrate(Long.parseLong(row.getCell(4).getStringCellValue()));
                if (row.getCell(5) != null)
                    member.setName(row.getCell(5).getStringCellValue());
                if (row.getCell(6) != null) {
                    if (row.getCell(6).getStringCellValue().equals("女")) {
                        member.setSex(0);
                    } else if (row.getCell(6).getStringCellValue().equals("男")) {
                        member.setSex(1);
                    } else {
                        member.setSex(3);
                    }
                }
                if (row.getCell(7) != null)
                    member.setIdcard_number(row.getCell(7).getStringCellValue());
                if (row.getCell(8) != null)
                    member.setEmail(row.getCell(8).getStringCellValue());
                if (row.getCell(13) != null)
                    member.setMemo(row.getCell(13).getStringCellValue());

                if (row.getCell(9) != null)
                    memberInfo.setAddress(row.getCell(9).getStringCellValue());
                if (row.getCell(10) != null)
                    memberInfo.setMembership_number(row.getCell(10).getStringCellValue());
                if (row.getCell(11) != null)
                    memberInfo.setBarcode(row.getCell(11).getStringCellValue());
                if (row.getCell(12) != null)
                    memberInfo.setTag(row.getCell(12).getStringCellValue());


                if (member != null && member.getMobile() != null && !storeMemberService.checkMobile

(member.getMobile(), siteId, member.getRegister_stores())) {
                    member.setSite_id(siteId);
                    memberInfo.setSite_id(siteId);
                    status = addMemberIntegral(member, memberInfo);

                } else {
                    status = 1;
                    seccess = 1;
                }

                if (status == 1 && seccess == 0) {
                    seccessCount++;
                } else {
                    Map<Integer,Object> falseResult = new HashMap();
                    for (int k=0; k<14; k++){
                        if(row.getCell(k) != null && !row.getCell(k).getStringCellValue().equals("")){
                            if(k==2){
                                falseResult.put(k,Integer.parseInt(row.getCell(k).getStringCellValue()));
                                continue;
                            }
                            if(k==3 || k==4){
                                falseResult.put(k,Long.parseLong(row.getCell(k).getStringCellValue()));
                                continue;
                            }
                            falseResult.put(k,row.getCell(k).getStringCellValue());
                        }else{
                            falseResult.put(k,"");
                        }
                    }
                    falseResult.put(14,"该电话号码已注册");
                    falseList.add(falseResult);
                    falseCount++;
                }
            }else if ((row.getCell(1) != null && row.getCell(1).getStringCellValue()!="")||
                (row.getCell(2) != null && row.getCell(2).getStringCellValue()!="")||
                (row.getCell(3) != null && row.getCell(3).getStringCellValue()!="")||
                (row.getCell(4) != null && row.getCell(4).getStringCellValue()!="")||
                (row.getCell(5) != null && row.getCell(5).getStringCellValue()!="")||
                (row.getCell(6) != null && row.getCell(6).getStringCellValue()!="")||
                (row.getCell(7) != null && row.getCell(7).getStringCellValue()!="")||
                (row.getCell(8) != null && row.getCell(8).getStringCellValue()!="")||
                (row.getCell(9) != null && row.getCell(9).getStringCellValue()!="")||
                (row.getCell(10) != null && row.getCell(10).getStringCellValue()!="")||
                (row.getCell(11) != null && row.getCell(11).getStringCellValue()!="")||
                (row.getCell(12) != null && row.getCell(12).getStringCellValue()!="")||
                (row.getCell(13) != null && row.getCell(13).getStringCellValue()!="")
                ){//没有填写mobile并且该行其他单元格有值
                    Map<Integer,Object> falseResult = new HashMap();
                    for (int k=0; k<14; k++){
                        if(row.getCell(k) != null && !row.getCell(k).getStringCellValue().equals("")){
                            if(k==2){
                                falseResult.put(k,Integer.parseInt(row.getCell(k).getStringCellValue()));
                                continue;
                            }
                            if(k==3 || k==4){
                                falseResult.put(k,Long.parseLong(row.getCell(k).getStringCellValue()));
                                continue;
                            }
                            falseResult.put(k,row.getCell(k).getStringCellValue());
                        }else{
                            falseResult.put(k,"");
                        }
                    }
                    falseResult.put(14,"请填写电话号码");  // 用最后一个单元格保留错误信息
                    falseList.add(falseResult);
                    falseCount++;
                }
        }
        Map<String,Object> result = new HashMap<>();
        result.put("seccessCount",seccessCount);
        result.put("falseCount",falseCount);
        result.put("falseList",falseList);
        //System.out.println(falseList);
        return result;
    }