From 179784a106eb61845176bed95242d15fdb8d25ff Mon Sep 17 00:00:00 2001
From: dongyukun <1208714201@qq.com>
Date: 星期五, 15 十一月 2024 17:05:36 +0800
Subject: [PATCH] 预测值导出
---
iailab-module-data/iailab-module-data-biz/src/main/java/com/iailab/module/data/point/controller/admin/DaPointController.java | 2
iailab-module-model/iailab-module-model-biz/src/main/java/com/iailab/module/model/common/utils/ExcelUtil.java | 292 ++++++++++++++++++++++++++++++++++++++++++++++++
iailab-module-model/iailab-module-model-biz/src/main/java/com/iailab/module/model/api/controller/admin/McsApiController.java | 45 +++++++
3 files changed, 338 insertions(+), 1 deletions(-)
diff --git a/iailab-module-data/iailab-module-data-biz/src/main/java/com/iailab/module/data/point/controller/admin/DaPointController.java b/iailab-module-data/iailab-module-data-biz/src/main/java/com/iailab/module/data/point/controller/admin/DaPointController.java
index e4d79db..a1788ea 100644
--- a/iailab-module-data/iailab-module-data-biz/src/main/java/com/iailab/module/data/point/controller/admin/DaPointController.java
+++ b/iailab-module-data/iailab-module-data-biz/src/main/java/com/iailab/module/data/point/controller/admin/DaPointController.java
@@ -166,7 +166,7 @@
if (end == null) {
queryDto.setEnd(new Date());
} else {
- queryDto.setStart(formatter.parse(end));
+ queryDto.setEnd(formatter.parse(end));
}
} catch (ParseException e) {
e.printStackTrace();
diff --git a/iailab-module-model/iailab-module-model-biz/src/main/java/com/iailab/module/model/api/controller/admin/McsApiController.java b/iailab-module-model/iailab-module-model-biz/src/main/java/com/iailab/module/model/api/controller/admin/McsApiController.java
index e02730c..f9a7648 100644
--- a/iailab-module-model/iailab-module-model-biz/src/main/java/com/iailab/module/model/api/controller/admin/McsApiController.java
+++ b/iailab-module-model/iailab-module-model-biz/src/main/java/com/iailab/module/model/api/controller/admin/McsApiController.java
@@ -1,20 +1,31 @@
package com.iailab.module.model.api.controller.admin;
+import com.iailab.framework.apilog.core.annotation.ApiAccessLog;
import com.iailab.framework.common.pojo.CommonResult;
+import com.iailab.framework.excel.core.util.ExcelUtils;
+import com.iailab.module.data.api.point.dto.ApiPointValueQueryDTO;
import com.iailab.module.model.api.mcs.McsApi;
import com.iailab.module.model.api.mcs.dto.*;
import com.iailab.module.model.common.utils.ApiSecurityUtils;
+import com.iailab.module.model.common.utils.ExcelUtil;
import io.swagger.v3.oas.annotations.Operation;
import io.swagger.v3.oas.annotations.tags.Tag;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
+import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.web.bind.annotation.*;
import javax.annotation.Resource;
import javax.annotation.security.PermitAll;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
+import java.io.IOException;
+import java.text.SimpleDateFormat;
+import java.util.ArrayList;
+import java.util.Date;
+import java.util.LinkedHashMap;
import java.util.List;
+import static com.iailab.framework.apilog.core.enums.OperateTypeEnum.EXPORT;
/**
* @author PanZhibao
@@ -67,4 +78,38 @@
PreDataItemChartRespVO respVO = mcsApi.getPreDataItemChart(reqVO);
return CommonResult.success(respVO);
}
+
+ @GetMapping("/predict-data/exportValue")
+ @Operation(summary = "导出预测数据")
+ @ApiAccessLog(operateType = EXPORT)
+ public void exportPointValue(@RequestParam("itemId") String itemId,
+ @RequestParam("startTime") String startTime,
+ @RequestParam("endTime") String endTime,
+ HttpServletResponse response) throws IOException {
+ SimpleDateFormat formatter = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
+ PreDataItemChartReqVO reqVO = new PreDataItemChartReqVO();
+ reqVO.setItemId(itemId);
+ try {
+ if (startTime == "") {
+ reqVO.setStartTime(new Date((new Date()).getTime() - 60*60*1000));
+ } else {
+ reqVO.setStartTime(formatter.parse(startTime));
+
+ }
+ if (endTime == "") {
+ reqVO.setEndTime(new Date((new Date()).getTime() + 60*60*1000));
+ } else {
+ reqVO.setEndTime(formatter.parse(endTime));
+ }
+ } catch (Exception e) {
+ e.printStackTrace();
+ }
+ PreDataItemChartRespVO respVO = mcsApi.getPreDataItemChart(reqVO);
+ try {
+ String sheetTitle = "sheet1";
+ ExcelUtil.exportchart(sheetTitle, respVO, response);
+ } catch (Exception ex) {
+ ex.printStackTrace();
+ }
+ }
}
\ No newline at end of file
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
new file mode 100644
index 0000000..ac1c313
--- /dev/null
+++ b/iailab-module-model/iailab-module-model-biz/src/main/java/com/iailab/module/model/common/utils/ExcelUtil.java
@@ -0,0 +1,292 @@
+package com.iailab.module.model.common.utils;
+
+import com.iailab.module.model.api.mcs.dto.PreDataItemChartRespVO;
+import com.iailab.module.model.api.mcs.dto.PreDataSampleViewRespDTO;
+import org.apache.poi.hssf.usermodel.*;
+import org.apache.poi.ss.usermodel.HorizontalAlignment;
+import org.apache.poi.ss.usermodel.VerticalAlignment;
+import org.apache.poi.ss.util.CellRangeAddress;
+
+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.LinkedHashMap;
+import java.util.List;
+import java.util.Map;
+import java.util.logging.Level;
+import java.util.logging.Logger;
+
+/**
+ * Excel 导出通用工具类
+ *
+ * @author PanZhibao
+ * @date 2021年06月29日 9:06
+ */
+public class ExcelUtil {
+
+ /**
+ * 导出PointValueExcel
+ *
+ * @param sheetTitle
+ * @param title
+ * @param list
+ * @param response
+ * @return
+ */
+ public static byte[] exportPointValue(String sheetTitle, String[] title, List<Object> list, HttpServletResponse response) {
+
+ HSSFWorkbook wb = new HSSFWorkbook();//创建excel表
+ HSSFSheet sheet = wb.createSheet(sheetTitle);
+ sheet.setDefaultColumnWidth(20);//设置默认行宽
+
+ //表头样式（加粗，水平居中，垂直居中）
+ HSSFCellStyle cellStyle = wb.createCellStyle();
+ cellStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
+ cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
+
+ HSSFFont fontStyle = wb.createFont();
+
+ cellStyle.setFont(fontStyle);
+
+ //标题样式（加粗，垂直居中）
+ HSSFCellStyle cellStyle2 = wb.createCellStyle();
+ cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
+ cellStyle2.setFont(fontStyle);
+
+ //字段样式（垂直居中）
+ HSSFCellStyle cellStyle3 = wb.createCellStyle();
+ cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
+
+ //数字样式
+ HSSFCellStyle cellStyle4 = wb.createCellStyle();
+ HSSFDataFormat df = wb.createDataFormat(); // 此处设置数据格式
+ cellStyle4.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
+ cellStyle4.setDataFormat(df.getFormat("#,##0.000000"));//设置数值类型格式为保留两位小数
+
+ //创建表头
+ HSSFRow row = sheet.createRow(0);
+ //row.setHeightInPoints(20);//行高
+
+ HSSFCell cell = row.createCell(0);
+ cell.setCellValue(sheetTitle);
+ cell.setCellStyle(cellStyle);
+
+ sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, (title.length - 1)));
+
+ //创建标题
+ HSSFRow rowTitle = sheet.createRow(1);
+ //rowTitle.setHeightInPoints(20);
+
+ HSSFCell hc;
+ for (int i = 0; i < title.length; i++) {
+ hc = rowTitle.createCell(i);
+ hc.setCellValue(title[i]);
+ hc.setCellStyle(cellStyle2);
+ }
+
+ byte result[] = null;
+
+ try {
+ //创建表格数据
+ Field[] fields;
+ int i = 2;
+ for (Object obj : list) {
+ fields = obj.getClass().getDeclaredFields();
+
+ HSSFRow rowBody = sheet.createRow(i);
+ //rowBody.setHeightInPoints(20);
+
+ int j = 0;
+ for (Field f : fields) {
+
+ f.setAccessible(true);
+
+ Object va = f.get(obj);
+ if (null == va) {
+ va = "";
+ }
+
+ hc = rowBody.createCell(j);
+
+ if (j == 0) {
+ hc.setCellValue(Double.parseDouble(va.toString()));
+ hc.setCellStyle(cellStyle4);
+ } else {
+ hc.setCellValue(va.toString());
+ hc.setCellStyle(cellStyle3);
+ }
+
+
+ j++;
+ }
+
+ i++;
+ }
+
+ //设置Http响应头告诉浏览器下载这个附件
+ response.setHeader("Content-Disposition", "attachment;Filename=" + System.currentTimeMillis() + ".xls");
+ OutputStream outputStream = response.getOutputStream();
+ wb.write(outputStream);
+ outputStream.close();
+ return wb.getBytes();
+ } catch (Exception ex) {
+ Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
+ return result;
+ } finally {
+ try {
+ wb.close();
+ } catch (IOException ex) {
+ Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
+ }
+ }
+ }
+
+ //预测图表数据转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);//设置默认行宽
+
+ // 表头样式（加粗，水平居中，垂直居中）
+ HSSFCellStyle cellStyle = wb.createCellStyle();
+ cellStyle.setAlignment(HorizontalAlignment.CENTER);
+ cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
+ HSSFFont fontStyle = wb.createFont();
+ cellStyle.setFont(fontStyle);
+
+ // 标题样式（加粗，垂直居中）
+ HSSFCellStyle cellStyle2 = wb.createCellStyle();
+ cellStyle2.setVerticalAlignment(VerticalAlignment.CENTER);
+ cellStyle2.setFont(fontStyle);
+
+ // 字段样式（垂直居中）
+ HSSFCellStyle cellStyle3 = wb.createCellStyle();
+ cellStyle3.setVerticalAlignment(VerticalAlignment.CENTER);
+
+ // 数字样式
+ HSSFCellStyle cellStyle4 = wb.createCellStyle();
+ HSSFDataFormat df = wb.createDataFormat();
+ cellStyle4.setVerticalAlignment(VerticalAlignment.CENTER);
+ cellStyle4.setDataFormat(df.getFormat("#,##0.000000"));
+
+ // 创建标题
+ String[] title = new String[respVO.getLegend().size() * 4];
+ 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) + ":预测值";
+ }
+ HSSFRow rowTitle = sheet.createRow(0);
+ HSSFCell hc;
+ for (int i = 0; i < title.length; i++) {
+ hc = rowTitle.createCell(i);
+ hc.setCellValue(title[i]);
+ hc.setCellStyle(cellStyle2);
+ }
+
+ 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;
+
+ //插入真实值
+ 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);
+ }
+ }
+ }
+ }
+ // 设置Http响应头告诉浏览器下载这个附件
+ response.setHeader("Content-Disposition", "attachment;Filename=" + System.currentTimeMillis() + ".xls");
+ OutputStream outputStream = response.getOutputStream();
+ wb.write(outputStream);
+ outputStream.close();
+ return wb.getBytes();
+ } catch (Exception ex) {
+ Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
+ return result;
+ } finally {
+ try {
+ wb.close();
+ } catch (IOException ex) {
+ Logger.getLogger(ExcelUtil.class.getName()).log(Level.SEVERE, null, ex);
+ }
+ }
+ }
+}
\ No newline at end of file
--
Gitblit v1.9.3