From d19e91bcc4ed9ab08e8be375fb5fc0f7e874a6a9 Mon Sep 17 00:00:00 2001
From: whycxzp <glperry@163.com>
Date: 星期三, 30 四月 2025 18:17:44 +0800
Subject: [PATCH] 测试调整格式
---
src/main/java/com/whyc/service/ExcelService.java | 453 +++++++++++++++++++++++++++++++++++++++++++-------------
1 files changed, 345 insertions(+), 108 deletions(-)
diff --git a/src/main/java/com/whyc/service/ExcelService.java b/src/main/java/com/whyc/service/ExcelService.java
index 88e77f6..2495b18 100644
--- a/src/main/java/com/whyc/service/ExcelService.java
+++ b/src/main/java/com/whyc/service/ExcelService.java
@@ -18,10 +18,12 @@
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;
@@ -52,22 +54,44 @@
*
* @throws IOException
*/
- public void dcReport(int battGroupId,int testRecordCount) throws IOException {
- String filePath = "C:\\Users\\29550\\Desktop\\褰撳墠椤圭洰\\2023\\0涔屼笢寰风郴缁焅\娴嬭瘯.xlsx";
+ 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
- createSheet1(workbook,battGroupId,testRecordCount);
+ PowerInf battInfo = createSheet1(workbook, battGroupId, testRecordCount);
//鍒涘缓sheet2 -- 姣忔湀鐢靛帇,12涓湀,鍙栨瘡鏈�1鍙烽浂鐐归浂鍒�
//鍒涘缓sheet3 -- 姣忔湀鍐呴樆, 12涓湀,鍙栨瘡鏈�1鍙烽浂鐐归浂鍒�
- //createSheet2And3(workbook,battGroupId);
+ createSheet2And3(workbook,battGroupId);
//鍒涘缓sheet4 -- 姣忓ぉ娓╁害, 30澶�, 鍙栨瘡澶╅浂鐐归浂鍒�
- //createSheet4(workbook,battGroupId);
+ createSheet4(workbook,battGroupId);
- // 灏嗗伐浣滅翱鍐欏叆鏂囦欢
+ /*// 灏嗗伐浣滅翱鍐欏叆鏂囦欢
FileOutputStream fileOut = new FileOutputStream(filePath);
- workbook.write(fileOut);
+ 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();
@@ -214,6 +238,10 @@
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");
// 鍒涘缓缁樺浘宸ュ叿
@@ -465,6 +493,8 @@
cell.setCellStyle(cellStyle);
}
+ //璁剧疆鏁板�肩被鍨嬪崟鍏冩牸淇濈暀浣嶆暟
+ setDateFormat(workbook,sheet4,3,110,1,columnCount-2, "0.0000");
createChart(sheet4, columnCount,3,"娓╁害鎶樼嚎鍥�");
}
@@ -495,7 +525,7 @@
}
//private static void createSheet1(XSSFWorkbook workbook, int battGroupId, int testRecordCount) {
- private void createSheet1(XSSFWorkbook workbook, int battGroupId, int testRecordCount) {
+ private PowerInf createSheet1(XSSFWorkbook workbook, int battGroupId, int testRecordCount) {
XSSFSheet sheet = workbook.createSheet("鏀剧數鍏呯數鐢靛帇鏁版嵁鎶ヨ〃");
//鎬荤殑鍒楁暟鏍规嵁 鏀剧數鏃堕暱鍜屽厖鐢垫椂闀垮喅瀹�
//鏁版嵁鍑嗗
@@ -572,26 +602,20 @@
CellRangeAddress region = new CellRangeAddress(0, 0, 0, columnCount-1);
sheet.addMergedRegion(region);
- //TODO 绗簩琛岀殑鍚堝苟鍒楁槸鏍规嵁鍙樺姩鐨�,鍚庣画璋冩暣
- CellRangeAddress region21 = new CellRangeAddress(1, 1, 0, 1);
- sheet.addMergedRegion(region21);
+ //绗笁琛�,绗簲琛�,绗叚琛岀殑鍚堝苟鍒�,鍓嶄袱涓崟鍏冩ā鍧楁槸鍥哄畾鐨�,鍏朵腑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);
- 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);
sheet.addMergedRegion(region31);
-
+ sheet.addMergedRegion(region32);
+ sheet.addMergedRegion(region51);
+ sheet.addMergedRegion(region52);
+ sheet.addMergedRegion(region61);
+ sheet.addMergedRegion(region62);
// 鍒涘缓灞呬腑鏍峰紡
CellStyle style = workbook.createCellStyle();
@@ -601,65 +625,94 @@
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(2);
- cell2Row2.setCellValue("");
- XSSFCell cell3Row2 = row1.createCell(3);
- cell3Row2.setCellValue("宸ヤ綔绁ㄥ彿锛�2273925\n" +
+ XSSFCell cell2Row2 = row1.createCell(1);
+ cell2Row2.setCellValue("宸ヤ綔绁ㄥ彿锛�2273925\n" +
"宸ヤ綔璐熻矗浜猴細椹簯鐕昞n" +
"宸ヤ綔鎴愬憳锛氬懆绱犳枃銆佹潕蹇楁爣銆佺帇鏈堢伩绛�");
- XSSFCell cell4Row2 = row1.createCell(10);
- cell4Row2.setCellValue("鏀剧數鏃ユ湡锛�2021.10.30\n" +
- "鏀剧數寮�濮嬫椂闂达細10:00\n" +
- "鏀剧數缁撴潫鏃堕棿锛�19:47");
+ 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 cell5Row2 = row1.createCell(14);
- cell5Row2.setCellValue("鏀剧數鐢垫祦锛圓锛夛細35\n" +
- "鏀剧數鏃堕暱锛堟椂.鍒嗭級锛�600min\n" +
- "鏀剧數瀹归噺锛圓h锛夛細300");
+ XSSFCell cell4Row2 = row1.createCell(5);
+ cell4Row2.setCellValue("鏀剧數鐢垫祦锛圓锛夛細"+ Math.abs(battTestInf.getTestCurr())+"\n" +
+ "鏀剧數鏃堕暱锛堟椂.鍒嗭級锛�"+testTimeMinutes+"\n" +
+ "鏀剧數瀹归噺锛圓h锛夛細"+Math.abs(battTestInf.getTestCap()));
- XSSFCell cell6Row2 = row1.createCell(18);
- cell6Row2.setCellValue("鐜娓╁害锛堚剝锛夛細24.5\n" +
+ XSSFCell cell5Row2 = row1.createCell(columnCount-1);
+ cell5Row2.setCellValue("鐜娓╁害锛堚剝锛夛細24.5\n" +
"鐜婀垮害锛�%锛夛細46.7");
- //绗簩琛岃缃姞绮�
- //setRowStyle(sheet.getRow(1),cellStyleFontBold);
+
+ //浜岃鐨勫悎骞跺垪鏄牴鎹彉鍔ㄧ殑
+ 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(2).setCellValue("鏀剧數鍓嶈搫鐢垫睜寮�璺數鍘嬪�硷紙V锛�");
+ row2.createCell(3).setCellValue("鏀剧數鍓嶈搫鐢垫睜寮�璺數鍘嬪�硷紙V锛�");
row3.createCell(0).setCellValue("椤圭洰");
row3.createCell(1).setCellValue("-1锛堢數鍘嬶級");
- row3.createCell(2).setCellValue("0锛堢數鍘嬶級");
- row3.createCell(3).setCellValue("0锛堟俯搴︼級");
+ row3.createCell(2).setCellValue("-1锛堟俯搴︼級");
+ row3.createCell(3).setCellValue("0锛堢數鍘嬶級");
+ row3.createCell(4).setCellValue("0锛堟俯搴︼級");
//鏀剧數鐗瑰畾鐢靛帇鍊�
- int columnIndex = 3;
+ int columnIndex = 5;
if(dischargeColumnCount > 0){
- row2.createCell(5).setCellValue("鏀剧數0.5h钃勭數姹犵數鍘嬪�硷紙V锛�");
- CellRangeAddress region33 = new CellRangeAddress(2, 2, 4, 5);
+ //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);
- row3.createCell(4).setCellValue("0.5锛堢數鍘嬶級");
- row3.createCell(5).setCellValue("0.5锛堟俯搴︼級");
+ 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锛�");
- CellRangeAddress region34 = new CellRangeAddress(2, 2, 6, 7);
+ //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);
- row3.createCell(6).setCellValue("1锛堢數鍘嬶級");
- row3.createCell(7).setCellValue("1锛堟俯搴︼級");
+ sheet.addMergedRegion(region54);
+ sheet.addMergedRegion(region64);
+
+ row3.createCell(7).setCellValue("1锛堢數鍘嬶級");
+ row3.createCell(8).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锛�");
@@ -672,28 +725,27 @@
}
//鍏呯數鐗瑰畾鐢靛帇鍊�
- row2.createCell(++columnIndex).setCellValue("鏀剧數缁撴潫0.5h鍚庤搫鐢垫睜寮�璺數鍘嬪�硷紙V锛�");
+ row2.createCell(columnIndex).setCellValue("鏀剧數缁撴潫0.5h鍚庤搫鐢垫睜寮�璺數鍘嬪�硷紙V锛�");
row3.createCell(columnIndex).setCellValue("10.5");
+ columnIndex++;
if(chargeColumnCount > 0){
- columnIndex++;
row2.createCell(columnIndex).setCellValue("鍏呯數0.5h");
-
+ columnIndex++;
if(chargeColumnCount > 1){
//鍏呯數nh,n浠�1寮�濮�
for(int i = 1;i < chargeColumnCount;i++){
- columnIndex++;
row2.createCell(columnIndex).setCellValue("鍏呯數" + (i) + "h钃勭數姹犵數鍘嬪�硷紙V锛�");
row3.createCell(columnIndex);//濉厖鍗曞厓鏍肩殑浣滅敤
+ columnIndex++;
}
}
}
- row2.createCell(++columnIndex).setCellValue("鍏呮弧鍚庤搫鐢垫睜鐢靛帇鍊硷紙V)锛堢數娴佷负0锛�");
+ row2.createCell(columnIndex).setCellValue("鍏呮弧鍚庤搫鐢垫睜鐢靛帇鍊硷紙V)锛堢數娴佷负0锛�");
row3.createCell(columnIndex).setCellValue("11");
- row2.createCell(++columnIndex).setCellValue("澶囨敞");
+ columnIndex++;
+ row2.createCell(columnIndex).setCellValue("澶囨敞");
row3.createCell(columnIndex);//濉厖鍗曞厓鏍肩殑浣滅敤
-
-
//浠巖owIndex=4寮�濮�
int rowIndex = 4;
@@ -703,7 +755,7 @@
List<BattRealTimeDataHistory> fcVolListOriginal = battRTDataHisService.getFcVolList(battGroupId,dischargeStartTime);
//鎸夊崟浣撶紪鍙锋帓搴�,鎸夌紪鍙峰崌搴�
fcVolListOriginal.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
- addStatistics(fcVolListOriginal,"vol");
+ addStatistics(fcVolListOriginal,"vol","tmp");
//鎬荤數娴佹�荤數鍘嬪垪鍏�
Float fcGroupVol = fcVolListOriginal.get(0).getGroupVol();
Float fcGroupCurr = fcVolListOriginal.get(0).getGroupCurr();
@@ -800,7 +852,7 @@
rowTemp.createCell(0).setCellValue("鎬荤數娴�");
}
else if(j<106) {
- rowTemp.createCell(0).setCellValue((j + 1) + "#");
+ rowTemp.createCell(0).setCellValue((j - 1) + "#");
}else{
if(j == 106){
rowTemp.createCell(0).setCellValue("鍗曡妭骞冲潎鐢靛帇");
@@ -818,50 +870,96 @@
//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).setCellValue(preVolList.get(j).getGroupVol());
}else if (j == 1){
rowTemp.createCell(1).setCellValue(fcVolList.get(j).getGroupCurr());
- rowTemp.createCell(2).setCellValue(preVolList.get(j).getGroupCurr());
+ rowTemp.createCell(3).setCellValue(preVolList.get(j).getGroupCurr());
}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());
+ 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 = 3;
+ 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 = battRTDataHisService.getRecordList(battGroupId,recordTime);
+ List<BattRealTimeDataHistory> dischargeListOne = new ArrayList<>();
+ List<BattRealTimeDataHistory> dischargeListOneOriginal = battRTDataHisService.getRecordList(battGroupId,recordTime);
//鎸夊崟浣撶紪鍙锋帓搴�,鎸夌紪鍙峰崌搴�
- dischargeListOne.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
- addStatistics(dischargeListOne,"vol");
+ 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)+"#");
- rowTemp.createCell(4).setCellValue(dischargeListOne.get(j).getMonVol());
- rowTemp.createCell(5).setCellValue(dischargeListOne.get(j).getMonTmp());
+ 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 = battRTDataHisService.getRecordList(battGroupId,recordTimeTwo);
- dischargeListTwo.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
- addStatistics(dischargeListTwo,"vol");
+ 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)+"#");
- rowTemp.createCell(6).setCellValue(dischargeListTwo.get(j).getMonVol());
- rowTemp.createCell(7).setCellValue(dischargeListTwo.get(j).getMonTmp());
+ 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){
- columnIndexTemp++;
//鏀剧數nh钃勭數姹犵數鍘嬪�硷紙V锛�,n浠�2寮�濮�
for(int i = 4;i < dischargeColumnCount;i++){
int dischargeHour = i-2;
@@ -869,11 +967,33 @@
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");
+ 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锛�");
- rowTemp.createCell(i+5).setCellValue(dischargeListN.get(j).getMonVol());
+ 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++;
}
@@ -883,36 +1003,68 @@
//濉厖鏀剧數0.5h鍚庡紑璺數鍘嬪��
if(j == 0){
- rowTemp.createCell(++columnIndexTemp).setCellValue(dischargeSetVolListOne.get(j).getGroupVol());
+ rowTemp.createCell(columnIndexTemp).setCellValue(dischargeSetVolListOne.get(j).getGroupVol());
}else if(j == 1){
- rowTemp.createCell(++columnIndexTemp).setCellValue(dischargeSetVolListOne.get(j).getGroupCurr());
+ rowTemp.createCell(columnIndexTemp).setCellValue(dischargeSetVolListOne.get(j).getGroupCurr());
}else {
- rowTemp.createCell(++columnIndexTemp).setCellValue(dischargeSetVolListOne.get(j).getMonVol());
+ 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 = battRTDataHisService.getRecordList(battGroupId, chargeCalendar.getTime());
- chargeVolListOne.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
- addStatistics(chargeVolListOne,"vol");
- columnIndexTemp++;
+ 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++){
- 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");
+ 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++;
}
}
@@ -922,16 +1074,27 @@
//rowTemp.createCell(++columnIndexTemp).setCellValue("鍏呮弧鍚庤搫鐢垫睜鐢靛帇鍊硷紙V)锛堢數娴佷负0锛�");
if (fcVolListAfter.size() > 0){
if(j == 0){
- rowTemp.createCell(++columnIndexTemp).setCellValue(fcVolListAfter.get(j).getGroupVol());
+ rowTemp.createCell(columnIndexTemp).setCellValue(fcVolListAfter.get(j).getGroupVol());
}else if(j == 1){
- rowTemp.createCell(++columnIndexTemp).setCellValue(fcVolListAfter.get(j).getGroupCurr());
+ rowTemp.createCell(columnIndexTemp).setCellValue(fcVolListAfter.get(j).getGroupCurr());
}else {
- rowTemp.createCell(++columnIndexTemp).setCellValue(fcVolListAfter.get(j).getMonVol());
+ rowTemp.createCell(columnIndexTemp).setCellValue(fcVolListAfter.get(j).getMonVol());
}
}else {
- rowTemp.createCell(++columnIndexTemp).setCellValue("-");
+ rowTemp.createCell(columnIndexTemp).setCellValue("-");
}
- 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);
+ }
+ }
}
//=====================鏍煎紡璁剧疆=====================//
@@ -943,12 +1106,33 @@
addGlobalStylesToAllCells(sheet, workbook);
//鍒涘缓绗竴琛屾槸鏍囬琛�
- setRowStyle(workbook,sheet.getRow(0),true,15);
+ setRowStyle(workbook,sheet.getRow(0),true,20);
//绗簩琛岃缃姞绮�
- setRowStyle(workbook,sheet.getRow(1),true,15);
-
+ 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) {
@@ -1080,6 +1264,7 @@
//璁剧疆瀛椾綋鍜屽姞绮�
Font font = workbook.createFont();
font.setFontHeightInPoints((short) (fontSize));
+ font.setFontName("瀹嬩綋");
font.setBold(isFontBold);
cellStyleNew.setFont(font);
@@ -1087,8 +1272,51 @@
}
}
+ 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
*/
@@ -1100,7 +1328,16 @@
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);
--
Gitblit v1.9.1