blame | 历史 | 补丁 | 提交 | 提交对比 | ignore whitespace
fuliqi
2024-11-19 3380b0f6767308fa91bd55d68a96a48be5029aa7 
 ycl-server/src/main/java/com/ycl/handler/CustomCellWriteHandler.java


@@ -14,6 +14,7 @@


import org.apache.poi.ss.util.CellRangeAddressList;


import org.apache.poi.ss.util.CellReference;


import org.apache.poi.xssf.usermodel.XSSFDataValidation;


import org.springframework.util.CollectionUtils;




import java.util.ArrayList;


import java.util.Arrays;


@@ -29,18 +30,17 @@


     * 所以能做的自定义操作很丰富


     */


    // static class CustomCellWriteHandler {




        @Override


        public void afterCellDispose(CellWriteHandlerContext context) {


            // 当前的 cell


            Cell cell = context.getCell();


            // 这里可以对cell进行任何操作


            // 这里就要考虑 你要针对哪一列进行个性化处理  一定要记得加判断  因为每个 cell 都会执行到这里 所以要记得区分


            if (BooleanUtils.isTrue(context.getHead()) && cell.getColumnIndex() == 1) {


                // 1 表示 省那一列 要对省市区进行联动下拉处理


                ExcelAreaUtil.writeAreaInfo(context, 0);


            }


    @Override


    public void afterCellDispose(CellWriteHandlerContext context) {


        // 当前的 cell


        Cell cell = context.getCell();


        // 这里可以对cell进行任何操作


        // 这里就要考虑 你要针对哪一列进行个性化处理  一定要记得加判断  因为每个 cell 都会执行到这里 所以要记得区分


        if (BooleanUtils.isTrue(context.getHead()) && cell.getColumnIndex() == 1) {


            // 1 表示 省那一列 要对省市区进行联动下拉处理


            ExcelAreaUtil.writeAreaInfo(context, 0);


        }


    }


    // }




    public static class ExcelAreaUtil {


@@ -76,7 +76,7 @@


            // 准备点数据


            List<AreaInfo> provinceList = new ArrayList<>();


            for (ContractRule contractRule : ContractRule.getParent()) {


                provinceList.add(new AreaInfo(contractRule.getName(), ContractRule.getChildren(contractRule).stream().map(ContractRule::getName).toList()));


                provinceList.add(new AreaInfo(contractRule.getName(), CollectionUtils.isEmpty(ContractRule.getChildren(contractRule)) ? null : ContractRule.getChildren(contractRule).stream().map(ContractRule::getName).toList()));


            }




            // 获取到当前的 excel 因为要创建隐藏的 sheet 也就是省市区的实际内容都来自于隐藏的 sheet


@@ -96,23 +96,24 @@


            for (AreaInfo pro : provinceList) {


                String pName = pro.getName();


                List<String> cList = pro.getAreaList();


                Row cRow = hideSheet.createRow(rowId++);


                cRow.createCell(0).setCellValue(pName);


                for (int j = 0; j < cList.size(); j++) {


                    String cInfo = cList.get(j);


                    Cell cCell = cRow.createCell(j + 1);


                    cCell.setCellValue(cInfo);


                if (!CollectionUtils.isEmpty(cList)) {


                    Row cRow = hideSheet.createRow(rowId++);


                    cRow.createCell(0).setCellValue(pName);


                    for (int j = 0; j < cList.size(); j++) {


                        String cInfo = cList.get(j);


                        Cell cCell = cRow.createCell(j + 1);


                        cCell.setCellValue(cInfo);


                    }




                    // 添加名称管理器


                    String range = getRange(1, rowId, cList.size());


                    Name name = workbook.createName();


                    // key不可重复


                    name.setNameName(pName);


                    String formula = "area!" + range;


                    name.setRefersToFormula(formula);


                }




                // 添加名称管理器


                String range = getRange(1, rowId, cList.size());


                Name name = workbook.createName();


                // key不可重复


                name.setNameName(pName);


                String formula = "area!" + range;


                name.setRefersToFormula(formula);


            }




            // 给省添加下拉


            int lastRow = 100;


            setDataValidation(sheet, 1, lastRow, startIndex, startIndex);


@@ -134,7 +135,7 @@


            CellRangeAddressList provRangeAddressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);


            DataValidation provinceDataValidation = dvHelper.createValidation(provConstraint, provRangeAddressList);


            // 验证


            provinceDataValidation.createErrorBox("error", "请选择正确的省份");


            provinceDataValidation.createErrorBox("error", "请选择正确的规则");


            provinceDataValidation.setShowErrorBox(true);


            provinceDataValidation.setSuppressDropDownArrow(true);


            sheetPro.addValidationData(provinceDataValidation);


@@ -196,63 +197,41 @@




    private static List<CalculateRule> getExcelData() {


        ArrayList<CalculateRule> list = new ArrayList<>();


        CalculateRule calculateRule1 = new CalculateRule();


        calculateRule1.setId(1);


        calculateRule1.setRuleName("视频平均在线率");


        calculateRule1.setRuleCondition("≥98%");


        calculateRule1.setMax(98D);


        calculateRule1.setDeductCategory(RuleDeductCategoryEnum.DEDUCT_POINTS);


        calculateRule1.setCalcFraction(0.00);


        list.add(calculateRule1);


        CalculateRule calculateRule2 = new CalculateRule();


        calculateRule2.setRuleName("视频平均在线率");


        calculateRule2.setRuleCondition("95%≤视频平均在线率＜98%");


        calculateRule2.setMax(97D);


        calculateRule2.setMin(95D);


        calculateRule2.setDeductCategory(RuleDeductCategoryEnum.DEDUCT_POINTS);


        calculateRule2.setCalcFraction(5.00);


        list.add(calculateRule2);


        CalculateRule calculateRule3 = new CalculateRule();


        calculateRule3.setRuleName("视频平均在线率");


        calculateRule3.setRuleCondition("90%≤视频平均在线率＜95%");


        calculateRule3.setMax(94D);


        calculateRule3.setMin(90D);


        calculateRule3.setDeductCategory(RuleDeductCategoryEnum.DEDUCT_POINTS);


        calculateRule3.setCalcFraction(10.00);


        list.add(calculateRule3);


        CalculateRule calculateRule33 = new CalculateRule();


        calculateRule33.setRuleName("视频平均在线率");


        calculateRule33.setRuleCondition("＜90%");


        calculateRule33.setMin(89D);


        calculateRule33.setDeductCategory(RuleDeductCategoryEnum.DEDUCT_POINTS);


        calculateRule33.setCalcFraction(10.00);


        list.add(calculateRule33);


        CalculateRule calculateRule4 = new CalculateRule();


        calculateRule4.setId(2);


        calculateRule4.setRuleName("前端感知源治理工作");


        calculateRule4.setRuleCondition("时钟同步（超过±3秒为不合格）");


        calculateRule4.setDeductCategory(RuleDeductCategoryEnum.MULTIPLY_POINTS_BY_QUANTITY);


        calculateRule4.setCalcFraction(0.1);


        list.add(calculateRule4);


        CalculateRule calculateRule7 = new CalculateRule();


        calculateRule7.setRuleName("前端感知源治理工作");


        calculateRule7.setRuleCondition("OSD标识");


        list.add(calculateRule7);


        CalculateRule calculateRule6 = new CalculateRule();


        calculateRule6.setRuleName("前端感知源治理工作");


        calculateRule6.setRuleCondition("一机一档");


        list.add(calculateRule6);


        CalculateRule calculateRule5 = new CalculateRule();


        calculateRule5.setId(3);


        calculateRule5.setRuleName("后台系统的保障");


        calculateRule5.setRuleCondition("单次故障时长若超出72小时不足144小时的，每超出12小时（不足12小时按12小时计）");


        calculateRule5.setMax(144D);


        calculateRule5.setMin(72D);


        calculateRule5.setDeductCategory(RuleDeductCategoryEnum.MULTIPLY_POINTS_AFTER_DIVIDING_QUANTITY);


        calculateRule5.setCalcFraction(2.00);


        calculateRule5.setCalcUnit(12);


        list.add(calculateRule5);




        setTemplateRule(list,1,"设备平均在线率","≥98%",null,98D,RuleDeductCategoryEnum.DEDUCT_POINTS,0.00);


        setTemplateRule(list,null,"设备平均在线率","95%≤设备平均在线率＜98%",97D,95D,RuleDeductCategoryEnum.DEDUCT_POINTS,5.00);


        setTemplateRule(list,null,"设备平均在线率","90%≤设备平均在线率＜95%",94D,90D,RuleDeductCategoryEnum.DEDUCT_POINTS,10.00);


        setTemplateRule(list,null,"设备平均在线率","＜90%",89D,null,RuleDeductCategoryEnum.DEDUCT_POINTS,20.00);




        setTemplateRule(list,2,"前端感知源治理工作","时钟同步（超过±3秒为不合格）",null,null,RuleDeductCategoryEnum.MULTIPLY_POINTS_BY_QUANTITY,0.1);


        setTemplateRule(list,null,"前端感知源治理工作","OSD标识",null,null,RuleDeductCategoryEnum.MULTIPLY_POINTS_BY_QUANTITY,0.1);


        setTemplateRule(list,null,"前端感知源治理工作","一机一档",null,null,RuleDeductCategoryEnum.MULTIPLY_POINTS_BY_QUANTITY,0.1);




        setTemplateRule(list,3,"对于前端点位异常情况的处理","镜头故障或污染或树枝遮挡或枪机视角偏移正常角度或补光灯应亮未亮,24小时后未修复的",null,24D,RuleDeductCategoryEnum.MULTIPLY_POINTS_BY_QUANTITY,0.5);


        setTemplateRule(list,null,"对于前端点位异常情况的处理","镜头故障或污染或树枝遮挡或枪机视角偏移正常角度或补光灯应亮未亮,48小时后未修复的",null,48D,RuleDeductCategoryEnum.MULTIPLY_POINTS_BY_QUANTITY,1.0);




        setTemplateRule(list,4,"确保录像完整不定期对所有点位录像完整性抽查","每路视频累计丢失10分钟以内",10D,null,RuleDeductCategoryEnum.MULTIPLY_POINTS_BY_QUANTITY,0.2);


        setTemplateRule(list,null,"确保录像完整不定期对所有点位录像完整性抽查","丢失10-60 分钟",10D,60D,RuleDeductCategoryEnum.MULTIPLY_POINTS_BY_QUANTITY,0.5);


        setTemplateRule(list,null,"确保录像完整不定期对所有点位录像完整性抽查","丢失1 小时-4 小时（含）",60D,240D,RuleDeductCategoryEnum.MULTIPLY_POINTS_BY_QUANTITY,1.0);


        setTemplateRule(list,null,"确保录像完整不定期对所有点位录像完整性抽查","丢失4 小时-12 小时（含）",240D,720D,RuleDeductCategoryEnum.MULTIPLY_POINTS_BY_QUANTITY,1.5);


        setTemplateRule(list,null,"确保录像完整不定期对所有点位录像完整性抽查","丢失12 小时以上",null,720D,RuleDeductCategoryEnum.MULTIPLY_POINTS_BY_QUANTITY,2.0);




        setTemplateRule(list,5,"确保图片完整不定期对所有人脸车辆以及智能前端抓拍的图片完整性抽查","发现后台存储不能调取前端设备图片",null,null,RuleDeductCategoryEnum.MULTIPLY_POINTS_BY_QUANTITY,2.0);






        return list;


    }




}


    private static void setTemplateRule(ArrayList<CalculateRule> list,Integer id,String ruleName,String condition,Double max,Double min,RuleDeductCategoryEnum deductCategoryEnum,Double calcFraction) {


        CalculateRule calculateRule = new CalculateRule();


        calculateRule.setId(id);


        calculateRule.setRuleName(ruleName);


        calculateRule.setRuleCondition(condition);


        calculateRule.setMax(max);


        calculateRule.setMin(min);


        calculateRule.setDeductCategory(deductCategoryEnum);


        calculateRule.setCalcFraction(calcFraction);


        list.add(calculateRule);


    }




}