package com.whyc.service;
|
|
import com.whyc.pojo.db_batt.PowerInf;
|
import com.whyc.pojo.db_dis_batt.BattTestInf;
|
import com.whyc.pojo.db_power_history.BattRealTimeDataHistory;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.ss.util.CellRangeAddress;
|
import org.apache.poi.xssf.usermodel.*;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Service;
|
|
import java.io.FileOutputStream;
|
import java.io.IOException;
|
import java.math.BigDecimal;
|
import java.math.RoundingMode;
|
import java.util.*;
|
import java.util.stream.Collectors;
|
|
@Service
|
public class ExcelService {
|
|
@Autowired
|
private PowerInfService powerInfService;
|
|
@Autowired
|
private BattTestInfService battTestInfService;
|
|
@Autowired
|
//private static BattRealTimeDataHistoryService battRTDataHisService;
|
private BattRealTimeDataHistoryService battRTDataHisService;
|
|
|
/**
|
* 将电池组放电充电数据写入excel
|
* 传入参数:
|
* 电池组id,放电第几次
|
*
|
* @throws IOException
|
*/
|
public void dcReport(int battGroupId,int testRecordCount) throws IOException {
|
String filePath = "C:\\Users\\29550\\Desktop\\当前项目\\2023\\0乌东德系统\\测试.xlsx";
|
//创建一个工作簿
|
XSSFWorkbook workbook = new XSSFWorkbook();
|
//创建sheet1
|
createSheet1(workbook,battGroupId,testRecordCount);
|
|
// 将工作簿写入文件
|
FileOutputStream fileOut = new FileOutputStream(filePath);
|
workbook.write(fileOut);
|
|
// 关闭工作簿
|
workbook.close();
|
|
}
|
|
public static void main(String[] args) throws IOException {
|
//dcReport(1,1);
|
}
|
|
//private static void createSheet1(XSSFWorkbook workbook, int battGroupId, int testRecordCount) {
|
private void createSheet1(XSSFWorkbook workbook, int battGroupId, int testRecordCount) {
|
XSSFSheet sheet = workbook.createSheet("放电充电电压数据报表");
|
//总的列数根据 放电时长和充电时长决定
|
//数据准备
|
PowerInf battInfo = powerInfService.getByBattGroupId(battGroupId);
|
//TODO 测试数据
|
//PowerInf battInfo = new PowerInf();
|
//battInfo.setPowerName("测试直流系统");
|
//battInfo.setBattGroupName("蓄电池组1");
|
BattTestInf battTestInf = battTestInfService.getDischargeRecord(battGroupId,testRecordCount);
|
//TODO 测试数据
|
//BattTestInf battTestInf = new BattTestInf();
|
//battTestInf.setTestTimelong(36000);
|
//battTestInf.setTestStarttime(new Date());
|
//单位是秒,转化为分钟
|
Integer testTimeLong = battTestInf.getTestTimelong();
|
Integer testTimeMinutes = testTimeLong /60;
|
//testTimeLong转为小时.保留1位小数,取小进行切割
|
Float testTimeHours = BigDecimal.valueOf(testTimeLong).divide(BigDecimal.valueOf(60 * 60), 1, RoundingMode.FLOOR).floatValue();
|
//获取放电开始时间和终止时间
|
Date dischargeStartTime = battTestInf.getTestStarttime();
|
//终止时间为开始时间加 测试时长
|
Calendar dischargeEndTime = Calendar.getInstance();
|
dischargeEndTime.setTime(dischargeStartTime);
|
dischargeEndTime.add(Calendar.SECOND, testTimeLong);
|
//放电结束后的充电终止记录
|
//BattRealTimeDataHistory hisChargeEnd = battRTDataHisService.getChargeEnd(battGroupId,dischargeEndTime);
|
//TODO 测试数据
|
//BattRealTimeDataHistory hisChargeEnd = new BattRealTimeDataHistory();
|
//Calendar chargeEndTime = Calendar.getInstance();
|
//chargeEndTime.setTime(new Date());
|
//chargeEndTime.add(Calendar.HOUR,15);
|
//hisChargeEnd.setRecordTime(chargeEndTime.getTime());
|
//充电时长=充电终止时间-放电终止时间
|
long chargeTimeSeconds = (dischargeEndTime.getTime().getTime() - dischargeStartTime.getTime()) / 1000;
|
//long chargeTimeSeconds = (hisChargeEnd.getRecordTime().getTime() - dischargeStartTime.getTime()) / 1000;
|
//chargeTimeSeconds转为小时.保留1位小数,取小进行切割
|
Float chargeTimeHours = BigDecimal.valueOf(chargeTimeSeconds).divide(BigDecimal.valueOf(60 * 60), 1, RoundingMode.FLOOR).floatValue();
|
//行数是固定的.总列数是根据放电时长和充电时长决定的
|
//放电时长导致的列数计算,列为0.5h,1h,2h,3h,...;
|
//小于0.5h,没列. 0.5h~1h之间,1列-0.5h. 大于1h,1+ 放电时长的列数.
|
int dischargeColumnCount;
|
if (testTimeHours < 0.5) {
|
dischargeColumnCount = 0;
|
} else if (testTimeHours >= 0.5 && testTimeHours < 1) {
|
//0.5h 占两列
|
dischargeColumnCount = 2;
|
}
|
else if (testTimeHours >= 1 && testTimeHours < 2) {
|
//0.5h 占两列,1h 占两列
|
dischargeColumnCount = 4;
|
} else {
|
dischargeColumnCount = (int) (testTimeHours + 3);
|
}
|
//充电时长导致的列数计算,列为0.5h,1h,2h,3h,...;
|
int chargeColumnCount;
|
if (chargeTimeHours < 0.5) {
|
chargeColumnCount = 0;
|
} else if (chargeTimeHours >= 0.5 && chargeTimeHours < 1) {
|
chargeColumnCount = 1;
|
} else {
|
chargeColumnCount = (int) (chargeTimeHours + 1);
|
}
|
//列数的计算= 蓄电池号(1行)+浮充电压值(2行)+放电前开路电压(2行)+放电特定时间电压(dischargeColumnCount行)+放电结束0.5h开路电压值(1行)+充电特定时间电压(chargeColumnCount行)+满电开路电压值(1行)+备注(1行)
|
int columnCount = 1 + 2 + 2 + dischargeColumnCount + 1 + chargeColumnCount + 1 + 1;
|
|
//字体加粗样式
|
XSSFCellStyle cellStyleFontBold = workbook.createCellStyle();
|
Font font = workbook.createFont();
|
font.setBold(true);
|
cellStyleFontBold.setFont(font);
|
|
//创建第一行是标题行
|
XSSFCell cellRow1 = sheet.createRow(0).createCell(0);
|
setRowStyle(sheet.getRow(0),cellStyleFontBold);
|
|
cellRow1.setCellValue(battInfo.getPowerName()+battInfo.getBattGroupName()+"核对性放电、充电试验记录表");
|
//合并行列,占据的列是columnCount列合并为1列并居中
|
CellRangeAddress region = new CellRangeAddress(0, 0, 0, columnCount-1);
|
sheet.addMergedRegion(region);
|
|
//TODO 第二行的合并列是根据变动的,后续调整
|
CellRangeAddress region21 = new CellRangeAddress(1, 1, 0, 1);
|
sheet.addMergedRegion(region21);
|
|
CellRangeAddress region22 = new CellRangeAddress(1, 1, 3, 9);
|
sheet.addMergedRegion(region22);
|
|
CellRangeAddress region23 = new CellRangeAddress(1, 1, 10, 13);
|
sheet.addMergedRegion(region23);
|
|
CellRangeAddress region24 = new CellRangeAddress(1, 1, 14, 17);
|
sheet.addMergedRegion(region24);
|
|
CellRangeAddress region25 = new CellRangeAddress(1, 1, 18, 24);
|
sheet.addMergedRegion(region25);
|
|
//第三行的合并列
|
CellRangeAddress region31 = new CellRangeAddress(2, 2, 1, 2);
|
CellRangeAddress region32 = new CellRangeAddress(2, 2, 3, 4);
|
CellRangeAddress region33 = new CellRangeAddress(2, 2, 5, 6);
|
CellRangeAddress region34 = new CellRangeAddress(2, 2, 7, 8);
|
sheet.addMergedRegion(region31);
|
sheet.addMergedRegion(region32);
|
sheet.addMergedRegion(region33);
|
sheet.addMergedRegion(region34);
|
|
// 创建居中样式
|
CellStyle style = workbook.createCellStyle();
|
style.setAlignment(HorizontalAlignment.CENTER);
|
style.setVerticalAlignment(VerticalAlignment.CENTER);
|
// 应用样式到单元格
|
cellRow1.setCellStyle(style);
|
|
//第二行是放电基本信息
|
XSSFRow row1 = sheet.createRow(1);
|
XSSFCell cell1Row2 = row1.createCell(0);
|
cell1Row2.setCellValue("蓄电池组号:" + battInfo.getBattGroupName());
|
|
XSSFCell cell2Row2 = row1.createCell(2);
|
cell2Row2.setCellValue("");
|
|
XSSFCell cell3Row2 = row1.createCell(3);
|
cell3Row2.setCellValue("工作票号：2273925\n" +
|
"工作负责人：马云燕\n" +
|
"工作成员：周素文、李志标、王月灿等");
|
|
XSSFCell cell4Row2 = row1.createCell(10);
|
cell4Row2.setCellValue("放电日期：2021.10.30\n" +
|
"放电开始时间：10:00\n" +
|
"放电结束时间：19:47");
|
|
XSSFCell cell5Row2 = row1.createCell(14);
|
cell5Row2.setCellValue("放电电流（A）：35\n" +
|
"放电时长（时.分）：600min\n" +
|
"放电容量（Ah）：300");
|
|
XSSFCell cell6Row2 = row1.createCell(18);
|
cell6Row2.setCellValue("环境温度（℃）：24.5\n" +
|
"环境湿度（%）：46.7");
|
//第二行设置加粗
|
setRowStyle(sheet.getRow(1),cellStyleFontBold);
|
|
//第三行是电池号和电压等相关抬头
|
XSSFRow row2 = sheet.createRow(2);
|
row2.createCell(0).setCellValue("蓄电池号（#）");
|
row2.createCell(1).setCellValue("蓄电池浮充电压值（V）");
|
row2.createCell(3).setCellValue("放电前蓄电池开路电压值（V）");
|
|
//放电特定电压值
|
int columnIndex = 3;
|
if(dischargeColumnCount > 0){
|
row2.createCell(5).setCellValue("放电0.5h蓄电池电压值（V）");
|
columnIndex+=2;
|
if(dischargeColumnCount > 2){ //合并单元格了,所以索引+2
|
row2.createCell(7).setCellValue("放电1h蓄电池电压值（V）");
|
columnIndex+=2;
|
if(dischargeColumnCount > 4){
|
columnIndex++;
|
//放电nh蓄电池电压值（V）,n从2开始
|
for(int i = 4;i < dischargeColumnCount;i++){
|
row2.createCell(i+5).setCellValue("放电" + (i-2) + "h蓄电池电压值（V）");
|
columnIndex++;
|
}
|
|
}
|
}
|
}
|
|
//充电特定电压值
|
row2.createCell(++columnIndex).setCellValue("放电结束0.5h后蓄电池开路电压值（V）");
|
if(chargeColumnCount > 0){
|
columnIndex++;
|
row2.createCell(columnIndex).setCellValue("充电0.5h");
|
|
if(chargeColumnCount > 1){
|
//充电nh,n从1开始
|
for(int i = 1;i < chargeColumnCount;i++){
|
columnIndex++;
|
row2.createCell(columnIndex).setCellValue("充电" + (i) + "h蓄电池电压值（V）");
|
}
|
|
}
|
}
|
row2.createCell(++columnIndex).setCellValue("充满后蓄电池电压值（V)（电流为0）");
|
row2.createCell(++columnIndex).setCellValue("备注");
|
|
|
//从rowIndex=6开始
|
int rowIndex = 6;
|
// 共104节单体,104行. 进行遍历,填充
|
//蓄电池浮充电压值列表
|
List<BattRealTimeDataHistory> fcVolList = new ArrayList<>();
|
fcVolList = battRTDataHisService.getFcVolList(battGroupId,dischargeStartTime);
|
//按单体编号排序,按编号升序
|
fcVolList.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
|
//放点前蓄电池开路电压值
|
List<BattRealTimeDataHistory> preVolList = new ArrayList<>();
|
preVolList = battRTDataHisService.getPreVolList(battGroupId,dischargeStartTime);
|
|
addStatistics(fcVolList);
|
addStatistics(preVolList);
|
|
//一共104节单体,再加上统计数据4列,一共108
|
for(int j = 0; j < 108; j++){
|
XSSFRow rowTemp = sheet.createRow(rowIndex++);
|
if(j<104) {
|
rowTemp.createCell(0).setCellValue((j + 1) + "#");
|
}else{
|
if(j == 104){
|
rowTemp.createCell(0).setCellValue("单节平均电压");
|
}else if (j == 105){
|
rowTemp.createCell(0).setCellValue("单节最大电压");
|
}else if (j == 106){
|
rowTemp.createCell(0).setCellValue("单节最小电压");
|
}else{
|
rowTemp.createCell(0).setCellValue("单节最大差值");
|
}
|
}
|
//rowTemp.createCell(1).setCellValue("蓄电池浮充电压值（V）:"+(j+1)+"#");
|
//rowTemp.createCell(2).setCellValue("蓄电池浮充温度值:"+(j+1)+"#");
|
//rowTemp.createCell(3).setCellValue("放电前蓄电池开路电压值（V）:"+(j+1)+"#");
|
//rowTemp.createCell(4).setCellValue("放电前蓄电池开路温度值:"+(j+1)+"#");
|
rowTemp.createCell(1).setCellValue(fcVolList.get(j).getMonVol());
|
rowTemp.createCell(1).setCellValue(fcVolList.get(j).getMonTmp());
|
|
rowTemp.createCell(1).setCellValue(preVolList.get(j).getMonVol());
|
rowTemp.createCell(1).setCellValue(preVolList.get(j).getMonTmp());
|
|
|
//放电特定电压值
|
int columnIndexTemp = 3;
|
Calendar dischargeCalendar = Calendar.getInstance();
|
dischargeCalendar.setTime(dischargeStartTime);
|
if(dischargeColumnCount > 0){
|
//时间在测试开始时间+0.5h
|
dischargeCalendar.add(Calendar.MINUTE,30);
|
Date recordTime = dischargeCalendar.getTime();
|
List<BattRealTimeDataHistory> dischargeListOne = battRTDataHisService.getRecordList(battGroupId,recordTime);
|
//按单体编号排序,按编号升序
|
dischargeListOne.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
|
addStatistics(dischargeListOne);
|
//rowTemp.createCell(5).setCellValue("放电0.5h电压值（V）:"+(j+1)+"#");
|
//rowTemp.createCell(6).setCellValue("放电0.5h温度值:"+(j+1)+"#");
|
rowTemp.createCell(5).setCellValue(dischargeListOne.get(j).getMonVol());
|
rowTemp.createCell(6).setCellValue(dischargeListOne.get(j).getMonTmp());
|
columnIndexTemp+=2;
|
if(dischargeColumnCount > 2){ //合并单元格了,所以索引+2
|
//时间在测试开始时间+1h
|
dischargeCalendar.setTime(dischargeStartTime);
|
dischargeCalendar.add(Calendar.HOUR,1);
|
Date recordTimeTwo = dischargeCalendar.getTime();
|
List<BattRealTimeDataHistory> dischargeListTwo = battRTDataHisService.getRecordList(battGroupId,recordTimeTwo);
|
dischargeListTwo.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
|
addStatistics(dischargeListTwo);
|
//rowTemp.createCell(7).setCellValue("放电1h电压值（V）:"+(j+1)+"#");
|
//rowTemp.createCell(8).setCellValue("放电1h温度值:"+(j+1)+"#");
|
rowTemp.createCell(7).setCellValue(dischargeListTwo.get(j).getMonVol());
|
rowTemp.createCell(8).setCellValue(dischargeListTwo.get(j).getMonTmp());
|
columnIndexTemp+=2;
|
if(dischargeColumnCount > 4){
|
columnIndexTemp++;
|
//放电nh蓄电池电压值（V）,n从2开始
|
for(int i = 4;i < dischargeColumnCount;i++){
|
int dischargeHour = i-2;
|
//时间在测试开始时间+nh
|
dischargeCalendar.setTime(dischargeStartTime);
|
dischargeCalendar.add(Calendar.HOUR,dischargeHour);
|
Date recordTimeN = dischargeCalendar.getTime();
|
List<BattRealTimeDataHistory> dischargeListN = battRTDataHisService.getRecordList(battGroupId,recordTimeN);
|
dischargeListN.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
|
addStatistics(dischargeListN);
|
//rowTemp.createCell(i+5).setCellValue("放电" + dischargeHour + "h蓄电池电压值（V）");
|
rowTemp.createCell(i+5).setCellValue(dischargeListN.get(j).getMonVol());
|
columnIndexTemp++;
|
}
|
|
}
|
}
|
}
|
|
//充电特定电压值
|
Calendar dischargeEndCalendar = Calendar.getInstance();
|
dischargeEndCalendar.setTime(dischargeEndTime.getTime());
|
dischargeEndCalendar.add(Calendar.MINUTE,30);
|
List<BattRealTimeDataHistory> dischargeSetVolListOne = battRTDataHisService.getRecordList(battGroupId, dischargeEndCalendar.getTime());
|
dischargeSetVolListOne.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
|
addStatistics(dischargeSetVolListOne);
|
rowTemp.createCell(++columnIndexTemp).setCellValue(dischargeSetVolListOne.get(j).getMonVol());
|
|
//定位到充电开始时间,状态变为充电的记录时间
|
Date chargeStartTime = battRTDataHisService.getChargeStartTime(battGroupId, dischargeEndTime.getTime());
|
if(chargeColumnCount > 0){
|
Calendar chargeCalendar = Calendar.getInstance();
|
chargeCalendar.setTime(chargeStartTime);
|
chargeCalendar.add(Calendar.MINUTE,30);
|
List<BattRealTimeDataHistory> chargeVolListOne = battRTDataHisService.getRecordList(battGroupId, chargeCalendar.getTime());
|
chargeVolListOne.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
|
addStatistics(chargeVolListOne);
|
columnIndexTemp++;
|
//rowTemp.createCell(columnIndexTemp).setCellValue("充电0.5h");
|
rowTemp.createCell(columnIndexTemp).setCellValue(chargeVolListOne.get(j).getMonVol());
|
|
if(chargeColumnCount > 1){
|
//充电nh,n从1开始
|
for(int i = 1;i < chargeColumnCount;i++){
|
columnIndexTemp++;
|
|
chargeCalendar.setTime(chargeStartTime);
|
chargeCalendar.add(Calendar.HOUR,i);
|
List<BattRealTimeDataHistory> chargeVolListN = battRTDataHisService.getRecordList(battGroupId, chargeCalendar.getTime());
|
chargeVolListN.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
|
addStatistics(chargeVolListN);
|
//rowTemp.createCell(columnIndexTemp).setCellValue("充电" + (i) + "h蓄电池电压值（V）");
|
rowTemp.createCell(columnIndexTemp).setCellValue(chargeVolListN.get(j).getMonVol());
|
}
|
|
}
|
}
|
|
Calendar chargeCalendar = Calendar.getInstance();
|
chargeCalendar.setTime(chargeStartTime);
|
List<BattRealTimeDataHistory> fcVolListAfter = battRTDataHisService.getFcVolListAfter(battGroupId, chargeCalendar.getTime());
|
fcVolListAfter.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
|
addStatistics(fcVolListAfter);
|
//rowTemp.createCell(++columnIndexTemp).setCellValue("充满后蓄电池电压值（V)（电流为0）");
|
if (fcVolListAfter.size() > 0){
|
rowTemp.createCell(++columnIndexTemp).setCellValue(fcVolListAfter.get(j).getMonVol());
|
}else {
|
rowTemp.createCell(++columnIndexTemp).setCellValue("-");
|
}
|
rowTemp.createCell(++columnIndexTemp).setCellValue("-");
|
}
|
|
//=====================格式设置=====================//
|
//设置列宽
|
for (int i = 0; i < columnCount; i++) {
|
sheet.setColumnWidth(i, 256 * 20);
|
}
|
//对整个表格进行全局设置,暂为设置边框
|
addGlobalStylesToAllCells(sheet, workbook);
|
}
|
|
//对传入的BattRealTimeDataHistory列表进行统计,将统计结果也追加到列表中
|
public void addStatistics(List<BattRealTimeDataHistory> battRealTimeDataHistoryList) {
|
if(battRealTimeDataHistoryList != null && battRealTimeDataHistoryList.size() > 0){
|
//电压平均值,最大值,最小值,最大差值
|
List<Float> volList = battRealTimeDataHistoryList.stream().map(BattRealTimeDataHistory::getMonVol).collect(Collectors.toList());
|
Double volAvgDouble = volList.stream().collect(Collectors.averagingDouble(Float::floatValue));
|
BigDecimal avg = new BigDecimal(volAvgDouble).setScale(4, RoundingMode.HALF_UP);
|
float volAvg = avg.floatValue();
|
|
//最大值
|
float volMax = volList.stream().max(Float::compareTo).get();
|
BigDecimal max = new BigDecimal(volMax).setScale(4, RoundingMode.HALF_UP);
|
volMax = max.floatValue();
|
|
//最小值
|
float volMin = volList.stream().min(Float::compareTo).get();
|
BigDecimal min = new BigDecimal(volMin).setScale(4, RoundingMode.HALF_UP);
|
volMin = min.floatValue();
|
|
//最大差值
|
float volGap = volMax - volMin;
|
|
BattRealTimeDataHistory hisAvg = new BattRealTimeDataHistory();
|
hisAvg.setMonVol(volAvg);
|
|
BattRealTimeDataHistory hisMax = new BattRealTimeDataHistory();
|
hisMax.setMonVol(volMax);
|
|
BattRealTimeDataHistory hisMin = new BattRealTimeDataHistory();
|
hisMin.setMonVol(volMin);
|
|
BattRealTimeDataHistory hisGap = new BattRealTimeDataHistory();
|
hisGap.setMonVol(volGap);
|
|
battRealTimeDataHistoryList.add(hisAvg);
|
battRealTimeDataHistoryList.add(hisMax);
|
battRealTimeDataHistoryList.add(hisMin);
|
battRealTimeDataHistoryList.add(hisGap);
|
}
|
}
|
|
public void setRowStyle(Row row, CellStyle style) {
|
for (Cell cell : row) {
|
cell.setCellStyle(style);
|
}
|
}
|
|
/**
|
* 这个方法最后执行,用于对已被创建的行内的所有单元格添加边框.能自动识别被创建的所有列和所有单元格
|
* @param sheet
|
* @param workbook
|
*/
|
public void addGlobalStylesToAllCells(Sheet sheet, Workbook workbook) {
|
//所有边框样式
|
CellStyle borderedStyle = workbook.createCellStyle();
|
//borderedStyle.cloneStyleFrom(sheet.getRow(0).getCell(0).getCellStyle()); // 可选：复制已有样式
|
borderedStyle.setBorderTop(BorderStyle.THIN);
|
borderedStyle.setBorderBottom(BorderStyle.THIN);
|
borderedStyle.setBorderLeft(BorderStyle.THIN);
|
borderedStyle.setBorderRight(BorderStyle.THIN);
|
|
for (Row row : sheet) {
|
for (Cell cell : row) {
|
cell.setCellStyle(borderedStyle);
|
}
|
}
|
}
|
|
|
}
|
