ssm使用poi导入或者导出excel文件

pom文件中添加依赖

<dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi</artifactId>
      <version>3.9</version>
    </dependency>
    <dependency>
      <groupId>org.apache.poi</groupId>
      <artifactId>poi-ooxml</artifactId>
      <version>3.9</version>
</dependency>

 导出excel

@RequestMapping("/createExcel")
    public String createExcel(String application_id, String bussiness_code, String s_deptcode, HttpServletResponse response, HttpServletRequest request) throws IOException {
        String num = organizationInfoService.findOrgApplicationNumber(application_id, s_deptcode);
        int number = 0;
        ApplicationBussiness applicationBussiness = applicationService.findBussinessApplicationById(application_id);
        if (num != null) {
            number = Integer.parseInt(num);
            //查询该企业应用已经创建的人数
            int peoplenumber = userService.selectPeoplenumberById(s_deptcode.toString(), application_id.toString());
            number = number - peoplenumber;
        } else {
            request.setAttribute("msg", "请给该企业授权应用的人数");
            return "bill_error";
        }
        //创建HSSFWorkbook对象(excel的文档对象)
        HSSFWorkbook wb = new HSSFWorkbook();
        //建立新的sheet对象（excel的表单）
        HSSFSheet sheet = wb.createSheet("用户信息表");
        //在sheet里创建第一行，参数为行索引(excel的行)，可以是0～65535之间的任何一个
        HSSFRow row1 = sheet.createRow(0);
        //创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
        HSSFCell cell = row1.createCell(0);
        //创建一个样式
        HSSFCellStyle cellStyle = wb.createCellStyle();
        //创建一个DataFormat对象
        HSSFDataFormat format = wb.createDataFormat();
        //这样才能真正的控制单元格格式，@就是指文本型，具体格式的定义还是参考上面的原文吧
        cellStyle.setDataFormat(format.getFormat("@"));
        //具体如何创建cell就省略了，最后设置单元格的格式这样
         /*cell.setCellStyle(cellStyle);*/
        //设置单元格内容
        cell.setCellValue("用户信息填写表");
        //合并单元格CellRangeAddress构造参数依次表示起始行，截至行，起始列， 截至列
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 12));
        sheet.autoSizeColumn(1, true);
        //在sheet里创建第二行
        HSSFRow row2 = sheet.createRow(1);
        //创建单元格并设置单元格内容

        row2.createCell(0).setCellValue("添加的用户个数");
        row2.createCell(1).setCellValue("用户名（手机号）");
        row2.createCell(2).setCellValue("真实姓名");
        row2.createCell(3).setCellValue("电话");
        row2.createCell(4).setCellValue("邮箱");
        row2.createCell(5).setCellValue("部门编号");
        row2.createCell(6).setCellValue("业务编号");
        row2.createCell(7).setCellValue("所属业务");
        row2.createCell(8).setCellValue("应用编号");
        row2.createCell(9).setCellValue("所属应用");
        row2.createCell(10).setCellValue("应用角色编号");
        row2.createCell(11).setCellValue("应用角色名称");
        row2.createCell(12).setCellValue("用户唯一标识");


        for (int i = 1; i <= number; i++) {
            HSSFRow row3 = sheet.createRow(i + 1);
            row3.createCell(0).setCellValue(i);
            row3.createCell(1).setCellValue("");
            row3.createCell(1).setCellStyle(cellStyle);
            row3.createCell(2).setCellValue("");
            row3.createCell(3).setCellValue("");
            row3.createCell(3).setCellStyle(cellStyle);
            row3.createCell(4).setCellValue("");
            row3.createCell(5).setCellValue(s_deptcode);
            row3.createCell(6).setCellValue(applicationBussiness.getBussiness_code());
            row3.createCell(7).setCellValue(applicationBussiness.getBussiness_name());
            row3.createCell(8).setCellValue(applicationBussiness.getApplication_id());
            row3.createCell(9).setCellValue(applicationBussiness.getApplication_name());
            row3.createCell(10).setCellValue("");
            row3.createCell(10).setCellStyle(cellStyle);
            row3.createCell(11).setCellValue("");
            row3.createCell(12).setCellValue("");
        }

        //输出Excel文件
        OutputStream output = response.getOutputStream();
        response.reset();
        response.setHeader("Content-disposition", "attachment; filename=details.xls");
        response.setContentType("application/msexcel");
        wb.write(output);
        output.close();
        return null;
    }

 导入

@RequestMapping("/uploadExcel")
    @ResponseBody
    public String uploadExcel(@RequestParam MultipartFile file, HttpServletRequest request) throws IOException {
        int people;
        if (!file.isEmpty()) {
            String filePath = file.getOriginalFilename();
            //windows
            String savePath = request.getSession().getServletContext().getRealPath(filePath);

            File targetFile = new File(savePath);

            if (!targetFile.exists()) {
                targetFile.mkdirs();
            }
            file.transferTo(targetFile);


            InputStream is = new FileInputStream(new File(savePath));
            Workbook hssfWorkbook = null;
            if (savePath.endsWith("xlsx")) {
                hssfWorkbook = new XSSFWorkbook(is);//Excel 2007
            } else if (savePath.endsWith("xls")) {
                hssfWorkbook = new HSSFWorkbook(is);//Excel 2003
            } else {
                /*request.setAttribute("msg", "仅支持导入Excel文件，请按照导出模板进行填写");
                return "bill_error";*/
                return "仅支持导入Excel文件，请按照导出模板进行填写";
            }
            //获取组织id
            Sheet sheet = hssfWorkbook.getSheetAt(0);
            Row row = sheet.getRow(2);
            Cell s_deptcode = row.getCell(5);
            Cell application_id = row.getCell(8);
            if (s_deptcode != null && application_id != null) {
                //查询企业应用的限制人数
                String peopleNumberById = userService.findPeopleNumberById(s_deptcode.toString(), application_id.toString());

                if (peopleNumberById == null || "".equals(peopleNumberById)) {
                    return "该组织还未购买该应用";
                } else {
                    people = Integer.parseInt(peopleNumberById);
                }
                //查询该企业应用已经创建的人数
                int number = userService.selectPeoplenumberById(s_deptcode.toString(), application_id.toString());
                if (people <= number) {

                    return "已超过使用人数，无法继续添加";
                }

            } else {
                return "表数据不全，请填写完整";
            }

            // 循环工作表Sheet
            for (int numSheet = 0; numSheet < hssfWorkbook.getNumberOfSheets(); numSheet++) {
                //HSSFSheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
                Sheet hssfSheet = hssfWorkbook.getSheetAt(numSheet);
                if (hssfSheet == null) {
                    continue;
                }
                System.out.println(hssfSheet.getLastRowNum());
                // 循环行Row
                for (int rowNum = 2; rowNum <= hssfSheet.getLastRowNum(); rowNum++) {

                    //HSSFRow hssfRow = hssfSheet.getRow(rowNum);
                    Row hssfRow = hssfSheet.getRow(rowNum);
                    if (hssfRow != null) {
                        UserAddBean userAddBean = new UserAddBean();
                       /* HSSFCell name = hssfRow.getCell(0);
                        HSSFCell pwd = hssfRow.getCell(1);*/

                        Cell user_id = hssfRow.getCell(1);
                        Cell real_name = hssfRow.getCell(2);
                        Cell phone = hssfRow.getCell(3);
                        Cell email = hssfRow.getCell(4);
                        Cell s_deptcode1 = hssfRow.getCell(5);
                        Cell bussiness_id = hssfRow.getCell(6);
                        Cell application_id1 = hssfRow.getCell(8);

                        Cell role_code = hssfRow.getCell(10);
                        Cell role_name = hssfRow.getCell(11);
                        Cell use_tag = hssfRow.getCell(12);

                        //添加到用户表中
                        if (user_id.toString() != null && !"".equals(user_id.toString())) {
                            userAddBean.setUser_id(user_id.toString());
                        } else {
                            return "表数据不全，请填写完整";
                        }
                        if (real_name.toString() != null && !"".equals(real_name.toString())) {
                            userAddBean.setReal_name(real_name.toString());
                        } else {
                            return "表数据不全，请填写完整";
                        }
                        if (phone.toString() != null && !"".equals(phone.toString())) {
                            userAddBean.setPhone(phone.toString());
                        } else {
                            return "表数据不全，请填写完整";
                        }
                        if (email.toString() != null && !"".equals(email.toString())) {
                            userAddBean.setEmail(email.toString());
                        }

                        if (s_deptcode1.toString() != null && !"".equals(s_deptcode1.toString())) {
                            userAddBean.setS_deptcode(s_deptcode1.toString());
                        } else {
                            return "表数据不全，请填写完整";
                        }
                        if (bussiness_id.toString() != null && !"".equals(bussiness_id.toString())) {
                            userAddBean.setBussiness_id(bussiness_id.toString());
                        } else {
                            return "表数据不全，请填写完整";
                        }
                        if (application_id1.toString() != null && !"".equals(application_id1.toString())) {
                            userAddBean.setApplication_id(application_id1.toString());
                        } else {
                            return "表数据不全，请填写完整";
                        }
                        if (role_code.toString() != null && !"".equals(role_code.toString())) {
                            userAddBean.setRole_code(role_code.toString());
                        } else {
                            return "表数据不全，请填写完整";
                        }
                        if (role_name.toString() != null && !"".equals(role_name.toString())) {
                            userAddBean.setRole_name(role_name.toString());
                        } else {
                            return "表数据不全，请填写完整";
                        }
                        if (use_tag.toString() != null && !"".equals(use_tag.toString())) {
                            userAddBean.setUser_tag(use_tag.toString());
                        } else {
                            return "表数据不全，请填写完整";
                        }
                        //判断用户是否存在
                        User user = userService.findUser(userAddBean.getUser_id());
                        if (user != null) {
                            //判断用户是否已经存在该应用下
                            User_Application_Role user_application_role = userService.isExistApplication(userAddBean.getUser_id(), userAddBean.getApplication_id());
                            if (user_application_role != null) {
                                //用户已经存在该应用下

                            } else {//添加用户信息(用户存在但是不在该应用下)
                               /* userAddBean.setPassword("123456");//设置初始密码
                                //用户表
                                int num =userService.addUser(userAddBean);*/
                                Application_Role application_role = userService.findApplicationRoleIsExists(userAddBean);
                                if (application_role == null) {
                                    //用户角色表
                                    int num1 = userService.addApplicationRole(userAddBean);
                                }
                                //用户应用角色表
                                int num2 = userService.addUserApplicationRole(userAddBean);
                                //用户应用唯一标识表
                                int num3 = userService.addUserApplicationTag(userAddBean);
                            }
                        } else {
                            userAddBean.setPassword("123456");//设置初始密码
                            //用户表
                            int num = userService.addUser(userAddBean);
                            Application_Role application_role = userService.findApplicationRoleIsExists(userAddBean);
                            if (application_role == null) {
                                //用户角色表
                                int num1 = userService.addApplicationRole(userAddBean);
                            }
                            //用户应用角色表
                            int num2 = userService.addUserApplicationRole(userAddBean);
                            //用户应用唯一标识表
                            int num3 = userService.addUserApplicationTag(userAddBean);
                        }


                        //查询该企业应用已经创建的人数
                        int number = userService.selectPeoplenumberById(s_deptcode.toString(), application_id.toString());
                        if (people == number) {
                            return "导入成功";
                        }

                    }
                }
            }
            return "导入成功";
        }
        return "导入失败";
    }

 前端jsp

                <form id="uploadExcel" action="${ctx}/user/uploadExcel.action" enctype="multipart/form-data" method="post">
                    <input type="file" name="file"/>
                    <input type="submit" value="批量导入用户">
                </form>