From ab0fbf96697a57d293111135ef46d735e17895b9 Mon Sep 17 00:00:00 2001
From: whycxzp <glperry@163.com>
Date: 星期五, 30 五月 2025 17:23:36 +0800
Subject: [PATCH] excel导出
---
src/main/java/com/whyc/controller/ExcelController.java | 28 ++
src/main/java/com/whyc/service/ExcelService.java | 456 ++++++++++++++++++++++++++++++++++++-
src/main/java/com/whyc/service/BattRealTimeDataHistoryService.java | 217 ++++++++++++++++++
3 files changed, 688 insertions(+), 13 deletions(-)
diff --git a/src/main/java/com/whyc/controller/ExcelController.java b/src/main/java/com/whyc/controller/ExcelController.java
new file mode 100644
index 0000000..4bb7c3b
--- /dev/null
+++ b/src/main/java/com/whyc/controller/ExcelController.java
@@ -0,0 +1,28 @@
+package com.whyc.controller;
+
+import com.whyc.service.ExcelService;
+import io.swagger.annotations.Api;
+import io.swagger.annotations.ApiOperation;
+import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.web.bind.annotation.GetMapping;
+import org.springframework.web.bind.annotation.RequestMapping;
+import org.springframework.web.bind.annotation.RestController;
+
+import java.io.IOException;
+
+@RequestMapping("excel")
+@RestController
+@Api("excel瀵煎嚭")
+public class ExcelController {
+
+ @Autowired
+ private ExcelService service;
+
+ @GetMapping("dcReport")
+ @ApiOperation(value = "鐩存祦鎶ヨ〃")
+ public void dcReport(int battGroupId,int testRecordCount) throws IOException {
+ service.dcReport(battGroupId,testRecordCount);
+ }
+
+
+}
diff --git a/src/main/java/com/whyc/service/BattRealTimeDataHistoryService.java b/src/main/java/com/whyc/service/BattRealTimeDataHistoryService.java
index 5f265bc..6894c2a 100644
--- a/src/main/java/com/whyc/service/BattRealTimeDataHistoryService.java
+++ b/src/main/java/com/whyc/service/BattRealTimeDataHistoryService.java
@@ -2,19 +2,29 @@
import com.github.pagehelper.PageInfo;
import com.whyc.dto.Response;
+import com.whyc.mapper.CallBack;
import com.whyc.pojo.db_power_history.BattRealTimeDataHistory;
import com.whyc.util.SubTablePageInfoUtil;
import com.whyc.util.ThreadLocalUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
+import java.sql.ResultSet;
+import java.sql.SQLException;
import java.text.ParseException;
+import java.util.Calendar;
+import java.util.Date;
+import java.util.LinkedList;
+import java.util.List;
@Service
public class BattRealTimeDataHistoryService {
@Autowired
private SubTablePageInfoUtil subTablePageInfoUtil;
+
+ @Autowired
+ private MybatisSqlExecuteService sqlExecuteService;
public Response<PageInfo> getPage(int pageNum, int pageSize, int battGroupId,
String startTime, String endTime) throws ParseException, InterruptedException {
@@ -28,4 +38,211 @@
return new Response<PageInfo>().set(1, pageInfo);
}
+ /**
+ * 鍏呯數缁撴潫鏃堕棿鐨勫畾浣嶉�昏緫涓�:
+ * _鐢垫睜缁刬d琛ㄤ腑,璁板綍鏃堕棿澶т簬dischargeEndTime涓旂數娴佷负0鐨勮褰曚腑,椤哄簭鍙栧嚭绗竴涓褰�.
+ * 鑰冭檻鍒板厖鐢电粨鏉熸椂闂村彲鑳借法琛�,鎵�浠ラ渶瑕佹煡璇笅涓�涓湀鐨勮〃.
+ * @return
+ */
+ public BattRealTimeDataHistory getChargeEnd(int battGroupId, Calendar dischargeEndTime) {
+ //鏌ヨ鐨勫勾鏈堜负dischargeEndTime鐨勫勾鏈�
+ int year = dischargeEndTime.get(Calendar.YEAR);
+ int month = dischargeEndTime.get(Calendar.MONTH);
+ //String tableName = "tb_batt_realdata_" + battGroupId + "_" + year + "_" + (month+1);
+ //鏌ヨdischargeEndTime鐨勫勾鏈堝姞涓婁竴涓湀
+ int yearOfNextMonth = year;
+ int monthOfNextMonth = month;
+ if (month == 11) {
+ yearOfNextMonth++;
+ monthOfNextMonth = 0;
+ }else{
+ monthOfNextMonth++;
+ }
+ String battGroupIdTable = getBattGroupIdTable(battGroupId, dischargeEndTime.getTime());
+ String sql = "select * from db_power_history.tb_batt_realdata_" + battGroupIdTable + " where record_time > '" + dischargeEndTime.getTime() + "' and group_curr = 0 limit 1";
+ List<BattRealTimeDataHistory> list = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List<BattRealTimeDataHistory> list = new LinkedList<>();
+ //濡傛灉瀛樺湪璁板綍
+ while (rs.next()) {
+ BattRealTimeDataHistory his = new BattRealTimeDataHistory();
+ his.setBattGroupId(rs.getInt("binf_id"));
+ his.setRecordTime(rs.getTimestamp("record_time"));
+ his.setOnlineVol(rs.getFloat("online_vol"));
+ his.setGroupVol(rs.getFloat("group_vol"));
+ his.setGroupTmp(rs.getFloat("group_tmp"));
+ his.setGroupCurr(rs.getFloat("group_curr"));
+
+ list.add(his);
+ }
+ return list;
+
+ }
+ });
+ //濡傛灉涓嶅瓨鍦ㄨ褰�,鍒欐煡璇笅涓�涓湀鐨勮〃
+ if (list.size() == 0) {
+ //String tableNameNextMonth = "tb_batt_realdata_" + battGroupId + "_" + yearOfNextMonth + "_" + (monthOfNextMonth+1);
+ dischargeEndTime.add(Calendar.MONTH, 1);
+ String battGroupITableNameNextMonth = getBattGroupIdTable(battGroupId, dischargeEndTime.getTime());
+ //杩樺師dischargeEndTime
+ dischargeEndTime.add(Calendar.MONTH, -1);
+ String sqlNextMonth = "select * from db_power_history." + battGroupITableNameNextMonth + " where record_time > '" + dischargeEndTime.getTime() + "' and group_curr = 0 limit 1";
+ List<BattRealTimeDataHistory> listNextMonth = sqlExecuteService.executeQuery_call(sqlNextMonth, new CallBack() {
+
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List<BattRealTimeDataHistory> list = new LinkedList<>();
+ //濡傛灉瀛樺湪璁板綍
+ while (rs.next()) {
+ BattRealTimeDataHistory his = new BattRealTimeDataHistory();
+ his.setBattGroupId(rs.getInt("binf_id"));
+ his.setRecordTime(rs.getTimestamp("record_time"));
+ his.setOnlineVol(rs.getFloat("online_vol"));
+ his.setGroupVol(rs.getFloat("group_vol"));
+ his.setGroupTmp(rs.getFloat("group_tmp"));
+ his.setGroupCurr(rs.getFloat("group_curr"));
+
+ list.add(his);
+ }
+ return list;
+
+ }
+ });
+ return listNextMonth.size() == 0 ? null : listNextMonth.get(0);
+ }else{
+ return list.get(0);
+ }
+ }
+
+ public List<BattRealTimeDataHistory> getFcVolList(int battGroupId, Date testStartTime) {
+ String battGroupIdTable = getBattGroupIdTable(battGroupId, testStartTime);
+ String sql = "select * from db_power_history.tb_batt_realdata_" + battGroupIdTable + " where record_time < '" + testStartTime + "' and batt_test_type = 1 order by num desc limit 104";
+ List<BattRealTimeDataHistory> fcVolList = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List<BattRealTimeDataHistory> list = new LinkedList<>();
+ //濡傛灉瀛樺湪璁板綍
+ while (rs.next()) {
+ BattRealTimeDataHistory his = new BattRealTimeDataHistory();
+ his.setMonNum(rs.getInt("mon_num"));
+ his.setMonVol(rs.getFloat("mon_vol"));
+ his.setMonTmp(rs.getFloat("mon_tmp"));
+
+ list.add(his);
+ }
+ return list;
+
+ }
+ });
+ return fcVolList;
+ }
+
+ public List<BattRealTimeDataHistory> getPreVolList(int battGroupId, Date testStartTime) {
+ String battGroupIdTable = getBattGroupIdTable(battGroupId, testStartTime);
+ String sql = "select * from db_power_history.tb_batt_realdata_" + battGroupIdTable + " where record_time = '" + testStartTime + "' order by mon_num asc limit 104";
+ List<BattRealTimeDataHistory> fcVolList = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List<BattRealTimeDataHistory> list = new LinkedList<>();
+ //濡傛灉瀛樺湪璁板綍
+ while (rs.next()) {
+ BattRealTimeDataHistory his = new BattRealTimeDataHistory();
+ his.setMonNum(rs.getInt("mon_num"));
+ his.setMonVol(rs.getFloat("mon_vol"));
+ his.setMonTmp(rs.getFloat("mon_tmp"));
+
+ list.add(his);
+ }
+ return list;
+
+ }
+ });
+ return fcVolList;
+ }
+
+ public List<BattRealTimeDataHistory> getRecordList(int battGroupId, Date recordTime) {
+ String battGroupIdTable = getBattGroupIdTable(battGroupId, recordTime);
+ String sql = "select * from db_power_history.tb_batt_realdata_" + battGroupIdTable + " where record_time >= '" + recordTime + "' order by num asc limit 104";
+ List<BattRealTimeDataHistory> fcVolList = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List<BattRealTimeDataHistory> list = new LinkedList<>();
+ //濡傛灉瀛樺湪璁板綍
+ while (rs.next()) {
+ BattRealTimeDataHistory his = new BattRealTimeDataHistory();
+ his.setMonNum(rs.getInt("mon_num"));
+ his.setMonVol(rs.getFloat("mon_vol"));
+ his.setMonTmp(rs.getFloat("mon_tmp"));
+
+ list.add(his);
+ }
+ return list;
+
+ }
+ });
+ return fcVolList;
+ }
+
+ public Date getChargeStartTime(int battGroupId, Date recordTime) {
+ String battGroupIdTable = getBattGroupIdTable(battGroupId, recordTime);
+ String sql = "select record_time from db_power_history.tb_batt_realdata_" + battGroupIdTable + " where record_time > '" + recordTime + "' and batt_test_type = 2 order limit 1";
+ List<Date> timeList = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List<Date> list = new LinkedList<>();
+ //濡傛灉瀛樺湪璁板綍
+ while (rs.next()) {
+ Date recordTime = rs.getTimestamp("record_time");
+
+ list.add(recordTime);
+ }
+ return list;
+
+ }
+ });
+ return timeList.get(0);
+ }
+
+ public List<BattRealTimeDataHistory> getFcVolListAfter(int battGroupId, Date testStartTime) {
+ String battGroupIdTable = getBattGroupIdTable(battGroupId, testStartTime);
+ String sql = "select * from db_power_history.tb_batt_realdata_" + battGroupIdTable + " where record_time > '" + testStartTime + "' and batt_test_type = 1 order by num desc limit 104";
+ List<BattRealTimeDataHistory> fcVolList = sqlExecuteService.executeQuery_call(sql, new CallBack() {
+
+ @Override
+ public List getResults(ResultSet rs) throws SQLException {
+ List<BattRealTimeDataHistory> list = new LinkedList<>();
+ //濡傛灉瀛樺湪璁板綍
+ while (rs.next()) {
+ BattRealTimeDataHistory his = new BattRealTimeDataHistory();
+ his.setMonNum(rs.getInt("mon_num"));
+ his.setMonVol(rs.getFloat("mon_vol"));
+ his.setMonTmp(rs.getFloat("mon_tmp"));
+
+ list.add(his);
+ }
+ return list;
+
+ }
+ });
+ return fcVolList;
+ }
+
+ private String getBattGroupIdTable(int battGroupId, Date time) {
+ Calendar timeCalendar = Calendar.getInstance();
+ timeCalendar.setTime(time);
+ int year = timeCalendar.get(Calendar.YEAR);
+ int month = timeCalendar.get(Calendar.MONTH)+1;
+ if (month < 10){
+ return battGroupId + "_" + year + "_0" + month;
+ }else{
+ return battGroupId + "_" + month + "_" + month;
+ }
+ }
+
}
diff --git a/src/main/java/com/whyc/service/ExcelService.java b/src/main/java/com/whyc/service/ExcelService.java
index 3f33c71..0e39f6f 100644
--- a/src/main/java/com/whyc/service/ExcelService.java
+++ b/src/main/java/com/whyc/service/ExcelService.java
@@ -1,25 +1,48 @@
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.pojo.db_batt.PowerInf;
+import com.whyc.pojo.db_dis_batt.BattTestInf;
+import com.whyc.pojo.db_power_history.BattRealTimeDataHistory;
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.ss.util.CellRangeAddress;
+import org.apache.poi.xssf.usermodel.*;
+import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import java.io.FileOutputStream;
import java.io.IOException;
-import java.util.Date;
+import java.math.BigDecimal;
+import java.math.RoundingMode;
+import java.util.*;
+import java.util.stream.Collectors;
@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;
+
+
+ /**
+ * 灏嗙數姹犵粍鏀剧數鍏呯數鏁版嵁鍐欏叆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);
// 灏嗗伐浣滅翱鍐欏叆鏂囦欢
FileOutputStream fileOut = new FileOutputStream(filePath);
@@ -30,13 +53,420 @@
}
- private void createSheet1(XSSFWorkbook workbook) {
+ 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 = workbook.createCellStyle();
+ Font font = workbook.createFont();
+ font.setBold(true);
+ cellStyleFontBold.setFont(font);
+
+ //鍒涘缓绗竴琛屾槸鏍囬琛�
+ XSSFCell cellRow1 = sheet.createRow(0).createCell(0);
+ setRowStyle(sheet.getRow(0),cellStyleFontBold);
+
+ cellRow1.setCellValue(battInfo.getPowerName()+battInfo.getBattGroupName()+"鏍稿鎬ф斁鐢点�佸厖鐢佃瘯楠岃褰曡〃");
+ //鍚堝苟琛屽垪,鍗犳嵁鐨勫垪鏄痗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);
+
+ //绗笁琛岀殑鍚堝苟鍒�
+ CellRangeAddress region31 = new CellRangeAddress(2, 2, 1, 2);
+ CellRangeAddress region32 = new CellRangeAddress(2, 2, 3, 4);
+ CellRangeAddress region33 = new CellRangeAddress(2, 2, 5, 6);
+ CellRangeAddress region34 = new CellRangeAddress(2, 2, 7, 8);
+ sheet.addMergedRegion(region31);
+ sheet.addMergedRegion(region32);
+ sheet.addMergedRegion(region33);
+ sheet.addMergedRegion(region34);
+
+ // 鍒涘缓灞呬腑鏍峰紡
+ CellStyle style = workbook.createCellStyle();
+ style.setAlignment(HorizontalAlignment.CENTER);
+ style.setVerticalAlignment(VerticalAlignment.CENTER);
+ // 搴旂敤鏍峰紡鍒板崟鍏冩牸
+ cellRow1.setCellStyle(style);
+
+ //绗簩琛屾槸鏀剧數鍩烘湰淇℃伅
+ XSSFRow row1 = sheet.createRow(1);
+ XSSFCell cell1Row2 = row1.createCell(0);
+ cell1Row2.setCellValue("钃勭數姹犵粍鍙�:" + battInfo.getBattGroupName());
+
+ XSSFCell cell2Row2 = row1.createCell(2);
+ cell2Row2.setCellValue("");
+
+ XSSFCell cell3Row2 = row1.createCell(3);
+ cell3Row2.setCellValue("宸ヤ綔绁ㄥ彿锛�2273925\n" +
+ "宸ヤ綔璐熻矗浜猴細椹簯鐕昞n" +
+ "宸ヤ綔鎴愬憳锛氬懆绱犳枃銆佹潕蹇楁爣銆佺帇鏈堢伩绛�");
+
+ XSSFCell cell4Row2 = row1.createCell(10);
+ cell4Row2.setCellValue("鏀剧數鏃ユ湡锛�2021.10.30\n" +
+ "鏀剧數寮�濮嬫椂闂达細10:00\n" +
+ "鏀剧數缁撴潫鏃堕棿锛�19:47");
+
+ XSSFCell cell5Row2 = row1.createCell(14);
+ cell5Row2.setCellValue("鏀剧數鐢垫祦锛圓锛夛細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);
+ row2.createCell(0).setCellValue("钃勭數姹犲彿锛�#锛�");
+ row2.createCell(1).setCellValue("钃勭數姹犳诞鍏呯數鍘嬪�硷紙V锛�");
+ row2.createCell(3).setCellValue("鏀剧數鍓嶈搫鐢垫睜寮�璺數鍘嬪�硷紙V锛�");
+
+ //鏀剧數鐗瑰畾鐢靛帇鍊�
+ int columnIndex = 3;
+ if(dischargeColumnCount > 0){
+ row2.createCell(5).setCellValue("鏀剧數0.5h钃勭數姹犵數鍘嬪�硷紙V锛�");
+ columnIndex+=2;
+ if(dischargeColumnCount > 2){ //鍚堝苟鍗曞厓鏍间簡,鎵�浠ョ储寮�+2
+ row2.createCell(7).setCellValue("鏀剧數1h钃勭數姹犵數鍘嬪�硷紙V锛�");
+ columnIndex+=2;
+ if(dischargeColumnCount > 4){
+ columnIndex++;
+ //鏀剧數nh钃勭數姹犵數鍘嬪�硷紙V锛�,n浠�2寮�濮�
+ for(int i = 4;i < dischargeColumnCount;i++){
+ row2.createCell(i+5).setCellValue("鏀剧數" + (i-2) + "h钃勭數姹犵數鍘嬪�硷紙V锛�");
+ columnIndex++;
+ }
+
+ }
+ }
+ }
+
+ //鍏呯數鐗瑰畾鐢靛帇鍊�
+ row2.createCell(++columnIndex).setCellValue("鏀剧數缁撴潫0.5h鍚庤搫鐢垫睜寮�璺數鍘嬪�硷紙V锛�");
+ if(chargeColumnCount > 0){
+ columnIndex++;
+ row2.createCell(columnIndex).setCellValue("鍏呯數0.5h");
+
+ if(chargeColumnCount > 1){
+ //鍏呯數nh,n浠�1寮�濮�
+ for(int i = 1;i < chargeColumnCount;i++){
+ columnIndex++;
+ row2.createCell(columnIndex).setCellValue("鍏呯數" + (i) + "h钃勭數姹犵數鍘嬪�硷紙V锛�");
+ }
+
+ }
+ }
+ row2.createCell(++columnIndex).setCellValue("鍏呮弧鍚庤搫鐢垫睜鐢靛帇鍊硷紙V)锛堢數娴佷负0锛�");
+ row2.createCell(++columnIndex).setCellValue("澶囨敞");
+
+
+ //浠巖owIndex=6寮�濮�
+ int rowIndex = 6;
+ // 鍏�104鑺傚崟浣�,104琛�. 杩涜閬嶅巻,濉厖
+ //钃勭數姹犳诞鍏呯數鍘嬪�煎垪琛�
+ List<BattRealTimeDataHistory> fcVolList = new ArrayList<>();
+ fcVolList = battRTDataHisService.getFcVolList(battGroupId,dischargeStartTime);
+ //鎸夊崟浣撶紪鍙锋帓搴�,鎸夌紪鍙峰崌搴�
+ fcVolList.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
+ //鏀剧偣鍓嶈搫鐢垫睜寮�璺數鍘嬪��
+ List<BattRealTimeDataHistory> preVolList = new ArrayList<>();
+ preVolList = battRTDataHisService.getPreVolList(battGroupId,dischargeStartTime);
+
+ addStatistics(fcVolList);
+ addStatistics(preVolList);
+
+ //涓�鍏�104鑺傚崟浣�,鍐嶅姞涓婄粺璁℃暟鎹�4鍒�,涓�鍏�108
+ for(int j = 0; j < 108; j++){
+ XSSFRow rowTemp = sheet.createRow(rowIndex++);
+ if(j<104) {
+ rowTemp.createCell(0).setCellValue((j + 1) + "#");
+ }else{
+ if(j == 104){
+ rowTemp.createCell(0).setCellValue("鍗曡妭骞冲潎鐢靛帇");
+ }else if (j == 105){
+ rowTemp.createCell(0).setCellValue("鍗曡妭鏈�澶х數鍘�");
+ }else if (j == 106){
+ rowTemp.createCell(0).setCellValue("鍗曡妭鏈�灏忕數鍘�");
+ }else{
+ rowTemp.createCell(0).setCellValue("鍗曡妭鏈�澶у樊鍊�");
+ }
+ }
+ //rowTemp.createCell(1).setCellValue("钃勭數姹犳诞鍏呯數鍘嬪�硷紙V锛�:"+(j+1)+"#");
+ //rowTemp.createCell(2).setCellValue("钃勭數姹犳诞鍏呮俯搴﹀��:"+(j+1)+"#");
+ //rowTemp.createCell(3).setCellValue("鏀剧數鍓嶈搫鐢垫睜寮�璺數鍘嬪�硷紙V锛�:"+(j+1)+"#");
+ //rowTemp.createCell(4).setCellValue("鏀剧數鍓嶈搫鐢垫睜寮�璺俯搴﹀��:"+(j+1)+"#");
+ rowTemp.createCell(1).setCellValue(fcVolList.get(j).getMonVol());
+ rowTemp.createCell(1).setCellValue(fcVolList.get(j).getMonTmp());
+
+ rowTemp.createCell(1).setCellValue(preVolList.get(j).getMonVol());
+ rowTemp.createCell(1).setCellValue(preVolList.get(j).getMonTmp());
+
+
+ //鏀剧數鐗瑰畾鐢靛帇鍊�
+ int columnIndexTemp = 3;
+ Calendar dischargeCalendar = Calendar.getInstance();
+ dischargeCalendar.setTime(dischargeStartTime);
+ if(dischargeColumnCount > 0){
+ //鏃堕棿鍦ㄦ祴璇曞紑濮嬫椂闂�+0.5h
+ dischargeCalendar.add(Calendar.MINUTE,30);
+ Date recordTime = dischargeCalendar.getTime();
+ List<BattRealTimeDataHistory> dischargeListOne = battRTDataHisService.getRecordList(battGroupId,recordTime);
+ //鎸夊崟浣撶紪鍙锋帓搴�,鎸夌紪鍙峰崌搴�
+ dischargeListOne.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
+ addStatistics(dischargeListOne);
+ //rowTemp.createCell(5).setCellValue("鏀剧數0.5h鐢靛帇鍊硷紙V锛�:"+(j+1)+"#");
+ //rowTemp.createCell(6).setCellValue("鏀剧數0.5h娓╁害鍊�:"+(j+1)+"#");
+ rowTemp.createCell(5).setCellValue(dischargeListOne.get(j).getMonVol());
+ rowTemp.createCell(6).setCellValue(dischargeListOne.get(j).getMonTmp());
+ columnIndexTemp+=2;
+ if(dischargeColumnCount > 2){ //鍚堝苟鍗曞厓鏍间簡,鎵�浠ョ储寮�+2
+ //鏃堕棿鍦ㄦ祴璇曞紑濮嬫椂闂�+1h
+ dischargeCalendar.setTime(dischargeStartTime);
+ dischargeCalendar.add(Calendar.HOUR,1);
+ Date recordTimeTwo = dischargeCalendar.getTime();
+ List<BattRealTimeDataHistory> dischargeListTwo = battRTDataHisService.getRecordList(battGroupId,recordTimeTwo);
+ dischargeListTwo.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
+ addStatistics(dischargeListTwo);
+ //rowTemp.createCell(7).setCellValue("鏀剧數1h鐢靛帇鍊硷紙V锛�:"+(j+1)+"#");
+ //rowTemp.createCell(8).setCellValue("鏀剧數1h娓╁害鍊�:"+(j+1)+"#");
+ rowTemp.createCell(7).setCellValue(dischargeListTwo.get(j).getMonVol());
+ rowTemp.createCell(8).setCellValue(dischargeListTwo.get(j).getMonTmp());
+ columnIndexTemp+=2;
+ if(dischargeColumnCount > 4){
+ columnIndexTemp++;
+ //鏀剧數nh钃勭數姹犵數鍘嬪�硷紙V锛�,n浠�2寮�濮�
+ for(int i = 4;i < dischargeColumnCount;i++){
+ int dischargeHour = i-2;
+ //鏃堕棿鍦ㄦ祴璇曞紑濮嬫椂闂�+nh
+ dischargeCalendar.setTime(dischargeStartTime);
+ dischargeCalendar.add(Calendar.HOUR,dischargeHour);
+ Date recordTimeN = dischargeCalendar.getTime();
+ List<BattRealTimeDataHistory> dischargeListN = battRTDataHisService.getRecordList(battGroupId,recordTimeN);
+ dischargeListN.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
+ addStatistics(dischargeListN);
+ //rowTemp.createCell(i+5).setCellValue("鏀剧數" + dischargeHour + "h钃勭數姹犵數鍘嬪�硷紙V锛�");
+ rowTemp.createCell(i+5).setCellValue(dischargeListN.get(j).getMonVol());
+ columnIndexTemp++;
+ }
+
+ }
+ }
+ }
+
+ //鍏呯數鐗瑰畾鐢靛帇鍊�
+ Calendar dischargeEndCalendar = Calendar.getInstance();
+ dischargeEndCalendar.setTime(dischargeEndTime.getTime());
+ dischargeEndCalendar.add(Calendar.MINUTE,30);
+ List<BattRealTimeDataHistory> dischargeSetVolListOne = battRTDataHisService.getRecordList(battGroupId, dischargeEndCalendar.getTime());
+ dischargeSetVolListOne.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
+ addStatistics(dischargeSetVolListOne);
+ rowTemp.createCell(++columnIndexTemp).setCellValue(dischargeSetVolListOne.get(j).getMonVol());
+
+ //瀹氫綅鍒板厖鐢靛紑濮嬫椂闂�,鐘舵�佸彉涓哄厖鐢电殑璁板綍鏃堕棿
+ Date chargeStartTime = battRTDataHisService.getChargeStartTime(battGroupId, dischargeEndTime.getTime());
+ if(chargeColumnCount > 0){
+ Calendar chargeCalendar = Calendar.getInstance();
+ chargeCalendar.setTime(chargeStartTime);
+ chargeCalendar.add(Calendar.MINUTE,30);
+ List<BattRealTimeDataHistory> chargeVolListOne = battRTDataHisService.getRecordList(battGroupId, chargeCalendar.getTime());
+ chargeVolListOne.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
+ addStatistics(chargeVolListOne);
+ columnIndexTemp++;
+ //rowTemp.createCell(columnIndexTemp).setCellValue("鍏呯數0.5h");
+ rowTemp.createCell(columnIndexTemp).setCellValue(chargeVolListOne.get(j).getMonVol());
+
+ if(chargeColumnCount > 1){
+ //鍏呯數nh,n浠�1寮�濮�
+ for(int i = 1;i < chargeColumnCount;i++){
+ columnIndexTemp++;
+
+ chargeCalendar.setTime(chargeStartTime);
+ chargeCalendar.add(Calendar.HOUR,i);
+ List<BattRealTimeDataHistory> chargeVolListN = battRTDataHisService.getRecordList(battGroupId, chargeCalendar.getTime());
+ chargeVolListN.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
+ addStatistics(chargeVolListN);
+ //rowTemp.createCell(columnIndexTemp).setCellValue("鍏呯數" + (i) + "h钃勭數姹犵數鍘嬪�硷紙V锛�");
+ rowTemp.createCell(columnIndexTemp).setCellValue(chargeVolListN.get(j).getMonVol());
+ }
+
+ }
+ }
+
+ Calendar chargeCalendar = Calendar.getInstance();
+ chargeCalendar.setTime(chargeStartTime);
+ List<BattRealTimeDataHistory> fcVolListAfter = battRTDataHisService.getFcVolListAfter(battGroupId, chargeCalendar.getTime());
+ fcVolListAfter.sort(Comparator.comparingInt(BattRealTimeDataHistory::getMonNum));
+ addStatistics(fcVolListAfter);
+ //rowTemp.createCell(++columnIndexTemp).setCellValue("鍏呮弧鍚庤搫鐢垫睜鐢靛帇鍊硷紙V)锛堢數娴佷负0锛�");
+ if (fcVolListAfter.size() > 0){
+ rowTemp.createCell(++columnIndexTemp).setCellValue(fcVolListAfter.get(j).getMonVol());
+ }else {
+ rowTemp.createCell(++columnIndexTemp).setCellValue("-");
+ }
+ rowTemp.createCell(++columnIndexTemp).setCellValue("-");
+ }
+
+ //=====================鏍煎紡璁剧疆=====================//
+ //璁剧疆鍒楀
+ for (int i = 0; i < columnCount; i++) {
+ sheet.setColumnWidth(i, 256 * 20);
+ }
+ //瀵规暣涓〃鏍艰繘琛屽叏灞�璁剧疆,鏆備负璁剧疆杈规
+ addGlobalStylesToAllCells(sheet, workbook);
+ }
+
+ //瀵逛紶鍏ョ殑BattRealTimeDataHistory鍒楄〃杩涜缁熻,灏嗙粺璁＄粨鏋滀篃杩藉姞鍒板垪琛ㄤ腑
+ public void addStatistics(List<BattRealTimeDataHistory> battRealTimeDataHistoryList) {
+ if(battRealTimeDataHistoryList != null && battRealTimeDataHistoryList.size() > 0){
+ //鐢靛帇骞冲潎鍊�,鏈�澶у��,鏈�灏忓��,鏈�澶у樊鍊�
+ List<Float> volList = battRealTimeDataHistoryList.stream().map(BattRealTimeDataHistory::getMonVol).collect(Collectors.toList());
+ Double volAvgDouble = volList.stream().collect(Collectors.averagingDouble(Float::floatValue));
+ BigDecimal avg = new BigDecimal(volAvgDouble).setScale(4, RoundingMode.HALF_UP);
+ float volAvg = avg.floatValue();
+
+ //鏈�澶у��
+ float volMax = volList.stream().max(Float::compareTo).get();
+ BigDecimal max = new BigDecimal(volMax).setScale(4, RoundingMode.HALF_UP);
+ volMax = max.floatValue();
+
+ //鏈�灏忓��
+ float volMin = volList.stream().min(Float::compareTo).get();
+ BigDecimal min = new BigDecimal(volMin).setScale(4, RoundingMode.HALF_UP);
+ volMin = min.floatValue();
+
+ //鏈�澶у樊鍊�
+ float volGap = volMax - volMin;
+
+ BattRealTimeDataHistory hisAvg = new BattRealTimeDataHistory();
+ hisAvg.setMonVol(volAvg);
+
+ BattRealTimeDataHistory hisMax = new BattRealTimeDataHistory();
+ hisMax.setMonVol(volMax);
+
+ BattRealTimeDataHistory hisMin = new BattRealTimeDataHistory();
+ hisMin.setMonVol(volMin);
+
+ BattRealTimeDataHistory hisGap = new BattRealTimeDataHistory();
+ hisGap.setMonVol(volGap);
+
+ battRealTimeDataHistoryList.add(hisAvg);
+ battRealTimeDataHistoryList.add(hisMax);
+ battRealTimeDataHistoryList.add(hisMin);
+ battRealTimeDataHistoryList.add(hisGap);
+ }
+ }
+
+ public void setRowStyle(Row row, CellStyle style) {
+ for (Cell cell : row) {
+ cell.setCellStyle(style);
+ }
+ }
+
+ /**
+ * 杩欎釜鏂规硶鏈�鍚庢墽琛�,鐢ㄤ簬瀵瑰凡琚垱寤虹殑琛屽唴鐨勬墍鏈夊崟鍏冩牸娣诲姞杈规.鑳借嚜鍔ㄨ瘑鍒鍒涘缓鐨勬墍鏈夊垪鍜屾墍鏈夊崟鍏冩牸
+ * @param sheet
+ * @param workbook
+ */
+ public void addGlobalStylesToAllCells(Sheet sheet, Workbook workbook) {
+ //鎵�鏈夎竟妗嗘牱寮�
+ CellStyle borderedStyle = workbook.createCellStyle();
+ //borderedStyle.cloneStyleFrom(sheet.getRow(0).getCell(0).getCellStyle()); // 鍙�夛細澶嶅埗宸叉湁鏍峰紡
+ borderedStyle.setBorderTop(BorderStyle.THIN);
+ borderedStyle.setBorderBottom(BorderStyle.THIN);
+ borderedStyle.setBorderLeft(BorderStyle.THIN);
+ borderedStyle.setBorderRight(BorderStyle.THIN);
+
+ for (Row row : sheet) {
+ for (Cell cell : row) {
+ cell.setCellStyle(borderedStyle);
+ }
+ }
}
--
Gitblit v1.9.1