PIO----创建Excel表格复杂使用

导出

 @RequestMapping(
            name = "下载模板附件实现Model",
            value = {"/uploadFileModel"},
            method = {RequestMethod.GET, RequestMethod.POST}
    )
    @ResponseBody
    public void uploadFileModel(HttpServletRequest request, @RequestParam("checkType") String checkType, @RequestParam("paramType") String paramType) {
        SessionUser sessionUser = super.getSessionUser();
        if (null == sessionUser) {
            throw new BusinessException("用户登录状态过期");
        }
        OutputStream outputStream = null;
        try {
            Workbook workbook = sampleParamNewService.createWorkBook(checkType, paramType);
            if (workbook != null) {
                ByteArrayOutputStream fos = new ByteArrayOutputStream();
                byte[] retArr = null;
                workbook.write(fos);
                retArr = fos.toByteArray();
                fos.close();
                workbook.close();
                outputStream = response.getOutputStream();
                String exportName = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
                response.reset();
                response.setCharacterEncoding("UTF-8");
                String filename = URLEncoder.encode(exportName + "参数模板下载" + ".xlsx", "UTF-8");
                response.setHeader("Content-Disposition", "attachment; filename=" + filename);//要保存的文件名
                response.setContentType("application/octet-stream; charset=utf-8");
                outputStream.write(retArr);
                outputStream.flush();
            }

        } catch (Exception e) {
            e.printStackTrace();

        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

实现

/**
 * SampleParamNewServiceImpl
 */
@Service
public class SampleParamNewServiceImpl {


    /**
     * @param sampleParamNew
     */
    public void afterSave(SampleParamNew sampleParamNew) {
        if (null != sampleParamNew.getParamRelationshipProjectList()) {
            sampleParamNew.getParamRelationshipProjectList().forEach(paramRelationshipProject -> {
                paramRelationshipProject.setSampleParamNewId(sampleParamNew.getMid());
                paramRelationshipProjectService.save(paramRelationshipProject);
            });
        }
        if (null != sampleParamNew.getSampleParamMappingList()) {
            sampleParamNew.getSampleParamMappingList().forEach(sampleParamMapping -> {
                sampleParamMapping.setSampleParamNewId(sampleParamNew.getMid());
                sampleParamMappingService.save(sampleParamMapping);
            });
        }
        if (null != sampleParamNew.getSampleParamSonList()) {
            sampleParamNew.getSampleParamSonList().forEach(sampleParamSon -> {
                sampleParamSon.setSampleParamNewId(sampleParamNew.getMid());
                sampleParamSonService.save(sampleParamSon);
            });
        }
    }

    /**
     * @param sampleParamNew
     */
    public void afterUpdate(SampleParamNew sampleParamNew) {
        if (null != sampleParamNew.getParamRelationshipProjectList()) {
            sampleParamNew.getParamRelationshipProjectList().forEach(paramRelationshipProject -> {
                paramRelationshipProject.setSampleParamNewId(sampleParamNew.getMid());
                if (null == paramRelationshipProject.getMid()) {
                    paramRelationshipProjectService.save(paramRelationshipProject);
                } else {
                    paramRelationshipProjectService.update(paramRelationshipProject);
                }
            });
        }
        if (null != sampleParamNew.getSampleParamMappingList()) {
            sampleParamNew.getSampleParamMappingList().forEach(sampleParamMapping -> {
                sampleParamMapping.setSampleParamNewId(sampleParamNew.getMid());
                if (null == sampleParamMapping.getMid()) {
                    sampleParamMappingService.save(sampleParamMapping);
                } else {
                    sampleParamMappingService.update(sampleParamMapping);
                }
            });
        }
        if (null != sampleParamNew.getSampleParamSonList()) {
            sampleParamNew.getSampleParamSonList().forEach(sampleParamSon -> {
                sampleParamSon.setSampleParamNewId(sampleParamNew.getMid());
                if (null == sampleParamSon.getMid()) {
                    sampleParamSonService.save(sampleParamSon);
                } else {
                    sampleParamSonService.update(sampleParamSon);
                }
            });
        }
    }

    @Override
    @Async
    public void buildUpdateLog(Long mid, SessionUser sessionUser) {
        SampleParamLog sampleParamLog = new SampleParamLog();
        sampleParamLog.setCreatorId(sessionUser.getUserId());
        sampleParamLog.setCreator(sessionUser.getUserName());
        sampleParamLog.setCreateTime(new Date());
        SampleParamNew aNew = this.get(mid);
        sampleParamLog.setParamCode(aNew.getParamCode());
        sampleParamLog.setParamName(aNew.getParamName());
        sampleParamLog.setStateId(1);
        sampleParamLog.setOperator(sessionUser.getUserName());
        sampleParamLog.setUpdateType("删除");
        sampleParamLog.setUpdateBeforeVal("ALL");
        sampleParamLog.setUpdateAfterVal("/");
        sampleParamLog.setUpdateContext(aNew.getParamCode());
        sampleParamLogService.save(sampleParamLog);
    }

    @Override
    public void buildDetail(SampleParamNew sampleParamNew, ModelAndView mav, HttpServletRequest request) {
        List<Dictionary> dictionaries = dictionaryRemoteService.listAll("assets.config.parameters");
        if (CollectionUtils.isNotEmpty(dictionaries)) {
            List<Map<String, String>> configParameters = new ArrayList<>();
            for (Dictionary dictionary : dictionaries) {
                Map<String, String> configParameter = new HashMap<>();
                configParameter.put("label", dictionary.getName());
                configParameter.put("value", dictionary.getCode());
                configParameters.add(configParameter);
            }
            mav.addObject("configParameters", configParameters);
        }
        mav.addObject("defalutConfigParameters", sampleParamNew.getMultipleConfigurations());
        String method = request.getParameter("method");
        if (StringUtils.isNotEmpty(method) && method.equals("add")) {
            String activeNameOne = request.getParameter("activeNameOne");
            String activeNameTwo = request.getParameter("activeNameTwo");
            sampleParamNew.setParamCode("系统自动生成");
            sampleParamNew.setMenuCode(activeNameOne + "-" + activeNameTwo);
        }
        String menuCode = sampleParamNew.getMenuCode();
        if (StringUtils.isNotEmpty(menuCode)) {
            String[] split = menuCode.split("-");
            sampleParamNew.setParamType(split[1]);
            sampleParamNew.setCheckType(split[0]);
        }
        Map<String, List<SampleParamTypeMenu>> checkTypeAndParamType = getCheckTypeAndParamType();
        mav.addObject("checkTypeOpt", checkTypeAndParamType.get("checkTypeOpt"));
        mav.addObject("paramTypeOpt", checkTypeAndParamType.get("paramTypeOpt"));

    }

    public Map<String, List<SampleParamTypeMenu>> getCheckTypeAndParamType() {
        SampleParamTypeMenu sampleParamTypeMenu = new SampleParamTypeMenu();
        sampleParamTypeMenu.setStateId(1);
        List<SampleParamTypeMenu> sampleParamTypeMenus = sampleParamTypeMenuService.listAll(sampleParamTypeMenu);
        List<SampleParamTypeMenu> checkTypeOpt = sampleParamTypeMenus.stream().filter(item -> item.getParamTypeMenuLayer() == 0).sorted(Comparator.comparing(SampleParamTypeMenu::getIsShow)).collect(Collectors.toList());
        List<SampleParamTypeMenu> paramTypeOpt = sampleParamTypeMenus.stream().filter(item -> item.getParamTypeMenuLayer() == 1).sorted(Comparator.comparing(SampleParamTypeMenu::getIsShow)).collect(Collectors.toList());
        Map<String, List<SampleParamTypeMenu>> resp = new HashMap<>();
        resp.put("checkTypeOpt", checkTypeOpt);
        resp.put("paramTypeOpt", paramTypeOpt);
        return resp;
    }

    /**
     * 异步构建操作的日志记录；记录失败也不影响原始业务
     *
     * @param sampleParamNew
     * @param sessionUser
     */
    @Override
    @Async
    public void buildSabeOrUpdateLog(SampleParamNew sampleParamNew, SessionUser sessionUser) {
        Long mid = sampleParamNew.getMid();
        SampleParamLog sampleParamLog = new SampleParamLog();
        sampleParamLog.setCreatorId(sessionUser.getUserId());
        sampleParamLog.setCreator(sessionUser.getUserName());
        sampleParamLog.setCreateTime(new Date());
        sampleParamLog.setParamCode(sampleParamNew.getParamCode());
        sampleParamLog.setParamName(sampleParamNew.getParamName());
        sampleParamLog.setOperator(sessionUser.getUserName());
        sampleParamLog.setStateId(1);
        if (mid == null) {
            sampleParamLog.setUpdateType("新增");
            sampleParamLog.setUpdateBeforeVal("/");
            sampleParamLog.setUpdateAfterVal("参数名称：" + sampleParamNew.getParamName());
            sampleParamLog.setUpdateContext(sampleParamNew.getParamCode());
            sampleParamLogService.save(sampleParamLog);
            return;
        }
        List<SampleParamLog> updateList = new ArrayList<>();
        // JSON序列化深度克隆
        String tempObjec = JSONObject.toJSONString(sampleParamLog);
        SampleParamNew aNew = this.get(mid);
        // 反射获取属性
        Field[] declaredFields = aNew.getClass().getDeclaredFields();
        Field[] declaredFields2 = sampleParamNew.getClass().getDeclaredFields();
        for (Field field : declaredFields) {
            field.setAccessible(true);
            try {
                Object o = field.get(aNew);
                boolean isContinue = isContinue(field);
                if (isContinue) {
                    continue;
                }
                for (Field field1 : declaredFields2) {
                    field1.setAccessible(true);
                    boolean isContinue2 = isContinue(field);
                    if (isContinue2) {
                        continue;
                    }
                    Object o1 = field1.get(sampleParamNew);
                    if (field.getName().equals(field1.getName()) && !Objects.equals(o, o1)) {
                        if (String.valueOf(o).equals("null") && StringUtils.isEmpty(String.valueOf(o1))) {
                            continue;
                        }
                        sampleParamLog = JSONObject.parseObject(tempObjec, SampleParamLog.class);
                        sampleParamLog.setUpdateType("修改");
                        sampleParamLog.setUpdateBeforeVal(String.valueOf(o));
                        sampleParamLog.setUpdateAfterVal(String.valueOf(o1));
                        sampleParamLog.setUpdateContext(buildEnglishKeyChangeToChina(field.getName()));
                        updateList.add(sampleParamLog);
                    }
                }
            } catch (IllegalAccessException e) {
                throw new RuntimeException(e);
            }
        }
        // 关联项目
        handlerRelationShipProject(sampleParamNew, updateList, tempObjec);

        // 映射参数
        handlerParamMapping(sampleParamNew, updateList, tempObjec);

        // 子参数
        handlerParamSon(sampleParamNew, updateList, tempObjec);

        if (CollectionUtils.isNotEmpty(updateList)) {
            for (SampleParamLog paramLog : updateList) {
                sampleParamLogService.save(paramLog);
            }
        }

    }

    @Override
    public void checkRequiredParam(SampleParamNew sampleParamNew) {
        Long combinedOk = sampleParamNew.getCombinedOk();
        String paramName = sampleParamNew.getParamName();
        String dataType = sampleParamNew.getDataType();
        String deptName = sampleParamNew.getDeptName();
        String multipleConfigurations = sampleParamNew.getMultipleConfigurations();
        String type = sampleParamNew.getType();
        // 非公用情况下
        if (combinedOk == 0 && (StringUtils.isEmpty(paramName)
                || StringUtils.isEmpty(dataType)
                || StringUtils.isEmpty(deptName)
                || StringUtils.isEmpty(type))) {
            throw new BusinessException("非通用的必填参数未完整填充");
        }
        if (combinedOk == 1 && (StringUtils.isEmpty(paramName)
                || StringUtils.isEmpty(dataType)
                || StringUtils.isEmpty(type))) {
            throw new BusinessException("通用下的必填参数未完整填充");
        }
        if (StringUtils.isNotEmpty(multipleConfigurations) && multipleConfigurations.startsWith(",")) {
            sampleParamNew.setMultipleConfigurations(multipleConfigurations.replaceFirst(",", ""));
        }
    }

    @Override
    public String getCheckTypeByMenu(String checkType) {
        SampleParamTypeMenu sampleParamTypeMenu = new SampleParamTypeMenu();
        sampleParamTypeMenu.setParamTypeMenuCode(checkType);
        sampleParamTypeMenu.setStateId(1);
        return sampleParamTypeMenuService.listAll(sampleParamTypeMenu).get(0).getRemark();
    }

    public String buildEnglishKeyChangeToChina(String key) {
        switch (key) {
            case "paramCode":
                return "参数编码";
            case "paramName":
                return "参数名称";
            case "deptName":
                return "所属部门";
        }
        return key;
    }

    private void handlerParamSon(SampleParamNew sampleParamNew, List<SampleParamLog> updateList, String tempObjec) {
        SampleParamLog sampleParamLog;
        List<SampleParamSon> sampleParamMappingList = sampleParamNew.getSampleParamSonList();
        SampleParamSon sampleParamMapping = new SampleParamSon();
        sampleParamMapping.setStateId(1);
        sampleParamMapping.setSampleParamNewId(sampleParamNew.getMid());
        List<SampleParamSon> sampleParamMappings = sampleParamSonService.listAll(sampleParamMapping);
        if (CollectionUtils.isNotEmpty(sampleParamMappingList) && CollectionUtils.isEmpty(sampleParamMappings)) {
            for (SampleParamSon relationshipProject : sampleParamMappingList) {
                sampleParamLog = JSONObject.parseObject(tempObjec, SampleParamLog.class);
                sampleParamLog.setUpdateType("新增");
                sampleParamLog.setUpdateBeforeVal("/");
                sampleParamLog.setUpdateAfterVal(relationshipProject.getParamName());
                sampleParamLog.setUpdateContext("组合子参数-" + relationshipProject.getParamName());
                updateList.add(sampleParamLog);
            }
        }
        if (CollectionUtils.isNotEmpty(sampleParamMappingList) && CollectionUtils.isNotEmpty(sampleParamMappings)) {
            for (SampleParamSon relationshipProject : sampleParamMappingList) {
                boolean isSuccess = true;
                for (SampleParamSon project : sampleParamMappings) {
                    // ID想通但是值不同
                    if (relationshipProject.getMid() != null && Objects.equals(project.getMid(), relationshipProject.getMid())) {
                        if (!project.getParamName().equals(relationshipProject.getParamName())) {
                            sampleParamLog = JSONObject.parseObject(tempObjec, SampleParamLog.class);
                            sampleParamLog.setUpdateType("修改");
                            sampleParamLog.setUpdateBeforeVal(project.getParamName());
                            sampleParamLog.setUpdateAfterVal(relationshipProject.getParamName());
                            sampleParamLog.setUpdateContext("组合子参数-" + relationshipProject.getParamName());
                            updateList.add(sampleParamLog);
                        }
                        isSuccess = false;
                        break;
                    }
                }
                if (isSuccess) {
                    sampleParamLog = JSONObject.parseObject(tempObjec, SampleParamLog.class);
                    sampleParamLog.setUpdateType("新增");
                    sampleParamLog.setUpdateBeforeVal("/");
                    sampleParamLog.setUpdateAfterVal(relationshipProject.getParamName());
                    sampleParamLog.setUpdateContext("组合子参数-" + relationshipProject.getParamName());
                    updateList.add(sampleParamLog);
                }
            }
        }
        if (CollectionUtils.isEmpty(sampleParamMappingList) && CollectionUtils.isNotEmpty(sampleParamMappings)) {
            for (SampleParamSon project : sampleParamMappings) {
                sampleParamLog = JSONObject.parseObject(tempObjec, SampleParamLog.class);
                sampleParamLog.setUpdateType("删除");
                sampleParamLog.setUpdateBeforeVal(project.getParamName());
                sampleParamLog.setUpdateAfterVal("/");
                sampleParamLog.setUpdateContext("组合子参数-" + project.getParamName());
                updateList.add(sampleParamLog);
            }
        }
    }

    private void handlerParamMapping(SampleParamNew sampleParamNew, List<SampleParamLog> updateList, String tempObjec) {
        SampleParamLog sampleParamLog;
        List<SampleParamMapping> sampleParamMappingList = sampleParamNew.getSampleParamMappingList();
        SampleParamMapping sampleParamMapping = new SampleParamMapping();
        sampleParamMapping.setStateId(1);
        sampleParamMapping.setSampleParamNewId(sampleParamNew.getMid());
        List<SampleParamMapping> sampleParamMappings = sampleParamMappingService.listAll(sampleParamMapping);
        if (CollectionUtils.isNotEmpty(sampleParamMappingList) && CollectionUtils.isEmpty(sampleParamMappings)) {
            for (SampleParamMapping relationshipProject : sampleParamMappingList) {
                sampleParamLog = JSONObject.parseObject(tempObjec, SampleParamLog.class);
                sampleParamLog.setUpdateType("新增");
                sampleParamLog.setUpdateBeforeVal("/");
                sampleParamLog.setUpdateAfterVal(relationshipProject.getParamName());
                sampleParamLog.setUpdateContext("映射参数-" + relationshipProject.getParamName());
                updateList.add(sampleParamLog);
            }
        }
        if (CollectionUtils.isNotEmpty(sampleParamMappingList) && CollectionUtils.isNotEmpty(sampleParamMappings)) {
            for (SampleParamMapping relationshipProject : sampleParamMappingList) {
                boolean isSuccess = true;
                for (SampleParamMapping project : sampleParamMappings) {
                    // ID想通但是值不同
                    if (relationshipProject.getMid() != null && Objects.equals(project.getMid(), relationshipProject.getMid())) {
                        if (!project.getParamName().equals(relationshipProject.getParamName())) {
                            sampleParamLog = JSONObject.parseObject(tempObjec, SampleParamLog.class);
                            sampleParamLog.setUpdateType("修改");
                            sampleParamLog.setUpdateBeforeVal(project.getParamName());
                            sampleParamLog.setUpdateAfterVal(relationshipProject.getParamName());
                            sampleParamLog.setUpdateContext("映射参数-" + relationshipProject.getParamName());
                            updateList.add(sampleParamLog);
                        }
                        isSuccess = false;
                        break;
                    }
                }
                if (isSuccess) {
                    sampleParamLog = JSONObject.parseObject(tempObjec, SampleParamLog.class);
                    sampleParamLog.setUpdateType("新增");
                    sampleParamLog.setUpdateBeforeVal("/");
                    sampleParamLog.setUpdateAfterVal(relationshipProject.getParamName());
                    sampleParamLog.setUpdateContext("映射参数-" + relationshipProject.getParamName());
                    updateList.add(sampleParamLog);
                }
            }
        }
        if (CollectionUtils.isEmpty(sampleParamMappingList) && CollectionUtils.isNotEmpty(sampleParamMappings)) {
            for (SampleParamMapping project : sampleParamMappings) {
                sampleParamLog = JSONObject.parseObject(tempObjec, SampleParamLog.class);
                sampleParamLog.setUpdateType("删除");
                sampleParamLog.setUpdateBeforeVal(project.getParamName());
                sampleParamLog.setUpdateAfterVal("/");
                sampleParamLog.setUpdateContext("映射参数-" + project.getParamName());
                updateList.add(sampleParamLog);
            }
        }

    }

    private void handlerRelationShipProject(SampleParamNew sampleParamNew, List<SampleParamLog> updateList, String tempObjec) {
        SampleParamLog sampleParamLog;
        // 传入的关联项目
        List<ParamRelationshipProject> paramRelationshipProjectList = sampleParamNew.getParamRelationshipProjectList();
        ParamRelationshipProject paramRelationshipProject = new ParamRelationshipProject();
        paramRelationshipProject.setSampleParamNewId(sampleParamNew.getMid());
        paramRelationshipProject.setStateId(1);
        // 数据库原始数据的关联项目
        List<ParamRelationshipProject> paramRelationshipProjects = paramRelationshipProjectService.listAll(paramRelationshipProject);
        // 如果传入的值，数据库原始无值{属于新增}
        if (CollectionUtils.isNotEmpty(paramRelationshipProjectList) && CollectionUtils.isEmpty(paramRelationshipProjects)) {
            for (ParamRelationshipProject relationshipProject : paramRelationshipProjectList) {
                sampleParamLog = JSONObject.parseObject(tempObjec, SampleParamLog.class);
                sampleParamLog.setUpdateType("新增");
                sampleParamLog.setUpdateBeforeVal("/");
                sampleParamLog.setUpdateAfterVal(relationshipProject.getTestItemName());
                sampleParamLog.setUpdateContext("关联项目-" + relationshipProject.getTestItemName());
                updateList.add(sampleParamLog);
            }
        }
        // 如果传入的数据有值，数据库里面也有值
        if (CollectionUtils.isNotEmpty(paramRelationshipProjectList) && CollectionUtils.isNotEmpty(paramRelationshipProjects)) {
            for (ParamRelationshipProject relationshipProject : paramRelationshipProjectList) {
                boolean isSuccess = true;
                // 遍历传入的是否是数据库原始的
                for (ParamRelationshipProject project : paramRelationshipProjects) {
                    // ID想通但是值不同====修改了原始数据
                    if (relationshipProject.getMid() != null && Objects.equals(project.getMid(), relationshipProject.getMid())) {
                        // 同一个ID下的名称不相同===数据有变更
                        if (!project.getTestItemName().equals(relationshipProject.getTestItemName())) {
                            sampleParamLog = JSONObject.parseObject(tempObjec, SampleParamLog.class);
                            sampleParamLog.setUpdateType("修改");
                            sampleParamLog.setUpdateBeforeVal(project.getTestItemName());
                            sampleParamLog.setUpdateAfterVal(relationshipProject.getTestItemName());
                            sampleParamLog.setUpdateContext("关联项目-" + relationshipProject.getTestItemName());
                            updateList.add(sampleParamLog);
                        }
                        // 同一个ID下的名称相同===数据没有变更
                        isSuccess = false;
                        break;
                    }
                }
                // 找不到有修改了数据情况====并没有修改数据||或者删除了原始数据，新增了新数据
                if (isSuccess) {
                    sampleParamLog = JSONObject.parseObject(tempObjec, SampleParamLog.class);
                    sampleParamLog.setUpdateType("新增");
                    sampleParamLog.setUpdateBeforeVal("/");
                    sampleParamLog.setUpdateAfterVal(relationshipProject.getTestItemName());
                    sampleParamLog.setUpdateContext("关联项目-" + relationshipProject.getTestItemName());
                    updateList.add(sampleParamLog);
                }
            }
        }
        // 如果传入的数据无值，数据库里面也有值{删除}
        if (CollectionUtils.isEmpty(paramRelationshipProjectList) && CollectionUtils.isNotEmpty(paramRelationshipProjects)) {
            for (ParamRelationshipProject project : paramRelationshipProjects) {
                sampleParamLog = JSONObject.parseObject(tempObjec, SampleParamLog.class);
                sampleParamLog.setUpdateType("删除");
                sampleParamLog.setUpdateBeforeVal(project.getTestItemName());
                sampleParamLog.setUpdateAfterVal("/");
                sampleParamLog.setUpdateContext("关联项目-" + project.getTestItemName());
                updateList.add(sampleParamLog);
            }
        }
    }


    private static boolean isContinue(Field field) {
        boolean isContinue = false;
        switch (field.getName()) {
            case "ParamRelationshipProjectArrayStr":
                isContinue = true;
                break;
            case "ParamRelationshipProjectList":
                isContinue = true;
                break;
            case "SampleParamMappingArrayStr":
                isContinue = true;
                break;
            case "SampleParamMappingList":
                isContinue = true;
                break;
            case "SampleParamSonArrayStr":
                isContinue = true;
                break;
            case "SampleParamSonList":
                isContinue = true;
                break;
            case "paramMappingId":
                isContinue = true;
                break;
            case "checkType":
                isContinue = true;
                break;
            case "paramType":
                isContinue = true;
                break;
            case "dateCreater":
                isContinue = true;
                break;
        }
        return isContinue;
    }

    /**
     * 生成规则为：KP+年份（4位）+月份（2位）+流水号（4位），流水号每月自动重置。
     */
    public String createApplicationNo(String prefix, String format) {
        // 先从回收箱里面获取已经释放的资源
        if (StringUtils.isEmpty(prefix)) {
            prefix = "CK";
        }
        if (StringUtils.isEmpty(format)) {
            format = "XXXX";
        }
        String nextValue = null;
        try {
            nextValue = sequenceRemoteService.nextValue(prefix + "_" + format + "_param_seq", 4);
        } catch (Exception e) {
            logger.info("部门编号为" + format + "的参数编码序列号未取到" + e.getMessage());
            nextValue = UUID.randomUUID().toString().substring(0, 4);
        }
        return prefix + format + nextValue;
    }

    @Autowired
    private SampleParamRecyclingBinService sampleParamRecyclingBinService;

    /**
     * 编码放入回收箱
     *
     * @param sampleParamNew
     */
    public void deleteApplicationNo(SampleParamNew sampleParamNew, SessionUser sessionUser) {
        // 部门编码
        SampleParamRecyclingBin sampleParamRecyclingBin = new SampleParamRecyclingBin();
        sampleParamRecyclingBin.setApplicationNo(sampleParamNew.getParamCode());
        // 是否通用
        sampleParamRecyclingBin.setCurrency(sampleParamNew.getCurrency().intValue());
        sampleParamRecyclingBin.setOldParamTableMid(sampleParamNew.getMid());
        sampleParamRecyclingBin.setDept(sampleParamNew.getDeptName());
        sampleParamRecyclingBin.setRemark("参数编码放入回收箱");
        sampleParamRecyclingBin.setDeptCode(sampleParamNew.getParamCode().substring(0, 6));
        sampleParamRecyclingBin.setStateId(1);
        sampleParamRecyclingBin.setCreator(sessionUser.getUserName());
        sampleParamRecyclingBin.setCreatorId(sessionUser.getUserId());
        sampleParamRecyclingBin.setCreateTime(new Date());
        sampleParamRecyclingBinService.save(sampleParamRecyclingBin);
    }

    @Override
    public List<UserTypeVo> listByAddUserAndUpdateUser() {
        return sampleParamNewRepository.listByAddUserAndUpdateUser();
    }

    @Override
    public List<SampleParamNew> listByParamCode(String[] paramCodes) {
        return sampleParamNewRepository.listByParamCode(paramCodes);
    }

    @Override
    public XSSFWorkbook createWorkBook(String checkType, String paramType) {
        XSSFWorkbook workbook = new XSSFWorkbook();

        XSSFWorkbook resp = handlerCommon(paramType, workbook, checkType);
        if (resp != null) {
            return resp;
        }
        return null;
    }

    @Override
    public List<SampleParamNew> geiListNewObj(XSSFSheet sheetAt, String paramTypeVar, String checkTypeVar) {
        List<SampleParamNew> sampleParamNewList = new ArrayList<>();
        // 获取校验参数
        Map<String, List<SampleParamTypeMenu>> checkTypeAndParamType = this.getCheckTypeAndParamType();
        List<SampleParamTypeMenu> checkTypeOpt = checkTypeAndParamType.get("checkTypeOpt");
        List<SampleParamTypeMenu> paramTypeOpt = checkTypeAndParamType.get("paramTypeOpt");
        // 获取标题----截取参数
        int indexBeginData = 0;
        for (Row cells : sheetAt) {
            if (indexBeginData < 2) {
                // 跳过标题
                indexBeginData++;
                continue;
            }
            SampleParamNew sampleParamNew = new SampleParamNew();
            for (Cell cell : cells) {
                // 获取列的索引
                int columnIndex = cell.getColumnIndex();
                // 导入模板中不允许包含参数编码。
                if (paramTypeVar.equals(ParamTypeInnerEnum.JCJGP.getCode()) || paramTypeVar.equals(ParamTypeInnerEnum.BCP.getCode())) {

                    handleRowCellValueNotHaveCode(sampleParamNew, cell, columnIndex, checkTypeOpt, paramTypeOpt);
                } else {
                    handleRowCellValue(sampleParamNew, cell, columnIndex, checkTypeOpt, paramTypeOpt);
                }
            }
            sampleParamNewList.add(sampleParamNew);
        }
        if (CollectionUtils.isNotEmpty(sampleParamNewList)) {
            for (SampleParamNew sampleParamNew : sampleParamNewList) {
                StringBuilder menuCode = new StringBuilder();
                for (SampleParamTypeMenu checkType : checkTypeOpt) {
                    if (sampleParamNew.getCheckType().equals(checkType.getParamTypeMenuName())) {
                        menuCode = new StringBuilder(checkType.getParamTypeMenuCode()).append("-");
                    }
                }
                for (SampleParamTypeMenu paramType : paramTypeOpt) {
                    if (sampleParamNew.getParamType().equals(paramType.getParamTypeMenuName())) {
                        menuCode.append(paramType.getParamTypeMenuCode());
                    }
                }
                sampleParamNew.setMenuCode(menuCode.toString());

            }
        }
        return sampleParamNewList;
    }

    @Override
    public boolean checkUserRole(SessionUser sessionUser, SampleParamNew sampleParamNew) {
        // 参数管理员能操作
        if (sessionUser.hasRole("Super") || sessionUser.hasRole("Admin")) {
            return true;
        }
        if (sessionUser.hasRole("sampleParamManager")) {
            // 获取修改之前的原始记录=== 防止绕过权限{给自己的部门加上后提交或者改成共享的提交}
            SampleParamNew oldData = sampleParamNewRepository.get(sampleParamNew.getMid());
            // 是否是共享的 是否通用
            Long currency = oldData.getCurrency();
            if (currency == 1) {
                return true;
            }
            // 获取部门
            Object orgName = sessionUser.getDepartments().get(0).get("orgName");
            String deptName = oldData.getDeptName();
            // 是否包含这个部门
            if (StringUtils.isNotEmpty(deptName) && deptName.contains(orgName.toString())) {
                return true;
            }
        }
        return false;
    }

    @Override
    public List<SampleParamNew> NotBindTestItemProjectlistByPage(SampleParamNew sampleParamNew, PageParameter pageParameter, String itemId) {
        List<SampleParamNew> sampleParamNewList = sampleParamNewRepository.NotBindTestItemProjectlistByPage(sampleParamNew, pageParameter, itemId);
        buildParamType(sampleParamNewList);
        return sampleParamNewList;
    }

    @Override
    public List<SampleParamNew> BindTestItemProjectlistByPage(SampleParamNew sampleParamNew, PageParameter pageParameter, String itemId) {
        List<SampleParamNew> sampleParamNewList = sampleParamNewRepository.BindTestItemProjectlistByPage(sampleParamNew, pageParameter, itemId);
        buildParamType(sampleParamNewList);
        return sampleParamNewList;
    }

    @Override
    public String getCheckTypeByName(String checkType) {
        SampleParamTypeMenu sampleParamTypeMenu = new SampleParamTypeMenu();
        sampleParamTypeMenu.setStateId(1);
        sampleParamTypeMenu.setParamTypeMenuName(checkType);
        return sampleParamTypeMenuService.listAll(sampleParamTypeMenu).get(0).getParamTypeMenuCode();
    }

    public void buildParamType(List<SampleParamNew> list) {
        if (CollectionUtils.isNotEmpty(list)) {
            for (SampleParamNew paramNew : list) {
                String menuCode = paramNew.getMenuCode();
                if (menuCode.contains(ParamTypeInnerEnum.BAP.getCode())) {
                    paramNew.setParamType(ParamTypeInnerEnum.BAP.getName());
                }
                if (menuCode.contains(ParamTypeInnerEnum.BCP.getCode())) {
                    paramNew.setParamType(ParamTypeInnerEnum.BCP.getName());
                }
                if (menuCode.contains(ParamTypeInnerEnum.GGP.getCode())) {
                    paramNew.setParamType(ParamTypeInnerEnum.GGP.getName());
                }
                if (menuCode.contains(ParamTypeInnerEnum.JCJGP.getCode())) {
                    paramNew.setParamType(ParamTypeInnerEnum.JCJGP.getName());
                }
            }
        }
    }

    private void handleRowCellValueNotHaveCode(SampleParamNew sampleParamNew, Cell cell, int columnIndex, List<SampleParamTypeMenu> checkTypeOpt, List<SampleParamTypeMenu> paramTypeOpt) {
        cell.setCellType(CellType.STRING);
        String stringCellValue = null;
        switch (columnIndex) {
            case 1:
                stringCellValue = cell.getStringCellValue();
                // 校验
                checkExcelDefaultType(stringCellValue,null,columnIndex);
                // 填值
                sampleParamNew.setParamName(stringCellValue);
                break;
            case 2:
                stringCellValue = cell.getStringCellValue();
                // 校验
                checkExcelDefaultType(stringCellValue,FinalMoreEnumUtils.dataType,columnIndex);
                // 填值
                sampleParamNew.setDataType(stringCellValue);
                break;
            case 3:
                stringCellValue = cell.getStringCellValue();
                sampleParamNew.setParamUnit(stringCellValue);
                break;
            case 4:
                stringCellValue = cell.getStringCellValue();
                // 校验
                checkExcelDefaultType(stringCellValue,FinalMoreEnumUtils.paramCarType,columnIndex);
                // 填值
                sampleParamNew.setType(stringCellValue);
                break;
            case 5:
                stringCellValue = cell.getStringCellValue();
                // 校验
                checkExcelDefaultCheckType(stringCellValue, checkTypeOpt, columnIndex);
                // 写入值
                sampleParamNew.setCheckType(stringCellValue);
                break;
            case 6:
                stringCellValue = cell.getStringCellValue();
                // 校验
                checkExcelDefaultParamType(stringCellValue, paramTypeOpt, columnIndex);
                // 写入值
                sampleParamNew.setParamType(stringCellValue);
                break;
            case 7:
                stringCellValue = cell.getStringCellValue();
                sampleParamNew.setDeptName(stringCellValue);
                break;
            case 8:
                stringCellValue = cell.getStringCellValue();
                sampleParamNew.setCarType(stringCellValue);
                break;
            case 9:
                stringCellValue = cell.getStringCellValue();
                sampleParamNew.setMultipleConfigurations(stringCellValue);
                break;
            case 10:
                stringCellValue = cell.getStringCellValue();
                sampleParamNew.setCombinedParameters(stringCellValue);
                break;
            case 11:
                stringCellValue = cell.getStringCellValue();
                sampleParamNew.setCombinedOk(stringCellValue.equals("是") ? 1L : 0L);
                break;
            case 12:
                stringCellValue = cell.getStringCellValue();
                sampleParamNew.setCurrency(stringCellValue.equals("否") ? 0L : 1L);
                break;
            case 13:
                stringCellValue = cell.getStringCellValue();
                sampleParamNew.setSpecialSplittingRules(stringCellValue);
                break;
            case 14:
                stringCellValue = cell.getStringCellValue();
                sampleParamNew.setCalculationRules(stringCellValue);
                break;
            case 15:
                stringCellValue = cell.getStringCellValue();
                sampleParamNew.setDeptId(stringCellValue);
                break;
        }
    }

    /**
     * 校验Excel导入的时候，某些限定输入的或者下拉框选择的数据是否是指定的值{若输入以外的其他的内容就读取失败--返回给前台}
     */
    public void checkExcelDefaultCheckType(String excelContext, List<SampleParamTypeMenu> checkTypeOpt, Integer columnIndex) {
        if (StringUtils.isEmpty(excelContext)) {
            throw new BusinessException("第" + columnIndex + "列出错了,红色列为必填列,该列未填入值,");
        }
        if (CollectionUtils.isNotEmpty(checkTypeOpt)) {
            List<String> collect = checkTypeOpt.stream().map(SampleParamTypeMenu::getParamTypeMenuName).filter(item -> item.equals(excelContext)).collect(Collectors.toList());
            if (CollectionUtils.isEmpty(collect)) {
                throw new BusinessException("第" + columnIndex + "列出错了,填入的值:" + excelContext + ",数据非法");
            }
        }
    }

    /**
     * 校验Excel导入的时候，某些限定输入的或者下拉框选择的数据是否是指定的值{若输入以外的其他的内容就读取失败--返回给前台}
     */
    public void checkExcelDefaultType(String excelContext, String[] values, Integer columnIndex) {
        if (StringUtils.isEmpty(excelContext)) {
            throw new BusinessException("第" + columnIndex + "列出错了,红色列为必填列,该列未填入值,");
        }
        if (ArrayUtils.isNotEmpty(values)) {
            boolean isOkThrowException = true;
            for (String value : values) {
                if (value.equals(excelContext)) {
                    isOkThrowException = false;
                }
            }
            if (isOkThrowException) {
                throw new BusinessException("第" + columnIndex + "列出错了,填入的值:" + excelContext + ",数据非法");
            }
        }
    }


    /**
     * 校验Excel导入的时候，某些限定输入的或者下拉框选择的数据是否是指定的值{若输入以外的其他的内容就读取失败--返回给前台}
     */
    public void checkExcelDefaultParamType(String excelContext, List<SampleParamTypeMenu> paramTypeOpt, Integer columnIndex) {
        if (StringUtils.isEmpty(excelContext)) {
            throw new BusinessException("第" + columnIndex + "列出错了,红色列为必填列,该列未填入值,");
        }
        if (CollectionUtils.isNotEmpty(paramTypeOpt)) {
            List<String> collect = paramTypeOpt.stream().map(SampleParamTypeMenu::getParamTypeMenuName).filter(item -> item.equals(excelContext)).collect(Collectors.toList());
            if (CollectionUtils.isEmpty(collect)) {
                throw new BusinessException("第" + columnIndex + "列出错了,填入的值:" + excelContext + ",数据非法");
            }
        }
    }

    private void handleRowCellValue(SampleParamNew sampleParamNew, Cell cell, int columnIndex, List<SampleParamTypeMenu> checkTypeOpt, List<SampleParamTypeMenu> paramTypeOpt) {
        cell.setCellType(CellType.STRING);
        String stringCellValue = null;
        switch (columnIndex) {
            case 1:
                stringCellValue = cell.getStringCellValue();
                // 校验
                checkExcelDefaultType(stringCellValue,null,columnIndex);
                // 填值
                sampleParamNew.setParamCode(stringCellValue);
                break;
            case 2:
                stringCellValue = cell.getStringCellValue();
                // 校验
                checkExcelDefaultType(stringCellValue,null,columnIndex);
                // 填值
                sampleParamNew.setParamName(stringCellValue);
                break;
            case 3:
                stringCellValue = cell.getStringCellValue();
                // 校验
                checkExcelDefaultType(stringCellValue,FinalMoreEnumUtils.dataType,columnIndex);
                // 填值
                sampleParamNew.setDataType(stringCellValue);
                break;
            case 4:
                stringCellValue = cell.getStringCellValue();
                sampleParamNew.setParamUnit(stringCellValue);
                break;
            case 5:
                stringCellValue = cell.getStringCellValue();
                // 校验
                checkExcelDefaultType(stringCellValue,FinalMoreEnumUtils.paramCarType,columnIndex);
                // 填值
                sampleParamNew.setType(stringCellValue);
                break;
            case 6:
                stringCellValue = cell.getStringCellValue();
                // 校验
                checkExcelDefaultCheckType(stringCellValue, checkTypeOpt, columnIndex);
                // 写入值
                sampleParamNew.setCheckType(stringCellValue);
                break;
            case 7:
                stringCellValue = cell.getStringCellValue();
                // 校验
                checkExcelDefaultParamType(stringCellValue, paramTypeOpt, columnIndex);
                // 写入值
                sampleParamNew.setParamType(stringCellValue);
                break;
            case 8:
                stringCellValue = cell.getStringCellValue();
                sampleParamNew.setDeptName(stringCellValue);
                break;
            case 9:
                stringCellValue = cell.getStringCellValue();
                sampleParamNew.setCarType(stringCellValue);
                break;
            case 10:
                stringCellValue = cell.getStringCellValue();
                sampleParamNew.setMultipleConfigurations(stringCellValue);
                break;
            case 11:
                stringCellValue = cell.getStringCellValue();
                sampleParamNew.setCombinedParameters(stringCellValue);
                break;
            case 12:
                stringCellValue = cell.getStringCellValue();
                sampleParamNew.setCombinedOk(stringCellValue.equals("是") ? 1L : 0L);
                break;
            case 13:
                stringCellValue = cell.getStringCellValue();
                sampleParamNew.setCurrency(stringCellValue.equals("否") ? 0L : 1L);
                break;
            case 14:
                stringCellValue = cell.getStringCellValue();
                sampleParamNew.setSpecialSplittingRules(stringCellValue);
                break;
            case 15:
                stringCellValue = cell.getStringCellValue();
                sampleParamNew.setCalculationRules(stringCellValue);
                break;
        }
    }

    private XSSFWorkbook handlerCommon(String paramType, XSSFWorkbook workbook, String checkType) {
        String[] titleArray = {"序号", "参数编码", "参数名称", "数据类型", "单位", "类型", "检测类别", "参数类别", "部门名称", "量型类别", "多配置分割符", "组合参数分隔符", "是否是组合参数", "是否通用", "特殊拆分规则", "计算规则"};
        XSSFSheet sheet = null;
        if (StringUtils.equals(paramType, ParamTypeInnerEnum.GGP.getCode())) {
            sheet = getInitXSSFSheet(workbook, ParamTypeInnerEnum.GGP.getName() + "导入模板", titleArray.length - 1);
        }
        if (StringUtils.equals(paramType, ParamTypeInnerEnum.BAP.getCode())) {
            sheet = getInitXSSFSheet(workbook, ParamTypeInnerEnum.BAP.getName() + "导入模板", titleArray.length - 1);
        }
        String[] titleArray2 = {"序号", "参数名称", "数据类型", "单位", "类型", "检测类别", "参数类别", "部门名称", "机型类别", "多配置分割符", "组合参数分隔符", "是否是组合参数", "是否通用", "特殊拆分规则", "计算规则", "部门编码"};
        if (StringUtils.equals(paramType, ParamTypeInnerEnum.JCJGP.getCode())) {
            sheet = getInitXSSFSheet(workbook, ParamTypeInnerEnum.JCJGP.getName() + "导入模板", titleArray2.length - 1);
        }
        if (StringUtils.equals(paramType, ParamTypeInnerEnum.BCP.getCode())) {
            sheet = getInitXSSFSheet(workbook, ParamTypeInnerEnum.BCP.getName() + "导入模板", titleArray2.length - 1);
        }
        int rownumIndex = 1;
        XSSFRow row2 = sheet.createRow(rownumIndex);
        if (StringUtils.equals(paramType, ParamTypeInnerEnum.JCJGP.getCode()) || StringUtils.equals(paramType, ParamTypeInnerEnum.BCP.getCode())) {
            // 扩大字段列
            int[] needAddColumnWidth = {1, 7};
            // 标记红色底色
            int[] needAddColumnRed = {1, 2, 4, 5, 6, 7, 15};
            this.setSheetBeginTitleRow(titleArray2, sheet, workbook, row2, needAddColumnWidth, needAddColumnRed);
            String[] strs = FinalMoreEnumUtils.dataType;
            // 绑定--选择器
            XSSFDataValidation dataValidation = setDataValidation(sheet, strs, 2, 100, 2, 2);
            sheet.addValidationData(dataValidation);

            String[] strs2 = FinalMoreEnumUtils.paramCarType;
            // 绑定--选择器
            XSSFDataValidation dataValidation2 = setDataValidation(sheet, strs2, 2, 100, 4, 4);
            sheet.addValidationData(dataValidation2);

            String[] strs4 = null;
            String[] strs5 = new String[]{checkType};
            if (StringUtils.equals(paramType, ParamTypeInnerEnum.GGP.getCode())) {
                strs4 = new String[]{
                        ParamTypeInnerEnum.GGP.getName()};
            }
            if (StringUtils.equals(paramType, ParamTypeInnerEnum.BAP.getCode())) {
                strs4 = new String[]{
                        ParamTypeInnerEnum.BAP.getName()};
            }
            if (StringUtils.equals(paramType, ParamTypeInnerEnum.JCJGP.getCode())) {
                strs4 = new String[]{
                        ParamTypeInnerEnum.JCJGP.getName()};
            }
            if (StringUtils.equals(paramType, ParamTypeInnerEnum.BCP.getCode())) {
                strs4 = new String[]{
                        ParamTypeInnerEnum.BCP.getName()};
            }
            // 绑定--选择器
            XSSFDataValidation dataValidation4 = setDataValidation(sheet, strs4, 2, 100, 6, 6);
            sheet.addValidationData(dataValidation4);

            XSSFDataValidation dataValidation5 = setDataValidation(sheet, strs5, 2, 100, 5, 5);
            sheet.addValidationData(dataValidation5);

            String[] strs9 = FinalMoreEnumUtils.carType;
            // 绑定--选择器
            XSSFDataValidation dataValidation9 = setDataValidation(sheet, strs9, 2, 100, 8, 8);
            sheet.addValidationData(dataValidation9);
        } else {
            // 扩大字段列
            int[] needAddColumnWidth = {1, 2};
            // 标记红色底色
            int[] needAddColumnRed = {1, 2, 3, 5, 6, 7, 13};
            this.setSheetBeginTitleRow(titleArray, sheet, workbook, row2, needAddColumnWidth, needAddColumnRed);

            String[] strs = FinalMoreEnumUtils.dataType;
            // 绑定--选择器
            XSSFDataValidation dataValidation = setDataValidation(sheet, strs, 2, 100, 3, 3);
            sheet.addValidationData(dataValidation);

            String[] strs2 = FinalMoreEnumUtils.paramCarType;
            // 绑定--选择器
            XSSFDataValidation dataValidation2 = setDataValidation(sheet, strs2, 2, 100, 5, 5);
            sheet.addValidationData(dataValidation2);

            String[] strs4 = null;
            String[] strs5 = new String[]{checkType};
            if (StringUtils.equals(paramType, ParamTypeInnerEnum.GGP.getCode())) {
                strs4 = new String[]{
                        ParamTypeInnerEnum.GGP.getName()};
            }
            if (StringUtils.equals(paramType, ParamTypeInnerEnum.BAP.getCode())) {
                strs4 = new String[]{
                        ParamTypeInnerEnum.BAP.getName()};
            }
            if (StringUtils.equals(paramType, ParamTypeInnerEnum.JCJGP.getCode())) {
                strs4 = new String[]{
                        ParamTypeInnerEnum.JCJGP.getName()};
            }
            if (StringUtils.equals(paramType, ParamTypeInnerEnum.BCP.getCode())) {
                strs4 = new String[]{
                        ParamTypeInnerEnum.BCP.getName()};
            }
            // 绑定--选择器
            XSSFDataValidation dataValidation4 = setDataValidation(sheet, strs4, 2, 100, 7, 7);
            sheet.addValidationData(dataValidation4);

            XSSFDataValidation dataValidation5 = setDataValidation(sheet, strs5, 2, 100, 6, 6);
            sheet.addValidationData(dataValidation5);

            String[] strs9 = FinalMoreEnumUtils.carType;
            // 绑定--选择器
            XSSFDataValidation dataValidation9 = setDataValidation(sheet, strs9, 2, 100, 9, 9);
            sheet.addValidationData(dataValidation9);


            // 设置必填
          /*  DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
            DataValidationConstraint customConstraint = dataValidationHelper.createCustomConstraint("NOT(ISBLANK(A1))");
            CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(2, 100, 1, 1);
            DataValidation validation = dataValidationHelper.createValidation(customConstraint, cellRangeAddressList);
            validation.setShowErrorBox(true);
            sheet.addValidationData(validation);*/
        }

        return workbook;
    }

    private XSSFDataValidation setDataValidation(XSSFSheet sheet, String[] textList, int firstRow, int endRow,
                                                 int firstCol, int endCol) {
        XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
        //不同点在于构造constraint对象的方式不一样
        XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint) dvHelper.createExplicitListConstraint(textList);
        CellRangeAddressList addressList = new CellRangeAddressList((short) firstRow, (short) endRow, (short) firstCol, (short) endCol);
        XSSFDataValidation validation = (XSSFDataValidation) dvHelper.createValidation(dvConstraint, addressList);
        return validation;
    }

    private void setSheetBeginTitleRow(String[] titleArray, XSSFSheet sheet, XSSFWorkbook workbook, XSSFRow row2, int[] needAddColumnWidth, int[] needAddColumnRed) {
        CellStyle cellStyle = getCellStyle2(workbook, 13);
        CellStyle cellStyleRed = getCellStyleRed(workbook, 13);
        for (int i = 0; i < titleArray.length; i++) {
            XSSFCell var0001 = row2.createCell(i);
            var0001.setCellValue(titleArray[i]);
            // 设置红底
            if (ArrayUtils.contains(needAddColumnRed, i)) {
                var0001.setCellStyle(cellStyleRed);
            } else {
                var0001.setCellStyle(cellStyle);
            }
            // 设置列宽
            if (ArrayUtils.contains(needAddColumnWidth, i)) {
                sheet.setColumnWidth(i, 35 * 256);
            } else {
                sheet.setColumnWidth(i, 15 * 256);
            }
        }
    }

    private XSSFSheet getInitXSSFSheet(XSSFWorkbook workbook, String sheetName, int cellRangeLastCol) {
        // 创建工作表并设置表名
        XSSFSheet sheet = workbook.createSheet(sheetName);
        // 设置字体样式
        CellStyle cellStyle = getCellStyle2(workbook, 14);
        // 【第一行】 创建行，下标从0开始
        XSSFRow row = sheet.createRow(0);
        XSSFCell cell = row.createCell(0);
        cell.setCellValue(sheetName);
        cell.setCellStyle(cellStyle);
        // 合并第一行数据 前10个单元格
        CellRangeAddress region = new CellRangeAddress(0, 0, 0, cellRangeLastCol);
        sheet.addMergedRegion(region);
        return sheet;
    }

    private CellStyle getCellStyle2(XSSFWorkbook workbook, int x) {
        Font font2 = getFont(workbook, (short) x);
        CellStyle cellStyle2 = workbook.createCellStyle();
        cellStyle2.setBorderBottom(BorderStyle.THIN); //下边框
        cellStyle2.setBorderLeft(BorderStyle.THIN);//左边框
        cellStyle2.setBorderTop(BorderStyle.THIN);//上边框
        cellStyle2.setBorderRight(BorderStyle.THIN);//右边框
        cellStyle2.setFont(font2);
        // 居中
        cellStyle2.setAlignment(HorizontalAlignment.CENTER);
        return cellStyle2;
    }

    private Font getFont(XSSFWorkbook workbook, short x) {
        Font font2 = workbook.createFont();
        font2.setFontHeightInPoints(x);
        return font2;
    }

    private CellStyle getCellStyleRed(XSSFWorkbook workbook, int x) {
        CellStyle cellStyle2 = getCellStyle2(workbook, x);
        Font font = getFont(workbook, (short) x);
        font.setBold(true);
        font.setColor(IndexedColors.DARK_RED.getIndex());
        cellStyle2.setFont(font);
        return cellStyle2;
    }


    public String getForeStr(String foreNum) {
        if (foreNum.length() != 4) {
            switch (foreNum.length()) {
                case 1:
                    foreNum = "000" + foreNum;
                    break;
                case 2:
                    foreNum = "00" + foreNum;
                    break;
                case 3:
                    foreNum = "00" + foreNum;
                    break;
            }
        }
        return foreNum;
    }

    /**
     * 编码从回收箱里获取使用消费|| 回收箱找不到就自动创建
     *
     * @param prefix
     * @param format
     */
    public String customApplicationNo(String prefix, String format) {
        String deptCode = prefix + format;
        // 编码从回收箱里获取使用消费
        if (StringUtils.isEmpty(prefix)) {
            deptCode = "CK" + "XXXX";
        }
        SampleParamRecyclingBin sampleParamRecyclingBin = new SampleParamRecyclingBin();
        sampleParamRecyclingBin.setDeptCode(deptCode);
        sampleParamRecyclingBin.setStateId(1);
        List<SampleParamRecyclingBin> sampleParamRecyclingBins = sampleParamRecyclingBinService.listAll(sampleParamRecyclingBin);
        if (CollectionUtils.isNotEmpty(sampleParamRecyclingBins)) {
            SampleParamRecyclingBin varT = sampleParamRecyclingBins.get(0);
            varT.setStateId(-1);
            // 从回收箱里移除
            sampleParamRecyclingBinService.update(varT);
            return varT.getApplicationNo();
        }
        return createApplicationNo(prefix, format);
    }
}