jxl 解析EXCL （项目笔记）

来源：互联网发布：淘宝星级怎么快速升级编辑：程序博客网时间：2024/05/29 18:46

public ActionForward bacthImportUserManager(ActionMapping mapping, ActionForm form,
            HttpServletRequest request, HttpServletResponse response) throws IOException, IOException {

        System.out.println(getClass().getName()+".bacthImportUserManager");
        // 从session对象中获取当前登录的用户
        HttpSession session = request.getSession();
        @SuppressWarnings("unused")
        ApdUser loginUser = (ApdUser) session.getAttribute("loginUser");
        UserMgrAction userMgrAction=new UserMgrAction();
        MD5 md5=new MD5();
        UserAddForm userfAddForm=(UserAddForm)form;
        FormFile file= userfAddForm.getExcelFile();
        InputStream is = file.getInputStream();
        InputStream is2 = file.getInputStream();
        String result=userMgrAction.ValdateExcle(is);//excl表头的验证
        List<ApdUser> userList=new ArrayList<ApdUser>();
        if("0".equals(result)){
            Workbook book = null;
            int rownum;
                    try {


                        book = Workbook.getWorkbook(is2);
                        Sheet sheet = book.getSheet(0);
                        // 行数
                        int rows = sheet.getRows();
                        if(rows>=2){// 表头位于第二行,有表头且有数据
                            for (rownum = 1; rownum < sheet.getRows(); rownum++) {
                                ApdUser userAdd = new ApdUser();
//                                Long userId = System.currentTimeMillis();
                                String u=loginUser.getUser_name();
//                                System.out.println(sheet.getCell(0, 1).getContents().trim());
                                userAdd.setUser_name(sheet.getCell(0, rownum).getContents().trim());
                                userAdd.setUser_pwd(md5.getMD5ofStr(sheet.getCell(1, rownum).getContents().trim()));
                                userAdd.setUser_cname(sheet.getCell(2, rownum).getContents().trim());
                                userAdd.setUser_email(sheet.getCell(3, rownum).getContents().trim());
                                userAdd.setTelephone(sheet.getCell(4, rownum).getContents().trim());
                                userAdd.setRemark(sheet.getCell(5, rownum).getContents().trim());
                                userAdd.setCreator(u);
                                userAdd.setLast_modifier(u);
                                userList.add(userAdd);
                            }
                        }
                    } catch (BiffException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    } catch (IOException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }finally{
                        book.close();
                    }


                for (ApdUser apdUser : userList) {


                    //首先判断三个必填参数
                    if(apdUser.getUser_pwd()==null || apdUser.getUser_name()==null || apdUser.getUser_cname()==null){
                        request.setAttribute("message", "用户名、用户密码、用户中文名必须提供，请检查！");
                        return mapping.findForward("batchImportMessage");
                    }
                    //验证该用户名是否存在
                    boolean userNameTest = userService.validateUserName(apdUser.getUser_name());
                    if (userNameTest) {
                        request.setAttribute("message", "用户名已存在");
                        return mapping.findForward("batchImportMessage");
                    }
                    //验证该用户中文名是否存在
                    boolean userCnameTest = userService.validateUserCname(apdUser.getUser_cname());
                    if (userCnameTest) {
                        request.setAttribute("message", "用户中文名已存在");
                        return mapping.findForward("batchImportMessage");
                    }
                    //保存用户到数据库
                    userService.saveUser(apdUser);

                    //保存用户的角色到数据库
                    Long userId=userService.getUserIdByName(apdUser.getUser_name());
                    //这里是标志默认的角色为系统补录员
                    userMenuRoleService.addUserRole(userId, Long.parseLong("21"));
                }

        }else{
//            request.setAttribute("message", "用户添加失败"+e.getMessage());
            request.setAttribute("message", "excel文件中所有的sheet文件表头不符合要求，不能对数据导入！");
            return mapping.findForward("batchImportMessage");
        }


        request.setAttribute("message", "导入成功！");
        return mapping.findForward("batchImportMessage");
    }