一、創建Word模板
{{company}}{{Date}}服務器運行情況報告一、服務器:總告警次數:{{ServerTotal}}
服務器IP:{{IPA}},總共告警次數:{{ServerATotal}}
服務器IP:{{IPB}},總共告警次數:{{ServerBTotal}}
服務器IP:{{IPC}},總共告警次數:{{ServerCTotal}} 二、應用程序:總告警次數{{ApplicationTotal}}
服務器IP:{{IPA}},包含{{ApplicationAName}}應用程序服務,總共告警次數:{{ApplicationATotal}}
服務器IP:{{IPB}},包含{{ApplicationBName}}應用程序服務,總共告警次數:{{ApplicationBTotal}}
服務器IP:{{IPC}},包含{{ApplicationCName}}應用程序服務,總共告警次數:{{ApplicationCTotal}}三、數據庫:總告警次數{{DBTotal}}
服務器IP:{{IPA}},包含{{DBAName}}數據庫,總共告警次數:{{DBATotal}}
服務器IP:{{IPB}},包含{{DBBName}}數據庫,總共告警次數:{{DBBTotal}}
服務器IP:{{IPC}},包含{{DBCName}}數據庫,總共告警次數:{{DBCTotal}}四、中間件:總告警次數{{MiddleWareTotal}}
服務器IP:{{IPA}},包含{{MiddleWareAName}}中間件,總共告警次數:{{MiddleWareATotal}}
服務器IP:{{IPB}},包含{{MiddleWareBName}}中間件,總共告警次數:{{MiddleWareBTotal}}
服務器IP:{{IPC}},包含{{MiddleWareCName}}中間件,總共告警次數:{{MiddleWareCTotal}} 五、接口:總告警次數{{InterfaceTotal}}
服務器IP:{{IPA}},包含{{InterfaceAName}}接口服務,總共告警次數:{{InterfaceATotal}}
服務器IP:{{IPB}},包含{{InterfaceBName}}接口服務,總共告警次數:{{InterfaceBTotal}}
服務器IP:{{IPC}},包含{{InterfaceCName}}接口服務,總共告警次數:{{InterfaceCTotal}}
將上述內容存入一個docx文件中,放到resources文件夾下
二、編寫代碼
package com.gitee.pifeng.monitoring.server.business.server.controller;import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.gitee.pifeng.monitoring.server.business.server.dao.*;
import com.gitee.pifeng.monitoring.server.business.server.entity.*;
import com.gitee.pifeng.monitoring.server.business.server.service.IAlarmService;
import com.gitee.pifeng.monitoring.server.config.WordTemplateConfig;import com.gitee.pifeng.monitoring.server.util.db.SelectEmailsService;
import org.apache.poi.util.StringUtil;
import org.apache.poi.xwpf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;import org.springframework.beans.factory.annotation.Value;
import org.springframework.http.MediaType;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.thymeleaf.util.StringUtils;import javax.servlet.http.HttpServletResponse;
import java.io.FileInputStream;
import java.io.IOException;
import java.net.URLDecoder;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.stream.Collectors;/*** description 導出word版本的報表** @author yhj* @date 2025-01-06 17:08:15*/@RequestMapping("/ExportWord")
@RestController
public class ExportWordReportController {@Autowiredprivate WordTemplateConfig wordTemplateConfig;/*** 告警服務接口*/@Autowiredprivate IAlarmService alarmService;@Autowiredprivate IMonitorInstanceDao monitorInstanceDao;@Autowiredprivate IMonitorAlarmRecordDao monitorAlarmRecordDao;@Autowiredprivate IMonitorDbDao monitorDbDao;@Autowiredprivate IMonitorTcpDao monitorTcpDao;@Autowiredprivate IMonitorHttpDao monitorHttpDao;@Autowiredprivate SelectEmailsService emailsService;public void exportWordTemplate(HttpServletResponse response, Map<String, String> data, String filename) {String companyName = emailsService.selectCompanyName();data.put("company", companyName);try {String filePath = URLDecoder.decode(getClass().getClassLoader().getResource("templates/static/WordReportTemplete.docx").getPath(), "UTF-8");// 讀取Word模板FileInputStream fis = new FileInputStream(filePath);XWPFDocument document = new XWPFDocument(fis);// 將填充后的文檔寫入響應輸出流wordTemplateConfig.replaceTextInDocument(document, data);// 設置響應頭,指定下載文件類型和名稱response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + filename + ".docx");document.write(response.getOutputStream());// 關閉資源document.close();fis.close();} catch (IOException e) {e.printStackTrace();}}private void exportEmptyWordTemplate(HttpServletResponse response, Map<String, String> data) {String companyName = emailsService.selectCompanyName();String filename = URLEncoder.encode(String.valueOf(new StringBuilder(companyName).append("現場暫無數據"))).replaceAll("\\+", "%20");data.put("company", companyName);try {String filePath = URLDecoder.decode(getClass().getClassLoader().getResource("templates/static/EmptyWordReportTemplete.docx").getPath(), "UTF-8");// 讀取Word模板FileInputStream fis = new FileInputStream(filePath);XWPFDocument document = new XWPFDocument(fis);// 將填充后的文檔寫入響應輸出流wordTemplateConfig.replaceTextInDocument(document, data);// 設置響應頭,指定下載文件類型和名稱response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + filename + ".docx");document.write(response.getOutputStream());// 關閉資源document.close();fis.close();} catch (IOException e) {e.printStackTrace();}}/*** 本日服務器報錯信息word導出** @param response*/@GetMapping("/getThisDayWordReport")public void getDayWordReport(HttpServletResponse response) {String companyName = emailsService.selectCompanyName();Map<String, String> data = new HashMap<>();SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");Date date = new Date();String TodayDay = simpleDateFormat.format(date);data.put("Date", TodayDay);Integer flag = 0; // 0代表今日//文件名,以下方法用于防止中文編碼錯誤String name = String.valueOf(new StringBuilder(companyName).append(TodayDay).append("服務器報錯信息"));String filename = URLEncoder.encode(name).replaceAll("\\+", "%20");thisWordInfo(response, flag, TodayDay, data, filename);}// 將導出的信息填充給wordprivate void thisWordInfo(HttpServletResponse response, Integer flag, String Date, Map<String, String> data, String filename) {// 查詢所有IPList<String> IpList = getIpList();if (IpList.size() == 0) {exportEmptyWordTemplate(response, data);} else {List<ExportWordReport> listA = new ArrayList<>();if (!IpList.get(0).isEmpty()) {String IPA = IpList.get(0);if (flag == 0) {// 當日報錯個數listA = alarmService.selectWordDayAlarmTotal(IPA, Date);} else if (flag == 1) {// 本周報錯個數listA = alarmService.selectWordWeekAlarmTotal(IPA, Date);} else if (flag == 2) {// 本月報錯個數listA = alarmService.selectWordMonthAlarmTotal(IPA, Date);} else if (flag == 3) {// 本年報錯個數listA = alarmService.selectWordYearAlarmTotal(IPA, Date);}// 匯總各項報錯個數Map<String, Integer> mapA = listA.stream().collect(Collectors.toMap(ExportWordReport::getName, ExportWordReport::getTotal));// 查詢該IP下所有應用程序的名稱String applicationName = getApplicationNameList(IPA);// 查詢該IP下所有數據庫名字String DBName = getDBNameList(IPA);// 查詢該IP下所有中間件名字String MiddleWareName = getMiddleNameList(IPA);// 查詢該IP下所有接口名稱String InterfaceName = getInterfaceNameList(IPA);// 填充word里面的字段data.put("IPA", IPA);data.put("ApplicationAName", !applicationName.isEmpty() ? applicationName : "0個");data.put("DBAName", !DBName.isEmpty() ? DBName : "0個");data.put("MiddleWareAName", !MiddleWareName.isEmpty() ? MiddleWareName : "0個");data.put("InterfaceAName", !InterfaceName.isEmpty() ? InterfaceName : "0個");// 服務器data.put("ServerATotal", mapA.get("SERVER") != null ? String.valueOf(mapA.get("SERVER")) : "0");// 應用data.put("ApplicationATotal", mapA.get("INSTANCE") != null ? String.valueOf(mapA.get("INSTANCE")) : "0");// 數據庫data.put("DBATotal", mapA.get("DATABASE") != null ? String.valueOf(mapA.get("DATABASE")) : "0");// 中間件data.put("MiddleWareATotal", mapA.get("TCP4SERVICE") != null ? String.valueOf(mapA.get("TCP4SERVICE")) : "0");// 接口data.put("InterfaceATotal", mapA.get("HTTP4SERVICE") != null ? String.valueOf(mapA.get("HTTP4SERVICE")) : "0");}if (!IpList.get(1).isEmpty()) {List<ExportWordReport> listB = new ArrayList<>();String IPB = IpList.get(1);if (flag == 0) {listB = alarmService.selectWordDayAlarmTotal(IPB, Date);} else if (flag == 1) {// 本周報錯信息listB = alarmService.selectWordWeekAlarmTotal(IPB, Date);} else if (flag == 2) {// 本月報錯個數listB = alarmService.selectWordMonthAlarmTotal(IPB, Date);} else if (flag == 3) {// 本年報錯個數listB = alarmService.selectWordYearAlarmTotal(IPB, Date);}// 匯總各項報錯個數Map<String, Integer> mapB = listB.stream().collect(Collectors.toMap(ExportWordReport::getName, ExportWordReport::getTotal));// 查詢該IP下所有應用程序的名稱String applicationName = getApplicationNameList(IPB);// 查詢該IP下所有數據庫名字String DBName = getDBNameList(IPB);// 查詢該IP下所有中間件名字String MiddleWareName = getMiddleNameList(IPB);// 查詢該IP下所有接口名稱String InterfaceName = getInterfaceNameList(IPB);data.put("IPB", IPB);data.put("ApplicationBName", !applicationName.isEmpty() ? applicationName : "0個");data.put("DBBName", !DBName.isEmpty() ? DBName : "0個");data.put("MiddleWareBName", !MiddleWareName.isEmpty() ? MiddleWareName : "0個");data.put("InterfaceBName", !InterfaceName.isEmpty() ? InterfaceName : "0個");// 服務器data.put("ServerBTotal", mapB.get("SERVER") != null ? String.valueOf(mapB.get("SERVER")) : "0");// 應用data.put("ApplicationBTotal", mapB.get("INSTANCE") != null ? String.valueOf(mapB.get("INSTANCE")) : "0");// 數據庫data.put("DBBTotal", mapB.get("DATABASE") != null ? String.valueOf(mapB.get("DATABASE")) : "0");// 中間件data.put("MiddleWareBTotal", mapB.get("TCP4SERVICE") != null ? String.valueOf(mapB.get("TCP4SERVICE")) : "0");// 接口data.put("InterfaceBTotal", mapB.get("HTTP4SERVICE") != null ? String.valueOf(mapB.get("HTTP4SERVICE")) : "0");}if (!IpList.get(2).isEmpty()) {List<ExportWordReport> listC = new ArrayList<>();String IPC = IpList.get(2);if (flag == 0) {listC = alarmService.selectWordDayAlarmTotal(IPC, Date);} else if (flag == 1) {// 本周報錯個數listC = alarmService.selectWordWeekAlarmTotal(IPC, Date);} else if (flag == 2) {// 本月報錯個數listC = alarmService.selectWordMonthAlarmTotal(IPC, Date);} else if (flag == 3) {// 本年報錯個數listC = alarmService.selectWordYearAlarmTotal(IPC, Date);}// 匯總各項報錯個數Map<String, Integer> mapC = listC.stream().collect(Collectors.toMap(ExportWordReport::getName, ExportWordReport::getTotal));// 查詢該IP下所有應用程序的名稱String applicationName = getApplicationNameList(IPC);// 查詢該IP下所有數據庫名字String DBName = getDBNameList(IPC);// 查詢該IP下所有中間件名字String MiddleWareName = getMiddleNameList(IPC);// 查詢該IP下所有接口名稱String InterfaceName = getInterfaceNameList(IPC);data.put("IPC", IPC);data.put("ApplicationCName", !applicationName.isEmpty() ? applicationName : "0個");data.put("DBCName", DBName);data.put("MiddleWareCName", !MiddleWareName.isEmpty() ? MiddleWareName : "0個");data.put("InterfaceCName", !InterfaceName.isEmpty() ? InterfaceName : "0個");// 服務器data.put("ServerCTotal", mapC.get("SERVER") != null ? String.valueOf(mapC.get("SERVER")) : "0");// 應用data.put("ApplicationCTotal", mapC.get("INSTANCE") != null ? String.valueOf(mapC.get("INSTANCE")) : "0");// 數據庫data.put("DBCTotal", mapC.get("DATABASE") != null ? String.valueOf(mapC.get("DATABASE")) : "0");// 中間件data.put("MiddleWareCTotal", mapC.get("TCP4SERVICE") != null ? String.valueOf(mapC.get("TCP4SERVICE")) : "0");// 接口data.put("InterfaceCTotal", mapC.get("HTTP4SERVICE") != null ? String.valueOf(mapC.get("HTTP4SERVICE")) : "0");}// 所有服務器報錯信息條數總和int ServerTotal = Integer.parseInt(data.get("ServerATotal")) + Integer.parseInt(data.get("ServerBTotal")) + Integer.parseInt(data.get("ServerCTotal"));data.put("ServerTotal", String.valueOf(ServerTotal));// 所有應用程序報錯信息總和int ApplicationTotal = Integer.parseInt(data.get("ApplicationATotal")) + Integer.parseInt(data.get("ApplicationBTotal")) + Integer.parseInt(data.get("ApplicationCTotal"));data.put("ApplicationTotal", String.valueOf(ApplicationTotal));//所有數據庫報錯信息總和int DBTotal = Integer.parseInt(data.get("DBATotal")) + Integer.parseInt(data.get("DBBTotal")) + Integer.parseInt(data.get("DBCTotal"));data.put("DBTotal", String.valueOf(DBTotal));//所有中間件報錯信息總和int MiddleWareTotal = Integer.parseInt(data.get("MiddleWareATotal")) + Integer.parseInt(data.get("MiddleWareBTotal")) + Integer.parseInt(data.get("MiddleWareCTotal"));data.put("MiddleWareTotal", String.valueOf(MiddleWareTotal));//所有接口報錯信息總和int InterfaceTotal = Integer.parseInt(data.get("InterfaceATotal")) + Integer.parseInt(data.get("InterfaceBTotal")) + Integer.parseInt(data.get("InterfaceCTotal"));data.put("InterfaceTotal", String.valueOf(InterfaceTotal));exportWordTemplate(response, data, filename);}}// 查詢所有接口名稱private String getInterfaceNameList(String IP) {LambdaQueryWrapper<MonitorHttp> queryInterfaceNameWrapper = new LambdaQueryWrapper<>();queryInterfaceNameWrapper.select(MonitorHttp::getDescr).ne(MonitorHttp::getDescr,null).ne(MonitorHttp::getDescr,"").eq(MonitorHttp::getHostnameSource, IP).groupBy(MonitorHttp::getDescr);List<String> InterfaceList = monitorHttpDao.selectList(queryInterfaceNameWrapper).stream().filter(item -> item!=null).map(MonitorHttp::getDescr).collect(Collectors.toList());String InterfaceName = InterfaceList.stream().map(String::toString).collect(Collectors.joining("、"));return InterfaceName;}// 查詢所有IPprivate List<String> getIpList() {LambdaQueryWrapper<MonitorAlarmRecord> queryIPWrapper = new LambdaQueryWrapper<>();queryIPWrapper.select(MonitorAlarmRecord::getIp).groupBy(MonitorAlarmRecord::getIp);List<String> IpList = monitorAlarmRecordDao.selectList(queryIPWrapper).stream().filter(item -> item != null).map(MonitorAlarmRecord::getIp).collect(Collectors.toList());return IpList;}// 查詢該IP下所有中間件的名稱private String getMiddleNameList(String IP) {LambdaQueryWrapper<MonitorTcp> queryMiddleNameWrapper = new LambdaQueryWrapper<>();queryMiddleNameWrapper.select(MonitorTcp::getDescr).ne(MonitorTcp::getDescr,"").ne(MonitorTcp::getDescr,null).eq(MonitorTcp::getHostnameSource, IP).groupBy(MonitorTcp::getDescr);List<String> MiddleWareList = monitorTcpDao.selectList(queryMiddleNameWrapper).stream().filter(item -> item != null).map(MonitorTcp::getDescr).collect(Collectors.toList());String MiddleWareName = MiddleWareList.stream().map(String::toString).collect(Collectors.joining("、"));return MiddleWareName;}// 查詢該IP下所有應用程序的名稱private String getApplicationNameList(String IP) {// 查詢該IP下所有應用程序的名稱LambdaQueryWrapper<MonitorInstance> queryInstanceNameWrapper = new LambdaQueryWrapper<>();queryInstanceNameWrapper.select(MonitorInstance::getInstanceName) // 指定查詢字段.ne(MonitorInstance::getInstanceName,"").ne(MonitorInstance::getInstanceName,null).eq(MonitorInstance::getIp, IP) // 添加 IP 條件.groupBy(MonitorInstance::getInstanceName); // 去重(相當于 DISTINCT)List<String> ApplicationNameList = monitorInstanceDao.selectList(queryInstanceNameWrapper).stream().map(MonitorInstance::getInstanceName) // 提取字段值.distinct().collect(Collectors.toList());// 確保去重String applicationName = ApplicationNameList.stream().map(String::toString).collect(Collectors.joining("、"));return applicationName;}// 查詢所有數據庫名字公共方法private String getDBNameList(String IP) {LambdaQueryWrapper<MonitorDb> queryDBListWrapper = new LambdaQueryWrapper<>();queryDBListWrapper.select(MonitorDb::getDbType).ne(MonitorDb::getDbType,"").ne(MonitorDb::getDbType,null).eq(MonitorDb::getIp, IP).groupBy(MonitorDb::getDbType);List<String> DBList = monitorDbDao.selectList(queryDBListWrapper).stream().filter(item -> item != null).map(MonitorDb::getDbType).collect(Collectors.toList());String DBName = DBList.stream().map(String::toString).collect(Collectors.joining("、"));return DBName;}}
工具類,主要讀取信息并寫入word模板內
package com.gitee.pifeng.monitoring.server.config;
import org.apache.poi.xwpf.usermodel.*;
import org.springframework.stereotype.Component;import java.util.List;
import java.util.Map;@Component
public class WordTemplateConfig {public void replaceTextInDocument(XWPFDocument document, Map<String, String> data) {// 替換段落中的占位符for (XWPFParagraph paragraph : document.getParagraphs()) {StringBuilder paragraphText = new StringBuilder();List<XWPFRun> runs = paragraph.getRuns();if (runs != null) {// 收集段落中的所有文本for (XWPFRun run : runs) {paragraphText.append(run.getText(0) != null ? run.getText(0) : "");}// 替換占位符String replacedText = paragraphText.toString();for (Map.Entry<String, String> entry : data.entrySet()) {replacedText = replacedText.replace("{{" + entry.getKey() + "}}", entry.getValue() != null ? entry.getValue() : "");}// 清空原有 runsfor (int i = runs.size() - 1; i >= 0; i--) {paragraph.removeRun(i);}// 重新設置替換后的文本XWPFRun newRun = paragraph.createRun();newRun.setText(replacedText);}}// 替換表格中的占位符for (XWPFTable table : document.getTables()) {for (XWPFTableRow row : table.getRows()) {for (XWPFTableCell cell : row.getTableCells()) {for (XWPFParagraph paragraph : cell.getParagraphs()) {for (XWPFRun run : paragraph.getRuns()) {String text = run.getText(0);if (text != null) {for (Map.Entry<String, String> entry : data.entrySet()) {if (text.contains("{{" + entry.getKey() + "}}")) {text = text.replace("{{" + entry.getKey() + "}}", entry.getValue());run.setText(text, 0);}}}}}}}}}
}
三、發送郵箱
@RequestMapping("/SendWord")
@RestController
public class SendWordReportController {@Autowiredprivate EmailSender emailSender;@Autowiredprivate SelectEmailsService emailsService;SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");String date = simpleDateFormat.format(new Date());/*** 發送日報表*/@PostMapping("/sendDayWordReport")public void sendDayWordReport() {List<String> emails = emailsService.findEmail();String companyName = emailsService.selectCompanyName();String name = companyName+new StringBuilder(date).append("日服務器報錯信息匯總");try {// 調用接口下載 Excel 文件String apiUrl = "http://localhost:16000/phoenix-server/ExportWord/getThisDayWordReport";File wordFile = FileDownloader.downloadWordFile(apiUrl);// 接收郵件的郵箱for (String email: emails) {emailSender.sendEmailWithAttachment(email,wordFile,name);}
// emailSender.sendEmailWithAttachment(toEmail,wordFile,name);} catch (IOException | MessagingException e) {e.printStackTrace();}}}}
package com.gitee.pifeng.monitoring.server.config;import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.mail.SimpleMailMessage;
import org.springframework.mail.javamail.JavaMailSender;
import org.springframework.mail.javamail.MimeMessageHelper;
import org.springframework.stereotype.Component;import javax.mail.*;
import javax.mail.internet.InternetAddress;
import javax.mail.internet.MimeBodyPart;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
import java.io.File;
import java.io.IOException;
import java.util.Properties;/*** description 發送報表數據** @author yhj* @date 2025-01-02 15:39:08*/@Component
public class EmailSender {@Value("${spring.mail.username}")private String username;@Value("${spring.mail.password}")private String password;@Autowiredprivate JavaMailSender sender;public void sendEmailWithAttachment(String toEmail,File attachmentFile,String name) throws MessagingException, IOException {// 設置郵件屬性Properties properties = new Properties();properties.put("mail.smtp.host","smtp.exmail.qq.com");properties.put("mail.smtp.port",465);properties.put("mail.smtp.auth","true");properties.put("mail.smtp.starttls.enable","true");// 獲取郵件會話
// Session session = Session.getInstance(properties, new Authenticator() {
// @Override
// protected PasswordAuthentication getPasswordAuthentication() {
// // 填寫發送者郵箱和授權碼
// return new PasswordAuthentication(username, password);
// }
// });//創建郵件內容// 創建MimeMessage對象MimeMessage message = sender.createMimeMessage();MimeMessageHelper helper = new MimeMessageHelper(message, true); // true表示支持附件helper.setFrom("249@qq.com");helper.setTo(toEmail);
// String subject = String.valueOf(new StringBuilder(companyName).append("服務器運行狀況報表"));message.setSubject(name);// 創建一個Multipart對象,包含郵件正文和附件Multipart multipart = new MimeMultipart();// 郵件正文部分
// MimeBodyPart messageBodyPart = new MimeBodyPart();
// messageBodyPart.setText(name);
// multipart.addBodyPart(messageBodyPart);// 郵件附件部分MimeBodyPart attachmentPart = new MimeBodyPart();attachmentPart.attachFile(attachmentFile);attachmentPart.setFileName(name+".docx");multipart.addBodyPart(attachmentPart);// 附加附件文件message.setContent(multipart);// 發送郵件sender.send(message);System.out.println("郵件發送成功!");}
}