POI实现excel导入

• 在很多地方，我们需要将数据多条数据添加到数据库，那么这个时候，如果实现了excel数据导入到数据库，我们的工作将会轻松很多，接下来我就给大家提供一个简单的POI导入excel数据到数据库，并且判断导入的数据是否存在于数据库，存在导入失败，否则，成功；
  
• 上传之前先对数据进行处理

$('#btnSub').click(function(){
    //会计科目这个是easyuitree设计，所以获得当前的id写法不同；
    var kjkm =$('#newkjkmTree').tree('getSelected');
    if(kjkm){
        kjkm = $('#newkjkmTree').tree('getSelected').id;
    }else{
        alert("请选择会计科目");
        return false;
    }
     var unit = $('#getUnit').val();
     if(unit ==""){
        alert("请选择计量单位");
        return false;  
    }
      var fileDir = $("#uploadExcel").val();  
      var suffix = fileDir.substr(fileDir.lastIndexOf("."));  
      if("" == fileDir){  
          alert("选择需要导入的Excel文件！");  
          return false;  
      }  if( ".xlsx" != suffix ){  
          alert("选择.xlsx格式的文件导入！");  
          return false;  
      }  
      $("#importForm").submit();
})

• 验证文件并上传后，这时候需要后台对接收到是excel数据进行处理：

public class ImportExcelUtil {
     private final static String excel2003L =".xls";    //2003- 版本的excel  
        private final static String excel2007U =".xlsx";   //2007+ 版本的excel  

        /** 
         * 描述：获取IO流中的数据，组装成List<List<Object>>对象 
         * @param in,fileName 
         * @return 
         * @throws IOException  
         */  
        public  List<List<Object>> getBankListByExcel(InputStream in,String fileName) throws Exception{  
            List<List<Object>> list = null;  

            //创建Excel工作薄  
            Workbook work = this.getWorkbook(in,fileName);  
            if(null == work){  
                throw new Exception("创建Excel工作薄为空！");  
            }  
            Sheet sheet = null;  
            Row row = null;  
            Cell cell = null;  

            list = new ArrayList<List<Object>>();  
            //遍历Excel中所有的sheet  
            for (int i = 0; i < work.getNumberOfSheets(); i++) {  
                sheet = work.getSheetAt(i);  
                if(sheet==null){continue;}  

                //遍历当前sheet中的所有行  
                for (int j = sheet.getFirstRowNum(); j < sheet.getLastRowNum(); j++) {  
                    row = sheet.getRow(j);  
                    if(row==null||row.getFirstCellNum()==j){continue;}  

                    //遍历所有的列  
                    List<Object> li = new ArrayList<Object>();  
                    for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {  
                        cell = row.getCell(y);  
                        li.add(this.getCellValue(cell));  
                    }  
                    list.add(li);  
                }  
            }  
            return list;  
        }  

        /** 
         * 描述：根据文件后缀，自适应上传文件的版本  
         * @param inStr,fileName 
         * @return 
         * @throws Exception 
         */  
        public  Workbook getWorkbook(InputStream inStr,String fileName) throws Exception{  
            Workbook wb = null;  
            String fileType = fileName.substring(fileName.lastIndexOf("."));  
            if(excel2003L.equals(fileType)){  
                wb = new HSSFWorkbook(inStr);  //2003-  
            }else if(excel2007U.equals(fileType)){  
                wb = new XSSFWorkbook(inStr);  //2007+  
            }else{  
                throw new Exception("解析的文件格式有误！");  
            }  
            return wb;  
        }  

        /** 
         * 描述：对表格中数值进行格式化 
         * @param cell 
         * @return 
         */  
        @SuppressWarnings("unused")
        public  Object getCellValue(Cell cell){  
            String strCell = "";  
             switch (cell.getCellType()) {  
                 case XSSFCell.CELL_TYPE_STRING:  
                     strCell = cell.getStringCellValue();  
                     break;  
                 case XSSFCell.CELL_TYPE_NUMERIC:  
                     if (XSSFDateUtil.isCellDateFormatted(cell)) {  
                         //  如果是date类型则 ，获取该cell的date值  
                         strCell = new SimpleDateFormat("yyyy-MM-dd").format(XSSFDateUtil.getJavaDate(cell.getNumericCellValue()));  
                     } else { // 纯数字  
                         strCell = String.valueOf(cell.getNumericCellValue());  
                         DecimalFormat df = new DecimalFormat("#.#########");
                         strCell=df.format(Double.valueOf(strCell));
                     }  
                         break;  
                 case XSSFCell.CELL_TYPE_BOOLEAN:  
                     strCell = String.valueOf(cell.getBooleanCellValue());  
                     break;  
                 case XSSFCell.CELL_TYPE_BLANK:  
                     strCell = "";  
                     break;  

                 case XSSFCell.CELL_TYPE_FORMULA:
                         strCell = String.valueOf(cell.getNumericCellValue());  
                         DecimalFormat df = new DecimalFormat("#.#########");
                         strCell=df.format(Double.valueOf(strCell));
                     break;  

                 default:  
                     strCell = "";  
                     break;  
             }  
             if (strCell.equals("") || strCell == null) {  
                 return "";  
             }  
             if (cell == null) {  
                 return "";  
             }  
             return strCell;  
        }  
        public static class XSSFDateUtil extends DateUtil {  
            protected static int absoluteDay(Calendar cal, boolean use1904windowing) {  
                return DateUtil.absoluteDay(cal, use1904windowing);  
            }  
        }
}

• 以上就是处理excel数据的工具类，接下来我们需要在控制层对数据进行处理：

@RequestMapping("importEcxel.do")
    public ModelAndView importExcel(HttpServletRequest request,HttpServletResponse response,HttpSession session,
            String kjkm,Model model,Integer unit) throws Exception{
        MultipartHttpServletRequest multRequest = (MultipartHttpServletRequest) request;    
         MultipartFile file = multRequest.getFile("uploadExcel");  //获得上传的excel文件；
        if(file.isEmpty()){  
            throw new Exception("文件不存在！");  
        }  
        InputStream in =null;  //创建输入流；
        List<List<Object>> listob = null;  
        in = file.getInputStream();  
        listob = new ImportExcelUtil().getBankListByExcel(in,file.getOriginalFilename());  //这个时候将获得的excel文件进行处理；
        in.close();  
        List<WyFeePlanPO> list = new ArrayList<WyFeePlanPO>();
        String kjkmName = planService.findWyFeePlanPOByKJKM(kjkm);//这里是将获得的中文描述转化为数据库中对应的字符串；
        int number = 0;
         //此处开始对数据进行遍历，并添加的list中；
        for (int i = 0; i < listob.size(); i++) {
                List<Object> lo = listob.get(i);  
                WyFeePlanPO planPo = new WyFeePlanPO();
                if (kjkmName!=null) {
                    if (lo.get(0).equals("")) {
                        break;//当获得的数据第一格为空的时候跳出当前循环，结束遍历；
                    } else {
                        planPo.setOwner_name(String.valueOf(lo.get(0)));
                    }
                    if (!lo.get(1).equals("")) {
                        if(StringUtil.isNotEmpty(String.valueOf(lo.get(1)))){
                            planPo.setResidence_code(RoomCodeDATAUtils.getInRoomNum(String.valueOf(lo.get(1)))); //此处对资源号进行处理，根据各自上传的excel字段需求来进行遍历；
                        }
                    }else {
                        planPo.setResidence_code("");
                    }
                    if (!lo.get(2).equals("")) {
                        String startUnit = String.valueOf(lo.get(2));
                        int start = Integer.parseInt(startUnit);
                        planPo.setStart_dosage(start);
                    } else {
                        planPo.setStart_dosage(0);
                    }
                    if (!lo.get(3).equals("")) {
                        String endUnit = String.valueOf(lo.get(3));
                        int end = Integer.parseInt(endUnit);
                        planPo.setEnd_dosage(end);
                    } else {
                        planPo.setEnd_dosage(0);
                    }
                    if (!lo.get(4).equals("")) {
                        String sjyl = String.valueOf(lo.get(4));
                        int SJ = Integer.parseInt(sjyl);
                        planPo.setActual_dosage(SJ);
                    } else {
                        planPo.setActual_dosage(0);
                    }
                    if (!lo.get(5).equals("")) {
                        planPo.setPay_company(String.valueOf(lo.get(5)));
                    }else {
                        planPo.setPay_company("");
                    }

                    Date recordDate = null;
                    if (!lo.get(6).equals("")) {
                        SimpleDateFormat sdf = new SimpleDateFormat(
                                "yyyy-MM-dd");
                        String recordTime = String.valueOf(lo.get(6));
                        recordDate = sdf.parse(recordTime);
                        planPo.setRecordTime(recordDate);
                    }else {
                        planPo.setRecordTime(new Date());
                    }
                    if (!lo.get(7).equals("")) {
                        String write_persion = String.valueOf(lo.get(7));
                        planPo.setWrite_persion(write_persion);
                    }else {
                        planPo.setWrite_persion("");
                    }
                    planPo.setKjkm(kjkm);
                    planPo.setUnit(unit);
                    String residence_code = String.valueOf(lo.get(1));
                    costDetailService.calculateWyFeePlanPO(planPo);//此处是调用方法计算费用；需求不同，则不需要；
                    if(StringUtil.isNotEmpty(residence_code)){
                        planPo.setResidence_code(RoomCodeDATAUtils.getInRoomNum(residence_code));
                    }
                    int residenceCodeCount = planService.findCodeCountByCodition(planPo.getResidence_code());//此处是查询业主列表中是否存在该房号；根据需求做处理；
                    if (residenceCodeCount>0) {
                        String owner_name = planPo.getOwner_name();
                        int sjyl = planPo.getActual_dosage();
                        unit = planPo.getUnit();
                        Double collection_fee = planPo.getAmount();
                        String pay_company = planPo.getPay_company();
                        int count = planService.findPlanCountByCodition(
                                owner_name, sjyl, collection_fee,
                                pay_company, residence_code, recordDate);
                        if (count > 0) {
                            planPo.setState(1);
                            planPo.setKjkm_name(kjkmName);
                            planPo.setUnit(unit);
                        } else {
                            planPo.setState(0);
                            planPo.setKjkm_name(kjkmName);
                            planPo.setUnit(unit);
                        }
                    }else {
                        planPo.setState(1);
                        planPo.setRemark("资源号不存在,请重新输入");
                    }
                    number +=1;
                }
                        list.add(planPo);
                }
        Map<String, Object> map = new HashMap<String, Object>();
        User user = currentUser(request);
        Integer role = user.getRoleId();
        WyFeePlanPO planPonum = new WyFeePlanPO();
        planPonum.setNumber(number);
        session.setAttribute("list", list);
        model.addAttribute("planPonum", planPonum);
        map.put("role",role);
        return new ModelAndView( "crm/wyfee/wyfeeplancheck",map);
    }//将数据处理好之后，将放到新的页面进行预览；

• 这时候我们将处理后的数据拿到显示到页面上；

@RequestMapping("importShow.do")
    public void importShow(HttpServletRequest request,HttpServletResponse response) throws Exception{
            @SuppressWarnings("unchecked")
            List<WyFeePlanPO> list = (List<WyFeePlanPO>) request.getSession().getAttribute("list");
            List<WyFeePlanVO> wyFeePlan = planService.showPlanPOsVo(list); 
            ResponseUtil.responseJson(response, wyFeePlan);
    }

• 图书已经看到了数据的处理，接下来就是需要将其导入到数据库了

@RequestMapping("importToServer.do")
    public void importToServer(HttpServletRequest request,HttpServletResponse response,Model model){
        @SuppressWarnings("unchecked")
        List<WyFeePlanPO> list = (List<WyFeePlanPO>) request.getSession().getAttribute("list");
        String userPin =  cookieUtil.getCookieValueByName(request, CookieConstants.SYS_USER_PIN);
        int amount = 0;
        for (WyFeePlanPO wyFeePlanPO : list) {
                if (wyFeePlanPO.getRemark()!=null) {
                    wyFeePlanPO.setState(1);
                    wyFeePlanPO.setRemark(wyFeePlanPO.getRemark());
                }else {
                    int count = planService.findPlanCountByCodition(wyFeePlanPO.getOwner_name(), wyFeePlanPO.getActual_dosage(),
                            wyFeePlanPO.getAmount(),wyFeePlanPO.getPay_company(),wyFeePlanPO.getResidence_code(),
                            wyFeePlanPO.getRecordTime());
//此处又是对数据进行查重处理；
                    if (count > 0) {
                        wyFeePlanPO.setState(1);
                        String remark = "导入失败，数据已存在";
                        wyFeePlanPO.setRemark(remark);
                    } else {
                        String remark = "导入成功";
                        wyFeePlanPO.setState(0);
                        wyFeePlanPO.setRemark(remark);
                        //获取订单编号
                        SnowflakeIdWorker snowfalake=SnowflakeIdWorker.getInstance();
                        long order_no=snowfalake.nextId();
                        wyFeePlanPO.setOrder_no(String.valueOf(order_no));
                        wyFeePlanPO.setCreateBy(userPin);
                        wyFeePlanPO.setModifyBy(userPin);
                        planService.handlewyfeePlanPo(wyFeePlanPO);
                        amount+=1;//这里是对导入成功的数据做一个记录数
                    }
                }
        }
        WyFeePlanPO wyFeePlanPO = new WyFeePlanPO();
        wyFeePlanPO.setNumber(amount);
        ResponseUtil.responseJson(response, wyFeePlanPO);
    }

---

• 那么到以上部分，POI对excel的导入基本上就完成了，此处又涉及到多数据判断的sql的书写格式也一并附上：

<select id="findPlanCountByCodition" parameterType="com.bckj.crm.wyfee.domain.po.WyFeePlanPO"
        resultType="java.lang.Integer">
        select count(*) from crm_wy_fee_plan where owner_name=#{0} and actual_dosage=#{1} and
        amount=#{2}
        and pay_company=#{3} and residence_code=#{4} and recordTime=#{5}

    </select>

• 以上就是对POI导入excel的全部描述，不管是导入还是导出，我都做了详细的代码演示，希望对大家有所帮助；当然药品那个POI做导入导出还是需要这些jar包：poi-3.9.jar ; poi-ooxml-3.9.jar ; poi-ooxml-schemas-3.9.jar ; xmlbeans-2.6.0jar ; dom4j-1.6.1jar ;具体哪些是导入需要的 ，哪些的导出需要的，记不清了，那就都一起把，反正都需要用。