From 3180878150f6e22b30394f745008d81cb80af12d Mon Sep 17 00:00:00 2001
From: Jay <csj123456>
Date: 星期四, 17 四月 2025 13:58:02 +0800
Subject: [PATCH] 新增累计真实值和累计预测值的导出
---
iailab-module-model/iailab-module-model-biz/src/main/java/com/iailab/module/model/common/utils/ExcelUtil.java | 173 ++++++++++++++++++++++++++++++---------------------------
1 files changed, 90 insertions(+), 83 deletions(-)
diff --git a/iailab-module-model/iailab-module-model-biz/src/main/java/com/iailab/module/model/common/utils/ExcelUtil.java b/iailab-module-model/iailab-module-model-biz/src/main/java/com/iailab/module/model/common/utils/ExcelUtil.java
index ac1c313..a933e7d 100644
--- a/iailab-module-model/iailab-module-model-biz/src/main/java/com/iailab/module/model/common/utils/ExcelUtil.java
+++ b/iailab-module-model/iailab-module-model-biz/src/main/java/com/iailab/module/model/common/utils/ExcelUtil.java
@@ -6,13 +6,13 @@
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
+import org.springframework.util.CollectionUtils;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
-import java.text.SimpleDateFormat;
-import java.util.Date;
+import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
@@ -147,8 +147,6 @@
//预测图表数据转excel
public static byte[] exportchart(String sheetTitle, PreDataItemChartRespVO respVO, HttpServletResponse response) {
- SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
-
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sheet = wb.createSheet(sheetTitle);
sheet.setDefaultColumnWidth(20);//设置默认行宽
@@ -176,12 +174,14 @@
cellStyle4.setDataFormat(df.getFormat("#,##0.000000"));
// 创建标题
- String[] title = new String[respVO.getLegend().size() * 4];
+ String[] title = new String[1 + respVO.getLegend().size() * 5];
+ title[0] = "日期时间";
for (int i = 0; respVO.getLegend().size() > i; i++) {
- title[i * 4] = "日期时间";
- title[i * 4 + 1] = respVO.getLegend().get(i) + ":真实值";
- title[i * 4 + 2] = "日期时间";
- title[i * 4 + 3] = respVO.getLegend().get(i) + ":预测值";
+ title[i * 3 + 1] = respVO.getLegend().get(i) + ":真实值";
+ title[i * 3 + 2] = respVO.getLegend().get(i) + ":预测值T+N";
+ title[i * 3 + 3] = respVO.getLegend().get(i) + ":预测值T+L";
+ title[i * 3 + 4] = respVO.getLegend().get(i) + ":累计真实值";
+ title[i * 3 + 5] = respVO.getLegend().get(i) + ":累计预测值";
}
HSSFRow rowTitle = sheet.createRow(0);
HSSFCell hc;
@@ -194,84 +194,91 @@
LinkedHashMap<String, PreDataSampleViewRespDTO> viewMap = respVO.getViewMap();
byte result[] = null;
try {
- // 创建表格数据
- //逐列插入
- for (int j = 0; viewMap.size() > j; j++) {
- //去掉标题行，从第一行开始
- //逐行插入
- for (Map.Entry<String, PreDataSampleViewRespDTO> entry : viewMap.entrySet()) {
- //真实值
- List<Object[]> realData = entry.getValue().getRealData();
- //预测值
- List<Object[]> preDataN = entry.getValue().getPreDataN();
- HSSFRow rowBody;
+ HSSFRow rowBody;
+ //插入日期时间
+ for (int i = 0; i < respVO.getCategories().size(); i++) {
+ if (sheet.getRow(i + 1) == null) {
+ rowBody = sheet.createRow(i + 1);
+ } else {
+ rowBody = sheet.getRow(i + 1);
+ }
+ hc = rowBody.createCell(0);
+ hc.setCellValue(respVO.getCategories().get(i));
+ }
- //插入真实值
- for (int i = 0; realData.size() > i; i++) {
- //判断是否创建过行，如果没有创建行，否则更新行
- if (sheet.getRow(i + 1) == null) {
- rowBody = sheet.createRow(i + 1);
- } else {
- rowBody = sheet.getRow(i + 1);
- }
- //插入时间
- hc = rowBody.createCell(j * 4);
- if (realData.get(i)[0] != null) {
- if (realData.get(i)[0] instanceof Date) {
- String formattedDate = sdf.format(realData.get(i)[0]);
- hc.setCellValue(formattedDate);
- } else {
- hc.setCellValue(realData.get(i)[0].toString());
- }
- hc.setCellStyle(cellStyle3);
- } else {
- hc.setCellValue("");
- hc.setCellStyle(cellStyle3);
- }
- //插入值
- hc = rowBody.createCell(j * 4 + 1);
- if (realData.get(i)[1] != null) {
- hc.setCellValue(realData.get(i)[1].toString());
- hc.setCellStyle(cellStyle4);
- } else {
- hc.setCellValue("");
- hc.setCellStyle(cellStyle4);
- }
- }
- //插入预测值
- for (int i = 0; preDataN.size() > i; i++) {
- //判断是否创建过行，如果没有创建行，否则更新行
- if (sheet.getRow(i + 1) == null) {
- rowBody = sheet.createRow(i + 1);
- } else {
- rowBody = sheet.getRow(i + 1);
- }
- //插入时间
- hc = rowBody.createCell(j * 4 + 2);
- if (preDataN.get(i)[0] != null) {
- if (preDataN.get(i)[0] instanceof Date) {
- String formattedDate = sdf.format(preDataN.get(i)[0]);
- hc.setCellValue(formattedDate);
- } else {
- hc.setCellValue(preDataN.get(i)[0].toString());
- }
- hc.setCellStyle(cellStyle3);
- } else {
- hc.setCellValue("");
- hc.setCellStyle(cellStyle3);
- }
- //插入值
- hc = rowBody.createCell(j * 4 + 3);
- if (preDataN.get(i)[1] != null) {
- hc.setCellValue(preDataN.get(i)[1].toString());
- hc.setCellStyle(cellStyle4);
- } else {
- hc.setCellValue("");
- hc.setCellStyle(cellStyle4);
- }
+ //逐列插入 真实值，预测值T+N，预测值T+L
+ int j = 0;
+ for (Map.Entry<String, PreDataSampleViewRespDTO> entry : viewMap.entrySet()) {
+ PreDataSampleViewRespDTO viewData = entry.getValue();
+ Map<String, Object> realDataMap = new HashMap<String, Object>();
+ Map<String, Object> preDataNMap = new HashMap<String, Object>();
+ Map<String, Object> preDataLMap = new HashMap<String, Object>();
+ Map<String, Object> cumulantRealDataMap = new HashMap<String, Object>();
+ Map<String, Object> cumulantPreDataMap = new HashMap<String, Object>();
+ //真实值
+ List<Object[]> realData = viewData.getRealData();
+ if (!CollectionUtils.isEmpty(realData)) {
+ for (Object[] item : realData) {
+ realDataMap.put(item[0].toString(), item[1]);
}
}
+ //预测值T+N
+ List<Object[]> preDataN = viewData.getPreDataN();
+ if (!CollectionUtils.isEmpty(preDataN)) {
+ for (Object[] item : preDataN) {
+ preDataNMap.put(item[0].toString(), item[1]);
+ }
+ }
+ //预测值T+L
+ List<Object[]> preDataL = viewData.getPreDataL();
+ if (!CollectionUtils.isEmpty(preDataL)) {
+ for (Object[] item : preDataL) {
+ preDataLMap.put(item[0].toString(), item[1]);
+ }
+ }
+ //累计真实值
+ List<Object[]> cumulantRealData = viewData.getCumulantRealData();
+ if (!CollectionUtils.isEmpty(cumulantRealData)) {
+ for (Object[] item : cumulantRealData) {
+ cumulantRealDataMap.put(item[0].toString(), item[1]);
+ }
+ }
+ //累计预测值
+ List<Object[]> cumulantPreData = viewData.getCumulantPreData();
+ if (!CollectionUtils.isEmpty(cumulantPreData)) {
+ for (Object[] item : cumulantPreData) {
+ cumulantPreDataMap.put(item[0].toString(), item[1]);
+ }
+ }
+
+ //逐行插入
+ for (int i = 0; i < respVO.getCategories().size(); i++) {
+ rowBody = sheet.getRow(i + 1);
+ if (realDataMap.get(respVO.getCategories().get(i)) != null) {
+ hc = rowBody.createCell(j * 3 + 1);
+ hc.setCellValue(realDataMap.get(respVO.getCategories().get(i)).toString());
+ }
+ if (preDataNMap.get(respVO.getCategories().get(i)) != null) {
+ hc = rowBody.createCell(j * 3 + 2);
+ hc.setCellValue(preDataNMap.get(respVO.getCategories().get(i)).toString());
+ }
+ if (preDataLMap.get(respVO.getCategories().get(i)) != null) {
+ hc = rowBody.createCell(j * 3 + 3);
+ hc.setCellValue(preDataLMap.get(respVO.getCategories().get(i)).toString());
+ }
+ if (cumulantRealDataMap.get(respVO.getCategories().get(i)) != null) {
+ hc = rowBody.createCell(j * 3 + 4);
+ hc.setCellValue(cumulantRealDataMap.get(respVO.getCategories().get(i)).toString());
+ }
+ if (cumulantPreDataMap.get(respVO.getCategories().get(i)) != null) {
+ hc = rowBody.createCell(j * 3 + 5);
+ hc.setCellValue(cumulantPreDataMap.get(respVO.getCategories().get(i)).toString());
+ }
+ }
+
+ j++;
}
+
// 设置Http响应头告诉浏览器下载这个附件
response.setHeader("Content-Disposition", "attachment;Filename=" + System.currentTimeMillis() + ".xls");
OutputStream outputStream = response.getOutputStream();
--
Gitblit v1.9.3