From c971825d2a2bc48c74f2ffe97127b50b14445e12 Mon Sep 17 00:00:00 2001
From: whycxzp <glperry@163.com>
Date: 星期一, 09 六月 2025 20:56:16 +0800
Subject: [PATCH] 压入变动的时间列的总电压和电流
---
src/main/java/com/whyc/service/ExcelService.java | 1119 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 1,106 insertions(+), 13 deletions(-)
diff --git a/src/main/java/com/whyc/service/ExcelService.java b/src/main/java/com/whyc/service/ExcelService.java
index 3f33c71..4927078 100644
--- a/src/main/java/com/whyc/service/ExcelService.java
+++ b/src/main/java/com/whyc/service/ExcelService.java
@@ -1,25 +1,68 @@
package com.whyc.service;
-import com.whyc.util.ThreadLocalUtil;
-import org.apache.poi.xssf.usermodel.XSSFSheet;
-import org.apache.poi.xssf.usermodel.XSSFWorkbook;
+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 java.io.FileOutputStream;
import java.io.IOException;
-import java.util.Date;
+import java.math.BigDecimal;
+import java.math.RoundingMode;
+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 {
- public void dcReport() throws IOException {
- String filePath = "C:\\Users\\29550\\Desktop\\褰撳墠椤圭洰\\2023\\0鏅鸿兘鏈哄櫒浜鸿繍缁寸郴缁焅\娴嬭瘯.xlsx";
+ @Autowired
+ private PowerInfService powerInfService;
+
+ @Autowired
+ private BattTestInfService battTestInfService;
+
+ @Autowired
+ //private static BattRealTimeDataHistoryService battRTDataHisService;
+ private BattRealTimeDataHistoryService battRTDataHisService;
+
+ @Autowired
+ private CommonMapper commonMapper;
+
+
+ /**
+ * 灏嗙數姹犵粍鏀剧數鍏呯數鏁版嵁鍐欏叆excel
+ * 浼犲叆鍙傛暟:
+ * 鐢垫睜缁刬d,鏀剧數绗嚑娆�
+ *
+ * @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);
-
-
+ createSheet1(workbook,battGroupId,testRecordCount);
+ //鍒涘缓sheet2 -- 姣忔湀鐢靛帇,12涓湀,鍙栨瘡鏈�1鍙烽浂鐐归浂鍒�
+ //鍒涘缓sheet3 -- 姣忔湀鍐呴樆, 12涓湀,鍙栨瘡鏈�1鍙烽浂鐐归浂鍒�
+ //createSheet2And3(workbook,battGroupId);
+ //鍒涘缓sheet4 -- 姣忓ぉ娓╁害, 30澶�, 鍙栨瘡澶╅浂鐐归浂鍒�
+ //createSheet4(workbook,battGroupId);
// 灏嗗伐浣滅翱鍐欏叆鏂囦欢
FileOutputStream fileOut = new FileOutputStream(filePath);
@@ -30,13 +73,1063 @@
}
- private void createSheet1(XSSFWorkbook workbook) {
+ 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鍜宻heet3鐨勬爣棰樻牱寮�
+ 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);
+
+
+ // 鍒涘缓缁樺浘宸ュ叿
+ 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();
+ //瀵筪ateKeySet鎸夋椂闂存帓搴�
+ 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鍜宻heet3鐨勬爣棰樻牱寮�
+ 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);
+ }
+
+ 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 void createSheet1(XSSFWorkbook workbook, int battGroupId, int testRecordCount) {
XSSFSheet sheet = workbook.createSheet("鏀剧數鍏呯數鐢靛帇鏁版嵁鎶ヨ〃");
//鎬荤殑鍒楁暟鏍规嵁 鏀剧數鏃堕暱鍜屽厖鐢垫椂闀垮喅瀹�
- Date dischargeStartTime = ThreadLocalUtil.parse("2023-01-01 11:00:00", 1);
- Date dischargeEndTime = ThreadLocalUtil.parse("2023-01-02 01:00:00", 1);
- //鏌ヨ鏃堕暱,鎸夊皬鏃�
+ //鏁版嵁鍑嗗
+ 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()+"鏍稿鎬ф斁鐢点�佸厖鐢佃瘯楠岃褰曡〃");
+ //鍚堝苟琛屽垪,鍗犳嵁鐨勫垪鏄痗olumnCount鍒楀悎骞朵负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);
+
+ //绗笁琛�,绗簲琛�,绗叚琛岀殑鍚堝苟鍒�,鍓嶄袱涓崟鍏冩ā鍧楁槸鍥哄畾鐨�,鍏朵腑1涓悎骞跺崟鍏�
+ CellRangeAddress region31 = new CellRangeAddress(2, 2, 2, 3);
+ CellRangeAddress region51 = new CellRangeAddress(4, 4, 2, 3);
+ CellRangeAddress region61 = new CellRangeAddress(5, 5, 2, 3);
+ sheet.addMergedRegion(region31);
+ sheet.addMergedRegion(region51);
+ sheet.addMergedRegion(region61);
+
+
+ // 鍒涘缓灞呬腑鏍峰紡
+ 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("鏀剧數鐢垫祦锛圓锛夛細35\n" +
+ "鏀剧數鏃堕暱锛堟椂.鍒嗭級锛�600min\n" +
+ "鏀剧數瀹归噺锛圓h锛夛細300");
+
+ XSSFCell cell6Row2 = row1.createCell(18);
+ cell6Row2.setCellValue("鐜娓╁害锛堚剝锛夛細24.5\n" +
+ "鐜婀垮害锛�%锛夛細46.7");
+ //绗簩琛岃缃姞绮�
+ //setRowStyle(sheet.getRow(1),cellStyleFontBold);
+
+ //绗笁琛屾槸鐢垫睜鍙峰拰鐢靛帇绛夌浉鍏虫姮澶�
+ //绗洓琛屾槸鐢靛帇鍒楀拰娓╁害鍒楁爣璇�
+ XSSFRow row2 = sheet.createRow(2);
+ XSSFRow row3 = sheet.createRow(3);
+ XSSFRow row4 = sheet.createRow(4);
+ XSSFRow row5 = sheet.createRow(5);
+
+ row2.createCell(0).setCellValue("钃勭數姹犲彿锛�#锛�");
+ row2.createCell(1).setCellValue("钃勭數姹犳诞鍏呯數鍘嬪�硷紙V锛�");
+ row2.createCell(2).setCellValue("鏀剧數鍓嶈搫鐢垫睜寮�璺數鍘嬪�硷紙V锛�");
+
+ row3.createCell(0).setCellValue("椤圭洰");
+ row3.createCell(1).setCellValue("-1锛堢數鍘嬶級");
+ row3.createCell(2).setCellValue("0锛堢數鍘嬶級");
+ row3.createCell(3).setCellValue("0锛堟俯搴︼級");
+
+ //鏀剧數鐗瑰畾鐢靛帇鍊�
+ int columnIndex = 3;
+ if(dischargeColumnCount > 0){
+ row2.createCell(5).setCellValue("鏀剧數0.5h钃勭數姹犵數鍘嬪�硷紙V锛�");
+ CellRangeAddress region33 = new CellRangeAddress(2, 2, 4, 5);
+ CellRangeAddress region53 = new CellRangeAddress(4, 4, 4, 5);
+ CellRangeAddress region63 = new CellRangeAddress(5, 5, 4, 5);
+
+ sheet.addMergedRegion(region33);
+ sheet.addMergedRegion(region53);
+ sheet.addMergedRegion(region63);
+
+ row3.createCell(4).setCellValue("0.5锛堢數鍘嬶級");
+ row3.createCell(5).setCellValue("0.5锛堟俯搴︼級");
+ columnIndex+=2;
+ if(dischargeColumnCount > 2){ //鍚堝苟鍗曞厓鏍间簡,鎵�浠ョ储寮�+2
+ row2.createCell(7).setCellValue("鏀剧數1h钃勭數姹犵數鍘嬪�硷紙V锛�");
+ CellRangeAddress region34 = new CellRangeAddress(2, 2, 6, 7);
+ CellRangeAddress region54 = new CellRangeAddress(4, 2, 6, 7);
+ CellRangeAddress region64 = new CellRangeAddress(5, 2, 6, 7);
+
+ sheet.addMergedRegion(region34);
+ sheet.addMergedRegion(region54);
+ sheet.addMergedRegion(region64);
+
+ row3.createCell(6).setCellValue("1锛堢數鍘嬶級");
+ row3.createCell(7).setCellValue("1锛堟俯搴︼級");
+ 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锛�");
+ row3.createCell(i+5).setCellValue(String.valueOf(i-2));
+ columnIndex++;
+ }
+
+ }
+ }
+ }
+
+ //鍏呯數鐗瑰畾鐢靛帇鍊�
+ row2.createCell(++columnIndex).setCellValue("鏀剧數缁撴潫0.5h鍚庤搫鐢垫睜寮�璺數鍘嬪�硷紙V锛�");
+ row3.createCell(columnIndex).setCellValue("10.5");
+ 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锛�");
+ row3.createCell(columnIndex);//濉厖鍗曞厓鏍肩殑浣滅敤
+ }
+
+ }
+ }
+ row2.createCell(++columnIndex).setCellValue("鍏呮弧鍚庤搫鐢垫睜鐢靛帇鍊硷紙V)锛堢數娴佷负0锛�");
+ row3.createCell(columnIndex).setCellValue("11");
+ row2.createCell(++columnIndex).setCellValue("澶囨敞");
+ row3.createCell(columnIndex);//濉厖鍗曞厓鏍肩殑浣滅敤
+
+
+
+ //浠巖owIndex=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");
+ //鎬荤數娴佹�荤數鍘嬪垪鍏�
+ 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(2).setCellValue(preVolList.get(j).getGroupVol());
+ rowTemp.createCell(3);
+ }else if (j == 1){
+ rowTemp.createCell(1).setCellValue(fcVolList.get(j).getGroupCurr());
+ rowTemp.createCell(2).setCellValue(preVolList.get(j).getGroupCurr());
+ rowTemp.createCell(3);
+ }else {
+ rowTemp.createCell(1).setCellValue(fcVolList.get(j).getMonVol());
+ rowTemp.createCell(2).setCellValue(preVolList.get(j).getMonVol());
+ rowTemp.createCell(3).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 = new ArrayList<>();
+ List<BattRealTimeDataHistory> dischargeListOneOriginal = battRTDataHisService.getRecordList(battGroupId,recordTime);
+ //鎸夊崟浣撶紪鍙锋帓搴�,鎸夌紪鍙峰崌搴�
+ dischargeListOneOriginal.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
+ addStatistics(dischargeListOneOriginal,"vol");
+ //鎬荤數娴佹�荤數鍘嬪垪鍏�
+
+
+ //rowTemp.createCell(5).setCellValue("鏀剧數0.5h鐢靛帇鍊硷紙V锛�:"+(j+1)+"#");
+ //rowTemp.createCell(6).setCellValue("鏀剧數0.5h娓╁害鍊�:"+(j+1)+"#");
+ rowTemp.createCell(4).setCellValue(dischargeListOne.get(j).getMonVol());
+ rowTemp.createCell(5).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,"vol");
+ //rowTemp.createCell(7).setCellValue("鏀剧數1h鐢靛帇鍊硷紙V锛�:"+(j+1)+"#");
+ //rowTemp.createCell(8).setCellValue("鏀剧數1h娓╁害鍊�:"+(j+1)+"#");
+ rowTemp.createCell(6).setCellValue(dischargeListTwo.get(j).getMonVol());
+ rowTemp.createCell(7).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,"vol");
+ //rowTemp.createCell(i+5).setCellValue("鏀剧數" + dischargeHour + "h钃勭數姹犵數鍘嬪�硷紙V锛�");
+ rowTemp.createCell(i+5).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());
+ }
+
+ if(chargeColumnCount > 0){
+ 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,"vol");
+ 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,"vol");
+ //rowTemp.createCell(columnIndexTemp).setCellValue("鍏呯數" + (i) + "h钃勭數姹犵數鍘嬪�硷紙V锛�");
+ rowTemp.createCell(columnIndexTemp).setCellValue(chargeVolListN.get(j).getMonVol());
+ }
+
+ }
+ }
+
+ //濉厖鍏呮弧鍚庤搫鐢垫睜鐢靛帇
+ //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("-");
+ }
+ rowTemp.createCell(++columnIndexTemp).setCellValue("-");
+ }
+
+ //=====================鏍煎紡璁剧疆=====================//
+ //璁剧疆鍒楀
+ for (int i = 0; i < columnCount; i++) {
+ sheet.setColumnWidth(i, 256 * 20);
+ }
+ //瀵规暣涓〃鏍艰繘琛屽叏灞�璁剧疆,鏆備负璁剧疆杈规
+ addGlobalStylesToAllCells(sheet, workbook);
+
+ //鍒涘缓绗竴琛屾槸鏍囬琛�
+ setRowStyle(workbook,sheet.getRow(0),true,15);
+ //绗簩琛岃缃姞绮�
+ setRowStyle(workbook,sheet.getRow(1),true,15);
+
+ //鐢熸垚鍥捐〃
+ createChart(sheet, columnCount, 6,"");
+ }
+
+ 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.setBold(isFontBold);
+
+ 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);
+
+ for (Row row : sheet) {
+ for (Cell cell : row) {
+ cell.setCellStyle(borderedStyle);
+ }
+ }
}
--
Gitblit v1.9.1