更新导出模板

whycxzp

2022-11-14 bcc957336749bde56862b59f84e7f17591e2a8ab

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

@@ -1,23 +1,32 @@
package com.whyc.service;

import cn.afterturn.easypoi.entity.ImageEntity;
import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import com.google.common.collect.Maps;
import com.whyc.dto.ResTestReportDTO;
import com.whyc.dto.BattGroupStationInfoWithFileParam;
import com.whyc.dto.Response;
import com.whyc.pojo.BattgroupData;
import com.whyc.pojo.FileParam;
import com.whyc.pojo.StationInfo;
import com.whyc.util.MathUtil;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.io.ClassPathResource;
import org.springframework.stereotype.Service;
import sun.misc.BASE64Decoder;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.net.URLEncoder;
import java.text.NumberFormat;
import java.text.ParseException;
import java.text.ParsePosition;
import java.text.SimpleDateFormat;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.*;
import java.util.stream.Collectors;

@Service
public class ExcelExportService {
@@ -28,27 +37,198 @@
    @Autowired
    private BattGroupDataService battGroupDataService;

    public void resTestReport(String fileId, HttpServletResponse response) {
        HashMap<String, Object> map = Maps.newHashMap();
        List list = new LinkedList<>();
        ResTestReportDTO dto = new ResTestReportDTO();
        dto.setP1("excel模板");
        list.add(dto);
        //基础测试数据
        //battGroupDataService.getListWithTestTime(fileId);
        //文件所属的电池组信息
        FileParam fileParam = fileParamService.getByFileId(fileId);
    @Autowired
    private StationInfoService stationInfoService;

        map.put("dto", list);
    public void resTestReport(BattGroupStationInfoWithFileParam info, HttpServletResponse response) throws IOException {
        HashMap<String, Object> map = Maps.newHashMap();
        //总体评价:内阻/电压/连接条/?容量
        int resGoodCount = 0;
        int volGoodCount = 0;
        int chainResGoodCount = 0;
        FileParam fileParam = info.getFileParam();
        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

        fileParam.setTestTimeStr(format.format(fileParam.getTestTime()));
        List<BattgroupData> dataList = info.getDataList();
        for(BattgroupData data : dataList){
            data.setMonNumStr("#"+data.getMonNum());
            if(!data.isHighResFlag1() && !data.isHighResFlag2()){
                resGoodCount +=1;
                data.setResEvaluation("优");
            }else if(data.isHighResFlag1()){
                data.setResEvaluation("差");
            }else{
                data.setResEvaluation("很差");
            }
            if(!data.isLowVolFlag() && !data.isHighVolFlag()){
                volGoodCount +=1;
            }
            if(!data.isHighChainResFlag()){
                chainResGoodCount +=1;
            }
        };
        info.setResGoodCount(resGoodCount);
        info.setVolGoodCount(volGoodCount);
        info.setChainResGoodCount(chainResGoodCount);

        StationInfo stationInfo = info.getStationInfo();
        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);
        }

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

        //获取导出模板地址
        ClassPathResource classPathResource = new ClassPathResource("excel_templates/rest_test_report_single_template.xls");
        ClassPathResource classPathResource = new ClassPathResource("excel_templates/res_test_report_single_template.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 = "内阻计测试"+time+".xls";
        try {
            response.setContentType("application/octet-stream;charset=utf-8");
            //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 {
        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();
        //变化率
        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("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;
        for(BattgroupData data : dataList2){
            data.setMonNumStr("#"+data.getMonNum());
            if(!data.isHighResFlag1() && !data.isHighResFlag2()){
                resGoodCount +=1;
                data.setResEvaluation("优");
            }else if(data.isHighResFlag1()){
                data.setResEvaluation("差");
            }else{
                data.setResEvaluation("很差");
            }
            if(!data.isLowVolFlag() && !data.isHighVolFlag()){
                volGoodCount +=1;
            }
            if(!data.isHighChainResFlag()){
                chainResGoodCount +=1;
            }
        };
        map.put("resGoodCount",resGoodCount);
        map.put("volGoodCount",volGoodCount);
        map.put("chainResGoodCount",chainResGoodCount);

        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 = new ClassPathResource("excel_templates/res_test_report_compare_template.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 = "内阻计测试"+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());