Excel -导入、导出功能

    MajorList = institutionBean.queryAllPerfessional(
                    institutionIdString, basicDataBase);// 该学院下的专业
            errorList = new ArrayList<>();
            String companyNumber = (String) request.getSession().getAttribute(
                    CloudContext.DatabaseName); // itoo
            // 查询该学院下的所有学生
            List<EnrollStudent> existEnrollList = new ArrayList<>();
            existEnrollList = enrollStudentBean.queryAllEnrollBycolleageName(
                    freshmenDataBase, institutionName);
            // 创建一个通用的多部分解析器
            CommonsMultipartResolver multipartResolver = new CommonsMultipartResolver(
                    request.getSession().getServletContext());
            InputStream inExcelFile = null;
            // 判断 request 是否有文件上传,即多部分请求
            if (multipartResolver.isMultipart(request)) {
                // 转换成多部分request
                MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;

                // 取得request中的所有文件名
                Iterator<String> iter = multiRequest.getFileNames();
                while (iter.hasNext()) {
                    // 记录上传过程起始时的时间，用来计算上传时间
                    // 取得上传文件
                    MultipartFile file = multiRequest.getFile(iter.next());
                    inExcelFile = file.getInputStream();
                }

            }

            // 创建一个list 用来存储读取的内容
            List list = new ArrayList();
            Cell cell;
            String result = ERROR;
            // 获取Excel文件对象
            Workbook rwb = Workbook.getWorkbook(inExcelFile);
            // 获取文件的指定工作表 默认的第一个
            Sheet sheet = rwb.getSheet(0);
            // 行数(表头的目录不需要，从1开始)
            for (int i = 0; i < sheet.getRows(); i++) {
                // 创建一个数组 用来存储每一列的值
                String[] str = new String[sheet.getColumns()];
                // 列数
                for (int j = 0; j < sheet.getColumns(); j++) {
                    // 获取第i行，第j列的值
                    cell = sheet.getCell(j, i);
                    str[j] = cell.getContents();
                }
                // 把刚获取的列存入list
                list.add(str);
            }
            // ==========保存错误信息的List
            // enrollStudent为要验证的List，error为保存错误信息的

            List<EnrollStudent> enrollStudentList = new ArrayList<>();
            List<FreshStudent> freshStudentList = new ArrayList<>();
            List<String> mapList = new ArrayList<>();

            if (!list.isEmpty()) {

                for (int i = 1; i < list.size(); i++) {
                    EnrollStudent importStudent = new EnrollStudent();

                    String[] str = (String[]) list.get(i);
                    if (list.get(i) == null || isAllEmpty(str)) {
                        continue;
                    }
                    // 向freshstudent表中添加数据
                    FreshStudent freshStudent = new FreshStudent();
                    String freshid = UuidUtils.base58Uuid().toString();
                    freshStudent.setId(freshid);
                    freshStudent.setIdentityCardID(str[1]);
                    freshStudent.setName(str[2]);
                    freshStudent.setSex(str[3]);
                    freshStudent.setNation(str[4]);
                    freshStudent.setIsHere("否");
                    freshStudent.setColleageId(institutionIdString);
                    freshStudent.setEntranceDate(entranceDate);
                    // 根据专业名称查询专业ID（str[9]为专业名称）
                    freshStudent.setMajorId("");
                    if (!"".equals(str[9]) && str[9] != null) {
                        for (Institution Major : MajorList) {
                            if (str[9].equals(Major.getInstitutionName())) {
                                freshStudent.setMajorId(Major.getId());
                                break;
                            }
                        }
                    }
                    freshStudent.setVersionStartTime(new Date());
                    freshStudent.setDataBaseName(freshmenDataBase);

                    mapList.add(str[0]);
                    // 调用新生接口，保存新生实体，并返回新生实体
                    // 学生信息
                    importStudent.setCEECode(str[0]); // 考号
                    importStudent.setFreshStudentId(freshid); // 新生id
                    // 此时不应该添加进去数据
                    importStudent.setIdentityCardID(str[1]); // 身份证
                    importStudent.setName(str[2]); // 姓名
                    importStudent.setSex(str[3]); // 性别
                    importStudent.setNation(str[4]); // 民族
                    importStudent.setCEEAspiration(str[5]); // 高考志愿
                    importStudent.setCEEScore(str[6]); // 高考分数
                    importStudent.setLevel(str[7]); // 层次
                    importStudent.setSecondaryCollege(str[8]); // 学院名称
                    importStudent.setMajor(str[9]); // 专业
                    importStudent.setComment(str[10]); // 备注
                    importStudent.setDataBaseName(freshmenDataBase);
                    // ===判断格式是否正确==================================

                    String errorInfo = Verification(importStudent,
                            freshStudent, enrollStudentList, existEnrollList);

                    if (!"".equals(errorInfo)) {
                        importStudent.setErrorInfo(errorInfo);
                        errorList.add(importStudent);
                    }
                    // 如果数据格式验证成功，则验证该数据的考生号或身份证号是否已经在要导入的list表中。 else {
                    else {// 如果有错的话，不加入到enrollStudentList和freshStudentList中去
                        enrollStudentList.add(importStudent);
                        freshStudentList.add(freshStudent);
                    }
                }
            }

            List<FreshStudent> freshList = new ArrayList<>();
            List<EnrollStudent> enrollList = new ArrayList<>();
            // FreshStudent去重
            for (FreshStudent freshStudent : freshStudentList) {
                if (!freshList.contains(freshStudent)) {
                    freshList.add(freshStudent);
                }
            }

            // enrollStudentList去重
            for (EnrollStudent enrollStudent : enrollStudentList) {
                if (!freshList.contains(enrollStudent)) {

                    enrollList.add(enrollStudent);
                }
            }

            // 批量添加考生
            Boolean it = enrollStudentBean.addEnrollListAndFreshList(
                    freshmenDataBase, authorityDataBase, companyNumber,
                    enrollList, freshList, mapList);
            StringBuilder sbResult = new StringBuilder();

            if (it) {// 导入成功
                sbResult.append("为您成功导入" + enrollList.size() + "条数据。");
                if (errorList.size() > 0)
                    sbResult.append("有" + errorList.size()
                            + "条数据未成功导入，您可以点击下载错误信息来获得错误原因。");
            } else {
                sbResult.append("未能为您导入任何信息,您可以下载错误信息来获得错误原因");
            }
            String strResult1 = sbResult.toString();
            // 将更新结果转成json输出
            String result1 = String.valueOf(strResult1);
            response.setContentType("text/html;charset=UTF-8");
            PrintWriter out = null;
            out = response.getWriter();
            out.write(result1);

        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        } finally {
            MajorList = Collections.emptyList();// 导入使用完该全局变量，将请内容清空。
        }
    }