POI导入Excel文档试题案例

一、 数据库

数据库中分为两个表格
考试题目表

考试题目答案

分析：由表可知是一对多的关系，一道题目有多个选择。这个试题导入也就是做一个批量增加的操作，在考试题目表中做一次真假然后，同时在考试题目答案做多次增加，题目和选择在同一行，先获取一行数据，再将选项的值放入list中，在利用for循环遍历数组，在获取一行时做操作去处理数据，大概就是这样。

二、导入的数据

三、核心代码

先导入POI的依赖包

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

核心代码：

 /*导入试题*/
    @Override
    public BaeSubjectDTO importExcel(MultipartFile file) throws Exception {
        Snowflake snowflake = IdUtil.createSnowflake(1, 1);
        HSSFWorkbook wookbook = new HSSFWorkbook(file.getInputStream());
        HSSFSheet sheet = wookbook.getSheetAt(0);
        // 		指的行数，一共有多少行+
        int rows = sheet.getLastRowNum();
        for (int i = 1; i <= rows; i++) {
            // 读取左上端单元格
            HSSFRow row = sheet.getRow(i);
            // 行不为空
            if (row != null) {
                // 获取到Excel文件中的所有的列
                int maxcell = row.getLastCellNum();
                // **读取cell**
                String subType = getCellValue(row.getCell((short) 0));// 题目类型 1 单选，2多选，3 判断，4填空
                String subName = getCellValue(row.getCell((short) 1));//题目
                String subFraction = getCellValue(row.getCell((short) 2));// 题目分数
                String keyTrue = getCellValue(row.getCell((short) 3)); // 答案
                String subCategory = getCellValue(row.getCell((short) 4));// 隶属科目
                String subExplainTime = getCellValue(row.getCell((short) 5));// 规定答题时间(秒)


                String optionA = getCellValue(row.getCell((short) 6));  // A
                String optionB = getCellValue(row.getCell((short) 7));  // B
                String optionC = getCellValue(row.getCell((short) 8));  // C
                String optionD = getCellValue(row.getCell((short) 9)); // D
                String optionE = getCellValue(row.getCell((short) 10)); // E
                String optionF = getCellValue(row.getCell((short) 11)); // F

                //判空
                if (StringUtils.isEmpty(subType) &&
                        StringUtils.isEmpty(subName) &&
                        StringUtils.isEmpty(subFraction) &&
                        StringUtils.isEmpty(subCategory) &&
                        StringUtils.isEmpty(subExplainTime)
                ) {
                    break;
                }


                // 题目类型，题目，题目分数，隶属科目
                if (StringUtils.isEmpty(subType) || StringUtils.isEmpty(subName) || StringUtils.isEmpty(subFraction) || StringUtils.isEmpty(subCategory)) {
                    throw new Exception(
                            "第" + i + "行，试题内容为<" + subName + ">的那条数据数据不能为空（题目类型，题目，题目分数，隶属科目）") {
                    };
                }

                Log.get("试题类型：" + subType);

                switch (subType) {
                    case "单选":
                        subType = "1";
                        break;
                    case "多选":
                        subType = "2";
                        break;
                    case "判断":
                        subType = "3";
                        break;
                    case "填空":
                        subType = "4";
                        break;
                    default:
                        throw new Exception("第" + i + "行，试题内容为<" + subName + ">的那条数据试题类型不正确（试题类型只能输入单选,多选,判断,填空）") {
                        };
                }


                int typeInt = ConvertUtils.objectToInt(subType);
                // 如果为判断题最多2个选项
                if (typeInt == 3) {
                    if (StringUtils.isEmpty(optionA) || StringUtils.isEmpty(optionB)) {
                        throw new Exception("第" + i + "行，试题内容为<" + subName + ">的那条数据为判断题，选项A或选项B不能为空");
                    }
                    if (StringUtils.isNotEmpty(optionC) || StringUtils.isNotEmpty(optionD) || StringUtils.isNotEmpty(optionE)
                            || StringUtils.isNotEmpty(optionF)) {
                        throw new Exception("第" + i + "行，试题内容为<" + subName + ">的那条数据为判断题，选项C，D，E，F必须为空");
                    }
                }
                // 如果不是填空题，AB项不能为空
                if ( typeInt!=4) {
                    if (StringUtils.isEmpty(optionA) || StringUtils.isEmpty(optionB)) {
                        throw new Exception("第" + i + "行，试题内容为<" + subName + ">的那条数据为选择题，选项A，B不能为空");
                    }
                }
                // 如果为多选题正确答案必须在两个以上
                if (typeInt == 2 && keyTrue.trim().length() < 2) {
                    throw new Exception("第" + i + "行，试题内容为<" + subName + ">的那条数据的为多选题，正确答案必须在两个以上（例：AB）");
                }
                // 如果为单选题或者判断题答案只能有一个
                if (typeInt == 1 || typeInt == 3) {
                    if (keyTrue.trim().length() > 1) {
                        throw new Exception("第" + i + "行，试题内容为<" + subName + ">的那条数据的正确答案只能有一个（例：A）");
                    }
                }
                // 选项不能超过7个字符
                if (keyTrue.trim().length() > 7) {
                    throw new Exception("第" + i + "行，试题内容为<" + subName + ">的那条数据正确答案不能超过7个字符（例AB）");
                }
                // 验证正确答案不能输入其他字符
                char[] asr = keyTrue.toString().trim().toCharArray();
                String asrStr = "";
                for (int y = 0; y < asr.length; y++) {
                    asrStr += asr[y] + ",";
                    if ("ABCDEF".indexOf(String.valueOf(asr[y])) == -1) {
                        throw new Exception("第" + i + "行，试题内容为<" + subName + ">的那条数据正确答案输入字符格式不正确（例AB）");
                    }
                }

                BaeSubject baeSubject = new BaeSubject();

                baeSubject.setId(snowflake.nextId());
                String current = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
                Timestamp timestamp = Timestamp.valueOf(current);
                baeSubject.setCreateDate(timestamp);
                baeSubject.setOperId(SecurityUtils.getUserId().toString());
                baeSubject.setOperName(SecurityUtils.getUsername());
                baeSubject.setSubName(subName);
                baeSubject.setSubFraction(Integer.valueOf(subFraction.trim()));
                baeSubject.setSubCategory(subCategory);
                baeSubject.setSubExplainTime(Integer.valueOf(subExplainTime.trim()));
                baeSubject.setSubType(typeInt);  //类型
                if (null == baeSubject.getSubStatus() || "".equals(baeSubject.getSubStatus())) {
                    baeSubject.setSubStatus(SubStatusEnum.SUB_NOR_STATUS.getSubstatusCode());
                }
                if (null == baeSubject.getSubUse() || "".equals(baeSubject.getSubUse())) {
                    baeSubject.setSubUse(SubUseEnum.SUB_USE_STATUS.getSubUseCode());
                }
                // TODO 查询显示分平台考题显示：平台考题（1-人云平台，2-知识云平台）
                String permission = getRolePermission(SecurityUtils.getUserId());
                //1-云平台
                if (permission.equals(RolePermissionEnum.RolePermissionEnum_HR.getRolePermissionCode())) {
                    baeSubject.setSubPlatform(SubPlatformEnum.subPlatformEnum_HR.getSubPlatformCode());
                    //2-知识云平台
                } else if (permission.equals(RolePermissionEnum.RolePermissionEnum_HEALTH.getRolePermissionCode())) {
                    baeSubject.setSubPlatform(SubPlatformEnum.subPlatformEnum_Health.getSubPlatformCode());
                } else if (permission.equals(RolePermissionEnum.RolePermissionEnum_RS.getRolePermissionCode())) {
                    baeSubject.setSubPlatform(SubPlatformEnum.subPlatformEnum_RS.getSubPlatformCode());
                    //如果permission是admin，那么就不做任何操作
                } else if (permission.equals(RolePermissionEnum.RolePermissionEnum_Admin.getRolePermissionCode())) {

                } else {
                    throw new BadRequestException(9000, "不在系统指定的角色Permission内,请确认！");
                }

                BaeSubjectDTO baeSubjectDTO = baeSubjectMapper.toDto(baeSubjectRepository.save(baeSubject));

                int AASC = 64;
                //保存答案
                List<String> str = new ArrayList<String>();
                // 把选项的值放入list中
                str.add(optionA);
                str.add(optionB);
                str.add(optionC);
                str.add(optionD);
                str.add(optionE);
                str.add(optionF);

                List<BaeSubjectKey> baeSubjectKeys = new ArrayList<>();
                for (int k = 0; k < str.size(); k++) {
                    if (!"".equals(str.get(k).trim())) {// 如果选项为空字符串则不添加该选项
                        BaeSubjectKey baeSubjectKey = new BaeSubjectKey();
                        baeSubjectKey.setId(snowflake.nextId());
                        baeSubjectKey.setSubId(baeSubjectDTO.getId());
                        baeSubjectKey.setKeyMsg(str.get(k).trim());

                        if (null == baeSubjectKey.getKeyUse() || "".equals(baeSubjectKey.getKeyUse())) {
                            baeSubjectKey.setKeyUse(SubUseEnum.SUB_USE_STATUS.getSubUseCode());
                        }

                        char data = (char) (AASC += 1);
                        baeSubjectKey.setShowOrder(String.valueOf(data));


                        if (typeInt != 2 && keyTrue.equals(baeSubjectKey.getShowOrder())) {
                            baeSubjectKey.setKeyTrue("t");
                        } else if (typeInt == 2) {
                            String str2 = keyTrue.replaceAll(" ", "");//去除空值
                            String[] chars = str2.split(""); //用分割
                            Arrays.sort(chars);  //多选题时调整答案顺序
                            if (Arrays.binarySearch(chars, baeSubjectKey.getShowOrder()) >= 0) { //二分法来查找是否存在某个值，如果某个值存在则返回值大于0，反之返回值则小于0
                                baeSubjectKey.setKeyTrue("t");
                            } else if (Arrays.binarySearch(chars, baeSubjectKey.getShowOrder()) <0){
                                baeSubjectKey.setKeyTrue("f");
                            }
                        } else {
                            baeSubjectKey.setKeyTrue("f");
                        }

                        baeSubjectKeys.add(baeSubjectKey);
                        baeSubjectKeyRepository.saveAll(baeSubjectKeys);
                    }
                }
            }
        }
        return null;
    }


    /**
     * 获得Hsscell内容
     *
     * @param cell
     * @return
     */
    private String getCellValue(HSSFCell cell) {
        if (cell != null) {
            String value = "";
            switch (cell.getCellType()) {
                case NUMERIC: // 数值型
                    DecimalFormat df = new DecimalFormat("#.##");
                    if (HSSFDateUtil.isCellDateFormatted(cell)) {
                        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
                        value = sdf.format(HSSFDateUtil.getJavaDate(cell.getNumericCellValue()));
                    } else { // 纯数字
                        value = df.format(cell.getNumericCellValue());
                    }
                    return value;
                case STRING: // 字符串型
                    value = cell.getRichStringCellValue().toString();
                    return value;
                case BOOLEAN: // 布尔
                    value = "" + cell.getBooleanCellValue();
                    return value;
                case BLANK: // 空值
                    value = "";
                case ERROR: // 故障
                    value = "";
                    return value;
                case FORMULA:
                    HSSFWorkbook wb = cell.getSheet().getWorkbook();
                    HSSFCreationHelper crateHelper = wb.getCreationHelper();
                    HSSFFormulaEvaluator evaluator = crateHelper.createFormulaEvaluator();
                    value = getCellValue(evaluator.evaluateInCell(cell));
                    return value;
                default:
                    value = cell.getRichStringCellValue().toString();
                    return value;
            }
        }
        return "";
    }

导入后看数据库：

具体细节后面再议，感谢这位博主：
https://blog.csdn.net/qq_35275233/article/details/84890809