解析数据

whyclxw

2024-10-16 df04d2f5d56cce23aa108c1cce1ce27e4bf52fa0

解析数据

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

New file
@@ -0,0 +1,32 @@
package com.whyc.controller;
import com.whyc.dto.Response;
import com.whyc.service.AnaysiService;
import com.whyc.util.AnalysisUtil;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;

import javax.servlet.http.HttpServletResponse;


@RequestMapping("anaysis")
@RestController
@Api(tags = "预估10小时数据")
public class AnaysisController {
    @Autowired
    private AnaysiService service;

    @ApiOperation("生成预估数据")
    @GetMapping("createAnaysisXls")
    public Response createAnaysisXls(@RequestParam int battGroupId, @RequestParam int testRecordCount){
        service.createAnaysisXls(battGroupId,testRecordCount);
        return new Response().set(1,true);
    }

    @ApiOperation("查询预估数据")
    @GetMapping("getAnaysisXls")
    public Response getAnaysisXls(@RequestParam int battGroupId, @RequestParam int testRecordCount){
        return service.getAnaysisXls(battGroupId,testRecordCount);
    }
}

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

New file
@@ -0,0 +1,21 @@
package com.whyc.pojo;

import lombok.Data;

@Data
public class AnaylsisId {

    private int num;

    private int testRecordCount;

    private int recordNum;

    private String recordTime;

    private double groupVol;

    private int monNum;

    private double monVol;
}

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

New file
@@ -0,0 +1,27 @@
package com.whyc.pojo;

import lombok.Data;

import java.util.Date;
import java.util.List;

@Data
public class AnaysisData {

    private int testTimelong;

    private String recordTime;

    private Float groupVol;

    private Float groupCurr;

    private Float testCap;

    /*private int monNum;

    private Float monVol;*/

    private List<Float> monVols;
    private List<Integer> monNums;
}

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

New file
@@ -0,0 +1,259 @@
package com.whyc.service;

import com.whyc.dto.FileDirPath;
import com.whyc.dto.Response;
import com.whyc.pojo.AnaylsisId;
import com.whyc.pojo.AnaysisData;
import com.whyc.pojo.BatttestdataId;
import com.whyc.util.AnalysisUtil;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.imageio.ImageIO;
import javax.servlet.http.HttpServletResponse;
import java.awt.image.BufferedImage;
import java.io.*;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;

@Service
public class AnaysiService {
    @Autowired(required = false)
    private SubTablePageInfoService subService;

    //预估数据
    public void createAnaysisXls(int battGroupId, int testRecordCount) {
        //1.查出需要预估的数据
       List<AnaysisData> list=subService.getAnaysisData(battGroupId,testRecordCount);
        String excelName="测试数据.xlsx";
        String root = FileDirPath.getFileDirName();
        String excelOutPutPath=root+ File.separator+"outPath";
        //2.生成解析的xls
        /* String root=exportDataToXls(excelName,list);
        String voltagePredictExePath=root+ File.separator+"10小时率电压预估\\voltage_predict_process\\main.exe";
        String excelOutPutPath=root+ File.separator+"outPath";
        String excelSourcePath=root+ File.separator+"测试数据.xlsx";
        //3.生成预估的文件xls
        AnalysisUtil.analysisXlx(excelSourcePath,voltagePredictExePath,excelOutPutPath);*/
        //4。将预估的数据存入数据库
        storeAnaysisToSql(battGroupId,testRecordCount,list,excelOutPutPath);
    }
    //将预估的数据存入数据库
    private   void  storeAnaysisToSql(int battGroupId,int testRecordCount, List<AnaysisData> list,String excelSourcePath) {
        //获取文件夹下所有的数据
        List<String> fileList=getFileNameWithOutDirectory(excelSourcePath);
        int recordNum=list.size()+1;
        List<AnaylsisId> groupVolList=new ArrayList<>();
        //验证分析表是否存在
        int flag=subService.judgeTable_anaysis(battGroupId,testRecordCount);
        //检测表是存在后testRecordCount数据是否存在
        if(flag!=0){
            int sum=subService.getAnaysisIdByTestRecordCount(battGroupId,testRecordCount);
            if(sum!=0) {
                if(fileList!=null){
                    for (String fileName:fileList) {
                        if(fileName.contains("总电压")){
                            //解析组端电压单个文件
                            groupVolList=anaysisFileByGroupVol(testRecordCount,recordNum,excelSourcePath+File.separator+fileName);
                        }else{
                            /*String name=fileName.substring(0,fileName.indexOf("#"));
                            int monNum=Integer.valueOf(name);
                            //解析单个文件
                            List<AnaylsisId> idList=anaysisFileByNum(monNum,testRecordCount,recordNum,excelSourcePath+File.separator+fileName);

                            //将解析出的数据存入数据库表
                            subService.storeAnaysisToSqlWithNum(battGroupId,testRecordCount,idList);*/
                        }
                    }
                    //将解析的组端电压修改到数据库
                    subService.updateAnaysisToSqlWithNum(battGroupId,testRecordCount,groupVolList);
                }
            }
        }else{
            //表不存在则创建表
            subService.creatAnasys(battGroupId);
        }
    }
    //解析单体的单个文件
    private static List<AnaylsisId> anaysisFileByNum(int monNum,int testRecordCount, int recordNum, String fileRoot) {
        List<AnaylsisId> idList=new ArrayList<>();
        Workbook workbook = null;
        File file=new File(fileRoot);
        try {
            workbook = WorkbookFactory.create(file);
            //取第一个sheet表
            Sheet sheet = workbook.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum();

            /*Row rowTemp = sheet.getRow(0);
            short lastCellNum = rowTemp.getLastCellNum();*/
            DataFormatter dataFormatter = new DataFormatter(Locale.US);
            for (int i = 1; i < lastRowNum; i++) {
                Row row = sheet.getRow(i);

                AnaylsisId aIdDta=new AnaylsisId();
                aIdDta.setTestRecordCount(testRecordCount);
                aIdDta.setMonNum(monNum);
                Cell cell0 = row.getCell(0);
                cell0.setCellType(Cell.CELL_TYPE_NUMERIC);
                aIdDta.setRecordNum( (recordNum+(int)cell0.getNumericCellValue()));

                Cell cell1 = row.getCell(1);
                String formattedValue = dataFormatter.formatCellValue(cell1);
                aIdDta.setRecordTime(formattedValue);

                Cell cell2 = row.getCell(2);
                cell2 .setCellType(Cell.CELL_TYPE_NUMERIC);
                aIdDta.setMonVol(cell2.getNumericCellValue());
                idList.add(aIdDta);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        return idList;
    }

    //解析组端电压单个文件
    private static List<AnaylsisId> anaysisFileByGroupVol(int testRecordCount, int recordNum, String fileRoot) {
        List<AnaylsisId> idList=new ArrayList<>();
        Workbook workbook = null;
        File file=new File(fileRoot);
        try {
            workbook = WorkbookFactory.create(file);
            //取第一个sheet表
            Sheet sheet = workbook.getSheetAt(0);
            int lastRowNum = sheet.getLastRowNum();

            /*Row rowTemp = sheet.getRow(0);
            short lastCellNum = rowTemp.getLastCellNum();*/
            DataFormatter dataFormatter = new DataFormatter(Locale.US);
            for (int i = 1; i < lastRowNum; i++) {
                Row row = sheet.getRow(i);

                AnaylsisId aIdDta=new AnaylsisId();
                aIdDta.setTestRecordCount(testRecordCount);
                Cell cell0 = row.getCell(0);
                cell0.setCellType(Cell.CELL_TYPE_NUMERIC);
                aIdDta.setRecordNum( (recordNum+(int)cell0.getNumericCellValue()));

                Cell cell1 = row.getCell(1);
                String formattedValue = dataFormatter.formatCellValue(cell1);
                aIdDta.setRecordTime(formattedValue);

                Cell cell2 = row.getCell(2);
                cell2 .setCellType(Cell.CELL_TYPE_NUMERIC);
                aIdDta.setGroupVol(cell2.getNumericCellValue());
                idList.add(aIdDta);
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        return idList;
    }

    //读取文件夹下的所有文件（不读取文件夹内的文件）
    public static List getFileNameWithOutDirectory(String filePath) {
        File folder = new File(filePath); // 文件夹路径
        List nameList=new ArrayList();
        File[] listOfFiles = folder.listFiles();
        if (listOfFiles != null) {
            for (File file : listOfFiles) {
                if (file.isFile()) {
                    nameList.add(file.getName());
                }
            }
        }
        return nameList;
    }

    //生成解析的xls
    private String exportDataToXls( String excelName,List<AnaysisData> list) {
        String fileDirName = FileDirPath.getFileDirName();
        String rootFace="";
        String[] titleNames=new String[]{"时间(HMS)","总电压(V)","总电流(A)","容量(AH)"};
        //创建单个sheet
        HSSFWorkbook wb = new HSSFWorkbook();
        //字体格式-加粗
        HSSFCellStyle cellStyle = wb.createCellStyle();
        cellStyle.setFillForegroundColor(IndexedColors.GOLD.getIndex());//添加前景色,内容看的清楚
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        HSSFFont font = wb.createFont();
        font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        //font.setColor(HSSFFont.COLOR_RED);
        cellStyle.setFont(font);

        HSSFSheet sheet = wb.createSheet("电压数据表格1");
        int rownum = 0;
        int monSum =24;//单体总数
        HSSFRow row=sheet.createRow(rownum);
        if(titleNames.length>0){
            HSSFCell cell=null;
            for (int i=0;i<titleNames.length;i++) {
                cell=row.createCell(i);
                cell.setCellValue(titleNames[i]);
            }
            for (int j=0;j<monSum;j++) {
                cell=row.createCell(j+4);
                cell.setCellValue(j+1+"#");
            }
        }
        rootFace=fileDirName;
        File destfile = new File(rootFace);
        if(!destfile.exists()) {
            destfile.mkdir();
        }
        //将选中的文件存入指定目录下打包下载
        if(list!=null&&list.size()>0){
            for (int i=0;i<list.size();i++) {
                AnaysisData data = list.get(i);
                rownum = rownum + 1;
                row = sheet.createRow(rownum);
                row.createCell(0).setCellValue(data.getRecordTime());
                row.createCell(1).setCellValue(data.getGroupVol());
                row.createCell(2).setCellValue(data.getGroupCurr());
                row.createCell(3).setCellValue(data.getTestCap());
                if (data.getMonVols() != null) {
                    List<Float> monVols = data.getMonVols();
                    for (int j = 0; j < monVols.size(); j++) {
                        row.createCell(4 + j).setCellValue(monVols.get(j));
                    }
                }
            }
        }
        FileOutputStream fileOut =null;
        excelName=rootFace+ File.separator+excelName;
        try {
            fileOut = new FileOutputStream(excelName);
            // 写入excel文件
            wb.write(fileOut);
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if(fileOut != null){
                try {
                    fileOut.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return rootFace;
    }

    //查询预估数据
    public Response getAnaysisXls(int battGroupId, int testRecordCount) {
        List<AnaylsisId> list=subService.getAnaysisXls(battGroupId,testRecordCount);
        return new Response().setII(1,list!=null,list,"查询预估数据");
    }
}

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

@@ -13,6 +13,7 @@

import javax.swing.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

@Service
@@ -138,4 +139,70 @@
            throwables.printStackTrace();
        }
    }

    public  boolean makeManualCommit( ArrayList<String> al_sql_strs){
        PreparedStatement ps = null;
        SqlSession sqlSession = openSession();
        Connection mysql_con=sqlSession.getConnection();
        boolean exe_res = true;
        try {
            mysql_con.setAutoCommit(false);

            for(int n=0; n<al_sql_strs.size(); n++) {
                if(true == exe_res) {
                    exe_res = executeCreateTableNoclose(mysql_con,al_sql_strs.get(n));
                } else {
                    break;
                }
            }
            if(true == exe_res) {
                mysql_con.commit();
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
            exe_res = false;
        } finally {
            try {
                if(false == exe_res) {
                    mysql_con.rollback();
                }
                mysql_con.setAutoCommit(true);
            } catch (SQLException e1) {
                // TODO Auto-generated catch block
                e1.printStackTrace();
            }finally {
                closeSession(null,ps,sqlSession);
            }
        }
        return exe_res;
    }

    //执行sql语句with no colse
    public static boolean executeCreateTableNoclose(Connection mysql_con,String sql_str)
    {
        PreparedStatement ps=null;

        boolean rest = true;
        try
        {
            ps = mysql_con.prepareStatement(sql_str);
            ps.setQueryTimeout(600);
            ps.execute();
        }
        catch(SQLException ex)
        {
            ex.printStackTrace();
            rest = false;

        }finally {
            try {
                ps.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        return rest;
    }
}

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

@@ -3664,4 +3664,190 @@
        });
        return list;
    }
    //查出需要预估的数据
    public List<AnaysisData> getAnaysisData(int battGroupId, int testRecordCount) {
        String sql="  SELECT  distinct  record_num,test_timelong,group_vol,test_cap,mon_num,mon_vol,test_curr FROM db_batt_testdata.tb_batttestdata_"+battGroupId+
                "        WHERE test_record_count = " +testRecordCount+
                "        order by record_num asc,mon_num asc ";
        List<AnaysisData> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
            @Override
            public List getResults(ResultSet rs) throws SQLException {
                List list=new ArrayList<>();
                List<Float> monVols=null;
                List<Integer> monNums=null;
                AnaysisData data=null;
                int num=0;
                try {
                    while (rs.next()){
                        int monNum=rs.getInt("mon_num");
                        float monvol=rs.getFloat("mon_vol");
                        int recordNum=rs.getInt("record_num");
                        if(num!=recordNum){
                            if(num!=0){
                                data.setMonVols(monVols);
                                data.setMonNums(monNums);
                                list.add(data);
                            }
                            data=new AnaysisData();
                            monVols=new ArrayList<>();
                            monNums=new ArrayList<>();
                            data.setTestTimelong(rs.getInt("test_timelong"));
                            data.setRecordTime(ActionUtil.secToTime(data.getTestTimelong()));
                            data.setGroupVol(rs.getFloat("group_vol"));
                            data.setTestCap(rs.getFloat("test_cap"));
                            data.setGroupCurr(rs.getFloat("test_curr"));
                            num=recordNum;
                        }
                        monVols.add(monvol);
                        monNums.add(monNum);
                    }
                    data.setMonVols(monVols);
                    data.setMonNums(monNums);
                    list.add(data);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return list;
            }
        });
        return list;
    }
    //将解析出的数据存入数据库表
    public void storeAnaysisToSqlWithNum(int battGroupId,int testRecordCount, List<AnaylsisId> idList) {
        ArrayList  sql_str=new ArrayList();
        String tableName="db_analysis."+"tb_analysis_"+battGroupId;
        //检测表是否存在
        if(idList!=null){
            String insertSql="insert into "+tableName+" "
                    + "("
                    + "test_record_count,"
                    + "record_num,"
                    + "record_time,"
                    + "mon_num,"
                    + "mon_vol"
                    + ") "
                    + " values ";
            for (int i=0;i<idList.size();i++) {
                AnaylsisId ays=idList.get(i);
                if(i>0){
                    insertSql+=" ,  ";
                }
                insertSql=insertSql
                        + "("
                        + ays.getTestRecordCount()+","
                        + ays.getRecordNum()+","
                        + "'"+ays.getRecordTime()+"',"
                        + ays.getMonNum()+","
                        + ays.getMonVol()+""
                        + ")";//如果对应是字符几个带上单引号
            }
            sql_str.add(insertSql);
        }
        sqlExecuteService.makeManualCommit(sql_str);
    }
    //将解析的组端电压修改到数据库
    public void updateAnaysisToSqlWithNum(int battGroupId, int testRecordCount, List<AnaylsisId> groupVolList) {
        ArrayList  sql_str=new ArrayList();
        String tableName="db_analysis."+"tb_analysis_"+battGroupId;
        if(groupVolList!=null){
            for ( AnaylsisId ays:groupVolList) {
                String updateSql="update "+tableName+" set group_vol="+ays.getGroupVol()+" where test_record_count="+testRecordCount+" and record_num="+ays.getRecordNum();
                sql_str.add(updateSql);
            }
        }
        sqlExecuteService.makeManualCommit(sql_str);
    }

    //验证分析表是否存在
    public int judgeTable_anaysis(int battGroupId, int testRecordCount) {
        String sql="select count(*) as tableNum " +
                "        from INFORMATION_SCHEMA.TABLES " +
                "        where TABLE_SCHEMA = 'db_analysis' " +
                "          and TABLE_NAME = 'tb_analysis_"+battGroupId+"'";
        List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
            @Override
            public List getResults(ResultSet rs) throws SQLException {
                LinkedList<Object> temp = new LinkedList<>();
                try {
                    while (rs.next())
                        temp.add(rs.getInt("tableNum"));
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return temp;
            }
        });
        int tableNum =0;
        if(list!=null){
            tableNum= (int) list.get(0);
        }
        return tableNum;
    }
    //检测表是存在后testRecordCount数据是否存在
    public int getAnaysisIdByTestRecordCount(int battGroupId, int testRecordCount) {
        String sql="  SELECT  count(*) as sum FROM db_analysis.tb_analysis_"+battGroupId+
                "        WHERE test_record_count = " +testRecordCount;
        List list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
            @Override
            public List getResults(ResultSet rs) throws SQLException {
                LinkedList<Object> temp = new LinkedList<>();
                try {
                    while (rs.next())
                        temp.add(rs.getInt("sum"));
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return temp;
            }
        });
        int sum =0;
        if(list!=null){
            sum= (int) list.get(0);
        }
        return sum;
    }

    //创建clear
    public void creatAnasys(int battGroupId) {
        String sql=" CREATE TABLE IF NOT EXISTS db_analysis.tb_analysis_"+battGroupId+" (" +
                "   `num` int(11) NOT NULL AUTO_INCREMENT," +
                "   `test_record_count` int(11) NOT NULL DEFAULT '0'," +
                "  `record_num` int not NULL DEFAULT 0," +
                "  `record_time` varchar(64) not NULL DEFAULT '00:00:00'," +
                "  `group_vol` float not NULL DEFAULT 0," +
                "  `mon_num` int  not NULL DEFAULT 0," +
                "  `mon_vol` float  not NULL DEFAULT 0," +
                "  PRIMARY KEY (`num`)" +
                ") ENGINE=InnoDB AUTO_INCREMENT=1 ";
        sqlExecuteService.execute(sql);
    }
    //查询预估数据
    public List<AnaylsisId> getAnaysisXls(int battGroupId, int testRecordCount) {
        String sql="  SELECT  distinct  num,test_record_count,record_num,record_time,group_vol,mon_vol,mon_num FROM db_analysis.tb_analysis_"+battGroupId+
                "        WHERE test_record_count = " +testRecordCount+
                "        order by test_record_count asc ,record_num asc ,mon_num ASC ";
        List<AnaylsisId> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
            @Override
            public List getResults(ResultSet rs) throws SQLException {
                List list=new ArrayList<>();
                try {
                    while (rs.next()){
                        AnaylsisId idData=new AnaylsisId();
                        idData.setNum(rs.getInt("num"));
                        idData.setTestRecordCount(rs.getInt("test_record_count"));
                        idData.setRecordNum(rs.getInt("record_num"));
                        idData.setRecordTime(rs.getString("record_time"));
                        idData.setGroupVol(rs.getFloat("group_vol"));
                        idData.setMonNum(rs.getInt("mon_num"));
                        idData.setMonVol(rs.getFloat("mon_vol"));
                        list.add(idData);
                    }
                } catch (SQLException e) {
                    e.printStackTrace();
                }
                return list;
            }
        });
        return list;
    }
}

 src/main/java/com/whyc/util/AnalysisUtil.java

New file
@@ -0,0 +1,73 @@
package com.whyc.util;

import java.io.BufferedReader;
import java.io.InputStreamReader;
import java.util.ArrayList;
import java.util.List;

public class AnalysisUtil {
    /*String voltagePredictExePath = "D:\\10小时率电压预估\\voltage_predict_process\\main.exe";
    String excelSourcePath = "D:\\10小时率电压预估\\测试数据.xlsx";
    String excelOutPutPath = "D:\\10小时率电压预估\\输出目录";*/
    public  static String voltagePredictExePath="E:\\GitWorkSpace\\fg_v2.0\\target\\10小时率电压预估\\voltage_predict_process\\main.exe";
    public  static String excelOutPutPath="E:\\GitWorkSpace\\fg_v2.0\\target\\outPath";
    public  static String excelSourcePath="E:\\GitWorkSpace\\fg_v2.0\\target\\anaysis\\测试数据.xlsx";

    public static void analysisXlx(String excelSourcePath,String voltagePredictExePath,String excelOutPutPath){
        Process mProcess;
        BufferedReader successResult = null;
        BufferedReader errorResult = null;
        try {
            // 启动进程，进行数据预估
            List<String> command = new ArrayList<>();
            command.add(voltagePredictExePath); // 电压趋势预测执行文件路径
            command.add(String.format("--read=\"%s\"", excelSourcePath)); // 源文件路径
            command.add(String.format("--write=\"%s\"", excelOutPutPath)); // 输出文件夹
            ProcessBuilder processBuilder = new ProcessBuilder(command);
            mProcess = processBuilder.start();
            if (mProcess == null) {
                System.out.println("预测主进程启动失败");
                return;
            }

            successResult = new BufferedReader(new InputStreamReader(mProcess.getInputStream(), "GBK"));
            errorResult = new BufferedReader(new InputStreamReader(mProcess.getErrorStream(), "GBK"));
            String s;
            while ((s = successResult.readLine()) != null) {
                if ((s.length() > 0) && !"\n".equals(s)) {
                    System.out.println(String.format("SuccessMsg：%s", s));
                }
            }
            while ((s = errorResult.readLine()) != null) {
                if ((s.length() > 0) && !"\n".equals(s)) {
                    System.out.println(String.format("ErrorMsg：%s", s));
                }
            }

            if (mProcess != null) {
                mProcess.waitFor();
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
        } finally {
            safeClose(errorResult);
            safeClose(successResult);
        }
    }
    /**
     * 安全的关闭资源
     *
     * @param autoCloseable 资源
     */
    public static void safeClose(AutoCloseable autoCloseable) {
        if (autoCloseable == null)
            return;

        try {
            autoCloseable.close();
        } catch (Exception e) {
            System.out.println(e.getMessage());
        }
    }

}