package com.wgcloud.service;
|
|
import com.alibaba.excel.EasyExcel;
|
import com.wgcloud.dto.*;
|
import com.wgcloud.entity.*;
|
import com.wgcloud.util.DateUtil;
|
import com.wgcloud.util.FormatUtil;
|
import com.wgcloud.util.staticvar.StaticKeys;
|
import org.slf4j.Logger;
|
import org.slf4j.LoggerFactory;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Service;
|
|
import javax.servlet.ServletOutputStream;
|
import javax.servlet.http.HttpServletResponse;
|
import java.io.IOException;
|
import java.util.*;
|
|
/**
|
* @version v3.3
|
* @ClassName:ExcelExportService.java
|
* @author: http://www.wgstart.com
|
* @date: 2021年8月22日
|
* @Description: 主机、进程趋势图导出excel
|
* @Copyright: 2019-2021 wgcloud. All rights reserved.
|
*/
|
@Service
|
public class ExcelExportService {
|
|
private static final Logger logger = LoggerFactory.getLogger(ExcelExportService.class);
|
|
@Autowired
|
CpuStateService cpuStateService;
|
@Autowired
|
CustomStateService customStateService;
|
@Autowired
|
MemStateService memStateService;
|
@Autowired
|
NetIoStateService netIoStateService;
|
@Autowired
|
SysLoadStateService sysLoadStateService;
|
@Autowired
|
AppStateService appStateService;
|
@Autowired
|
DbTableCountService dbTableCountService;
|
@Autowired
|
DockerStateService dockerStateService;
|
@Autowired
|
HeathStateService heathStateService;
|
@Autowired
|
DceStateService dceStateService;
|
@Autowired
|
SnmpStateService snmpStateService;
|
@Autowired
|
FileWarnStateService fileWarnStateService;
|
|
|
/**
|
* 导出主机趋势图指标excel
|
*
|
* @param params
|
* @param response
|
*/
|
public void exportExcel(Map<String, Object> params, HttpServletResponse response) {
|
ServletOutputStream out = null;
|
try {
|
List<String> datetimeList = new ArrayList<>();
|
List<CpuState> cpuStateList = cpuStateService.selectAllByParams(params);
|
Map<String, CpuState> cpuStateMap = new HashMap<>();
|
for (CpuState cpuState : cpuStateList) {
|
datetimeList.add(cpuState.getDateStr());
|
cpuStateMap.put(cpuState.getDateStr(), cpuState);
|
}
|
List<MemState> memStateList = memStateService.selectAllByParams(params);
|
Map<String, MemState> memStateMap = new HashMap<>();
|
for (MemState memState : memStateList) {
|
memStateMap.put(memState.getDateStr(), memState);
|
}
|
List<SysLoadState> ysLoadSstateList = sysLoadStateService.selectAllByParams(params);
|
Map<String, SysLoadState> ysLoadSstateMap = new HashMap<>();
|
for (SysLoadState sysLoadState : ysLoadSstateList) {
|
ysLoadSstateMap.put(sysLoadState.getDateStr(), sysLoadState);
|
}
|
List<NetIoState> netIoStateList = netIoStateService.selectAllByParams(params);
|
Map<String, NetIoState> netIoStateMap = new HashMap<>();
|
for (NetIoState netIoState : netIoStateList) {
|
netIoStateMap.put(netIoState.getDateStr(), netIoState);
|
}
|
List<ExcelChartDto> excelChartList = new ArrayList<>();
|
for (String datetimeStr : datetimeList) {
|
ExcelChartDto dto = new ExcelChartDto();
|
dto.setDatetime(datetimeStr);
|
if (null != cpuStateMap.get(datetimeStr)) {
|
dto.setCpuPer(cpuStateMap.get(datetimeStr).getSys());
|
}
|
if (null != memStateMap.get(datetimeStr)) {
|
dto.setMemPer(memStateMap.get(datetimeStr).getUsePer());
|
}
|
if (null != netIoStateMap.get(datetimeStr)) {
|
dto.setDropin(netIoStateMap.get(datetimeStr).getDropin());
|
dto.setDropout(netIoStateMap.get(datetimeStr).getDropout());
|
dto.setRxbyt(netIoStateMap.get(datetimeStr).getRxbyt());
|
dto.setTxbyt(netIoStateMap.get(datetimeStr).getTxbyt());
|
dto.setRxpck(netIoStateMap.get(datetimeStr).getRxpck());
|
dto.setTxpck(netIoStateMap.get(datetimeStr).getTxpck());
|
dto.setNetConnections(netIoStateMap.get(datetimeStr).getNetConnections());
|
}
|
if (null != ysLoadSstateMap.get(datetimeStr)) {
|
dto.setOneLoad(ysLoadSstateMap.get(datetimeStr).getOneLoad());
|
dto.setFiveLoad(ysLoadSstateMap.get(datetimeStr).getFiveLoad());
|
dto.setFifteenLoad(ysLoadSstateMap.get(datetimeStr).getFifteenLoad());
|
}
|
excelChartList.add(dto);
|
}
|
String hostname = params.get("hostname").toString();
|
String fileName = DateUtil.getCurrentDateTimeNoChar() + "_" + hostname;
|
response.setContentType("application/vnd.ms-exce");
|
response.setCharacterEncoding("utf-8");
|
response.addHeader("Content-Disposition", "filename=" + fileName + ".xlsx");
|
EasyExcel.write(response.getOutputStream(), ExcelChartDto.class).sheet("sheet").doWrite(excelChartList);
|
} catch (Exception e) {
|
logger.error("主机趋势图导出excel错误", e);
|
} finally {
|
try {
|
// 关闭输出流
|
if (out != null) {
|
out.close();
|
}
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
|
/**
|
* 导出所有主机列表excel
|
*
|
* @param params
|
* @param response
|
*/
|
public void exportHostListExcel(List<SystemInfo> systemInfoList, HttpServletResponse response) {
|
ServletOutputStream out = null;
|
try {
|
List<HostListExcelDto> excelList = new ArrayList<>();
|
for (SystemInfo systemInfo : systemInfoList) {
|
HostListExcelDto dto = new HostListExcelDto();
|
dto.setAgentVer(systemInfo.getAgentVer());
|
dto.setSubmitSeconds(systemInfo.getSubmitSeconds());
|
dto.setBootTimeStr(systemInfo.getBootTimeStr());
|
dto.setBytesRecv(systemInfo.getBytesRecv());
|
dto.setBytesSent(systemInfo.getBytesSent());
|
dto.setFifteenLoad(systemInfo.getFifteenLoad());
|
dto.setCpuPer(systemInfo.getCpuPer());
|
dto.setMemPer(systemInfo.getMemPer());
|
dto.setFiveLoad(systemInfo.getFiveLoad());
|
dto.setRxbyt(systemInfo.getRxbyt());
|
dto.setTxbyt(systemInfo.getTxbyt());
|
dto.setCpuCoreNum(systemInfo.getCpuCoreNum());
|
dto.setCpuXh(systemInfo.getCpuXh());
|
dto.setDiskPer(systemInfo.getDiskPer());
|
dto.setGroupId(systemInfo.getGroupId());
|
dto.setHostname(systemInfo.getHostname());
|
dto.setHostnameExt(systemInfo.getHostnameExt());
|
dto.setRemark(systemInfo.getRemark());
|
dto.setNetConnections(systemInfo.getNetConnections());
|
dto.setPlatformVersion(systemInfo.getPlatformVersion());
|
if (StaticKeys.DOWN_STATE.equals(systemInfo.getState())) {
|
dto.setState("下线");
|
} else {
|
dto.setState("在线");
|
}
|
dto.setPlatForm(systemInfo.getPlatForm());
|
dto.setPlatformVersion(systemInfo.getPlatformVersion());
|
dto.setProcs(systemInfo.getProcs());
|
dto.setWarnCount(systemInfo.getWarnCount());
|
dto.setTotalMem(systemInfo.getTotalMem());
|
dto.setCreateTime(DateUtil.getDateTimeString(systemInfo.getCreateTime()));
|
dto.setUptimeStr(systemInfo.getUptimeStr());
|
dto.setTotalSwapMem(systemInfo.getTotalSwapMem());
|
dto.setSwapMemPer(systemInfo.getSwapMemPer());
|
|
excelList.add(dto);
|
}
|
String fileName = DateUtil.getCurrentDateTimeNoChar() + "_hostList";
|
response.setContentType("application/vnd.ms-exce");
|
response.setCharacterEncoding("utf-8");
|
response.addHeader("Content-Disposition", "filename=" + fileName + ".xlsx");
|
EasyExcel.write(response.getOutputStream(), HostListExcelDto.class).sheet("sheet").doWrite(excelList);
|
} catch (Exception e) {
|
logger.error("主机列表导出excel错误", e);
|
} finally {
|
try {
|
// 关闭输出流
|
if (out != null) {
|
out.close();
|
}
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
|
/**
|
* 导出进程趋势图指标excel
|
*
|
* @param params
|
* @param response
|
*/
|
public void exportAppExcel(Map<String, Object> params, HttpServletResponse response) {
|
ServletOutputStream out = null;
|
try {
|
List<AppState> appStateList = appStateService.selectAllByParams(params);
|
List<AppExcelChartDto> excelChartList = new ArrayList<>();
|
for (AppState appState : appStateList) {
|
AppExcelChartDto appExcelChartDto = new AppExcelChartDto();
|
appExcelChartDto.setDatetime(appState.getDateStr());
|
appExcelChartDto.setCpuPer(appState.getCpuPer());
|
appExcelChartDto.setMemPer(appState.getMemPer());
|
appExcelChartDto.setThreadsNum(appState.getThreadsNum());
|
excelChartList.add(appExcelChartDto);
|
}
|
String fileName = DateUtil.getCurrentDateTimeNoChar() + "_appState";
|
response.setContentType("application/vnd.ms-exce");
|
response.setCharacterEncoding("utf-8");
|
response.addHeader("Content-Disposition", "filename=" + fileName + ".xlsx");
|
EasyExcel.write(response.getOutputStream(), AppExcelChartDto.class).sheet("sheet").doWrite(excelChartList);
|
} catch (Exception e) {
|
logger.error("进程趋势图导出excel错误", e);
|
} finally {
|
try {
|
// 关闭输出流
|
if (out != null) {
|
out.close();
|
}
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
|
|
/**
|
* 导出服务接口趋势图指标excel
|
*
|
* @param params
|
* @param response
|
*/
|
public void exportHeathExcel(Map<String, Object> params, HttpServletResponse response) {
|
ServletOutputStream out = null;
|
try {
|
List<HeathState> appStateList = heathStateService.selectAllByParams(params);
|
List<HeathExcelChartDto> excelChartList = new ArrayList<>();
|
for (HeathState heathState : appStateList) {
|
HeathExcelChartDto heathExcelChartDto = new HeathExcelChartDto();
|
heathExcelChartDto.setDatetime(heathState.getDateStr());
|
heathExcelChartDto.setResTimes(heathState.getResTimes());
|
excelChartList.add(heathExcelChartDto);
|
}
|
String fileName = DateUtil.getCurrentDateTimeNoChar() + "_heathState";
|
response.setContentType("application/vnd.ms-exce");
|
response.setCharacterEncoding("utf-8");
|
response.addHeader("Content-Disposition", "filename=" + fileName + ".xlsx");
|
EasyExcel.write(response.getOutputStream(), HeathExcelChartDto.class).sheet("sheet").doWrite(excelChartList);
|
} catch (Exception e) {
|
logger.error("服务接口趋势图导出excel错误", e);
|
} finally {
|
try {
|
// 关闭输出流
|
if (out != null) {
|
out.close();
|
}
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
|
/**
|
* 导出ping设备趋势图指标excel
|
*
|
* @param params
|
* @param response
|
*/
|
public void exportDceExcel(Map<String, Object> params, HttpServletResponse response) {
|
ServletOutputStream out = null;
|
try {
|
List<DceState> appStateList = dceStateService.selectAllByParams(params);
|
List<DceExcelChartDto> excelChartList = new ArrayList<>();
|
for (DceState dceState : appStateList) {
|
DceExcelChartDto dceExcelChartDto = new DceExcelChartDto();
|
dceExcelChartDto.setDatetime(dceState.getDateStr());
|
dceExcelChartDto.setResTimes(dceState.getResTimes());
|
excelChartList.add(dceExcelChartDto);
|
}
|
String fileName = DateUtil.getCurrentDateTimeNoChar() + "_pingState";
|
response.setContentType("application/vnd.ms-exce");
|
response.setCharacterEncoding("utf-8");
|
response.addHeader("Content-Disposition", "filename=" + fileName + ".xlsx");
|
EasyExcel.write(response.getOutputStream(), DceExcelChartDto.class).sheet("sheet").doWrite(excelChartList);
|
} catch (Exception e) {
|
logger.error("ping设备趋势图导出excel错误", e);
|
} finally {
|
try {
|
// 关闭输出流
|
if (out != null) {
|
out.close();
|
}
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
|
|
/**
|
* 导出snmp设备趋势图指标excel
|
*
|
* @param params
|
* @param response
|
*/
|
public void exportSnmpExcel(Map<String, Object> params, HttpServletResponse response) {
|
ServletOutputStream out = null;
|
try {
|
List<SnmpState> appStateList = snmpStateService.selectAllByParams(params);
|
List<SnmpExcelChartDto> excelChartList = new ArrayList<>();
|
Double recvAvgTmp = 0d;
|
Double sentAvgTmp = 0d;
|
for (SnmpState snmpState : appStateList) {
|
SnmpExcelChartDto snmpExcelChartDto = new SnmpExcelChartDto();
|
recvAvgTmp = snmpState.getRecvAvgDouble();
|
sentAvgTmp = snmpState.getSentAvgDouble();
|
//转为MB begin
|
//单位是byte
|
recvAvgTmp = recvAvgTmp / 1024 / 1024;
|
sentAvgTmp = sentAvgTmp / 1024 / 1024;
|
//转为MB end
|
sentAvgTmp = FormatUtil.formatDouble(sentAvgTmp, 2);
|
recvAvgTmp = FormatUtil.formatDouble(recvAvgTmp, 2);
|
snmpExcelChartDto.setSentAvg(String.valueOf(sentAvgTmp));
|
snmpExcelChartDto.setRecvAvg(String.valueOf(recvAvgTmp));
|
snmpExcelChartDto.setMemPer(snmpState.getMemPer());
|
snmpExcelChartDto.setCpuPer(snmpState.getCpuPer());
|
snmpExcelChartDto.setDatetime(snmpState.getDateStr());
|
excelChartList.add(snmpExcelChartDto);
|
}
|
String fileName = DateUtil.getCurrentDateTimeNoChar() + "_snmpState";
|
response.setContentType("application/vnd.ms-exce");
|
response.setCharacterEncoding("utf-8");
|
response.addHeader("Content-Disposition", "filename=" + fileName + ".xlsx");
|
EasyExcel.write(response.getOutputStream(), SnmpExcelChartDto.class).sheet("sheet").doWrite(excelChartList);
|
} catch (Exception e) {
|
logger.error("snmp设备趋势图导出excel错误", e);
|
} finally {
|
try {
|
// 关闭输出流
|
if (out != null) {
|
out.close();
|
}
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
|
/**
|
* 导出自定义监控项值趋势图excel
|
*
|
* @param params
|
* @param response
|
*/
|
public void exportCustomExcel(Map<String, Object> params, HttpServletResponse response) {
|
ServletOutputStream out = null;
|
try {
|
List<CustomState> customStateList = customStateService.selectAllByParams(params);
|
List<CustomExcelChartDto> excelChartList = new ArrayList<>();
|
for (CustomState customState : customStateList) {
|
CustomExcelChartDto customExcelChartDto = new CustomExcelChartDto();
|
customExcelChartDto.setDatetime(customState.getDateStr());
|
customExcelChartDto.setCustomValue(customState.getCustomValue());
|
excelChartList.add(customExcelChartDto);
|
}
|
String fileName = DateUtil.getCurrentDateTimeNoChar() + "_customState";
|
response.setContentType("application/vnd.ms-exce");
|
response.setCharacterEncoding("utf-8");
|
response.addHeader("Content-Disposition", "filename=" + fileName + ".xlsx");
|
EasyExcel.write(response.getOutputStream(), CustomExcelChartDto.class).sheet("sheet").doWrite(excelChartList);
|
} catch (Exception e) {
|
logger.error("自定义监控项值趋势图导出excel错误", e);
|
} finally {
|
try {
|
// 关闭输出流
|
if (out != null) {
|
out.close();
|
}
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
|
/**
|
* 导出docker趋势图指标excel,只有内存大小指标
|
*
|
* @param params
|
* @param response
|
*/
|
public void exportDockerExcel(Map<String, Object> params, HttpServletResponse response) {
|
ServletOutputStream out = null;
|
try {
|
List<DockerState> dockerStateList = dockerStateService.selectAllByParams(params);
|
List<DockerExcelChartDto> excelChartList = new ArrayList<>();
|
for (DockerState dockerState : dockerStateList) {
|
DockerExcelChartDto dockerExcelChartDto = new DockerExcelChartDto();
|
dockerExcelChartDto.setDatetime(dockerState.getDateStr());
|
dockerExcelChartDto.setMemPer(dockerState.getMemPer());
|
excelChartList.add(dockerExcelChartDto);
|
}
|
String fileName = DateUtil.getCurrentDateTimeNoChar() + "_dockerState";
|
response.setContentType("application/vnd.ms-exce");
|
response.setCharacterEncoding("utf-8");
|
response.addHeader("Content-Disposition", "filename=" + fileName + ".xlsx");
|
EasyExcel.write(response.getOutputStream(), DockerExcelChartDto.class).sheet("sheet").doWrite(excelChartList);
|
} catch (Exception e) {
|
logger.error("docker趋势图导出excel错误", e);
|
} finally {
|
try {
|
// 关闭输出流
|
if (out != null) {
|
out.close();
|
}
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
|
/**
|
* 导出数据表统计趋势图excel
|
*
|
* @param params
|
* @param response
|
*/
|
public void exportDbTableExcel(Map<String, Object> params, HttpServletResponse response) {
|
ServletOutputStream out = null;
|
try {
|
List<DbTableCount> dbTableCountList = dbTableCountService.selectAllByParams(params);
|
List<DbTableExcelChartDto> excelChartList = new ArrayList<>();
|
for (DbTableCount dbTableCount : dbTableCountList) {
|
DbTableExcelChartDto dbTableExcelChartDto = new DbTableExcelChartDto();
|
if (null != dbTableCount.getCreateTime()) {
|
dbTableExcelChartDto.setDatetime(DateUtil.getDateTimeString(dbTableCount.getCreateTime()));
|
}
|
dbTableExcelChartDto.setTableCount(dbTableCount.getTableCount());
|
excelChartList.add(dbTableExcelChartDto);
|
}
|
String fileName = DateUtil.getCurrentDateTimeNoChar() + "_dbTableCount";
|
response.setContentType("application/vnd.ms-exce");
|
response.setCharacterEncoding("utf-8");
|
response.addHeader("Content-Disposition", "filename=" + fileName + ".xlsx");
|
EasyExcel.write(response.getOutputStream(), DbTableExcelChartDto.class).sheet("sheet").doWrite(excelChartList);
|
} catch (Exception e) {
|
logger.error("数据表趋势图导出excel错误", e);
|
} finally {
|
try {
|
// 关闭输出流
|
if (out != null) {
|
out.close();
|
}
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
|
/**
|
* 导出日志监控信息excel
|
*
|
* @param params
|
* @param response
|
*/
|
public void exportFileWarnStateExcel(FileWarnInfo fileWarnInfo, Map<String, Object> params, HttpServletResponse response) {
|
ServletOutputStream out = null;
|
try {
|
List<FileWarnState> fileWarnStateList = fileWarnStateService.selectAllByParams(params);
|
List<FileWarnStateExcelDto> excelChartList = new ArrayList<>();
|
for (FileWarnState fileWarnState : fileWarnStateList) {
|
FileWarnStateExcelDto fileWarnStateExcelDto = new FileWarnStateExcelDto();
|
if (null != fileWarnState.getCreateTime()) {
|
fileWarnStateExcelDto.setDatetime(DateUtil.getDateTimeString(fileWarnState.getCreateTime()));
|
}
|
fileWarnStateExcelDto.setWarContent(fileWarnState.getWarContent());
|
excelChartList.add(fileWarnStateExcelDto);
|
}
|
String fileName = fileWarnInfo.getHostname() + "_fileWarnInfo";
|
response.setContentType("application/vnd.ms-exce");
|
response.setCharacterEncoding("utf-8");
|
response.addHeader("Content-Disposition", "filename=" + fileName + ".xlsx");
|
EasyExcel.write(response.getOutputStream(), FileWarnStateExcelDto.class).sheet("sheet").doWrite(excelChartList);
|
} catch (Exception e) {
|
logger.error("日志监控列表导出excel错误", e);
|
} finally {
|
try {
|
// 关闭输出流
|
if (out != null) {
|
out.close();
|
}
|
} catch (IOException e) {
|
e.printStackTrace();
|
}
|
}
|
}
|
|
}
|