package com.whyc.service;
|
|
import com.whyc.mapper.CommonMapper;
|
import com.whyc.pojo.db_batt.PowerInf;
|
import com.whyc.pojo.db_dis_batt.BattTestInf;
|
import com.whyc.pojo.db_power_history.BattRealTimeDataHistory;
|
import com.whyc.util.DateUtil;
|
import org.apache.poi.ss.usermodel.*;
|
import org.apache.poi.ss.util.CellRangeAddress;
|
import org.apache.poi.ss.util.CellReference;
|
import org.apache.poi.xddf.usermodel.CompoundLine;
|
import org.apache.poi.xddf.usermodel.XDDFLineProperties;
|
import org.apache.poi.xddf.usermodel.XDDFShapeProperties;
|
import org.apache.poi.xddf.usermodel.chart.*;
|
import org.apache.poi.xssf.usermodel.*;
|
import org.openxmlformats.schemas.drawingml.x2006.chart.*;
|
import org.openxmlformats.schemas.drawingml.x2006.main.*;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Service;
|
|
import javax.servlet.http.HttpServletResponse;
|
import java.io.FileOutputStream;
|
import java.io.IOException;
|
import java.math.BigDecimal;
|
import java.math.RoundingMode;
|
import java.nio.charset.StandardCharsets;
|
import java.text.SimpleDateFormat;
|
import java.util.*;
|
import java.util.concurrent.atomic.AtomicInteger;
|
import java.util.stream.Collectors;
|
import java.util.stream.Stream;
|
|
@Service
|
public class ExcelService {
|
|
@Autowired
|
private PowerInfService powerInfService;
|
|
@Autowired
|
private BattTestInfService battTestInfService;
|
|
@Autowired
|
//private static BattRealTimeDataHistoryService battRTDataHisService;
|
private BattRealTimeDataHistoryService battRTDataHisService;
|
|
@Autowired
|
private CommonMapper commonMapper;
|
|
|
/**
|
* 将电池组放电充电数据写入excel
|
* 传入参数:
|
* 电池组id,放电第几次
|
*
|
* @throws IOException
|
*/
|
public void dcReport(int battGroupId, int testRecordCount, HttpServletResponse response) throws IOException {
|
//String filePath = "C:\\Users\\29550\\Desktop\\当前项目\\2023\\0乌东德系统\\测试.xlsx";
|
//创建一个工作簿
|
XSSFWorkbook workbook = new XSSFWorkbook();
|
//创建sheet1
|
PowerInf battInfo = createSheet1(workbook, battGroupId, testRecordCount);
|
//创建sheet2 -- 每月电压,12个月,取每月1号零点零分
|
//创建sheet3 -- 每月内阻, 12个月,取每月1号零点零分
|
createSheet2And3(workbook,battGroupId);
|
//创建sheet4 -- 每天温度, 30天, 取每天零点零分
|
createSheet4(workbook,battGroupId);
|
|
/*// 将工作簿写入文件
|
FileOutputStream fileOut = new FileOutputStream(filePath);
|
workbook.write(fileOut);*/
|
String filename ="直流报表_"+battInfo.getPowerName()+"_"+battInfo.getBattGroupName()+".xlsx";
|
// 根据浏览器客户端做不同编码，兼容性更好
|
String encodedFilename = java.net.URLEncoder.encode(filename, String.valueOf(StandardCharsets.UTF_8));
|
boolean isMSBrowser = false;
|
String ua = response.getHeader("User-Agent");
|
if (ua != null) {
|
ua = ua.toLowerCase();
|
if (ua.contains("msie") || ua.contains("edge") || ua.contains("trident")) {
|
isMSBrowser = true;
|
}
|
}
|
|
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
|
if (isMSBrowser) {
|
// IE/Edge 使用 UTF-8 编码并用 URLEncoder
|
response.setHeader("Content-Disposition", "attachment;filename=" + encodedFilename + ";filename*=utf-8''" + encodedFilename);
|
} else {
|
// Chrome/Firefox 等使用 RFC 5987 编码方式
|
response.setHeader("Content-Disposition", "attachment;filename*=UTF-8''" + encodedFilename);
|
}
|
|
|
workbook.write(response.getOutputStream());
|
// 关闭工作簿
|
workbook.close();
|
|
}
|
|
private void createSheet2And3(XSSFWorkbook workbook, int battGroupId) {
|
XSSFSheet sheet2 = workbook.createSheet("每月电压");
|
XSSFSheet sheet3 = workbook.createSheet("每月内阻");
|
//数据准备
|
PowerInf battInfo = powerInfService.getByBattGroupId(battGroupId);
|
//创建第一行
|
XSSFCell cellOfSheet2 = sheet2.createRow(0).createCell(0);
|
XSSFCell cellOfSheet3 = sheet3.createRow(0).createCell(0);
|
cellOfSheet2.setCellValue( battInfo.getPowerName()+ battInfo.getBattGroupName()+"浮充电压");
|
cellOfSheet3.setCellValue( battInfo.getPowerName()+ battInfo.getBattGroupName()+"内阻数据");
|
|
int columnCount = 2;
|
//查询历史实时表. 存在的月份. 如果存在的话,取第一笔记录(104条)
|
//首先查询当前月份对应的日期
|
List<String> tableListLike = commonMapper.getTableListLike("db_power_history", "tb_batt_realdata_"+battGroupId);
|
//当前月份往前推11个月
|
List<String> tableListInDB = new ArrayList<>();
|
Calendar calendar = Calendar.getInstance();
|
for (int i = 0; i < 12; i++) {
|
int year = calendar.get(Calendar.YEAR);
|
int month = calendar.get(Calendar.MONTH) + 1;
|
String monthStr;
|
if (month < 10) {
|
monthStr = "0" + month;
|
} else {
|
monthStr = String.valueOf(month);
|
}
|
if (tableListLike.contains("tb_batt_realdata_" + battGroupId + "_" + year + "_" + monthStr)) {
|
//存在,列数+1
|
columnCount++;
|
tableListInDB.add("tb_batt_realdata_" + battGroupId + "_" + year + "_" + monthStr);
|
}
|
calendar.add(Calendar.MONTH, -1);
|
}
|
|
//列根据实际情况变动
|
sheet2.addMergedRegion(new CellRangeAddress(0, 0, 0, columnCount-1));
|
sheet3.addMergedRegion(new CellRangeAddress(0, 0, 0, columnCount-1));
|
//列宽
|
for (int j = 0; j < columnCount; j++ ) {
|
if(j==0){
|
sheet2.setColumnWidth(j, 170 * 20);
|
sheet3.setColumnWidth(j, 170 * 20);
|
}else {
|
sheet2.setColumnWidth(j, 128 * 20);
|
sheet3.setColumnWidth(j, 128 * 20);
|
}
|
}
|
//行宽
|
sheet2.getRow(0).setHeightInPoints(40);
|
sheet3.getRow(0).setHeightInPoints(40);
|
//List元素升序排序
|
tableListInDB.sort(Comparator.comparing(String::toString));
|
|
//创建第二行
|
XSSFRow row1OfSheet2 = sheet2.createRow(1);
|
XSSFRow row1OfSheet3 = sheet3.createRow(1);
|
row1OfSheet2.createCell(0).setCellValue("蓄电池号（#）");
|
row1OfSheet3.createCell(0).setCellValue("蓄电池号（#）");
|
//总列数-1
|
for (int i = 0; i < columnCount - 1; i++) {
|
if(i == columnCount-2){
|
row1OfSheet2.createCell(i+1).setCellValue("备注");
|
row1OfSheet3.createCell(i+1).setCellValue("备注");
|
}else {
|
row1OfSheet2.createCell(i + 1).setCellValue("记录" + (i + 1));
|
row1OfSheet3.createCell(i + 1).setCellValue("记录" + (i + 1));
|
}
|
}
|
|
//填充数据
|
for (int i = 1; i <= 109; i++) {
|
if(i<=104) {
|
sheet2.createRow(i + 2).createCell(0).setCellValue(i + "#");
|
sheet3.createRow(i + 2).createCell(0).setCellValue(i + "#");
|
}else{
|
if(i == 105){
|
sheet2.createRow(i + 2).createCell(0).setCellValue("单节平均电压");
|
sheet3.createRow(i + 2).createCell(0).setCellValue("单节平均内阻");
|
}else if (i == 106){
|
sheet2.createRow(i + 2).createCell(0).setCellValue("单节最大电压");
|
sheet3.createRow(i + 2).createCell(0).setCellValue("单节最大内阻");
|
} else if (i == 107) {
|
sheet2.createRow(i + 2).createCell(0).setCellValue("单节最小电压");
|
sheet3.createRow(i + 2).createCell(0).setCellValue("单节最小内阻");
|
}else if (i == 108) {
|
sheet2.createRow(i + 2).createCell(0).setCellValue("单节最大差值");
|
sheet3.createRow(i + 2).createCell(0).setCellValue("单节最大内阻差值");
|
}else {
|
sheet2.createRow(i + 2).createCell(0).setCellValue("均压系数");
|
}
|
}
|
}
|
//创建第三行
|
XSSFRow row2OfSheet2 = sheet2.createRow(2);
|
XSSFRow row2OfSheet3 = sheet3.createRow(2);
|
|
sheet2.getRow(0).createCell(columnCount-1);
|
sheet3.getRow(0).createCell(columnCount-1);
|
for (int i = 0; i <= tableListInDB.size(); i++) { //这个是列的遍历
|
if (i == tableListInDB.size()) {
|
//最后一列备注:全部填空
|
row2OfSheet2.createCell(i + 1);
|
row2OfSheet3.createCell(i + 1);
|
for (int j = 1; j <= 108; j++) { //这个是行的遍历
|
sheet2.getRow(j + 2).createCell(i + 1);
|
sheet3.getRow(j + 2).createCell(i + 1);
|
}
|
sheet2.getRow(108 + 2 + 1).createCell(i + 1);
|
} else {
|
//获取数据
|
List<BattRealTimeDataHistory> list = battRTDataHisService.getFirstRecordList(tableListInDB.get(i));
|
list.sort(Comparator.comparing(BattRealTimeDataHistory::getMonNum));
|
//第三行的时间列填充
|
Date testStartTime = list.get(0).getTestStartTime();
|
//获取年月
|
String testMonth = DateUtil.YYYY_DOT_MM.format(testStartTime);
|
row2OfSheet2.createCell(i + 1).setCellValue(testMonth);
|
row2OfSheet3.createCell(i + 1).setCellValue(testMonth);
|
addStatistics(list, "vol", "res");
|
for (int j = 1; j <= 108; j++) { //这个是行的遍历
|
sheet2.getRow(j + 2).createCell(i + 1).setCellValue((list.get(j - 1).getMonVol()));
|
sheet3.getRow(j + 2).createCell(i + 1).setCellValue((list.get(j - 1).getMonRes()));
|
}
|
Float volAvg = list.get(104).getMonVol();
|
Float volMax = list.get(105).getMonVol();
|
sheet2.getRow(108 + 2 + 1).createCell(i + 1).setCellValue(new BigDecimal(volAvg).divide(new BigDecimal(volMax), 4, RoundingMode.HALF_UP).floatValue());
|
}
|
}
|
|
addGlobalStylesToAllCells(sheet2, workbook);
|
addGlobalStylesToAllCells(sheet3, workbook);
|
|
//设置sheet2和sheet3的标题样式
|
XSSFCellStyle cellStyleOriginal = cellOfSheet2.getCellStyle();
|
XSSFCellStyle cellStyleNew = getCellStyleFont(cellStyleOriginal,workbook, true, 15);
|
cellOfSheet2.setCellStyle(cellStyleNew);
|
|
XSSFCellStyle cellStyleOriginal2 = cellOfSheet3.getCellStyle();
|
XSSFCellStyle cellStyleNew2 = getCellStyleFont(cellStyleOriginal2,workbook, true, 15);
|
cellOfSheet3.setCellStyle(cellStyleNew2);
|
|
//设置数值类型保留位数
|
setDateFormat(workbook,sheet2,3,110,1,columnCount-2, "0.0000");
|
setDateFormat(workbook,sheet3,3,110,1,columnCount-2, "0.0000");
|
|
|
// 创建绘图工具
|
createChart(sheet2, columnCount,3,"电压折线图");
|
createChart(sheet3, columnCount,3,"内阻折线图");
|
|
}
|
|
private void createChart(XSSFSheet sheet2, int columnCount,int rowStartIndex, String titleTextSuffix) {
|
XSSFDrawing drawing = sheet2.createDrawingPatriarch();
|
//108个单体,10个单体一个图标
|
int chartCol = 1;
|
|
for (int i = 0; i < 104; i += 10) {
|
//XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, 15, chartCol, 25, chartCol+12);
|
XSSFClientAnchor anchor = drawing.createAnchor(0, 0, 0, 0, columnCount+1, chartCol, columnCount+11, chartCol+12);
|
//图表间每十二列一个间隔
|
chartCol += 12;
|
String titleText;
|
if(i == 100){
|
titleText = "101~104#蓄电池"+titleTextSuffix;
|
}else{
|
titleText = (i+1)+"~"+(i+10)+"#蓄电池"+titleTextSuffix;
|
}
|
// 创建图表
|
XSSFChart chart = drawing.createChart(anchor);
|
//chart.setTitleText(titleText);
|
CTChart ctChart = chart.getCTChart();
|
CTTitle title = ctChart.getTitle();
|
if (title == null) {
|
title = ctChart.addNewTitle();
|
}
|
// 创建文本体（如果不存在）
|
CTTx tx = title.addNewTx();
|
CTTextBody txBody = tx.isSetStrRef() ? null : tx.addNewRich();
|
|
if (txBody == null) {
|
txBody = tx.getRich();
|
}
|
|
// 清空已有段落
|
txBody.setPArray(new CTTextParagraph[0]);
|
|
// 添加新的段落和文本运行
|
CTTextParagraph p = txBody.addNewP();
|
CTRegularTextRun r = p.addNewR();
|
r.setT(titleText);
|
|
// 设置字体大小（14pt -> 1400）
|
CTTextCharacterProperties rPr = r.addNewRPr();
|
rPr.setSz(900); // 字号：14pt
|
// 设置坐标轴
|
XDDFCategoryAxis categoryAxis = chart.createCategoryAxis(AxisPosition.BOTTOM);
|
//categoryAxis.setTitle("电池编号");
|
CTCatAx ctCatAx = getCTCatAx(categoryAxis, chart);
|
if(ctCatAx.isSetTitle()){
|
ctCatAx.unsetTitle();
|
}
|
|
XDDFValueAxis valueAxis = chart.createValueAxis(AxisPosition.LEFT);
|
valueAxis.setCrosses(AxisCrosses.AUTO_ZERO);
|
CTValAx ctValAx = getCTValAx(valueAxis, chart);
|
if(ctValAx.isSetTitle()){
|
ctValAx.unsetTitle();
|
}
|
|
//增加 网格线和图例
|
XDDFShapeProperties gridProperties = valueAxis.getOrAddMajorGridProperties();
|
XDDFLineProperties lineProperties = new XDDFLineProperties();
|
lineProperties.setCompoundLine(CompoundLine.SINGLE);
|
lineProperties.setWidth(0.5);
|
gridProperties.setLineProperties(lineProperties);
|
|
chart.getOrAddLegend().setPosition(LegendPosition.TOP_RIGHT);
|
chart.setTitleOverlay(true);
|
|
// X轴数据源-记录编号
|
//XDDFDataSource<String> xs = XDDFDataSourcesFactory.fromStringCellRange(sheet2, new CellRangeAddress(2, 2, 1, 12));
|
XDDFDataSource<String> xs = XDDFDataSourcesFactory.fromStringCellRange(sheet2, new CellRangeAddress(2, 2, 1, columnCount-2));
|
// 每个电池单体作为一个系列,对应一条折线
|
// 创建折线图系列
|
XDDFLineChartData dataChart = (XDDFLineChartData) chart.createData(ChartTypes.LINE, categoryAxis, valueAxis);
|
for (int j = i ; j < i+10; j++) {
|
if(j<104) {
|
XDDFNumericalDataSource<Double> ys = XDDFDataSourcesFactory.fromNumericCellRange(sheet2, new CellRangeAddress(j + rowStartIndex, j + rowStartIndex, 1, columnCount -2));
|
XDDFLineChartData.Series series = (XDDFLineChartData.Series) dataChart.addSeries(xs, ys);
|
//选择布局1
|
series.setTitle(((XSSFRow) sheet2.getRow(j + 6)).getCell(0).getStringCellValue(), new CellReference(sheet2.getSheetName(), j + rowStartIndex, 0, true, true));
|
series.setMarkerStyle(MarkerStyle.CIRCLE); // 设置标记点
|
}
|
|
}
|
|
// 将数据填充到图表中
|
chart.plot(dataChart);
|
}
|
}
|
|
private void createSheet4(XSSFWorkbook workbook, int battGroupId) {
|
XSSFSheet sheet4 = workbook.createSheet("每天温度");
|
//数据准备
|
PowerInf battInfo = powerInfService.getByBattGroupId(battGroupId);
|
//创建第一行
|
XSSFCell cellOfSheet4 = sheet4.createRow(0).createCell(0);
|
cellOfSheet4.setCellValue( battInfo.getPowerName()+ battInfo.getBattGroupName()+"温度数据");
|
|
//30天+2列其他列
|
int columnCount = 30+2;
|
//查询历史实时表. 存在的月份. 如果存在的话,取第一笔记录(104条)
|
//首先查询当前月份对应的日期
|
List<String> tableListLike = commonMapper.getTableListLike("db_power_history", "tb_batt_realdata_"+battGroupId);
|
//获取当前日期往前30天的凌晨零秒的数据. 往前推30天
|
List<String> tableListInDB = new ArrayList<>();
|
Calendar calendar = Calendar.getInstance();
|
int year = calendar.get(Calendar.YEAR);
|
int month = calendar.get(Calendar.MONTH) + 1;
|
String monthStr;
|
if (month < 10) {
|
monthStr = "0" + month;
|
} else {
|
monthStr = String.valueOf(month);
|
}
|
//当前时间往前30天
|
calendar.add(Calendar.DAY_OF_MONTH, -30);
|
Date timeAgo = calendar.getTime();
|
int yearAgo = calendar.get(Calendar.YEAR);
|
int monthAgo = calendar.get(Calendar.MONTH) + 1;
|
String monthAgoStr;
|
if (monthAgo < 10) {
|
monthAgoStr = "0" + (monthAgo);
|
} else {
|
monthAgoStr = String.valueOf(monthAgo);
|
}
|
String tableName = "tb_batt_realdata_" + battGroupId + "_" + year + "_" + monthStr;
|
//30天前的时间
|
String tableNameAgo = "tb_batt_realdata_" + battGroupId + "_" + yearAgo + "_" + monthAgoStr;
|
if (tableListLike.contains(tableName)) {
|
tableListInDB.add(tableName);
|
}
|
if (tableListLike.contains(tableNameAgo)) {
|
tableListInDB.add(tableNameAgo);
|
}
|
|
//获取所有数据
|
List<BattRealTimeDataHistory> dataList = new ArrayList<>();
|
for (int i = 0; i < tableListInDB.size(); i++) {
|
String tableNameTmp = tableListInDB.get(i);
|
List<BattRealTimeDataHistory> list;
|
if(tableNameTmp.equals(tableName)){
|
list = battRTDataHisService.getFirstRecordListOfDay(tableNameTmp, null);
|
}else{
|
//说明是上个月的记录,需要记录时间大于30天前的时间
|
list = battRTDataHisService.getFirstRecordListOfDay(tableNameTmp, timeAgo);
|
}
|
dataList.addAll(list);
|
}
|
//按时间排序和分组
|
dataList.sort(Comparator.comparing(BattRealTimeDataHistory::getRecordTime));
|
Map<Date, List<BattRealTimeDataHistory>> dataListMap = dataList.stream().collect(Collectors.groupingBy(BattRealTimeDataHistory::getRecordTime));
|
Set<Date> dateKeySet = dataListMap.keySet();
|
dataListMap.forEach((key,data)->{
|
addStatistics(data, "tmp");
|
});
|
//给列数赋值
|
columnCount = dateKeySet.size()+2;
|
|
//列根据实际情况变动
|
sheet4.addMergedRegion(new CellRangeAddress(0, 0, 0, columnCount-1));
|
//列宽
|
for (int j = 0; j < columnCount; j++ ) {
|
if(j==0){
|
sheet4.setColumnWidth(j, 170 * 20);
|
}else {
|
sheet4.setColumnWidth(j, 138 * 20);
|
}
|
}
|
//行宽
|
sheet4.getRow(0).setHeightInPoints(40);
|
//List元素升序排序
|
tableListInDB.sort(Comparator.comparing(String::toString));
|
|
//创建第二行
|
XSSFRow row1OfSheet4 = sheet4.createRow(1);
|
row1OfSheet4.createCell(0).setCellValue("蓄电池号（#）");
|
//总列数-1
|
for (int i = 0; i < columnCount - 1; i++) {
|
if(i == columnCount-2){
|
row1OfSheet4.createCell(i+1).setCellValue("备注");
|
}else {
|
row1OfSheet4.createCell(i + 1).setCellValue("记录" + (i + 1));
|
}
|
}
|
|
//填充数据
|
for (int i = 1; i <= 109; i++) {
|
if(i<=104) {
|
sheet4.createRow(i + 2).createCell(0).setCellValue(i + "#");
|
}else{
|
if(i == 105){
|
sheet4.createRow(i + 2).createCell(0).setCellValue("单节平均温度");
|
}else if (i == 106){
|
sheet4.createRow(i + 2).createCell(0).setCellValue("单节最大温度");
|
} else if (i == 107) {
|
sheet4.createRow(i + 2).createCell(0).setCellValue("单节最小温度");
|
}else if (i == 108) {
|
sheet4.createRow(i + 2).createCell(0).setCellValue("单节最大差值");
|
}
|
}
|
}
|
//创建第三行
|
XSSFRow row2OfSheet4 = sheet4.createRow(2);
|
row2OfSheet4.setHeightInPoints(40);
|
sheet4.getRow(0).createCell(columnCount-1);
|
|
//填充数据列,key是列日期. value是列数据
|
AtomicInteger count = new AtomicInteger();
|
//对dateKeySet按时间排序
|
Stream<Date> dateKeySetSorted = dateKeySet.stream().sorted(Comparator.comparing(Date::getTime));
|
dateKeySetSorted.forEach(key -> {
|
List<BattRealTimeDataHistory> value = dataListMap.get(key);
|
//第三行时间列填充
|
SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd\nHH:mm:ss");
|
row2OfSheet4.createCell(count.get() + 1).setCellValue(format.format(key));
|
for (int j = 1; j <= 108; j++) { //这个是行的遍历
|
sheet4.getRow(j + 2).createCell(count.get() + 1).setCellValue(value.get(j - 1).getMonTmp());
|
}
|
count.getAndIncrement();
|
});
|
//最后一列备注:全部填空
|
row2OfSheet4.createCell(columnCount-1);
|
for (int j = 1; j <= 108; j++) { //这个是行的遍历
|
sheet4.getRow(j + 2).createCell(columnCount-1);
|
}
|
|
addGlobalStylesToAllCells(sheet4, workbook);
|
|
//设置sheet4和sheet3的标题样式
|
XSSFCellStyle cellStyleOriginal = cellOfSheet4.getCellStyle();
|
XSSFCellStyle cellStyleNew = getCellStyleFont(cellStyleOriginal,workbook, true, 15);
|
cellOfSheet4.setCellStyle(cellStyleNew);
|
|
//第三行 所有列水平垂直居中,自动换行
|
for (int i = 1; i < columnCount; i++) {
|
XSSFCell cell = sheet4.getRow(2).getCell(i);
|
XSSFCellStyle cellStyle = cell.getCellStyle();
|
cellStyle.setAlignment(HorizontalAlignment.CENTER);
|
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
|
cellStyle.setWrapText(true);
|
cell.setCellStyle(cellStyle);
|
}
|
|
//设置数值类型单元格保留位数
|
setDateFormat(workbook,sheet4,3,110,1,columnCount-2, "0.0000");
|
createChart(sheet4, columnCount,3,"温度折线图");
|
|
}
|
public CTValAx getCTValAx(XDDFValueAxis valueAxis, XSSFChart chart) {
|
// 使用反射获取私有字段
|
try {
|
java.lang.reflect.Field field = valueAxis.getClass().getDeclaredField("ctValAx");
|
field.setAccessible(true);
|
return (CTValAx) field.get(valueAxis);
|
} catch (Exception e) {
|
throw new RuntimeException("无法获取 CTValAx 实例", e);
|
}
|
}
|
|
public CTCatAx getCTCatAx(XDDFCategoryAxis categoryAxis, XSSFChart chart) {
|
// 使用反射获取私有字段
|
try {
|
java.lang.reflect.Field field = categoryAxis.getClass().getDeclaredField("ctCatAx");
|
field.setAccessible(true);
|
return (CTCatAx) field.get(categoryAxis);
|
} catch (Exception e) {
|
throw new RuntimeException("无法获取 CTCatAx 实例", e);
|
}
|
}
|
|
public static void main(String[] args) throws IOException {
|
//dcReport(1,1);
|
}
|
|
//private static void createSheet1(XSSFWorkbook workbook, int battGroupId, int testRecordCount) {
|
private PowerInf 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 = getCellStyleFont(cellStyleOriginal, workbook,true,20);
|
|
//创建第一行是标题行
|
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);
|
|
//第三行,第五行,第六行的合并列,前两个单元模块是固定的,其中1个合并单元
|
CellRangeAddress region31 = new CellRangeAddress(2, 2, 1, 2);
|
CellRangeAddress region32 = new CellRangeAddress(2, 2, 3, 4);
|
CellRangeAddress region51 = new CellRangeAddress(4, 4, 1, 2);
|
CellRangeAddress region52 = new CellRangeAddress(4, 4, 3, 4);
|
CellRangeAddress region61 = new CellRangeAddress(5, 5, 1, 2);
|
CellRangeAddress region62 = new CellRangeAddress(5, 5, 3, 4);
|
|
sheet.addMergedRegion(region31);
|
sheet.addMergedRegion(region32);
|
sheet.addMergedRegion(region51);
|
sheet.addMergedRegion(region52);
|
sheet.addMergedRegion(region61);
|
sheet.addMergedRegion(region62);
|
|
// 创建居中样式
|
CellStyle style = workbook.createCellStyle();
|
style.setAlignment(HorizontalAlignment.CENTER);
|
style.setVerticalAlignment(VerticalAlignment.CENTER);
|
// 应用样式到单元格
|
cellRow1.setCellStyle(style);
|
|
//第二行是放电基本信息
|
//因为是动态的,没办法像模板一样.
|
//蓄电池组号:第一列
|
//工作票信息:二三列
|
//放电日期:第四列起步-放电结束静置0.5h
|
//放电电流,时长,容量:放电结束后静置0.5h起到充满电
|
//环境温湿度:备注
|
XSSFRow row1 = sheet.createRow(1);
|
XSSFCell cell1Row2 = row1.createCell(0);
|
cell1Row2.setCellValue("蓄电池组号:" + battInfo.getBattGroupName());
|
|
|
XSSFCell cell2Row2 = row1.createCell(1);
|
cell2Row2.setCellValue("工作票号：2273925\n" +
|
"工作负责人：马云燕\n" +
|
"工作成员：周素文、李志标、王月灿等");
|
|
XSSFCell cell3Row2 = row1.createCell(3);
|
cell3Row2.setCellValue("放电日期："+ DateUtil.YYYY_DOT_MM_DOT_DD.format(dischargeStartTime) +"\n" +
|
"放电开始时间："+ DateUtil.YYYY_MM_DD_HH_MM.format(dischargeStartTime) +"\n" +
|
"放电结束时间："+ DateUtil.YYYY_MM_DD_HH_MM.format(dischargeEndTime.getTime()));
|
|
XSSFCell cell4Row2 = row1.createCell(5);
|
cell4Row2.setCellValue("放电电流（A）："+ Math.abs(battTestInf.getTestCurr())+"\n" +
|
"放电时长（时.分）："+testTimeMinutes+"\n" +
|
"放电容量（Ah）："+Math.abs(battTestInf.getTestCap()));
|
|
XSSFCell cell5Row2 = row1.createCell(columnCount-1);
|
cell5Row2.setCellValue("环境温度（℃）：24.5\n" +
|
"环境湿度（%）：46.7");
|
|
//二行的合并列是根据变动的
|
row1.setHeightInPoints(57);
|
sheet.getRow(0).setHeightInPoints(42);
|
CellRangeAddress region22 = new CellRangeAddress(1, 1, 1, 2);
|
sheet.addMergedRegion(region22);
|
|
CellRangeAddress region23 = new CellRangeAddress(1, 1, 3, 4);
|
sheet.addMergedRegion(region23);
|
|
CellRangeAddress region24 = new CellRangeAddress(1, 1, 5, columnCount-2);
|
sheet.addMergedRegion(region24);
|
|
//第三行是电池号和电压等相关抬头
|
//第四行是电压列和温度列标识
|
XSSFRow row2 = sheet.createRow(2);
|
XSSFRow row3 = sheet.createRow(3);
|
|
row2.createCell(0).setCellValue("蓄电池号（#）");
|
row2.createCell(1).setCellValue("蓄电池浮充电压值（V）");
|
row2.createCell(3).setCellValue("放电前蓄电池开路电压值（V）");
|
|
row3.createCell(0).setCellValue("项目");
|
row3.createCell(1).setCellValue("-1（电压）");
|
row3.createCell(2).setCellValue("-1（温度）");
|
row3.createCell(3).setCellValue("0（电压）");
|
row3.createCell(4).setCellValue("0（温度）");
|
|
//放电特定电压值
|
int columnIndex = 5;
|
if(dischargeColumnCount > 0){
|
//row2.createCell(5).setCellValue("放电0.5h蓄电池电压值（V）");
|
row2.createCell(columnCount).setCellValue("放电0.5h蓄电池电压值（V）");
|
CellRangeAddress region33 = new CellRangeAddress(2, 2, 5, 6);
|
CellRangeAddress region53 = new CellRangeAddress(4, 4, 5, 6);
|
CellRangeAddress region63 = new CellRangeAddress(5, 5, 5, 6);
|
|
sheet.addMergedRegion(region33);
|
sheet.addMergedRegion(region53);
|
sheet.addMergedRegion(region63);
|
|
row3.createCell(5).setCellValue("0.5（电压）");
|
row3.createCell(6).setCellValue("0.5（温度）");
|
columnIndex+=2;
|
if(dischargeColumnCount > 2){ //合并单元格了,所以索引+2
|
//row2.createCell(7).setCellValue("放电1h蓄电池电压值（V）");
|
row2.createCell(columnCount).setCellValue("放电1h蓄电池电压值（V）");
|
CellRangeAddress region34 = new CellRangeAddress(2, 2, 7, 8);
|
CellRangeAddress region54 = new CellRangeAddress(4, 4, 7, 8);
|
CellRangeAddress region64 = new CellRangeAddress(5, 5, 7, 8);
|
|
sheet.addMergedRegion(region34);
|
sheet.addMergedRegion(region54);
|
sheet.addMergedRegion(region64);
|
|
row3.createCell(7).setCellValue("1（电压）");
|
row3.createCell(8).setCellValue("1（温度）");
|
columnIndex+=2;
|
if(dischargeColumnCount > 4){
|
//放电nh蓄电池电压值（V）,n从2开始
|
for(int i = 4;i < dischargeColumnCount;i++){
|
row2.createCell(i+5).setCellValue("放电" + (i-2) + "h蓄电池电压值（V）");
|
row3.createCell(i+5).setCellValue(String.valueOf(i-2));
|
columnIndex++;
|
}
|
|
}
|
}
|
}
|
|
//充电特定电压值
|
row2.createCell(columnIndex).setCellValue("放电结束0.5h后蓄电池开路电压值（V）");
|
row3.createCell(columnIndex).setCellValue("10.5");
|
columnIndex++;
|
if(chargeColumnCount > 0){
|
row2.createCell(columnIndex).setCellValue("充电0.5h");
|
columnIndex++;
|
if(chargeColumnCount > 1){
|
//充电nh,n从1开始
|
for(int i = 1;i < chargeColumnCount;i++){
|
row2.createCell(columnIndex).setCellValue("充电" + (i) + "h蓄电池电压值（V）");
|
row3.createCell(columnIndex);//填充单元格的作用
|
columnIndex++;
|
}
|
|
}
|
}
|
row2.createCell(columnIndex).setCellValue("充满后蓄电池电压值（V)（电流为0）");
|
row3.createCell(columnIndex).setCellValue("11");
|
columnIndex++;
|
row2.createCell(columnIndex).setCellValue("备注");
|
row3.createCell(columnIndex);//填充单元格的作用
|
|
//从rowIndex=4开始
|
int rowIndex = 4;
|
// 共104节单体,104行. 进行遍历,填充
|
//蓄电池浮充电压值列表
|
List<BattRealTimeDataHistory> fcVolList = new ArrayList<>();
|
List<BattRealTimeDataHistory> fcVolListOriginal = battRTDataHisService.getFcVolList(battGroupId,dischargeStartTime);
|
//按单体编号排序,按编号升序
|
fcVolListOriginal.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
|
addStatistics(fcVolListOriginal,"vol","tmp");
|
//总电流总电压列入
|
Float fcGroupVol = fcVolListOriginal.get(0).getGroupVol();
|
Float fcGroupCurr = fcVolListOriginal.get(0).getGroupCurr();
|
|
BattRealTimeDataHistory fcGroupVolData = new BattRealTimeDataHistory();
|
fcGroupVolData.setMonNum(-2);
|
fcGroupVolData.setGroupVol(fcGroupVol);
|
|
BattRealTimeDataHistory fcGroupCurrData = new BattRealTimeDataHistory();
|
fcGroupCurrData.setMonNum(-1);
|
fcGroupCurrData.setGroupCurr(fcGroupCurr);
|
|
fcVolList.add(fcGroupVolData);
|
fcVolList.add(fcGroupCurrData);
|
fcVolList.addAll(fcVolListOriginal);
|
|
//放点前蓄电池开路电压值
|
List<BattRealTimeDataHistory> preVolList = new ArrayList<>();
|
List<BattRealTimeDataHistory> preVolListOriginal = battRTDataHisService.getPreVolList(battGroupId,dischargeStartTime);
|
addStatistics(preVolListOriginal,"vol","tmp");
|
//总电流总电压列入
|
Float preGroupVol = preVolListOriginal.get(0).getGroupVol();
|
Float preGroupCurr = preVolListOriginal.get(0).getGroupCurr();
|
|
BattRealTimeDataHistory preGroupVolData = new BattRealTimeDataHistory();
|
preGroupVolData.setMonNum(-2);
|
preGroupVolData.setGroupVol(preGroupVol);
|
|
BattRealTimeDataHistory preGroupCurrData = new BattRealTimeDataHistory();
|
preGroupCurrData.setMonNum(-1);
|
preGroupCurrData.setGroupCurr(preGroupCurr);
|
|
preVolList.add(preGroupVolData);
|
preVolList.add(preGroupCurrData);
|
preVolList.addAll(preVolListOriginal);
|
|
//放电结束0.5h后蓄电池开路电压值
|
Calendar dischargeEndCalendar = Calendar.getInstance();
|
dischargeEndCalendar.setTime(dischargeEndTime.getTime());
|
dischargeEndCalendar.add(Calendar.MINUTE,30);
|
List<BattRealTimeDataHistory> dischargeSetVolListOne = new ArrayList<>();
|
List<BattRealTimeDataHistory> dischargeSetVolListOneOriginal = battRTDataHisService.getRecordList(battGroupId, dischargeEndCalendar.getTime());
|
dischargeSetVolListOneOriginal.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
|
addStatistics(dischargeSetVolListOneOriginal,"vol","tmp");
|
//总电流总电压列入
|
Float groupVolOne = dischargeSetVolListOneOriginal.get(0).getGroupVol();
|
Float groupCurrOne = dischargeSetVolListOneOriginal.get(0).getGroupCurr();
|
|
BattRealTimeDataHistory groupVolDataOne = new BattRealTimeDataHistory();
|
groupVolDataOne.setMonNum(-2);
|
groupVolDataOne.setGroupVol(groupVolOne);
|
|
BattRealTimeDataHistory groupCurrDataOne = new BattRealTimeDataHistory();
|
groupCurrDataOne.setMonNum(-1);
|
groupCurrDataOne.setGroupCurr(groupCurrOne);
|
|
dischargeSetVolListOne.add(groupVolDataOne);
|
dischargeSetVolListOne.add(groupCurrDataOne);
|
dischargeSetVolListOne.addAll(dischargeSetVolListOneOriginal);
|
|
//定位到充电开始时间,状态变为充电的记录时间
|
Date chargeStartTime = battRTDataHisService.getChargeStartTime(battGroupId, dischargeEndTime.getTime());
|
//充满后蓄电池的电压
|
Calendar chargeCalendar = Calendar.getInstance();
|
chargeCalendar.setTime(chargeStartTime);
|
List<BattRealTimeDataHistory> fcVolListAfter = new ArrayList<>();
|
List<BattRealTimeDataHistory> fcVolListAfterOriginal = battRTDataHisService.getFcVolListAfter(battGroupId, chargeCalendar.getTime());
|
fcVolListAfterOriginal.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
|
addStatistics(fcVolListAfterOriginal,"vol","tmp");
|
//总电流总电压列入
|
if(fcVolListAfterOriginal.size()>0) {
|
Float groupVolAfter = fcVolListAfterOriginal.get(0).getGroupVol();
|
Float groupCurrAfter = fcVolListAfterOriginal.get(0).getGroupCurr();
|
|
BattRealTimeDataHistory groupVolDataAfter = new BattRealTimeDataHistory();
|
groupVolDataAfter.setMonNum(-2);
|
groupVolDataAfter.setGroupVol(groupVolAfter);
|
|
BattRealTimeDataHistory groupCurrDataAfter = new BattRealTimeDataHistory();
|
groupCurrDataAfter.setMonNum(-1);
|
groupCurrDataAfter.setGroupCurr(groupCurrAfter);
|
|
fcVolListAfter.add(groupVolDataAfter);
|
fcVolListAfter.add(groupCurrDataAfter);
|
fcVolListAfter.addAll(fcVolListAfterOriginal);
|
}
|
|
//一共104节单体,再加上前面总电压和总电流2列,后面统计数据4列,一共110
|
for(int j = 0; j < 110; j++){
|
XSSFRow rowTemp = sheet.createRow(rowIndex++);
|
if(j == 0){
|
rowTemp.createCell(0).setCellValue("总电压");
|
}else if(j == 1){
|
rowTemp.createCell(0).setCellValue("总电流");
|
}
|
else if(j<106) {
|
rowTemp.createCell(0).setCellValue((j - 1) + "#");
|
}else{
|
if(j == 106){
|
rowTemp.createCell(0).setCellValue("单节平均电压");
|
}else if (j == 107){
|
rowTemp.createCell(0).setCellValue("单节最大电压");
|
}else if (j == 108){
|
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)+"#");
|
if(j == 0){
|
rowTemp.createCell(1).setCellValue(fcVolList.get(j).getGroupVol());
|
rowTemp.createCell(3).setCellValue(preVolList.get(j).getGroupVol());
|
}else if (j == 1){
|
rowTemp.createCell(1).setCellValue(fcVolList.get(j).getGroupCurr());
|
rowTemp.createCell(3).setCellValue(preVolList.get(j).getGroupCurr());
|
}else {
|
rowTemp.createCell(1).setCellValue(fcVolList.get(j).getMonVol());
|
rowTemp.createCell(2).setCellValue(fcVolList.get(j).getMonTmp());
|
rowTemp.createCell(3).setCellValue(preVolList.get(j).getMonVol());
|
rowTemp.createCell(4).setCellValue(preVolList.get(j).getMonTmp());
|
}
|
|
|
//放电特定电压值
|
int columnIndexTemp = 5;
|
Calendar dischargeCalendar = Calendar.getInstance();
|
dischargeCalendar.setTime(dischargeStartTime);
|
if(dischargeColumnCount > 0){
|
//时间在测试开始时间+0.5h
|
dischargeCalendar.add(Calendar.MINUTE,30);
|
Date recordTime = dischargeCalendar.getTime();
|
List<BattRealTimeDataHistory> dischargeListOne = new ArrayList<>();
|
List<BattRealTimeDataHistory> dischargeListOneOriginal = battRTDataHisService.getRecordList(battGroupId,recordTime);
|
//按单体编号排序,按编号升序
|
dischargeListOneOriginal.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
|
addStatistics(dischargeListOneOriginal,"vol");
|
//总电流总电压列入
|
Float groupVolDisOne = dischargeListOneOriginal.get(0).getGroupVol();
|
Float groupCurrDisOne = dischargeListOneOriginal.get(0).getGroupCurr();
|
|
BattRealTimeDataHistory groupVolDataDisOne = new BattRealTimeDataHistory();
|
groupVolDataDisOne.setMonNum(-2);
|
groupVolDataDisOne.setGroupVol(groupVolDisOne);
|
|
BattRealTimeDataHistory groupCurrDataDisOne = new BattRealTimeDataHistory();
|
groupCurrDataDisOne.setMonNum(-1);
|
groupCurrDataDisOne.setGroupCurr(groupCurrDisOne);
|
|
dischargeListOne.add(groupVolDataDisOne);
|
dischargeListOne.add(groupCurrDataDisOne);
|
dischargeListOne.addAll(dischargeListOneOriginal);
|
|
//rowTemp.createCell(5).setCellValue("放电0.5h电压值（V）:"+(j+1)+"#");
|
//rowTemp.createCell(6).setCellValue("放电0.5h温度值:"+(j+1)+"#");
|
if(j==0){
|
//rowTemp.createCell(5).setCellValue(dischargeListOne.get(j).getGroupVol());
|
rowTemp.createCell(columnIndexTemp).setCellValue(dischargeListOne.get(j).getGroupVol());
|
}else if (j==1){
|
rowTemp.createCell(columnIndexTemp).setCellValue(dischargeListOne.get(j).getGroupCurr());
|
}else {
|
rowTemp.createCell(columnIndexTemp).setCellValue(dischargeListOne.get(j).getMonVol());
|
rowTemp.createCell(columnIndexTemp+1).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 = new ArrayList<>();
|
List<BattRealTimeDataHistory> dischargeListTwoOriginal = battRTDataHisService.getRecordList(battGroupId,recordTimeTwo);
|
dischargeListTwoOriginal.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
|
addStatistics(dischargeListTwoOriginal,"vol");
|
//总电流总电压列入
|
Float groupVolDisTwo = dischargeListTwoOriginal.get(0).getGroupVol();
|
Float groupCurrDisTwo = dischargeListTwoOriginal.get(0).getGroupCurr();
|
|
BattRealTimeDataHistory groupVolDataDisTwo = new BattRealTimeDataHistory();
|
groupVolDataDisTwo.setMonNum(-2);
|
groupVolDataDisTwo.setGroupVol(groupVolDisTwo);
|
|
BattRealTimeDataHistory groupCurrDataDisTwo = new BattRealTimeDataHistory();
|
groupCurrDataDisTwo.setMonNum(-1);
|
groupCurrDataDisTwo.setGroupCurr(groupCurrDisTwo);
|
|
dischargeListTwo.add(groupVolDataDisTwo);
|
dischargeListTwo.add(groupCurrDataDisTwo);
|
dischargeListTwo.addAll(dischargeListTwoOriginal);
|
|
//rowTemp.createCell(7).setCellValue("放电1h电压值（V）:"+(j+1)+"#");
|
//rowTemp.createCell(8).setCellValue("放电1h温度值:"+(j+1)+"#");
|
if (j==0) {
|
rowTemp.createCell(columnIndexTemp).setCellValue(dischargeListTwo.get(j).getGroupVol());
|
}else if (j==1){
|
rowTemp.createCell(columnIndexTemp).setCellValue(dischargeListTwo.get(j).getGroupCurr());
|
}else {
|
rowTemp.createCell(columnIndexTemp).setCellValue(dischargeListTwo.get(j).getMonVol());
|
rowTemp.createCell(columnIndexTemp+1).setCellValue(dischargeListTwo.get(j).getMonTmp());
|
}
|
columnIndexTemp+=2;
|
if(dischargeColumnCount > 4){
|
//放电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 = new ArrayList<>();
|
List<BattRealTimeDataHistory> dischargeListNOriginal = battRTDataHisService.getRecordList(battGroupId,recordTimeN);
|
dischargeListNOriginal.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
|
addStatistics(dischargeListNOriginal,"vol");
|
//总电流总电压列入
|
Float groupVolDisN = dischargeListNOriginal.get(0).getGroupVol();
|
Float groupCurrDisN = dischargeListNOriginal.get(0).getGroupCurr();
|
|
BattRealTimeDataHistory groupVolDataDisN = new BattRealTimeDataHistory();
|
groupVolDataDisN.setMonNum(-2);
|
groupVolDataDisN.setGroupVol(groupVolDisN);
|
|
BattRealTimeDataHistory groupCurrDataDisN = new BattRealTimeDataHistory();
|
groupCurrDataDisN.setMonNum(-1);
|
groupCurrDataDisN.setGroupCurr(groupCurrDisN);
|
|
dischargeListN.add(groupVolDataDisN);
|
dischargeListN.add(groupCurrDataDisN);
|
dischargeListN.addAll(dischargeListNOriginal);
|
//rowTemp.createCell(i+5).setCellValue("放电" + dischargeHour + "h蓄电池电压值（V）");
|
if(j==0){
|
rowTemp.createCell(columnIndexTemp).setCellValue(dischargeListN.get(j).getGroupVol());
|
}else if (j==1){
|
rowTemp.createCell(columnIndexTemp).setCellValue(dischargeListN.get(j).getGroupCurr());
|
}else {
|
rowTemp.createCell(columnIndexTemp).setCellValue(dischargeListN.get(j).getMonVol());
|
}
|
columnIndexTemp++;
|
}
|
|
}
|
}
|
}
|
|
//填充放电0.5h后开路电压值
|
if(j == 0){
|
rowTemp.createCell(columnIndexTemp).setCellValue(dischargeSetVolListOne.get(j).getGroupVol());
|
}else if(j == 1){
|
rowTemp.createCell(columnIndexTemp).setCellValue(dischargeSetVolListOne.get(j).getGroupCurr());
|
}else {
|
rowTemp.createCell(columnIndexTemp).setCellValue(dischargeSetVolListOne.get(j).getMonVol());
|
}
|
columnIndexTemp++;
|
|
if(chargeColumnCount > 0){
|
chargeCalendar = Calendar.getInstance();
|
chargeCalendar.setTime(chargeStartTime);
|
chargeCalendar.add(Calendar.MINUTE,30);
|
List<BattRealTimeDataHistory> chargeVolListOne = new ArrayList<>();
|
List<BattRealTimeDataHistory> chargeVolListOneOriginal = battRTDataHisService.getRecordList(battGroupId, chargeCalendar.getTime());
|
chargeVolListOneOriginal.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
|
addStatistics(chargeVolListOneOriginal,"vol");
|
//总电流总电压列入
|
Float groupVolChargeOne = chargeVolListOneOriginal.get(0).getGroupVol();
|
Float groupCurrChargeOne = chargeVolListOneOriginal.get(0).getGroupCurr();
|
|
BattRealTimeDataHistory groupVolDataChargeOne = new BattRealTimeDataHistory();
|
groupVolDataChargeOne.setMonNum(-2);
|
groupVolDataChargeOne.setGroupVol(groupVolChargeOne);
|
|
BattRealTimeDataHistory groupCurrDataChargeOne = new BattRealTimeDataHistory();
|
groupCurrDataChargeOne.setMonNum(-1);
|
groupCurrDataChargeOne.setGroupCurr(groupCurrChargeOne);
|
|
chargeVolListOne.add(groupVolDataChargeOne);
|
chargeVolListOne.add(groupCurrDataChargeOne);
|
chargeVolListOne.addAll(chargeVolListOneOriginal);
|
//rowTemp.createCell(columnIndexTemp).setCellValue("充电0.5h");
|
rowTemp.createCell(columnIndexTemp).setCellValue(chargeVolListOne.get(j).getMonVol());
|
columnIndexTemp++;
|
|
if(chargeColumnCount > 1){
|
//充电nh,n从1开始
|
for(int i = 1;i < chargeColumnCount;i++){
|
chargeCalendar.setTime(chargeStartTime);
|
chargeCalendar.add(Calendar.HOUR,i);
|
List<BattRealTimeDataHistory> chargeVolListN = new ArrayList<>();
|
List<BattRealTimeDataHistory> chargeVolListNOriginal = battRTDataHisService.getRecordList(battGroupId, chargeCalendar.getTime());
|
chargeVolListNOriginal.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
|
addStatistics(chargeVolListNOriginal,"vol");
|
//总电流总电压列入
|
Float groupVolChargeN = chargeVolListNOriginal.get(0).getGroupVol();
|
Float groupCurrChargeN = chargeVolListNOriginal.get(0).getGroupCurr();
|
|
BattRealTimeDataHistory groupVolDataChargeN = new BattRealTimeDataHistory();
|
groupVolDataChargeN.setMonNum(-2);
|
groupVolDataChargeN.setGroupVol(groupVolChargeN);
|
|
BattRealTimeDataHistory groupCurrDataChargeN = new BattRealTimeDataHistory();
|
groupCurrDataChargeN.setMonNum(-1);
|
groupCurrDataChargeN.setGroupCurr(groupCurrChargeN);
|
|
chargeVolListN.add(groupVolDataChargeN);
|
chargeVolListN.add(groupCurrDataChargeN);
|
chargeVolListN.addAll(chargeVolListNOriginal);
|
//rowTemp.createCell(columnIndexTemp).setCellValue("充电" + (i) + "h蓄电池电压值（V）");
|
rowTemp.createCell(columnIndexTemp).setCellValue(chargeVolListN.get(j).getMonVol());
|
columnIndexTemp++;
|
}
|
|
}
|
}
|
|
//填充充满后蓄电池电压
|
//rowTemp.createCell(++columnIndexTemp).setCellValue("充满后蓄电池电压值（V)（电流为0）");
|
if (fcVolListAfter.size() > 0){
|
if(j == 0){
|
rowTemp.createCell(columnIndexTemp).setCellValue(fcVolListAfter.get(j).getGroupVol());
|
}else if(j == 1){
|
rowTemp.createCell(columnIndexTemp).setCellValue(fcVolListAfter.get(j).getGroupCurr());
|
}else {
|
rowTemp.createCell(columnIndexTemp).setCellValue(fcVolListAfter.get(j).getMonVol());
|
}
|
}else {
|
rowTemp.createCell(columnIndexTemp).setCellValue("-");
|
}
|
columnIndexTemp++;
|
rowTemp.createCell(columnIndexTemp);
|
}
|
|
//设置前6行所有的表格创建,避免出现无边框
|
for (int i = 0; i < 6; i++) {
|
XSSFRow rowTmp = sheet.getRow(i);
|
for (int j = 0; j < columnCount; j++) {
|
if(rowTmp.getCell(j) == null){
|
rowTmp.createCell(j);
|
}
|
}
|
}
|
|
//=====================格式设置=====================//
|
//设置列宽
|
for (int i = 0; i < columnCount; i++) {
|
sheet.setColumnWidth(i, 256 * 20);
|
}
|
//对整个表格进行全局设置,暂为设置边框
|
addGlobalStylesToAllCells(sheet, workbook);
|
|
//创建第一行是标题行
|
setRowStyle(workbook,sheet.getRow(0),true,20);
|
//第二行设置加粗
|
setRowStyle(workbook,sheet.getRow(1),true,11,HorizontalAlignment.LEFT);
|
//第四行设置字体颜色
|
setRowStyle(workbook,sheet.getRow(3),false,9,IndexedColors.GREY_40_PERCENT.getIndex());
|
//第5行到最后一行,设置数值的小数点为4位
|
setDateFormat(workbook,sheet,4,113,1,columnCount-1,"0.0000");
|
//生成图表
|
createChart(sheet, columnCount, 6,"");
|
|
return battInfo;
|
}
|
|
private void setDateFormat(Workbook workbook,XSSFSheet sheet, int rowIndexStart, int rowIndexEnd,int columnIndexStart,int columnIndexEnd, String formatStr) {
|
CellStyle decimalStyle = workbook.createCellStyle();
|
DataFormat format = workbook.createDataFormat();
|
decimalStyle.setDataFormat(format.getFormat(formatStr));
|
for (int i = rowIndexStart; i <= rowIndexEnd; i++) {
|
XSSFRow row = sheet.getRow(i);
|
for (int j = columnIndexStart; j <= columnIndexEnd; j++) {
|
XSSFCell cell = row.getCell(j);
|
if(cell.getCellType() == CellType.NUMERIC) {
|
cell.getCellStyle().setDataFormat(format.getFormat(formatStr));
|
}
|
}
|
|
}
|
}
|
|
private XSSFCellStyle getCellStyleFont(XSSFCellStyle cellStyleOriginal, XSSFWorkbook workbook, boolean isFontBold, int fontSize) {
|
//字体加粗样式
|
Font font = workbook.createFont();
|
font.setFontHeightInPoints((short) (fontSize));
|
font.setBold(isFontBold);
|
|
XSSFCellStyle cellStyle = workbook.createCellStyle();
|
cellStyle.cloneStyleFrom(cellStyleOriginal);
|
cellStyle.setFont(font);
|
cellStyle.setAlignment(HorizontalAlignment.CENTER);
|
cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
|
return cellStyle;
|
}
|
|
//对传入的BattRealTimeDataHistory列表进行统计,将统计结果也追加到列表中. 统计的为电压
|
public void addStatistics(List<BattRealTimeDataHistory> battRealTimeDataHistoryList,String... fields) {
|
List<String> fieldList = Arrays.asList(fields);
|
if(battRealTimeDataHistoryList != null && battRealTimeDataHistoryList.size() > 0){
|
BattRealTimeDataHistory hisAvg = new BattRealTimeDataHistory();
|
BattRealTimeDataHistory hisMax = new BattRealTimeDataHistory();
|
BattRealTimeDataHistory hisMin = new BattRealTimeDataHistory();
|
BattRealTimeDataHistory hisGap = new BattRealTimeDataHistory();
|
if(fieldList.contains("vol")) {
|
//电压平均值,最大值,最小值,最大差值
|
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;
|
|
hisAvg.setMonVol(volAvg);
|
|
hisMax.setMonVol(volMax);
|
|
hisMin.setMonVol(volMin);
|
|
hisGap.setMonVol(volGap);
|
}
|
|
if(fieldList.contains("res")) {
|
//平均值,最大值,最小值,最大差值
|
List<Float> resList = battRealTimeDataHistoryList.stream().map(BattRealTimeDataHistory::getMonRes).collect(Collectors.toList());
|
Double resAvgDouble = resList.stream().collect(Collectors.averagingDouble(Float::floatValue));
|
BigDecimal avg = new BigDecimal(resAvgDouble).setScale(4, RoundingMode.HALF_UP);
|
float resAvg = avg.floatValue();
|
|
//最大值
|
float resMax = resList.stream().max(Float::compareTo).get();
|
BigDecimal max = new BigDecimal(resMax).setScale(4, RoundingMode.HALF_UP);
|
resMax = max.floatValue();
|
|
//最小值
|
float resMin = resList.stream().min(Float::compareTo).get();
|
BigDecimal min = new BigDecimal(resMin).setScale(4, RoundingMode.HALF_UP);
|
resMin = min.floatValue();
|
|
//最大差值
|
float resGap = resMax - resMin;
|
|
hisAvg.setMonRes(resAvg);
|
hisMax.setMonRes(resMax);
|
hisMin.setMonRes(resMin);
|
hisGap.setMonRes(resGap);
|
}
|
|
if(fieldList.contains("tmp")) {
|
//平均值,最大值,最小值,最大差值
|
List<Float> tmpList = battRealTimeDataHistoryList.stream().map(BattRealTimeDataHistory::getMonTmp).collect(Collectors.toList());
|
Double tmpAvgDouble = tmpList.stream().collect(Collectors.averagingDouble(Float::floatValue));
|
BigDecimal avg = new BigDecimal(tmpAvgDouble).setScale(4, RoundingMode.HALF_UP);
|
float tmpAvg = avg.floatValue();
|
|
//最大值
|
float tmpMax = tmpList.stream().max(Float::compareTo).get();
|
BigDecimal max = new BigDecimal(tmpMax).setScale(4, RoundingMode.HALF_UP);
|
tmpMax = max.floatValue();
|
|
//最小值
|
float tmpMin = tmpList.stream().min(Float::compareTo).get();
|
BigDecimal min = new BigDecimal(tmpMin).setScale(4, RoundingMode.HALF_UP);
|
tmpMin = min.floatValue();
|
|
//最大差值
|
float tmpGap = tmpMax - tmpMin;
|
|
Date now = new Date();
|
|
hisAvg.setMonTmp(tmpAvg);
|
hisAvg.setRecordTime(now);
|
|
hisMax.setMonTmp(tmpMax);
|
hisMax.setRecordTime(now);
|
|
hisMin.setMonTmp(tmpMin);
|
hisMin.setRecordTime(now);
|
|
hisGap.setMonTmp(tmpGap);
|
hisGap.setRecordTime(now);
|
}
|
|
|
battRealTimeDataHistoryList.add(hisAvg);
|
battRealTimeDataHistoryList.add(hisMax);
|
battRealTimeDataHistoryList.add(hisMin);
|
battRealTimeDataHistoryList.add(hisGap);
|
}
|
}
|
|
public void setRowStyle(Workbook workbook,Row row,boolean isFontBold,int fontSize) {
|
for (Cell cell : row) {
|
//先克隆原来的属性
|
CellStyle cellStyleNew = workbook.createCellStyle();
|
cellStyleNew.cloneStyleFrom(cell.getCellStyle());
|
|
//设置字体和加粗
|
Font font = workbook.createFont();
|
font.setFontHeightInPoints((short) (fontSize));
|
font.setFontName("宋体");
|
font.setBold(isFontBold);
|
|
cellStyleNew.setFont(font);
|
cell.setCellStyle(cellStyleNew);
|
}
|
}
|
|
public void setRowStyle(Workbook workbook,Row row,boolean isFontBold,int fontSize,HorizontalAlignment horizontalAlignment) {
|
for (Cell cell : row) {
|
//先克隆原来的属性
|
CellStyle cellStyleNew = workbook.createCellStyle();
|
cellStyleNew.cloneStyleFrom(cell.getCellStyle());
|
|
//设置字体和加粗
|
Font font = workbook.createFont();
|
font.setFontHeightInPoints((short) (fontSize));
|
font.setBold(isFontBold);
|
font.setFontName("宋体");
|
|
//设置水平对齐方式
|
cellStyleNew.setAlignment(horizontalAlignment);
|
|
cellStyleNew.setFont(font);
|
cell.setCellStyle(cellStyleNew);
|
}
|
}
|
|
public void setRowStyle(Workbook workbook,Row row,boolean isFontBold,int fontSize,short color) {
|
for (Cell cell : row) {
|
//跳过第一列
|
if(cell.getColumnIndex() == 0) {
|
continue;
|
}
|
//先克隆原来的属性
|
CellStyle cellStyleNew = workbook.createCellStyle();
|
cellStyleNew.cloneStyleFrom(cell.getCellStyle());
|
|
//设置字体和加粗
|
Font font = workbook.createFont();
|
font.setFontHeightInPoints((short) (fontSize));
|
font.setBold(isFontBold);
|
font.setFontName("宋体");
|
font.setColor(color);
|
|
cellStyleNew.setFont(font);
|
cell.setCellStyle(cellStyleNew);
|
}
|
}
|
|
/**
|
* 设置全局的属性.这个方法在所有单元格创建后调用
|
* 用于对已被创建的行内的所有单元格添加边框.能自动识别被创建的所有列和所有单元格
|
* @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);
|
Font font = workbook.createFont();
|
//默认字体大小为9,宋体
|
font.setFontHeightInPoints((short) 9);
|
font.setFontName("宋体");
|
borderedStyle.setFont(font);
|
//默认横竖居中
|
borderedStyle.setAlignment(HorizontalAlignment.CENTER);
|
borderedStyle.setVerticalAlignment(VerticalAlignment.CENTER);
|
//默认自动换行
|
borderedStyle.setWrapText(true);
|
for (Row row : sheet) {
|
for (Cell cell : row) {
|
cell.setCellStyle(borderedStyle);
|
}
|
}
|
}
|
|
|
}
|
