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