ld9导出

whyclxw

2022-03-10 9c795fd14effa658af076d38c38973a6f7f8ffef

ld9导出

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

@@ -32,5 +32,10 @@
        Map numMap=service.getLD9AllGroupTimeLong(ld9inf);
        return  new Response().setIII(1,list,numMap,list.size()>0?true:false,"");
    }
    @ApiOperation(tags = "在线监测-历史监控",value = "ld9导入整组放电图片",notes = "Ld9testdataAction_ld9action_filePicUpload:传参BattGroupId,testRecordCount")
    @PostMapping("filePicUpload")
    public Response filePicUpload(@RequestBody Ld9testdataInf ld9inf){
        return service.filePicUpload(ld9inf);
    }

}

 src/main/java/com/whyc/dto/BattTestData.java

@@ -320,6 +320,45 @@
            } 
        return stop_type; 
    }

    //4019(LD9)放电终止原因
    public static String getTestType_4019(int testType)
    {
        String test_Type = "未知";
        switch(testType)
        {
            case 1 : test_Type = "核容放电"; break;
            case 2 : test_Type = "核容充电"; break;
            case 3 : test_Type = "监测放电"; break;
            case 4 : test_Type = "监测充电"; break;
        }
        return test_Type;
    }
    //4019(LD9)放电终止原因
    public static String getStopType_4019(int stop_type_t)
    {
        String stop_type = "未知";
        switch(stop_type_t)
        {
            case 0 : stop_type = "无"; break;
            case 1 : stop_type = "手动终止"; break;
            case 2 : stop_type = "单体下限到"; break;
            case 3 : stop_type = "充电完成停止"; break;
            case 4 : stop_type = "测试完成停止"; break;
            case 5 : stop_type = "远程停止"; break;
            case 6 : stop_type = "在线电压低"; break;
            case 7 : stop_type = "温度异常高"; break;
            case 8 : stop_type = "电流异常"; break;
            case 9 : stop_type = "系统正在工作"; break;
            case 10 : stop_type = "正在均衡测试"; break;
            case 11 : stop_type = "正在内阻测试"; break;
            case 12 : stop_type = "汇集器通信异常"; break;
            case 13 : stop_type = "功率电缆异常"; break;
            case 14 : stop_type = "主机停止（ld-18）"; break;
            case 15 : stop_type = "从机通讯异常(ld-18)"; break;
        }
        return stop_type;
    }
    // 逆变设备放电终止原因
    public static String getStopType_9120(int stop_type_t)
    {

 src/main/java/com/whyc/mapper/Ld9testdatastopMapper.java

@@ -1,5 +1,6 @@
package com.whyc.mapper;

import com.whyc.pojo.Ld9testdataInf;
import com.whyc.pojo.Ld9testdatastop;

import java.util.List;
@@ -9,4 +10,7 @@
    List<Ld9testdatastop> serchByInfo(int testRecordCount, int battGroupId, int testMonNum);
    //查询LD9一次测试中所有单体的实际容量
    List<Ld9testdatastop> serchByCondition(int testRecordCount, int battGroupId);

    //查询存放ld9stop在testrecordcount下有哪些单体存在放电记录
    List<Ld9testdatastop> searchlinftestdata(Ld9testdataInf linf);
}

 src/main/java/com/whyc/pojo/Ld9testdataInf.java

@@ -11,6 +11,7 @@

import java.io.Serializable;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Scanner;

@@ -80,4 +81,7 @@
    @TableField(exist = false)
    private List Ld9testdata;

    @TableField(exist = false)
    private HashMap<String,String> mapPic;

}

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

@@ -11,14 +11,14 @@
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.system.ApplicationHome;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Service;
import sun.misc.BASE64Decoder;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.io.UnsupportedEncodingException;
import java.io.*;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
@@ -40,6 +40,12 @@
    @Autowired
    private Ld9testdataService ld9service; //ld9的service

    @Autowired
    private Ld9testdatastopService stopservice; //ld9stop的service

    @Autowired
    private Environment environment;

    //文件导出
    public void exportExcel( HttpServletRequest req, HttpServletResponse resp) {
        String pageName = req.getParameter("pageName");
@@ -51,7 +57,8 @@
            }else if("exportTbal".equalsIgnoreCase(pageName)){
                ExprotReport(req,resp);                        //导出BTS常规报表
            }else if("charge-test-monData".equalsIgnoreCase(pageName)){
                ExprotLD9MonTestData(req,resp);                //导出LD-9度历史数据
                //ExprotLD9MonTestData(req,resp);                //导出LD-9度历史数据
                ExprotLD9Group(req,resp);
            }else if("exportCTExcel".equalsIgnoreCase(pageName)){
                ExprotCTReport(req,resp);                    //导出CT机类似报表
            }else if("ePrice".equalsIgnoreCase(pageName)){
@@ -218,6 +225,169 @@
            e.printStackTrace();
        }
    }

    /**
     * 历史放电数据页面中导出LD9设备的整组放电数据(最新版)
     * @param req
     * @param resp
     */
    private void ExprotLD9Group(HttpServletRequest req,HttpServletResponse resp) {
        String battinf = req.getParameter("battinf");                    //电池组信息
        String testdatainfo = req.getParameter("testdatainfo");            //单体测试数据
        Title echarttitle = ActionUtil.getGson().fromJson(req.getParameter("obj-title"), Title.class);  //最大最小信息
        String[][] datas = ActionUtil.getGson().fromJson(req.getParameter("arr-data"), String[][].class);//二维数据信息
        //电池组信息
        Battinf binf = ActionUtil.getGson().fromJson(battinf, Battinf.class);
        Ld9testdataInf linf = ActionUtil.getGson().fromJson(testdatainfo, Ld9testdataInf.class);
        try {
            // 创建一个工作薄
            HSSFWorkbook wb = new HSSFWorkbook();
            //LD9放电整组信息
            createLd9GroupInfo(wb,echarttitle,datas);
            //ld9整组放电数据
            createLD9AllGroup(wb,linf);
            //查询存放ld9在testrecordcount下有哪些单体存在放电记录
            List<Ld9testdatastop> list=stopservice.searchlinftestdata(linf);
            if(list!=null&&list.size()>0){
                for(int i=0;i<list.size();i++){
                    Ld9testdatastop ld9stop=list.get(i);
                    createGroupSheet(wb,binf,ld9stop);//创建每个单体的sheet
                }
            }
            resp.addHeader("Content-Disposition", "attachment;filename="
                    + new String(excelName.getBytes("gb2312"), "ISO8859-1")
                    + ".xls");
            OutputStream out = resp.getOutputStream();
            wb.write(out);
            out.close();
        } catch (UnsupportedEncodingException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //LD9放电整组信息
    private void createLd9GroupInfo(HSSFWorkbook wb, Title title, String[][] datas) {
        HSSFSheet sheet1 = wb.createSheet("数据总览");
        int rownum = 1;
        sheet1.createRow(rownum);

        sheet1.getRow(rownum).createCell(1).setCellValue("最高单体电压");
        sheet1.getRow(rownum).createCell(2).setCellValue("最低单体电压");
        sheet1.getRow(rownum).createCell(3).setCellValue("平均单体电压");

        rownum++;

        sheet1.createRow(rownum);
        sheet1.getRow(rownum).createCell(1).setCellValue(title.getMax());
        sheet1.getRow(rownum).createCell(2).setCellValue(title.getMin());
        sheet1.getRow(rownum).createCell(3).setCellValue(title.getAvg());

        rownum+=2;


        String[] arrTh = new String[]{
                "单体编号","起始单体电压(V)","截止单体电压(V)","测试容量(AH)","单体内阻(mΩ)","起始单体温度(℃)","终止单体温度(℃)"
        };
        sheet1.createRow(rownum);
        for(int i = 0 ;i<arrTh.length;i++){
            sheet1.getRow(rownum).createCell(i+1).setCellValue(arrTh[i]);
        }
        rownum++;

        if(datas!=null && datas.length>0){
            for(int i=0;i<datas.length;i++){
                sheet1.createRow(rownum);
                for(int j=0;j<datas[i].length;j++){
                    sheet1.getRow(rownum).createCell(j+1).setCellValue(datas[i][j]);
                }
                rownum++;
            }
        }
    }
    //生成每个单体放电单体表
    private void createGroupSheet(HSSFWorkbook wb,Battinf binf,Ld9testdatastop ld9stop) {
        List<byte[]> listbyte=new ArrayList<byte[]>();//读取图片
        //-----------------------读取4张图：单体电压折线图，组端/在线电压折线图，测试电流折线图，单体实际容量柱状图
        String fileDirName = "";
        int configType = Integer.parseInt(environment.getProperty("configFile.type"));
        ApplicationHome applicationHome = new ApplicationHome(getClass());
        File jarFile = applicationHome.getDir();
        //测试版
        if(configType==1){
            fileDirName = jarFile.getParentFile().toString();
        }else{
            //打包版
            fileDirName = jarFile.toString();
        }
        String picroot=fileDirName+ File.separator+"ld9"+File.separator+ld9stop.getBattGroupId()+File.separator+ld9stop.getTestRecordCount()+File.separator+ld9stop.getMonNum()+File.separator;
        File f = new File(picroot);
        List<String> picName = new ArrayList();
        if(f.exists()){
            File[] files=f.listFiles();
            for(int i=0;i<files.length;i++){
                picName.add(files[i].getName().substring(0,files[i].getName().lastIndexOf(".")));//存放图片的名称
                InputStream in = null;
                byte[] data = null;
                // 读取图片字节数组
                try {
                    in = new FileInputStream(picroot+files[i].getName());
                    data = new byte[in.available()];
                    in.read(data);
                    in.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
                listbyte.add(data);
            }
        }
        HSSFSheet sheet1 = wb.createSheet(ld9stop.getMonNum()+"#历史放电信息表");
        HSSFPatriarch patriarch = sheet1.createDrawingPatriarch();
        int rownum = 1;

        sheet1.createRow(rownum);
        sheet1.getRow(rownum).createCell(1).setCellValue("电池组名称");
        sheet1.getRow(rownum).createCell(2).setCellValue(binf.getStationName()+"-"+binf.getBattGroupName());
        rownum++;

        sheet1.createRow(rownum);
        sheet1.getRow(rownum).createCell(1).setCellValue( "电池品牌："+binf.getBattProducer());
        sheet1.getRow(rownum).createCell(2).setCellValue( "安装日期："+formartDate(binf.getBattInUseDate(),"yyyy-MM-dd"));
        sheet1.getRow(rownum).createCell(3).setCellValue( "单体数量："+binf.getMonCount());
        sheet1.getRow(rownum).createCell(4).setCellValue( "单体电压："+binf.getMonVolStd()+"V");
        sheet1.getRow(rownum).createCell(5).setCellValue( "单体标称容量："+formartDouble(binf.getMonCapStd(),0)+"AH");
        sheet1.getRow(rownum).createCell(6).setCellValue( "单体标称内阻："+formartDouble(binf.getMonResStd(),3)+"mΩ");
        sheet1.getRow(rownum).createCell(7).setCellValue( "单体标称电导："+formartDouble(binf.getMonSerStd(),0));

        rownum++;
        sheet1.createRow(rownum);
        sheet1.getRow(rownum).createCell(1).setCellValue( "被测单体编号：#"+ld9stop.getMonNum());
        sheet1.getRow(rownum).createCell(2).setCellValue( "测试类型："+BattTestData.getTestType_4019(ld9stop.getTestType()));
        sheet1.getRow(rownum).createCell(3).setCellValue( "测试时间："+formartDate(ld9stop.getTestStarttime(),"yyyy-MM-dd HH:mm:ss"));
        sheet1.getRow(rownum).createCell(4).setCellValue( "测试电流："+ld9stop.getTestCurr()+"A");
        sheet1.getRow(rownum).createCell(5).setCellValue( "测试时长："+formatTestLong(ld9stop.getTestTimelong()));
        sheet1.getRow(rownum).createCell(6).setCellValue( "测试容量："+formartDouble(ld9stop.getTestCap(),0)+"AH");
        sheet1.getRow(rownum).createCell(7).setCellValue( "终止原因："+BattTestData.getStopType_4019(ld9stop.getTestStopreason()));

        rownum++;
        sheet1.createRow(rownum);
        sheet1.getRow(rownum).createCell(1).setCellValue( "报告日期：");
        sheet1.getRow(rownum).createCell(2).setCellValue(formartDate(new Date(),"yyyy-MM-dd HH:mm:ss"));
        rownum+=2;

        //画图
        if(listbyte!=null&&listbyte.size()>0){
            for(int i=0;i<listbyte.size();i++){
                sheet1.createRow(rownum);
                sheet1.getRow(rownum).createCell(1).setCellValue(picName.get(i));
                rownum++;
                HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 512, 255,(short) 1, rownum, (short) 10, 25 * (i + 1));
                anchor.setAnchorType(3);
                patriarch.createPicture(anchor,wb.addPicture(listbyte.get(i),HSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
                rownum+=25;
            }
        }
    }
    /**
     * 导出报表
     * @param req

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

@@ -7,10 +7,20 @@
import com.whyc.mapper.Ld9testdataMapper;
import com.whyc.pojo.Ld9testdata;
import com.whyc.pojo.Ld9testdataInf;
import com.whyc.util.ActionUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.system.ApplicationHome;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Service;
import org.springframework.web.bind.annotation.RequestParam;
import sun.misc.BASE64Decoder;

import javax.annotation.Resource;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLDecoder;
import java.util.List;
import java.util.Map;

@@ -18,6 +28,9 @@
public class Ld9testdataService {
    @Resource
    private Ld9testdataMapper mapper;

    @Autowired
    private Environment environment;

    //查询LD9单体测试数据
    public Response serchByCondition(int recordNum,  int testRecordCount, int battGroupId,int testMonNum) {
@@ -47,4 +60,82 @@
        Map numMap=mapper.getLD9AllGroupTimeLong(ld9inf);
        return numMap;
    }
    //ld9导入整组放电图片
    public Response filePicUpload(Ld9testdataInf linf) {
        String fileDirName = "";
        int configType = Integer.parseInt(environment.getProperty("configFile.type"));
        ApplicationHome applicationHome = new ApplicationHome(getClass());
        File jarFile = applicationHome.getDir();
        //测试版
        if(configType==1){
            fileDirName = jarFile.getParentFile().toString();
        }else{
            //打包版
            fileDirName = jarFile.toString();
        }
        String picroot=fileDirName+File.separator+"ld9"+File.separator+linf.getBattGroupId()+File.separator+linf.getTestRecordCount()+File.separator+linf.getMonNum()+File.separator;
        boolean b=createFile(picroot);//创建文件路径
        boolean bl=false;
        String msg="";
        if(b){
           msg="图片存在,图片不用上传!";
        }else {
            bl=GenerateImage(linf.getMapPic(),picroot);
            if(bl){
                msg="图片不存在,图片重新上传成功!";
            }else{
                msg="导入失败!";
            }
        }
        return new Response().setII(1,b,bl,msg);
    }
    //判断路径是否存在，不存在则创建。存在就判断是否有四张图
    public boolean createFile(String pathName) {
        File f = new File(pathName);
        boolean bl=false;
        if(!f.exists()){
            f.mkdirs();
        }/*else{
            File[] files=f.listFiles();
            if(files.length>=4){
                bl=true;
            }
        }*/
        return bl;
    }

    // 对字节数组字符串进行Base64解码并生成图片
    public  boolean GenerateImage(Map<String,String> map_pic, String root)  {
        // 图像数据为空
        if (map_pic == null|| map_pic.size() == 0){
            return false;
        }
        BASE64Decoder decoder = new BASE64Decoder();
        map_pic.forEach((key,value)->{
            OutputStream out = null;
            try {
                String imgFilePath=root+File.separator+key;
                out = new FileOutputStream(imgFilePath);
                // Base64解码
                byte[] b = decoder.decodeBuffer(URLDecoder.decode(value, "utf-8"));
                for (int i = 0; i < b.length; ++i) {
                    if (b[i] < 0) {// 调整异常数据
                        b[i] += 256;
                    }
                }
                out.write(b);
            } catch (IOException e) {
                e.printStackTrace();
            } finally {
                try {
                    out.flush();
                    out.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }

            }
        });
        return true;
    }
}

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

@@ -3,6 +3,7 @@
import com.github.pagehelper.PageInfo;
import com.whyc.dto.Response;
import com.whyc.mapper.Ld9testdatastopMapper;
import com.whyc.pojo.Ld9testdataInf;
import com.whyc.pojo.Ld9testdatastop;
import org.springframework.stereotype.Service;

@@ -26,4 +27,9 @@
        PageInfo pageInfo=new PageInfo(list);
        return new Response().set(1,pageInfo);
    }
    //查询存放ld9stop在testrecordcount下有哪些单体存在放电记录
    public List<Ld9testdatastop> searchlinftestdata(Ld9testdataInf linf) {
        List<Ld9testdatastop> list=mapper.searchlinftestdata(linf);
        return list;
    }
}

 src/main/resources/mapper/Ld9testdatastopMapper.xml

@@ -13,4 +13,9 @@
        from db_ld9_testdata.tb_ld9testdatastop_${battGroupId}
        where test_record_count=#{testRecordCount}
    </select>
    <select id="searchlinftestdata" resultType="com.whyc.pojo.Ld9testdatastop">
        select *
        from db_ld9_testdata.tb_ld9testdatastop_${battGroupId}
        where test_record_count=#{testRecordCount}
    </select>
</mapper>