预测值导出

 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();

 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();
        }
    }
}

 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);
            }
        }
    }
}