feat: NEZ-3485 alert_message 导出 excel 文件增加统计 sheetrel-24.01.25

1. 第一个 sheet 使用 数据透视表 统计数据

4 files changed, 181 insertions, 25 deletions

diff --git a/nz-admin/src/main/java/com/nis/common/utils/ExcelUtils.java b/nz-admin/src/main/java/com/nis/common/utils/ExcelUtils.java
index b6fbdd70..4096b8e3 100644
--- a/nz-admin/src/main/java/com/nis/common/utils/ExcelUtils.java
+++ b/nz-admin/src/main/java/com/nis/common/utils/ExcelUtils.java
@@ -34,6 +34,7 @@ public class ExcelUtils {
     private final static Log log = Log.get();
 
     private static NumberFormat numberFormat = NumberFormat.getNumberInstance();
+    public static String DEFAULT_SHEETNAME = "Datasheet";
 
     static {
         numberFormat.setGroupingUsed(false);
@@ -132,19 +133,45 @@ public class ExcelUtils {
     }
 
     /**
-     * 创建 excel
+     * create SXSSFWorkbook
      *
      * @param headerInfo 表头信息 结构为 Map  key=cellName,value=cellComment
-     * @param sheetName
-     * @param isComment  是否需要批注信息
      * @return
-     * @throws IOException
      */
-    public static SXSSFWorkbook createExcel07(Map<String, String> headerInfo, String sheetName, boolean isComment) throws IOException {
+    public static SXSSFWorkbook createSXSSFWorkbook(Map<String, String> headerInfo) {
         SXSSFWorkbook wb = new SXSSFWorkbook();
+
+        // sheet & header
+        createSheetWithHeader(wb, headerInfo);
+        return wb;
+    }
+
+    /**
+     * create XSSFWorkbook
+     *
+     * @param headerInfo 表头信息 结构为 Map  key=cellName,value=cellComment
+     * @return
+     */
+    public static XSSFWorkbook createXSSFWorkbook(Map<String, String> headerInfo) {
+        XSSFWorkbook wb = new XSSFWorkbook();
+
+        // sheet & header
+        createSheetWithHeader(wb, headerInfo);
+        return wb;
+    }
+
+    /**
+     * 创建默认 sheet 页
+     * 添加标题行
+     * 添加标题行样式
+     *
+     * @param wb
+     * @param headerInfo
+     */
+    private static void createSheetWithHeader(Workbook wb, Map<String, String> headerInfo) {
         // 标题样式
         CellStyle colStyle = createHeaderStyle(wb);
-        Sheet sheet = wb.createSheet(sheetName);
+        Sheet sheet = wb.createSheet(DEFAULT_SHEETNAME);
         Drawing dp = sheet.createDrawingPatriarch();
         // 设置默认列宽
         sheet.setDefaultColumnWidth(25);
@@ -157,15 +184,12 @@ public class ExcelUtils {
             headerCell.setCellValue(entry.getKey());
             headerCell.setCellStyle(colStyle);
 
-            if (isComment) {
-                cellComment = dp.createCellComment(dp.createAnchor(Units.EMU_PER_POINT * 9, Units.EMU_PER_PIXEL, Units.EMU_PER_CENTIMETER * 4, Units.EMU_PER_POINT * 9, i, 0, i, 4));
-                cellComment.setString(new XSSFRichTextString(entry.getValue()));
-                cellComment.setAuthor("nezha");
-                headerCell.setCellComment(cellComment);
-            }
+            cellComment = dp.createCellComment(dp.createAnchor(Units.EMU_PER_POINT * 9, Units.EMU_PER_PIXEL, Units.EMU_PER_CENTIMETER * 4, Units.EMU_PER_POINT * 9, i, 0, i, 4));
+            cellComment.setString(new XSSFRichTextString(entry.getValue()));
+            cellComment.setAuthor("nezha");
+            headerCell.setCellComment(cellComment);
             i++;
         }
-        return wb;
     }
 
     /**
diff --git a/nz-admin/src/main/java/com/nis/modules/alert/service/AlertHandlerService.java b/nz-admin/src/main/java/com/nis/modules/alert/service/AlertHandlerService.java
index 77f0d5a6..d63fad96 100644
--- a/nz-admin/src/main/java/com/nis/modules/alert/service/AlertHandlerService.java
+++ b/nz-admin/src/main/java/com/nis/modules/alert/service/AlertHandlerService.java
@@ -84,7 +84,6 @@ public interface AlertHandlerService  {
 
 	/**
 	 * 删除告警消息
-	 * @param state
 	 * @param ids
 	 */
 	void removeMessageByIds(Long ...ids);
diff --git a/nz-admin/src/main/java/com/nis/modules/alert/service/impl/AlertHandlerServiceImpl.java b/nz-admin/src/main/java/com/nis/modules/alert/service/impl/AlertHandlerServiceImpl.java
index 261fcf07..b99e7865 100644
--- a/nz-admin/src/main/java/com/nis/modules/alert/service/impl/AlertHandlerServiceImpl.java
+++ b/nz-admin/src/main/java/com/nis/modules/alert/service/impl/AlertHandlerServiceImpl.java
@@ -37,15 +37,29 @@ import com.nis.modules.project.entity.MonitorProject;
 import com.nis.modules.project.service.MonitorProjectService;
 import com.nis.modules.sys.service.BasicImportAndExportServices;
 import com.nis.modules.sys.service.SysConfService;
+import com.nis.modules.sys.service.SysI18nService;
 import com.nis.modules.tool.service.FullTextSearchService;
 import com.nis.setup.controller.TimezoneController;
 import jakarta.servlet.http.HttpServletResponse;
+import org.apache.commons.io.IOUtils;
+import org.apache.poi.ss.SpreadsheetVersion;
+import org.apache.poi.ss.usermodel.CellStyle;
+import org.apache.poi.ss.usermodel.DataConsolidateFunction;
+import org.apache.poi.ss.usermodel.Row;
+import org.apache.poi.ss.usermodel.Sheet;
+import org.apache.poi.ss.util.AreaReference;
+import org.apache.poi.ss.util.CellReference;
+import org.apache.poi.xssf.usermodel.XSSFPivotTable;
+import org.apache.poi.xssf.usermodel.XSSFSheet;
+import org.apache.poi.xssf.usermodel.XSSFWorkbook;
+import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTPivotField;
 import org.springframework.beans.factory.annotation.Autowired;
 import org.springframework.beans.factory.annotation.Value;
 import org.springframework.stereotype.Service;
 import org.springframework.transaction.annotation.Transactional;
 
 import java.io.IOException;
+import java.net.URLEncoder;
 import java.nio.charset.Charset;
 import java.text.DateFormat;
 import java.util.*;
@@ -78,6 +92,8 @@ public class AlertHandlerServiceImpl implements AlertHandlerService {
 	@Autowired
 	private SysConfService sysConfService;
 	@Autowired
+	private SysI18nService sysI18nService;
+	@Autowired
 	private BasicImportAndExportServices basicImportAndExportServices;
 	@Autowired
 	private FullTextSearchService fullTextSearchService;
@@ -563,12 +579,131 @@ public class AlertHandlerServiceImpl implements AlertHandlerService {
 		Map queryBody = JSONUtil.toBean(queryBodyJsonStr, HashMap.class);
 		String alertState = Tool.MapUtil.getStr(queryBody, "state", "1");
 
+		// export data
 		List<List<String>> exportDataList = this.handleExportDataList(list, alertState);
 
-		String sysHeaderJsonStr = sysConfService.getValue("alert_export_header");
-		String fileName = "AlertMessages";
-		String formatStr = params.get("format").toString();
-		basicImportAndExportServices.exportDataByFormat(response, exportDataList, sysHeaderJsonStr, formatStr, fileName);
+		String format = StrUtil.toString(params.get("format"));
+		switch (Constant.ImportFileType.getInstanceByValue(StrUtil.toString(params.get("format")))) {
+			case XLS:
+			case XLSX:
+				// alertMessage 导出 excel, sheet1 展示 数据透视表 统计告警信息，sheet2 展示 告警消息数据
+				this.exportAlertMessageExcel(response, exportDataList);
+				break;
+			case CSV:
+			case JSON:
+				String fileName = "AlertMessages";
+				String sysHeaderJsonStr = sysConfService.getValue("alert_export_header");
+				basicImportAndExportServices.exportDataByFormat(response, exportDataList, sysHeaderJsonStr, format, fileName);
+				break;
+		}
+	}
+
+	/**
+	 * export alert message excel
+	 *
+	 * @param response
+	 * @param exportDataList
+	 * @throws IOException
+	 */
+	private void exportAlertMessageExcel(HttpServletResponse response, List<List<String>> exportDataList) throws IOException {
+		log.info("[exportAlertMessageExcel] [begin]");
+
+		XSSFWorkbook workbook = null;
+		try {
+			String sysHeaderJsonStr = sysConfService.getValue("alert_export_header");
+			Map<String, String> exportHeaderMap = basicImportAndExportServices.getI18nHeaderMap(sysHeaderJsonStr);
+
+			workbook = ExcelUtils.createXSSFWorkbook(exportHeaderMap);
+
+			// Datasheet
+			Sheet dataSheet = workbook.getSheetAt(0);
+			CellStyle dataStyle = ExcelUtils.createCellStyle(workbook, (short) 13, false);
+			int rowNum = 1;
+			for (List<String> dataList : exportDataList) {
+				Row row = dataSheet.createRow(rowNum++);
+				for (int i = 0; i < dataList.size(); i++) {
+					row.createCell(i).setCellValue(dataList.get(i));
+				}
+				ExcelUtils.setDataCellStyle(dataStyle, row, dataList.size());
+			}
+
+			// PivotSheet
+			if (Tool.CollUtil.isNotEmpty(exportDataList)) {
+				String datasheetName = dataSheet.getSheetName();
+				String pivotSheetName = "Statistic";
+
+				// 创建数据透视表
+				this.createPivotTable(workbook, datasheetName, pivotSheetName);
+
+				// 调整顺序 Statistic - Datasheet
+				workbook.setSheetOrder(datasheetName, 1);
+				workbook.setSheetOrder(pivotSheetName, 0);
+			}
+
+			// response
+			response.setContentType("application/excel");
+			response.setHeader("Content-disposition", "attachment;filename=AlertMessages.xlsx;filename*=utf-8''" + URLEncoder.encode("AlertMessages.xlsx", "UTF-8"));
+			workbook.write(response.getOutputStream());
+		} finally {
+			IOUtils.closeQuietly(workbook);
+		}
+		log.info("[exportAlertMessageExcel] [finshed]");
+	}
+
+	/**
+	 * 创建数据透视表
+	 */
+	private void createPivotTable(XSSFWorkbook xssfWorkbook, String datasheetName, String pivotSheetName) {
+		log.info("[createPivotTable] [begin]");
+		XSSFSheet dataSheet = xssfWorkbook.getSheet(datasheetName);
+		int firstRow = dataSheet.getFirstRowNum();
+		int lastRow = dataSheet.getLastRowNum();
+
+		// source area
+		Row headerRow = dataSheet.getRow(firstRow);
+		int lastCol = headerRow.getLastCellNum() - 1;
+		CellReference topLeft = new CellReference(0, 0);
+		CellReference botRight = new CellReference(lastRow, lastCol);
+		String sourceAreaStr = new AreaReference(topLeft, botRight).formatAsString();
+
+		AreaReference sourceArea = new AreaReference(Tool.StrUtil.concat(true, dataSheet.getSheetName(), "!", sourceAreaStr), SpreadsheetVersion.EXCEL2007);
+		CellReference position = new CellReference(Tool.StrUtil.concat(true, pivotSheetName, "!A1"));
+
+		log.info("[createPivotTable] [sourceArea: {}]", sourceArea.formatAsString());
+		log.info("[createPivotTable] [positionArea:{}]", position.formatAsString());
+
+		XSSFSheet pivotSheet = xssfWorkbook.createSheet(pivotSheetName);
+		XSSFPivotTable pivotTable = pivotSheet.createPivotTable(sourceArea, position);
+
+		pivotTable.addRowLabel(1); // Rule
+		pivotTable.addRowLabel(6); // Asset
+
+		String headercountStr = sysI18nService.queryValue("overall.count");
+		headercountStr = StrUtil.emptyToDefault(headercountStr, "Count");
+		pivotTable.addColumnLabel(DataConsolidateFunction.COUNT, 0, headercountStr); // count by id
+
+		// 布局调整 不勾选 压缩表单
+		CTPivotField[] pivotFieldArray = pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray();
+		for (CTPivotField ctPivotField : pivotFieldArray) {
+			ctPivotField.setCompact(false);
+		}
+
+		// 表头、统计 名称 - 国际化
+		String headerRuleStr = sysI18nService.queryValue("alert.rule");
+		headerRuleStr = StrUtil.emptyToDefault(headerRuleStr, "Rule");
+
+		String headerAssetStr = sysI18nService.queryValue("asset.asset");
+		headerAssetStr = StrUtil.emptyToDefault(headerAssetStr, "Asset");
+
+		String headerTotalStr = sysI18nService.queryValue("overall.total");
+		headerTotalStr = StrUtil.emptyToDefault(headerTotalStr, "Total");
+
+		pivotTable.getCTPivotTableDefinition().setRowHeaderCaption(headerRuleStr);
+		pivotTable.getCTPivotTableDefinition().setGrandTotalCaption(headerTotalStr);
+
+		pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(1).setName(headerRuleStr);
+		pivotTable.getCTPivotTableDefinition().getPivotFields().getPivotFieldArray(6).setName(headerAssetStr);
+		log.info("[createPivotTable] [finshed]");
 	}
 
 	private List<List<String>> handleExportDataList(List<AlertMessageEntity> list, String alertState) {
diff --git a/nz-admin/src/main/java/com/nis/modules/sys/service/impl/BasicImportAndExportServicesImpl.java b/nz-admin/src/main/java/com/nis/modules/sys/service/impl/BasicImportAndExportServicesImpl.java
index 49a85dc2..24b192da 100644
--- a/nz-admin/src/main/java/com/nis/modules/sys/service/impl/BasicImportAndExportServicesImpl.java
+++ b/nz-admin/src/main/java/com/nis/modules/sys/service/impl/BasicImportAndExportServicesImpl.java
@@ -75,7 +75,7 @@ public class BasicImportAndExportServicesImpl implements BasicImportAndExportSer
             case XLSX: {
                 SXSSFWorkbook workbook = null;
                 try {
-                    workbook = ExcelUtils.createExcel07(exportHeaderMap, "Datasheet", true);
+                    workbook = ExcelUtils.createSXSSFWorkbook(exportHeaderMap);
                     fileName += ".xlsx";
                     response.setContentType("application/excel");
                     response.setHeader("Content-disposition", "attachment;filename=" + fileName + ";filename*=utf-8''" + URLEncoder.encode(fileName, "UTF-8"));
@@ -223,9 +223,8 @@ public class BasicImportAndExportServicesImpl implements BasicImportAndExportSer
             case XLSX: {
                 SXSSFWorkbook workbook = null;
                 try {
-                    String sheetName = "Datasheet";
-                    workbook = ExcelUtils.createExcel07(exportHeaderMap, sheetName, true);
-                    SXSSFSheet sheet = workbook.getSheet(sheetName);
+                    workbook = ExcelUtils.createSXSSFWorkbook(exportHeaderMap);
+                    SXSSFSheet sheet = workbook.getSheetAt(0);
                     CellStyle dataStyle = ExcelUtils.createCellStyle(workbook, (short) 13, false);
                     int rowNum = 1;
                     for (List<String> dataList : exportDataList) {
@@ -308,9 +307,8 @@ public class BasicImportAndExportServicesImpl implements BasicImportAndExportSer
             switch (ImportFileType.getInstanceByType(fileExtension)) {
                 case XLS:
                 case XLSX: {
-                    String sheetName = "Datasheet";
-                    SXSSFWorkbook workbook = ExcelUtils.createExcel07(exportHeaderMap, sheetName, true);
-                    SXSSFSheet sheet = workbook.getSheet(sheetName);
+                    SXSSFWorkbook workbook = ExcelUtils.createSXSSFWorkbook(exportHeaderMap);
+                    SXSSFSheet sheet = workbook.getSheetAt(0);
                     CellStyle dataStyle = ExcelUtils.createCellStyle(workbook, (short) 13, false);
                     int rowNum = 1;
                     for (List<String> dataList : list) {