From 0a7d0fda65fc792d4549c266001f890c1a201e16 Mon Sep 17 00:00:00 2001
From: 潘志宝 <979469083@qq.com>
Date: 星期三, 08 一月 2025 08:20:43 +0800
Subject: [PATCH] 获取预警信息和调度建议分页列表
---
iailab-framework/iailab-common-excel/src/main/java/com/iailab/framework/excel/core/handler/SelectSheetWriteHandler.java | 77 ++++++++++++++++++++++++--------------
1 files changed, 49 insertions(+), 28 deletions(-)
diff --git a/iailab-framework/iailab-common-excel/src/main/java/com/iailab/framework/excel/core/handler/SelectSheetWriteHandler.java b/iailab-framework/iailab-common-excel/src/main/java/com/iailab/framework/excel/core/handler/SelectSheetWriteHandler.java
index 7efdc0a..9e5e3df 100644
--- a/iailab-framework/iailab-common-excel/src/main/java/com/iailab/framework/excel/core/handler/SelectSheetWriteHandler.java
+++ b/iailab-framework/iailab-common-excel/src/main/java/com/iailab/framework/excel/core/handler/SelectSheetWriteHandler.java
@@ -7,24 +7,21 @@
import cn.hutool.core.util.StrUtil;
import cn.hutool.extra.spring.SpringUtil;
import cn.hutool.poi.excel.ExcelUtil;
-import com.iailab.framework.common.core.KeyValue;
-import com.iailab.framework.dict.core.DictFrameworkUtils;
-import com.iailab.framework.excel.core.annotations.ExcelColumnSelect;
-import com.iailab.framework.excel.core.function.ExcelColumnSelectFunction;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
+import com.iailab.framework.common.core.KeyValue;
+import com.iailab.framework.dict.core.DictFrameworkUtils;
+import com.iailab.framework.excel.core.annotations.ExcelColumnSelect;
+import com.iailab.framework.excel.core.function.ExcelColumnSelectFunction;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import java.lang.reflect.Field;
-import java.util.Comparator;
-import java.util.HashMap;
-import java.util.List;
-import java.util.Map;
+import java.util.*;
import static com.iailab.framework.common.util.collection.CollectionUtils.convertList;
@@ -38,7 +35,6 @@
/**
* 数据起始行从 0 开始
- *
* 约定：本项目第一行有标题所以从 1 开始如果您的 Excel 有多行标题请自行更改
*/
public static final int FIRST_ROW = 1;
@@ -54,22 +50,29 @@
*/
private final Map<Integer, List<String>> selectMap = new HashMap<>();
- public SelectSheetWriteHandler(Class<?> head) {
+ private static Boolean ifSetSelect;
+
+ public SelectSheetWriteHandler(Class<?> head, Boolean selectFlag) {
+ ifSetSelect = selectFlag;
// 加载下拉数据获取接口
Map<String, ExcelColumnSelectFunction> beansMap = SpringUtil.getBeanFactory().getBeansOfType(ExcelColumnSelectFunction.class);
if (MapUtil.isEmpty(beansMap)) {
return;
}
-
+ List<Field> fields = new ArrayList<>();
+ for (Class<?> c = head; c != null; c = c.getSuperclass()) {
+ Collections.addAll(fields, c.getDeclaredFields());
+ }
// 解析下拉数据
int colIndex = 0;
- for (Field field : head.getDeclaredFields()) {
+ for (Field field : fields) {
if (field.isAnnotationPresent(ExcelColumnSelect.class)) {
ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
if (excelProperty != null && excelProperty.index() != -1) {
- colIndex = excelProperty.index();
+ getSelectDataList(excelProperty.index(), field);
+ }else{
+ getSelectDataList(colIndex, field);
}
- getSelectDataList(colIndex, field);
}
colIndex++;
}
@@ -106,27 +109,45 @@
if (CollUtil.isEmpty(selectMap)) {
return;
}
-
// 1. 获取相应操作对象
DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper(); // 需要设置下拉框的 sheet 页的数据验证助手
Workbook workbook = writeWorkbookHolder.getWorkbook(); // 获得工作簿
List<KeyValue<Integer, List<String>>> keyValues = convertList(selectMap.entrySet(), entry -> new KeyValue<>(entry.getKey(), entry.getValue()));
keyValues.sort(Comparator.comparing(item -> item.getValue().size())); // 升序不然创建下拉会报错
-
- // 2. 创建数据字典的 sheet 页
- Sheet dictSheet = workbook.createSheet(DICT_SHEET_NAME);
- for (KeyValue<Integer, List<String>> keyValue : keyValues) {
- int rowLength = keyValue.getValue().size();
- // 2.1 设置字典 sheet 页的值，每一列一个字典项
- for (int i = 0; i < rowLength; i++) {
- Row row = dictSheet.getRow(i);
- if (row == null) {
- row = dictSheet.createRow(i);
+ if (ifSetSelect){
+ for (KeyValue<Integer, List<String>> keyValue : keyValues) {
+ /*起始行、终止行、起始列、终止列 起始行为1即表示表头不设置**/
+ CellRangeAddressList addressList = new CellRangeAddressList(FIRST_ROW, LAST_ROW, keyValue.getKey(), keyValue.getKey());
+ /*设置下拉框数据**/
+ DataValidationConstraint constraint = helper.createExplicitListConstraint(keyValue.getValue().toArray(new String[0]));
+ DataValidation dataValidation = helper.createValidation(constraint, addressList);
+ if (dataValidation instanceof HSSFDataValidation) {
+ dataValidation.setSuppressDropDownArrow(false);
+ } else {
+ dataValidation.setSuppressDropDownArrow(true);
+ dataValidation.setShowErrorBox(true);
}
- row.createCell(keyValue.getKey()).setCellValue(keyValue.getValue().get(i));
+ // 2.2 阻止输入非下拉框的值
+ dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
+ dataValidation.createErrorBox("提示", "此值不存在于下拉选择中！");
+ writeSheetHolder.getSheet().addValidationData(dataValidation);
}
- // 2.2 设置单元格下拉选择
- setColumnSelect(writeSheetHolder, workbook, helper, keyValue);
+ }else{
+ // 2. 创建数据字典的 sheet 页
+ Sheet dictSheet = workbook.createSheet(DICT_SHEET_NAME);
+ for (KeyValue<Integer, List<String>> keyValue : keyValues) {
+ int rowLength = keyValue.getValue().size();
+ // 2.1 设置字典 sheet 页的值，每一列一部字典项
+ for (int i = 0; i < rowLength; i++) {
+ Row row = dictSheet.getRow(i);
+ if (row == null) {
+ row = dictSheet.createRow(i);
+ }
+ row.createCell(keyValue.getKey()).setCellValue(keyValue.getValue().get(i));
+ }
+ // 2.2 设置单元格下拉选择
+ setColumnSelect(writeSheetHolder, workbook, helper, keyValue);
+ }
}
}
--
Gitblit v1.9.3