预测值导出

 iailab-module-model/iailab-module-model-api/src/main/java/com/iailab/module/model/api/mcs/dto/PreDataSampleViewRespDTO.java

@@ -22,6 +22,9 @@
    @Schema(description = "T+N预测值，N表示预测频率")
    private List<Object[]> preDataN;

    @Schema(description = "T+L预测值，L表示预测长度")
    private List<Object[]> preDataL;

    @Schema(description = "累计真实值")
    private List<Object[]> cumulantRealData;


 iailab-module-model/iailab-module-model-biz/src/main/java/com/iailab/module/model/api/McsApiImpl.java

@@ -335,6 +335,7 @@
                viewDto.setRealData(getHisData(out.getPointid(), startTime, endTime, reqVO.getPrec()));
            }
            viewDto.setPreDataN(mmItemResultService.getData(out.getId(), startTime, endTime, DateUtils.FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND));
            viewDto.setPreDataL(mmItemResultLastPointService.getData(out.getId(), startTime, endTime, DateUtils.FORMAT_YEAR_MONTH_DAY_HOUR_MINUTE_SECOND, 3));
            //处理预测累计
            if (out.getIscumulant() == 1) {
                if (StringUtils.isNotBlank(out.getCumulpoint())) {

 iailab-module-model/iailab-module-model-biz/src/main/java/com/iailab/module/model/common/utils/ExcelUtil.java

@@ -6,16 +6,14 @@
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.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.*;
import java.util.logging.Level;
import java.util.logging.Logger;

@@ -147,8 +145,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 +172,12 @@
        cellStyle4.setDataFormat(df.getFormat("#,##0.000000"));

        // 创建标题
        String[] title = new String[respVO.getLegend().size() * 4];
        String[] title = new String[1 + respVO.getLegend().size() * 3];
        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";
        }
        HSSFRow rowTitle = sheet.createRow(0);
        HSSFCell hc;
@@ -194,84 +190,68 @@
        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>();
                //真实值
                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]);
                    }
                }

                //逐行插入
                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());
                    }
                }

                j++;
            }

            // 设置Http响应头告诉浏览器下载这个附件
            response.setHeader("Content-Disposition", "attachment;Filename=" + System.currentTimeMillis() + ".xls");
            OutputStream outputStream = response.getOutputStream();