2025-3-22定制修改导出去掉容量和连接条

whyclxw

2025-03-22 a09523e8eeb5ae9fd7998feaa2a8886c369066a2

2025-3-22定制修改导出去掉容量和连接条

 src/main/java/com/whyc/controller/ExcelExportController.java

@@ -30,4 +30,16 @@
        service.resTestCompareReport(fileId,fileId2,dto,response);
    }

    @ApiOperation("内阻计测试-单数据分析导出(定制去除容量和连接条)")
    @PostMapping("resTestReport_cg")
    public void resTestReport_cg(@RequestBody BattGroupStationInfoWithFileParam info, HttpServletResponse response) throws IOException {
        service.resTestReport_cg(info,response);
    }

    @ApiOperation("内阻计测试-对比分析导出(定制去除容量和连接条)")
    @PostMapping("resTestCompareReport_cg")
    public void resTestCompareReport_cg(@RequestParam String fileId,@RequestParam String fileId2,@RequestBody BattGroupStationInfoWithFileParam dto, HttpServletResponse response) throws IOException {
        service.resTestCompareReport_cg(fileId,fileId2,dto,response);
    }

}

 src/main/java/com/whyc/service/ExcelExportService.java

@@ -252,6 +252,210 @@
        }
    }

    public void resTestReport_cg(BattGroupStationInfoWithFileParam info, HttpServletResponse response) throws IOException {
        Locale local = i18nLocaleResolver.getLocal();
        boolean zhFlag = local.getLanguage().toLowerCase().equals("zh");
        HashMap<String, Object> map = Maps.newHashMap();
        //总体评价:内阻/电压/连接条/?容量
        int resGoodCount = 0;
        int volGoodCount = 0;
        int chainResGoodCount = 0;
        //总体评价:有效的电阻/电压/连接条 数
        int battVolTestCount = 0;
        int battResTestCount = 0;
        int battChainResTestCount = 0;
        FileParam param = info.getFileParam();
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        param.setTestTimeStr(format.format(param.getTestTime()));
        //查询全局的测试系数和参数
        TestParam param2 = testParamService.getFactorsAndThreshold();
        //判断测试数据告警范围
        Float stdRes = param.getBattRes();
        Float volLowCoeK1 = null;
        Float volHighCoeK2 = null;
        //电压不同,电压阈值不同
        if(param.getBattVol() == 2){
            volLowCoeK1 = param2.getVol2LowCoeK1();
            volHighCoeK2 = param2.getVol2HighCoeK2();
        }
        else if(param.getBattVol() == 1.2){
            volLowCoeK1 = param2.getVol1d2LowCoeK1();
            volHighCoeK2 = param2.getVol1d2HighCoeK2();
        }
        else if(param.getBattVol() == 6){
            volLowCoeK1 = param2.getVol6LowCoeK1();
            volHighCoeK2 = param2.getVol6HighCoeK2();
        }
        else if(param.getBattVol() == 12){
            volLowCoeK1 = param2.getVol12LowCoeK1();
            volHighCoeK2 = param2.getVol12HighCoeK2();
        }else{ //电压非常规值,则默认设置为2v的电压阈值
            volLowCoeK1 = param2.getVol2LowCoeK1();
            volHighCoeK2 = param2.getVol2HighCoeK2();
        }
        Float resGoodCoeK3 = param2.getResGoodCoeK3();
        Float resBadCoeK4 = param2.getResBadCoeK4();
        Float chainRes = param.getVersion().toUpperCase().contains("V2.0")?param.getChainRes():param.getConnRes();
        Float finalVolLowCoeK = volLowCoeK1;
        Float finalVolHighCoeK = volHighCoeK2;
        List<BattgroupData> dataList = info.getDataList();
        for(BattgroupData data : dataList){
            //计算电压,内阻,连接条数值是否有效,被测试过
            float bv = Float.parseFloat(data.getBv());
            float br = Float.parseFloat(data.getBr());
            float cr = Float.parseFloat(data.getCr());
            //计算容量百分比,并回填到dataList中
            float preCap = MathUtil.getPreCapTest(br, stdRes);
            String preCapStr = NumberFormat.getPercentInstance().format(preCap);
            data.setPreCapPercentStr(preCapStr);

            if (bv!=0.0){
                battVolTestCount++;
            }
            if(br!=0.0){
                battResTestCount++;
            }
            if (cr!=0.0){
                battChainResTestCount++;
            }

            data.setMonNumStr("#"+data.getMonNum());
            if(br==0.0){
                data.setResEvaluation("—");
            }
            /*else if(!data.isHighResFlag1() && !data.isHighResFlag2()){
                resGoodCount +=1;
                data.setResEvaluation("优");
            }else if(data.isHighResFlag1()){
                data.setResEvaluation("良");
            }else{
                data.setResEvaluation("差");
            }*/
            else if(br >= stdRes*resGoodCoeK3/100){
                if(br >= stdRes*resBadCoeK4/100){
                    // data.setHighResFlag2(true);
                    if(zhFlag) {
                        data.setResEvaluation("差");
                    }else{
                        data.setResEvaluation("bad");
                    }
                }else{
                    // data.setHighResFlag1(true);
                    if(zhFlag) {
                        data.setResEvaluation("良");
                    }else {
                        data.setResEvaluation("good");
                    }
                }
            }else{
                resGoodCount +=1;
                if(zhFlag) {
                    data.setResEvaluation("优");
                }else {
                    data.setResEvaluation("excellent");
                }
            }
            /*if(bv!=0.0 && !data.isLowVolFlag() && !data.isHighVolFlag()){
                volGoodCount +=1;
            }*/
            if(bv!=0.0 && bv > finalVolLowCoeK && bv < finalVolHighCoeK){
                volGoodCount +=1;
            }
            /*if(cr!=0.0 && !data.isHighChainResFlag1() && !data.isHighChainResFlag2()){
                chainResGoodCount +=1;
            }*/
            if(cr!=0.0 && cr < chainRes*param2.getChainResCoeK5()/100){
                chainResGoodCount +=1;
            }
        };
        info.setResGoodCount(resGoodCount);
        info.setVolGoodCount(volGoodCount);
        info.setChainResGoodCount(chainResGoodCount);

        info.setBattVolTestCount(battVolTestCount);
        info.setBattResTestCount(battResTestCount);
        info.setBattChainResTestCount(battChainResTestCount);

        StationInfo stationInfo = info.getStationInfo();
        if(stationInfo!=null) {
            if (stationInfo.getStationName2().equals("-")) {
                stationInfo.setStationName(stationInfo.getStationName1());
            } else {
                stationInfo.setStationName(stationInfo.getStationName1() + "-" + stationInfo.getStationName2());
            }
        }
        map.put("dto", info);
        //图片
        //电压柱状图
        BASE64Decoder decoder = new BASE64Decoder();
        if(!info.getVolPic().equals("")) {
            ImageEntity volImage = new ImageEntity();
            volImage.setData(decoder.decodeBuffer(info.getVolPic().substring(info.getVolPic().indexOf(",") + 1)));
            volImage.setColspan(6);
            volImage.setRowspan(17);

            map.put("volImage",volImage);
        }
        //内阻柱状图
        if(!info.getResPic().equals("")) {
            ImageEntity resImage = new ImageEntity();
            resImage.setData(decoder.decodeBuffer(info.getResPic().substring(info.getResPic().indexOf(",") + 1)));
            resImage.setColspan(6);
            resImage.setRowspan(16);

            map.put("resImage", resImage);
        }
        //电导柱状图
        if(!info.getCondPic().equals("")) {
            ImageEntity condImage = new ImageEntity();
            condImage.setData(decoder.decodeBuffer(info.getCondPic().substring(info.getCondPic().indexOf(",") + 1)));
            condImage.setColspan(6);
            condImage.setRowspan(16);

            map.put("condImage", condImage);
        }
        //链接条柱状图
        if(!info.getChainPic().equals("")) {
            ImageEntity chainImage = new ImageEntity();
            chainImage.setData(decoder.decodeBuffer(info.getChainPic().substring(info.getChainPic().indexOf(",") + 1)));
            chainImage.setColspan(6);
            chainImage.setRowspan(16);

            map.put("chainImage", chainImage);
        }

        //map.put("volImage","volImage");
        //map.put("resImage","resImage");

        //获取导出模板地址
        ClassPathResource classPathResource;
        if(zhFlag){
            classPathResource = new ClassPathResource("excel_templates/res_test_report_single_template_cg.xls");
        }else {
            classPathResource = new ClassPathResource("excel_templates/res_test_report_single_template_en_cg.xls");
        }
        String path = classPathResource.getPath();
        TemplateExportParams templateExportParams1 = new TemplateExportParams(path);
        Workbook wb = ExcelExportUtil.exportExcel(templateExportParams1, map);
        String time = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy_MM_dd_HH_mm_ss"));
        String fileName;
        if(zhFlag) {
            fileName = "内阻计测试" + time + ".xls";
        }else{
            fileName = "Resistance Meter Test Report" + time + ".xls";
        }
        try {
            //response.setContentType("application/octet-stream;charset=utf-8");
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            response.flushBuffer();
            wb.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public void resTestCompareReport(String fileId, String fileId2, BattGroupStationInfoWithFileParam dto, HttpServletResponse response) throws IOException {
        Locale local = i18nLocaleResolver.getLocal();
        boolean zhFlag = local.getLanguage().toLowerCase().equals("zh");
@@ -433,4 +637,189 @@
            e.printStackTrace();
        }
    }


    public void resTestCompareReport_cg(String fileId, String fileId2, BattGroupStationInfoWithFileParam dto, HttpServletResponse response) throws IOException {
        Locale local = i18nLocaleResolver.getLocal();
        boolean zhFlag = local.getLanguage().toLowerCase().equals("zh");
        Map<String,Object> map = new HashMap<>();
        Response comparedList = battGroupDataService.getComparedList(fileId, fileId2);
        List<BattgroupData> dataList = (List<BattgroupData>) comparedList.getData();
        List<BattgroupData> dataList2 = (List<BattgroupData>) comparedList.getData2();
        Map<String,List<String>> data3 = (Map<String, List<String>>) comparedList.getData3();

        int baseSize = dataList.size();
        int size = dataList2.size();
        if(baseSize > size){
            dataList = dataList.subList(0,size);
        }else{
            dataList2 = dataList2.subList(0,baseSize);
        }


        //变化率
        List<String> resChangeRateList = data3.get("resChangeRate");
        List<String> volChangeRateList = data3.get("volChangeRate");
        /*map.put("resChangeRateList",resChangeRateList.subList(0,resChangeRateList.size()-1));
        map.put("volChangeRateList",volChangeRateList.subList(0,volChangeRateList.size()-1));*/

        map.put("resChangeRateList",resChangeRateList);
        map.put("volChangeRateList",volChangeRateList);

        map.put("volBalanceRateChangeRate",volChangeRateList.get(volChangeRateList.size()-1));
        map.put("resBalanceRateChangeRate",resChangeRateList.get(resChangeRateList.size()-1));
        //均一性:res/vol
        List<String> resBalanceRateList = data3.get("resBalanceRate");
        List<String> volBalanceRateList = data3.get("volBalanceRate");
        //均一性:chainRes/?容量/bs(电导)
        NumberFormat percentInstance = NumberFormat.getPercentInstance();
        percentInstance.setMinimumFractionDigits(2);

        List<Float> chainResList = dataList2.stream().map(BattgroupData::getCr).map(Float::parseFloat).collect(Collectors.toList());
        List<Float> bsList = dataList2.stream().map(BattgroupData::getBs).map(Float::parseFloat).collect(Collectors.toList());
        String chainResBalanceRate = percentInstance.format(MathUtil.getStandardDeviation(chainResList));
        String bsBalanceRate = percentInstance.format(MathUtil.getStandardDeviation(bsList));
        //获取FileParam和StationInfo
        StationInfo stationInfo = stationInfoService.getByFileId(fileId2);
        FileParam param = fileParamService.getByFileId(fileId2);
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        param.setTestTimeStr(format.format(param.getTestTime()));

        FileParam paramBase = fileParamService.getByFileId(fileId);
        String testTimeStrBase = format.format(paramBase.getTestTime());
        //图片
        BASE64Decoder decoder = new BASE64Decoder();
        if(!dto.getVolPic().equals("")) {
            ImageEntity volImage = new ImageEntity();
            volImage.setData(decoder.decodeBuffer(dto.getVolPic().substring(dto.getVolPic().indexOf(",") + 1)));
            volImage.setColspan(6);
            volImage.setRowspan(17);

            map.put("volImage",volImage);
        }

        if(!dto.getResPic().equals("")) {
            ImageEntity resImage = new ImageEntity();
            resImage.setData(decoder.decodeBuffer(dto.getResPic().substring(dto.getResPic().indexOf(",") + 1)));
            resImage.setColspan(6);
            resImage.setRowspan(16);

            map.put("resImage",resImage);
        }

        if(stationInfo.getStationName2().equals("-")){
            stationInfo.setStationName(stationInfo.getStationName1());
        }else{
            stationInfo.setStationName(stationInfo.getStationName1()+"-"+stationInfo.getStationName2());
        }

        map.put("fileParam",param);
        map.put("stationInfo",stationInfo);
        //总体评价:内阻/电压/连接条/?容量
        int resGoodCount = 0;
        int volGoodCount = 0;
        int chainResGoodCount = 0;

        //总体评价:有效的电阻/电压/连接条 数
        int battVolTestCount = 0;
        int battResTestCount = 0;
        int battChainResTestCount = 0;

        for(BattgroupData data : dataList2){
            //计算电压,内阻,连接条数值是否有效,被测试过
            float bv = Float.parseFloat(data.getBv());
            float br = Float.parseFloat(data.getBr());
            float cr = Float.parseFloat(data.getCr());
            if (bv!=0.0){
                battVolTestCount++;
            }
            if(br!=0.0){
                battResTestCount++;
            }
            if (cr!=0.0){
                battChainResTestCount++;
            }

            data.setMonNumStr("#"+data.getMonNum());
            if(br==0.0){
                data.setResEvaluation("—");
            }
            else if(!data.isHighResFlag1() && !data.isHighResFlag2() ){
                resGoodCount +=1;
                if(zhFlag) {
                    data.setResEvaluation("优");
                }else {
                    data.setResEvaluation("excellent");
                }
            }else if(data.isHighResFlag1()){
                if(zhFlag) {
                    data.setResEvaluation("良");
                }else{
                    data.setResEvaluation("good");
                }
            }else{
                if(zhFlag) {
                    data.setResEvaluation("差");
                }else{
                    data.setResEvaluation("bad");
                }
            }
            if(bv!=0.0 && !data.isLowVolFlag() && !data.isHighVolFlag()){
                volGoodCount +=1;
            }
            if(cr!=0.0 && !data.isHighChainResFlag1() && !data.isHighChainResFlag2()){
                chainResGoodCount +=1;
            }
        };
        map.put("resGoodCount",resGoodCount);
        map.put("volGoodCount",volGoodCount);
        map.put("chainResGoodCount",chainResGoodCount);

        map.put("battVolTestCount",battVolTestCount);
        map.put("battResTestCount",battResTestCount);
        map.put("battChainResTestCount",battChainResTestCount);

        map.put("dataList",dataList);
        map.put("dataList2",dataList2);

        map.put("stdDevBv",volBalanceRateList.get(1));
        map.put("stdDevBr",resBalanceRateList.get(1));
        map.put("stdDevCr",chainResBalanceRate);
        map.put("stdDevBs",bsBalanceRate);
        map.put("testTimeStrBase",testTimeStrBase);

        map.put("stdDevBvBase",volBalanceRateList.get(0));
        map.put("stdDevBrBase",resBalanceRateList.get(0));


        //获取导出模板地址
        ClassPathResource classPathResource;
        if(zhFlag){
            classPathResource  = new ClassPathResource("excel_templates/res_test_report_compare_template_cg.xls");
        }else{
            classPathResource  = new ClassPathResource("excel_templates/res_test_report_compare_template_en_cg.xls");
        }
        String path = classPathResource.getPath();
        TemplateExportParams templateExportParams1 = new TemplateExportParams(path);
        templateExportParams1.setColForEach(true);
        Workbook wb = ExcelExportUtil.exportExcel(templateExportParams1, map);
        String time = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyy_MM_dd_HH_mm_ss"));
        String fileName;
        if(zhFlag) {
            fileName = "内阻计测试" + time + ".xls";
        }else{
            fileName = "Resistance Meter Test Report" + time + ".xls";
        }
        try {
            //response.setContentType("application/octet-stream;charset=utf-8");
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));
            response.flushBuffer();
            wb.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


}

 src/main/resources/excel_templates/res_test_report_compare_template_cg.xls

Binary files differ

 src/main/resources/excel_templates/res_test_report_compare_template_en_cg.xls

Binary files differ

 src/main/resources/excel_templates/res_test_report_single_template_cg.xls

Binary files differ

 src/main/resources/excel_templates/res_test_report_single_template_en_cg.xls

Binary files differ