package com.whyc.service;
|
|
import com.whyc.dto.FileDirPath;
|
import com.whyc.dto.Response;
|
import com.whyc.pojo.*;
|
import com.whyc.util.ActionUtil;
|
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 org.springframework.web.bind.annotation.RequestParam;
|
|
import javax.imageio.ImageIO;
|
import javax.servlet.http.HttpServletResponse;
|
import java.awt.image.BufferedImage;
|
import java.io.*;
|
import java.net.URLEncoder;
|
import java.text.ParseException;
|
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;
|
|
@Autowired(required = false)
|
private BatttestdataInfService tinfService;
|
|
|
//预估数据
|
public String createAnaysisXls(int battGroupId, int testRecordCount) {
|
UserInf uinf= ActionUtil.getUser();
|
String uid=String.valueOf(uinf.getUId());
|
//1.查出需要预估的数据
|
List<AnaysisData> list=subService.getAnaysisData(battGroupId,testRecordCount);
|
String excelName="测试数据"+"_"+battGroupId+"_"+testRecordCount+".xlsx";
|
//2.生成解析的xls
|
String root=exportDataToXls(uid,excelName,list);
|
String voltagePredictExePath=root+ File.separator+"10小时率电压预估\\voltage_predict_process\\main.exe";
|
String excelOutPutPath=root+ File.separator+"analysis"+File.separator+uid+ File.separator+"outPath"+File.separator+battGroupId+File.separator+testRecordCount;
|
String excelSourcePath=root+ File.separator+"analysis"+File.separator+uid+File.separator+excelName;
|
//4。将预估的数据存入数据库
|
if(list!=null){
|
int recordNum=list.size()+1;
|
//验证分析表是否存在
|
int flag=subService.judgeTable_anaysis(battGroupId,testRecordCount);
|
int insertFlag=0;
|
//检测表是存在后testRecordCount数据是否存在
|
if(flag!=0){
|
int sum=subService.getAnaysisIdByTestRecordCount(battGroupId,testRecordCount);
|
if(sum==0) {
|
insertFlag=1;
|
}
|
}else{
|
//表不存在则创建表
|
subService.creatAnasys(battGroupId);
|
insertFlag=1;
|
}
|
if(insertFlag!=0){
|
//3.生成预估的文件xls
|
AnalysisUtil.analysisXlx(excelSourcePath,voltagePredictExePath,excelOutPutPath);
|
//补前面的数据
|
subService.insertBeforeData(battGroupId,testRecordCount,list);
|
|
storeAnaysisToSql(battGroupId,testRecordCount,recordNum,excelOutPutPath,insertFlag);
|
}
|
|
}
|
return voltagePredictExePath;
|
}
|
//将预估的数据存入数据库
|
private void storeAnaysisToSql(int battGroupId,int testRecordCount,int recordNum,String excelSourcePath,int insertFlag) {
|
//获取文件夹下所有的数据
|
List<String> fileList=getFileNameWithOutDirectory(excelSourcePath);
|
List<AnaylsisId> groupVolList=new ArrayList<>();
|
if(insertFlag!=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);
|
}
|
}
|
}
|
//解析单体的单个文件
|
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 uid, 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.separator+"analysis";
|
File destfile = new File(rootFace);
|
if(!destfile.exists()) {
|
destfile.mkdir();
|
}
|
rootFace=fileDirName+File.separator+"analysis"+File.separator+uid;
|
File destfile1 = new File(rootFace);
|
if(!destfile1.exists()) {
|
destfile1.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 fileDirName;
|
}
|
|
//查询预估数据
|
public Response getAnaysisXls(int battGroupId, int testRecordCount) {
|
List<AnaylsisId> list=subService.getAnaysisXls(battGroupId,testRecordCount);
|
return new Response().setII(1,list!=null,list,"查询预估数据");
|
}
|
//解析xls文件到放电数据中
|
public Response<Object> anaysisXlsToTdata(int battGroupId,InputStream inputStream) throws IOException, InvalidFormatException, ParseException {
|
Workbook workbook = null;
|
workbook = WorkbookFactory.create(inputStream);
|
inputStream.close();
|
//取第一个sheet表
|
Sheet sheet = workbook.getSheetAt(2);
|
int lastRowNum = sheet.getLastRowNum();
|
Row rowTemp = sheet.getRow(0);
|
short lastCellNum = rowTemp.getLastCellNum();
|
DataFormatter dataFormatter = new DataFormatter(Locale.US);
|
List<XlsToTdata> list=new ArrayList<>();
|
for (int i=1;i<lastRowNum;i++){
|
XlsToTdata tData=new XlsToTdata();
|
Row row = sheet.getRow(i);
|
Cell cell0 = row.getCell(0);
|
String formattedValue = dataFormatter.formatCellValue(cell0);
|
tData.setRecordTime(ActionUtil.parseDurationDirectly(formattedValue));
|
|
tData.setTestTimelong(ActionUtil.convertToSeconds(formattedValue));
|
|
Cell cell1 = row.getCell(1);
|
cell1.setCellType(Cell.CELL_TYPE_NUMERIC);
|
tData.setGroupVol((float) cell1.getNumericCellValue());
|
|
Cell cell2 = row.getCell(2);
|
cell2.setCellType(Cell.CELL_TYPE_NUMERIC);
|
tData.setGroupCurr((float) cell2.getNumericCellValue());
|
|
Cell cell3 = row.getCell(3);
|
cell3.setCellType(Cell.CELL_TYPE_NUMERIC);
|
tData.setTestCap((float) cell3.getNumericCellValue());
|
|
List<Float> monVols=new ArrayList<>();
|
for (int j=4;j<lastCellNum;j++){
|
Cell cell = row.getCell(j);
|
cell.setCellType(Cell.CELL_TYPE_NUMERIC);
|
monVols.add((float) cell.getNumericCellValue());
|
}
|
tData.setMonVols(monVols);
|
list.add(tData);
|
}
|
|
//1.获取当前最大的放电信息testRecordCount
|
BatttestdataInf tinf=tinfService.getMaxRecord(battGroupId);
|
int testRecordCount=0;
|
if(tinf!=null) {
|
testRecordCount = tinf.getTestRecordCount();
|
}
|
|
|
//3.将tinf补齐
|
tinf=new BatttestdataInf();
|
tinf.setBattGroupId(battGroupId);
|
tinf.setDataNew((byte) 1);
|
tinf.setDataAvailable((byte) 1);
|
tinf.setTestType(3);
|
tinf.setRecordTimeInterval(10);
|
tinf.setTestRecordCount(testRecordCount+1);
|
tinf.setTestRecordCountEx(testRecordCount+2);
|
tinf.setRecordNum(list.size()+1);
|
tinf.setTestStarttime(new Date());
|
tinf.setRecordTime(new Date());
|
tinf.setTestStarttype(3);
|
tinfService.insertTinf(tinf);
|
|
//2.将数据插入电池组数据
|
subService.insertAnaysisXlsToTdata(battGroupId,testRecordCount+1,list);
|
return new Response<>().set(1);
|
}
|
|
|
}
|
