文件解析告警数据导出

lxw

2023-05-17 c067f815a7460700334c858d449c0ee197c0d84f

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

@@ -1,7 +1,10 @@
package com.whyc.service;

import com.whyc.charge.Fbs5100DisChargeData;
import com.whyc.charge.Fbs5100FbsData;
import com.whyc.alarm.Fbs5100Alarm;
import com.whyc.alarm.Fbs5100AlarmData;
import com.whyc.alarm.Fbs5100AlarmParam;
import com.whyc.charge.*;
import com.whyc.dto.AlarmFactoryDto;
import com.whyc.dto.ServletUtils;
import org.apache.poi.ss.usermodel.ClientAnchor;
import org.apache.poi.xssf.usermodel.*;
@@ -21,7 +24,7 @@

@Service
public class ExcelExportService {
   /* @Autowired
    @Autowired
    private Fbs5100DisChargeDataService disChargeDataService;

    @Autowired
@@ -31,21 +34,14 @@
    private Fbs5100AlarmDataService alarmDataService;

    //导出放电数据bcp
    public void exportBcp(HttpServletRequest req, HttpServletResponse resp){

        String curr_echart = req.getParameter("curr_echart");      //电池电流折线图
        String groupVol_echart = req.getParameter("groupVol_echart");      //组端电压折线图
        String cap_echart = req.getParameter("cap_echart");         //测试容量折线图
        //String actualCap_echart = req.getParameter("actualCap_echart");         //实际容量折线图
        String vol_echart = req.getParameter("vol_echart");         //单体电压折线图
        String tempEnvi_echart = req.getParameter("tempEnvi_echart");//环境温度折线图
        String humiEnvi_echart = req.getParameter("humiEnvi_echart");//环境湿度折线图

    public void exportBcp(HttpServletRequest req, HttpServletResponse resp) {
        String curr_echart = req.getParameter("curr_echart");        //电池电流折线图
        String groupVol_echart = req.getParameter("groupVol_echart");//组端电压折线图
        String cap_echart = req.getParameter("cap_echart");            //测试容量折线图
        String temp_echart = req.getParameter("temp_echart");//温度折线图

        String filePath = req.getParameter("filePath");
        Fbs5100DisChargeData data=disChargeDataService.readFboFile(filePath);
        //从文件中获取数据
        List<Fbs5100FbsData> list = data.fbsDatas;
        Fbs5100DisChargeData data = disChargeDataService.readFboFile(filePath);
        //图片base64后的数据
        List<byte[]> bytes = new ArrayList<>();
        try {
@@ -61,70 +57,58 @@
                String[] url = cap_echart.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(vol_echart)) {
                String[] url = vol_echart.split(",");
            if (ServletUtils.isNotNull(temp_echart)) {
                String[] url = temp_echart.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(tempEnvi_echart)) {
                String[] url = tempEnvi_echart.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(humiEnvi_echart)) {
                String[] url = humiEnvi_echart.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
        }catch (Exception e){
        } catch (Exception e) {
            e.printStackTrace();
        }
        //当前日期
        String nowFormat = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
        String excelName = "FBX-"+nowFormat;
        String excelName = "bcp-" + nowFormat;

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("数据总表");

        XSSFDrawing patriarch = sheet.createDrawingPatriarch();
        int rowNumSheet = 0;
        FboDataHeadStart start = fboDataInf.fboDataStart;
        FboDataHeadStop stop = fboDataInf.fboDataStop;
        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("电池组名称："+start.battNameStr);
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("电池品牌："+start.battBrandStr);
        rowNumSheet++;
        sheet.createRow(rowNumSheet);
        //从文件中获取数据
        List<Fbs5100FbsData> list = data.fbsDatas;
        Fbs5100BattParam battParam = data.battParam;
        Fbs5100TestParam testParam = data.testParam;
        Fbs5100CapState capState = data.capState;

        sheet.getRow(rowNumSheet).createCell(0).setCellValue("安装日期："+start.batt_date_str);
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("单体数量："+start.batt_mon_num);
        sheet.getRow(rowNumSheet).createCell(2).setCellValue("标称容量："+start.batt_std_cap+"AH");
        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("电池组数：" + battParam.BattGroupCount);
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("每组单体数：" + battParam.EachGroupBattCount);
        sheet.getRow(rowNumSheet).createCell(2).setCellValue("标称单体电压：" + battParam.MonomerVol + "V");

        rowNumSheet++;
        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("测试开始时间："+formartDate(start.testStartTime,"yyyy-MM-dd HH:mm:ss"));
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("测试时长："+stop.TestTimeLong.hms());
        sheet.getRow(rowNumSheet).createCell(2).setCellValue("单体电压："+start.batt_mon_vol+"V");
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("浮充电流阈值：" + battParam.FloatChargeVol + "A");
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("在线电压低阈值：" + battParam.OnlineVolLow + "A");
        sheet.getRow(rowNumSheet).createCell(2).setCellValue("均充电压：" + battParam.JunChargeVol + "A");

        rowNumSheet++;
        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("正极绝缘电阻："+list.get(list.size()-1).PosInsRes+"kΩ");
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("负极绝缘电阻："+list.get(list.size()-1).NegInsRes+"kΩ");
        sheet.getRow(rowNumSheet).createCell(2).setCellValue("标称内阻："+start.batt_std_res+"mΩ");
        if(start.DataType==0xFD){
            rowNumSheet++;
            sheet.createRow(rowNumSheet);
            sheet.getRow(rowNumSheet).createCell(0).setCellValue("预估实际容量："+list.get(list.size()-1).actualCap+"AH");
        }
        rowNumSheet+=4;
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("测试开始时间：" + formartDate(capState.StartDT.time,"yyyy-MM-dd HH:mm:ss"));
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("测试时长：" + capState.Test_Time.hour+":"+capState.Test_Time.minute+":"+capState.Test_Time.second);

        rowNumSheet += 4;
        //插入图片
        int picNum = 0;
        String[] picName = getPicName(start.DataType);
        if (picName.length==bytes.size()){
            for(int i=0;i<picName.length;i++){
                sheet.createRow(rowNumSheet-1);
                sheet.getRow(rowNumSheet-1).createCell(0).setCellValue(picName[i]);
        String[] picName = getPicName("bcp");
        if (picName.length == bytes.size()) {
            for (int i = 0; i < picName.length; i++) {
                sheet.createRow(rowNumSheet - 1);
                sheet.getRow(rowNumSheet - 1).createCell(0).setCellValue(picName[i]);
                //rowNum++;
                XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 512, 255,(short) 0, rowNumSheet, (short) 10, rowNumSheet+27);
                XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 512, 255, (short) 0, rowNumSheet, (short) 10, rowNumSheet + 27);
                anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
                patriarch.createPicture(anchor,wb.addPicture(bytes.get(picNum),XSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
                picNum ++;
                rowNumSheet+=27;
                patriarch.createPicture(anchor, wb.addPicture(bytes.get(picNum), XSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
                picNum++;
                rowNumSheet += 27;
            }
        }

@@ -134,33 +118,31 @@
        sheet1.createRow(rowNum).createCell(0).setCellValue("测试数据");
        rowNum++;
        //属性栏
        String[] rowName = getRowName(start.DataType);
        String[] rowName = getRowName("bcp");
        XSSFRow row = sheet1.createRow(rowNum);
        for (int i=0;i<rowName.length;i++){
        for (int i = 0; i < rowName.length; i++) {
            row.createCell(i).setCellValue(rowName[i]);
        }
        for(int i=0;i<list.get(0).SingleVol.length;i++){
            row.createCell(rowName.length+i).setCellValue("#"+Integer.valueOf(i+1));
        for (int i = 0; i < battParam.BattGroupCount; i++) {
            row.createCell(rowName.length + i*2).setCellValue("#" + Integer.valueOf(i + 1)+"在线电压");
            row.createCell(rowName.length + i*2+1).setCellValue("#" + Integer.valueOf(i + 1)+"组端电压");
        }
        rowNum++;
        //数据栏
        for (int i = 0; i < list.size(); i++) {
            sheet1.createRow(rowNum);  //创建行
            FboData fbo=list.get(i);
            sheet1.getRow(rowNum).createCell(0).setCellValue(fbo.m_TestTime.hms());
            sheet1.getRow(rowNum).createCell(1).setCellValue(fbo.SumVoltage);
            sheet1.getRow(rowNum).createCell(2).setCellValue(fbo.SumCurrent);
            sheet1.getRow(rowNum).createCell(3).setCellValue(fbo.Temp_Envi);
            sheet1.getRow(rowNum).createCell(4).setCellValue(fbo.Humi_Envi);
            if (start.DataType!=0xFB){
                sheet1.getRow(rowNum).createCell(5).setCellValue(fbo.testCap);
            }
            for (int k = 0;k<list.get(0).SingleVol.length;k++){
                sheet1.getRow(rowNum).createCell(rowName.length+k).setCellValue(fbo.SingleVol[k]);
            Fbs5100FbsData fbsData = list.get(i);
            sheet1.getRow(rowNum).createCell(0).setCellValue(fbsData.testTime.hour+":"+fbsData.testTime.minute+":"+fbsData.testTime.second);
            sheet1.getRow(rowNum).createCell(1).setCellValue(String.valueOf(fbsData.muxianvol_discharge));
            sheet1.getRow(rowNum).createCell(2).setCellValue(String.valueOf(fbsData.muxianvol_charge));
            sheet1.getRow(rowNum).createCell(3).setCellValue(String.valueOf(fbsData.boostDCDC_OutVol));
            sheet1.getRow(rowNum).createCell(4).setCellValue(String.valueOf(fbsData.muxianvol));
            for (int j = 0; j < battParam.BattGroupCount; j++) {
                sheet1.getRow(rowNum).createCell(rowName.length + j*2).setCellValue(String.valueOf(fbsData.onlinevol[j]));
                sheet1.getRow(rowNum).createCell(rowName.length + j*2+1).setCellValue(String.valueOf(fbsData.groupvol[j]));
            }
            rowNum++;
        }

        rowNum++;

        try {
@@ -178,68 +160,107 @@
        }
    }


    public String[] getRowName(int dateType){
        if (dateType==0xFB){
            String[] rowName = {"测试时间","总电压(V)","总电流(A)","环境温度","环境湿度"};
            return rowName;
    //将日期格式转换成指定的字符串格式
    public static String formartDate(Date date, String str){
        return new SimpleDateFormat(str).format(date);
    }
    public String[] getRowName(String bcp){
        if (bcp.equals("bcp")){
            String[] rowNameBcp = {"测试时间","放电母线电压(V)","充电母线电压(V)","升压DCDC输出电压","48V母线电压"};
            return rowNameBcp;
        }else if (bcp.equals("chr")){
            String[] rowNameChr = {"测试时间","放电母线电压(V)","充电母线电压(V)","升压DCDC输出电压","48V母线电压"};
            return rowNameChr;
        }else if (bcp.equals("alm")){
            String[] rowNameAlm = {"告警开始时间","告警名称","告警类型","告警值"};
            return rowNameAlm;
        }else{
            String[] rowName1 = {"测试时间","总电压(V)","总电流(A)","环境温度","环境湿度","测试容量(AH)"};
            return rowName1;
        }
    }

    public String[] getPicName(int dateType){
        if (dateType==0xFB){
            String[] picName1 = new String[]{
                    "电池电流折线图","组端电压折线图","单体电压折线图","环境温度折线图","环境湿度折线图"
    public String[] getPicName(String fileType){
        if (fileType.equals("bcp")){
            String[] picNameBcp = new String[]{
                    "电池电流折线图","组端电压折线图","测试容量折线图","温度折线图"
            };
            return picName1;
            return picNameBcp;
        }else if(fileType.equals("chr")){
            String[] picNameChr = new String[]{
                    "电池电流折线图","组端电压折线图","测试容量折线图","温度折线图"
            };
            return picNameChr;
        }else{
            String[] picName = new String[]{
                    "电池电流折线图","组端电压折线图","测试容量折线图","单体电压折线图","环境温度折线图","环境湿度折线图"
            };
            return picName;
            return null;
        }
    }


    //导出充电数据chr
    public void exportChr(HttpServletRequest req, HttpServletResponse resp){
        String volEchart = req.getParameter("vol_echart");         //单体电压折线图
        String resEchart = req.getParameter("res_echart");         //单体内阻折线图
        String tmpEchart = req.getParameter("tmp_echart");         //单体温度折线图

        String curr_echart = req.getParameter("curr_echart");        //电池电流折线图
        String groupVol_echart = req.getParameter("groupVol_echart");//组端电压折线图
        String cap_echart = req.getParameter("cap_echart");            //测试容量折线图
        String temp_echart = req.getParameter("temp_echart");//温度折线图
        String filePath = req.getParameter("filePath");
        Fbs5100ChargeData data=chargeDataService.readFileData(filePath);
        //图片base64后的数据
        List<byte[]> bytes = new ArrayList<>();
        try {
            if (ServletUtils.isNotNull(volEchart)) {
                String[] url = volEchart.split(",");
            if (ServletUtils.isNotNull(curr_echart)) {
                String[] url = curr_echart.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(resEchart)) {
                String[] url = resEchart.split(",");
            if (ServletUtils.isNotNull(groupVol_echart)) {
                String[] url = groupVol_echart.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(tmpEchart)) {
                String[] url = tmpEchart.split(",");
            if (ServletUtils.isNotNull(cap_echart)) {
                String[] url = cap_echart.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(temp_echart)) {
                String[] url = temp_echart.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        //数据
        Fbs5100BattParam battParam =data.battParam;
        Fbs5100ChargeParam chargeParam =data.chargeParam;
        Fbs5100ChargeState chargeState =data.chargeState;
        //从文件中获取数据
        List<Fbs5100FbsData> list = data.fbsDatas;
        //当前日期
        String nowFormat = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
        String excelName = "BRES-"+nowFormat;
        String excelName = "chr-"+nowFormat;
        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("数据总表");
        int rowNumSheet = 0;
        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("电池组数：" + battParam.BattGroupCount);
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("每组单体数：" + battParam.EachGroupBattCount);
        sheet.getRow(rowNumSheet).createCell(2).setCellValue("标称单体电压：" + battParam.MonomerVol + "V");

        rowNumSheet++;
        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("浮充电流阈值：" + battParam.FloatChargeVol + "A");
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("在线电压低阈值：" + battParam.OnlineVolLow + "A");
        sheet.getRow(rowNumSheet).createCell(2).setCellValue("均充电压：" + battParam.JunChargeVol + "A");

        rowNumSheet++;
        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("测试开始时间：" + formartDate(chargeState.StartDT.time,"yyyy-MM-dd HH:mm:ss"));
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("测试时长：" + chargeState.Test_Time.hour+":"+chargeState.Test_Time.minute+":"+chargeState.Test_Time.second);

        rowNumSheet += 4;
        //图片元素
        XSSFDrawing patriarch = sheet.createDrawingPatriarch();
        int rowNumSheet = 1;
        //插入图片
        int picNum = 0;
        String[] picName =new String[]{"单体电压折线图","单体内阻折线图","单体温度折线图"};
        String[] picName =getPicName("chr");
        if (picName.length==bytes.size()){
            for(int i=0;i<picName.length;i++){
                sheet.createRow(rowNumSheet-1);
@@ -252,15 +273,39 @@
                rowNumSheet+=30;
            }
        }
        //数据
        RESDataInfo resDataInfo =resService.readFileData(filePath);
        //从文件中获取数据
        List<RESData> list = resDataInfo.resDatas;
        int battNum=list.get(0).getBattSum();//单体个数
        //创建不同sheet
        createBresSheet(wb,battNum,list,1);//创建内阻数据
        createBresSheet(wb,battNum,list,2);//创建温度数据
        createBresSheet(wb,battNum,list,3);//创建电压数据
        XSSFSheet sheet1 = wb.createSheet("数据");
        //抬头
        int rowNum = 0;
        sheet1.createRow(rowNum).createCell(0).setCellValue("测试数据");
        rowNum++;
        //属性栏
        String[] rowName = getRowName("chr");
        XSSFRow row = sheet1.createRow(rowNum);
        for (int i = 0; i < rowName.length; i++) {
            row.createCell(i).setCellValue(rowName[i]);
        }
        for (int i = 0; i < battParam.BattGroupCount; i++) {
            row.createCell(rowName.length + i*2).setCellValue("#" + Integer.valueOf(i + 1)+"在线电压");
            row.createCell(rowName.length + i*2+1).setCellValue("#" + Integer.valueOf(i + 1)+"组端电压");
        }
        rowNum++;
        //数据栏
        for (int i = 0; i < list.size(); i++) {
            sheet1.createRow(rowNum);  //创建行
            Fbs5100FbsData fbsData = list.get(i);
            sheet1.getRow(rowNum).createCell(0).setCellValue(fbsData.testTime.hour+":"+fbsData.testTime.minute+":"+fbsData.testTime.second);
            sheet1.getRow(rowNum).createCell(1).setCellValue(String.valueOf(fbsData.muxianvol_discharge));
            sheet1.getRow(rowNum).createCell(2).setCellValue(String.valueOf(fbsData.muxianvol_charge));
            sheet1.getRow(rowNum).createCell(3).setCellValue(String.valueOf(fbsData.boostDCDC_OutVol));
            sheet1.getRow(rowNum).createCell(4).setCellValue(String.valueOf(fbsData.muxianvol));
            for (int j = 0; j < battParam.BattGroupCount; j++) {
                sheet1.getRow(rowNum).createCell(rowName.length + j*2).setCellValue(String.valueOf(fbsData.onlinevol[j]));
                sheet1.getRow(rowNum).createCell(rowName.length + j*2+1).setCellValue(String.valueOf(fbsData.groupvol[j]));
            }
            rowNum++;
        }
        rowNum++;

        try {
            // 转码防止乱码
            resp.addHeader("Content-Disposition", "attachment;filename="
@@ -275,206 +320,57 @@
            e.printStackTrace();
        }
    }
    //bres中内阻数据/温度数据/单体电压数据放在不同的sheet中
    public void createBresSheet(XSSFWorkbook wb,int battNum,List<RESData> list,int type){
        String sheetName="";
        String testName="";
        switch (type){
            case 1:sheetName= "内阻数据";testName="测试数据(内阻)";break;
            case 2:sheetName= "温度数据";testName="测试数据(温度)";break;
            case 3:sheetName= "电压数据";testName="测试数据(电压)";break;
        }
        XSSFSheet sheet1 = wb.createSheet(sheetName);
        //抬头
        int rowNum = 0;
        sheet1.createRow(rowNum).createCell(0).setCellValue(testName);
        rowNum++;
        //属性栏
        XSSFRow row = sheet1.createRow(rowNum);
        row.createCell(0).setCellValue("测试时间");

        for(int i=0;i<battNum;i++){
            row.createCell(1+i).setCellValue("#"+Integer.valueOf(i+1));
        }
        rowNum++;
        float[] datas=new float[]{};
        //数据栏
        for (int i = 0; i < list.size(); i++) {
            sheet1.createRow(rowNum);  //创建行
            RESData resData= list.get(i);
            switch (type){
                case 1:datas= resData.getMonomerRes();break;
                case 2:datas= resData.getMonomerTemp();break;
                case 3:datas= resData.getMonomerVol();break;
            }
            sheet1.getRow(rowNum).createCell(0).setCellValue(formartDate(resData.getData_Time(),"yyyy-MM-dd HH:mm:ss"));
            for (int k = 0;k<battNum;k++){
                sheet1.getRow(rowNum).createCell(k+1).setCellValue(datas[k]);
            }
            rowNum++;
        }
        rowNum++;
    }

    *//**
     * 将日期格式转换成指定的字符串格式
     * @param date   日期
     * @param str   字符串的格式
     * @return
     *//*
    public static String formartDate(Date date, String str){
        return new SimpleDateFormat(str).format(date);
    }



    //导出告警数据alm
    public void exportAlm(HttpServletRequest req, HttpServletResponse resp){
        String volEchart = req.getParameter("vol_echart");         //单体电压折线图
        String currEchart = req.getParameter("curr_echart");      //电池电流折线图
        String groupVolEchart = req.getParameter("groupVol_echart");      //组端电压折线图
        String capEchart = req.getParameter("cap_echart");         //测试容量折线图
        String filePath = req.getParameter("filePath");
        //数据
        TestDataInfo testDataInfoMcp=testDataInfoService.readFileData(filePath);
        MonitorDataInfo info = testDataInfoMcp.monitorDataInfo;//组端数据

        //图片base64后的数据
        List<byte[]> bytes = new ArrayList<>();
        try {
            if (ServletUtils.isNotNull(volEchart)) {
                String[] url = volEchart.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(currEchart)) {
                String[] url = currEchart.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(groupVolEchart)) {
                String[] url = groupVolEchart.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
            if (ServletUtils.isNotNull(capEchart)) {
                String[] url = capEchart.split(",");
                bytes.add(new BASE64Decoder().decodeBuffer(url[1]));
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        Fbs5100AlarmData data=alarmDataService.readFileData(filePath);
        Fbs5100AlarmParam alarmParam = data.alarmParam;
        List<Fbs5100Alarm> list=data.alarms;
        //当前日期
        String nowFormat = new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());
        String excelName ="";
        int fileType=testDataInfoMcp.getFile_type();
        if(fileType==0xFA){
            excelName="MCP-"+nowFormat;
        }else{
            excelName="MCH-"+nowFormat;
        }
        String excelName ="alm-"+new SimpleDateFormat("yyyyMMddHHmmss").format(new Date());

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet sheet = wb.createSheet("数据总表");
        //图片元素
        XSSFDrawing patriarch = sheet.createDrawingPatriarch();
        BattParam battParam=info.battparam;//电池参数
        SYSMonitorParam sysParam=info.monitorparam;//组端参数
        SYSMonitorState sysState=info.monitorstate;//组端状态
        int rowNumSheet = 0;
        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("电池参数:");
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("告警参数:");
        rowNumSheet++;
        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("标称容量(1AH):"+battParam.STD_CAP);
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("标称内阻(0.001mΩ):"+battParam.STD_RES);
        sheet.getRow(rowNumSheet).createCell(2).setCellValue("单体标称电压(0.1V):"+battParam.monomerVol);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("标称容量:"+alarmParam.Batt_CAP);
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("组端电压上限值:"+alarmParam.GroupVol_H);
        sheet.getRow(rowNumSheet).createCell(2).setCellValue("组端电压下限值:"+alarmParam.GroupVol_L);
        rowNumSheet++;
        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("组数:"+battParam.battGroupCount);

        int battNum=battParam.eachGroupBattCount;//单体个数
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("每组单体个数:"+battNum);
        rowNumSheet++;

        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("组端参数:");
        rowNumSheet++;
        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("放电电流:"+sysParam.disCurr);
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("放电容量:"+sysParam.disCap);
        sheet.getRow(rowNumSheet).createCell(2).setCellValue("放电时长:"+sysParam.disTime);
        rowNumSheet++;
        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("组端下限:"+sysParam.groupVol_LOW);
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("单体下限:"+sysParam.monomerVol_LOW);
        sheet.getRow(rowNumSheet).createCell(2).setCellValue("下限个数:"+sysParam.monomerLowCount);
        rowNumSheet++;
        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("充电电流:"+sysParam.chrCurr);
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("充电容量:"+sysParam.chrCap);
        sheet.getRow(rowNumSheet).createCell(2).setCellValue("充电时长:"+sysParam.chrTime);
        rowNumSheet++;

        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("组端状态:");
        rowNumSheet++;
        sheet.createRow(rowNumSheet);
        sheet.getRow(rowNumSheet).createCell(0).setCellValue("启动放电的日期时间:"+formartDate(sysState.startDT.time,"yyyy-MM-dd HH:mm:ss"));
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("终止放电的日期时间:"+formartDate(sysState.stopDT.time,"yyyy-MM-dd HH:mm:ss"));
        sheet.getRow(rowNumSheet).createCell(2).setCellValue("已测试时间(HMS):"+sysState.test_Time.testTime);

        sheet.getRow(rowNumSheet).createCell(0).setCellValue("交流电压上限值:"+alarmParam.AC_VOL_H);
        sheet.getRow(rowNumSheet).createCell(1).setCellValue("交流电压下限值:"+alarmParam.AC_VOL_L);
        rowNumSheet+=4;
        //插入图片
        int picNum = 0;
        String[] picName =new String[]{"单体电压折线图","电池电流折线图","组端电压折线图","测试容量折线图"};
        if (picName.length==bytes.size()){
            for(int i=0;i<picName.length;i++){
                sheet.createRow(rowNumSheet-1);
                sheet.getRow(rowNumSheet-1).createCell(0).setCellValue(picName[i]);
                //rowNum++;
                XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 512, 255,(short) 0, rowNumSheet, (short) 10, rowNumSheet+27);
                anchor.setAnchorType(ClientAnchor.AnchorType.DONT_MOVE_AND_RESIZE);
                patriarch.createPicture(anchor,wb.addPicture(bytes.get(picNum),XSSFWorkbook.PICTURE_TYPE_PNG)).resize(1);
                picNum ++;
                rowNumSheet+=30;
            }
        }

        //从文件中获取数据
        List<FBSData> list=testDataInfoMcp.fbsDatas;//充放电数据
        String sheetName="放电数据";
        String testName="测试数据";
        XSSFSheet sheet1 = wb.createSheet(sheetName);
        //抬头
        int rowNum = 0;
        sheet1.createRow(rowNum).createCell(0).setCellValue(testName);
        rowNum++;
        sheet.createRow(rowNumSheet).createCell(0).setCellValue(testName);
        rowNumSheet++;

        XSSFRow row = sheet1.createRow(rowNum);
        XSSFRow row = sheet.createRow(rowNumSheet);
        //属性栏
        String[] rowName =new String[]{"测试时间","在线电压","组端电压","电池电流","电池容量","电池温度"};
        String[] rowName =getRowName("alm");
        for (int i=0;i<rowName.length;i++){
            row.createCell(i).setCellValue(rowName[i]);
        }
        for(int i=0;i<battNum;i++){
            row.createCell(6+i).setCellValue("#"+Integer.valueOf(i+1));
        }
        rowNum++;
        rowNumSheet++;
        //数据栏
        for (int i = 0; i < list.size(); i++) {
            sheet1.createRow(rowNum);  //创建行
            FBSData fbsData= list.get(i);
            sheet1.getRow(rowNum).createCell(0).setCellValue(fbsData.testTime.testTime);
            sheet1.getRow(rowNum).createCell(1).setCellValue(fbsData.vcData.onlinevol[0]);
            sheet1.getRow(rowNum).createCell(2).setCellValue(fbsData.vcData.groupvol[0]);
            sheet1.getRow(rowNum).createCell(3).setCellValue(fbsData.vcData.battcurr[0]);
            sheet1.getRow(rowNum).createCell(4).setCellValue(fbsData.vcData.battcap[0]);
            sheet1.getRow(rowNum).createCell(5).setCellValue(fbsData.vcData.batttemp[0]);
            float[]  datas=fbsData.mvol.vol;
            for (int k = 0;k<battNum;k++){
                sheet1.getRow(rowNum).createCell(k+6).setCellValue(datas[k]);
            }
            rowNum++;
            sheet.createRow(rowNumSheet);  //创建行
            Fbs5100Alarm alarm= list.get(i);
            sheet.getRow(rowNumSheet).createCell(0).setCellValue(formartDate(alarm.StartTime.time,"yyyy-MM-dd HH:mm:ss"));
            sheet.getRow(rowNumSheet).createCell(1).setCellValue(AlarmFactoryDto.getAlmName(alarm.AlarmItem));
            sheet.getRow(rowNumSheet).createCell(2).setCellValue(AlarmFactoryDto.getAlmType(alarm.AlarmType));
            sheet.getRow(rowNumSheet).createCell(3).setCellValue(alarm.AlarmValue);
            rowNumSheet++;
        }
        rowNum++;

        rowNumSheet++;
        try {
            // 转码防止乱码
            resp.addHeader("Content-Disposition", "attachment;filename="
@@ -488,5 +384,5 @@
        } catch (IOException e) {
            e.printStackTrace();
        }
    }*/
    }
}