iailab-plat.git

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 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.*;
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) {
 
        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[1 + respVO.getLegend().size() * 3];
        title[0] = "日期时间";
        for (int i = 0; respVO.getLegend().size() > i; 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;
        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 {
            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));
            }
 
            //逐列插入 真实值，预测值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();
            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);
            }
        }
    }
}