From 4fce1788cfb5bc37484060b068daf6303a584058 Mon Sep 17 00:00:00 2001
From: whycxzp <glperry@163.com>
Date: 星期三, 30 四月 2025 14:38:46 +0800
Subject: [PATCH] 表格生成调整
---
src/main/java/com/whyc/service/ExcelService.java | 125 +++++++++++++++++++++++++++++++++--------
1 files changed, 100 insertions(+), 25 deletions(-)
diff --git a/src/main/java/com/whyc/service/ExcelService.java b/src/main/java/com/whyc/service/ExcelService.java
index cbba9d6..9d16272 100644
--- a/src/main/java/com/whyc/service/ExcelService.java
+++ b/src/main/java/com/whyc/service/ExcelService.java
@@ -22,7 +22,9 @@
import java.io.IOException;
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;
@Service
@@ -317,29 +319,65 @@
XSSFCell cellOfSheet4 = sheet4.createRow(0).createCell(0);
cellOfSheet4.setCellValue( battInfo.getPowerName()+ battInfo.getBattGroupName()+"娓╁害鏁版嵁");
- int columnCount = 2;
+ //30澶�+2鍒楀叾浠栧垪
+ int columnCount = 30+2;
//鏌ヨ鍘嗗彶瀹炴椂琛�. 瀛樺湪鐨勬湀浠�. 濡傛灉瀛樺湪鐨勮瘽,鍙栫涓�绗旇褰�(104鏉�)
//棣栧厛鏌ヨ褰撳墠鏈堜唤瀵瑰簲鐨勬棩鏈�
List<String> tableListLike = commonMapper.getTableListLike("db_power_history", "tb_batt_realdata_"+battGroupId);
- //褰撳墠鏈堜唤寰�鍓嶆帹11涓湀
+ //鑾峰彇褰撳墠鏃ユ湡寰�鍓�30澶╃殑鍑屾櫒闆剁鐨勬暟鎹�. 寰�鍓嶆帹30澶�
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);
+ 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));
@@ -348,7 +386,7 @@
if(j==0){
sheet4.setColumnWidth(j, 170 * 20);
}else {
- sheet4.setColumnWidth(j, 128 * 20);
+ sheet4.setColumnWidth(j, 138 * 20);
}
}
//琛屽
@@ -380,24 +418,42 @@
} else if (i == 107) {
sheet4.createRow(i + 2).createCell(0).setCellValue("鍗曡妭鏈�灏忔俯搴�");
}else if (i == 108) {
- sheet4.createRow(i + 2).createCell(0).setCellValue("鍗曡妭鏈�澶ф俯搴�");
+ sheet4.createRow(i + 2).createCell(0).setCellValue("鍗曡妭鏈�澶у樊鍊�");
}
}
}
//鍒涘缓绗笁琛�
XSSFRow row2OfSheet4 = sheet4.createRow(2);
-
+ row2OfSheet4.setHeightInPoints(40);
sheet4.getRow(0).createCell(columnCount-1);
- for (int i = 0; i <= tableListInDB.size(); i++) { //杩欎釜鏄垪鐨勯亶鍘�
- if (i == tableListInDB.size()) {
+
+ //濉厖鏁版嵁鍒�,key鏄垪鏃ユ湡. value鏄垪鏁版嵁
+ AtomicInteger count = new AtomicInteger();
+ dataListMap.forEach((key, value) -> {
+ //绗笁琛屾椂闂村垪濉厖
+ 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);
+ }
+
+ /*for (int i = 0; i < columnCount; i++) { //杩欎釜鏄垪鐨勯亶鍘�
+ if (i == columnCount - 1) {
//鏈�鍚庝竴鍒楀娉�:鍏ㄩ儴濉┖
row2OfSheet4.createCell(i + 1);
for (int j = 1; j <= 108; j++) { //杩欎釜鏄鐨勯亶鍘�
- sheet4.getRow(j + 2).createCell(i + 1);
+ sheet4.getRow(j + 2).createCell(i );
}
} else {
//鑾峰彇鏁版嵁
- List<BattRealTimeDataHistory> list = battRTDataHisService.getFirstRecordList(tableListInDB.get(i));
+ List<BattRealTimeDataHistory> list = battRTDataHisService.getFirstRecordListOfDay(tableListInDB.get(i), timeAgo);
list.sort(Comparator.comparing(BattRealTimeDataHistory::getMonNum));
//绗笁琛岀殑鏃堕棿鍒楀～鍏�
Date testStartTime = list.get(0).getTestStartTime();
@@ -409,7 +465,7 @@
sheet4.getRow(j + 2).createCell(i + 1).setCellValue((list.get(j - 1).getMonVol()));
}
}
- }
+ }*/
addGlobalStylesToAllCells(sheet4, workbook);
@@ -417,6 +473,16 @@
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,"娓╁害鎶樼嚎鍥�");
@@ -889,10 +955,19 @@
//鏈�澶у樊鍊�
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);
}
--
Gitblit v1.9.1