iailab-plat.git

Merge branch 'master' of http://dlindusit.com:53929/r/iailab-plat

潘志宝

2024-12-25 874dd8d939b4ae61efbb799855c9ddf328722972

提交 \| 用户 \| 时间
e7c126	1	package com.iailab.framework.excel.core.handler;
H	2
	3	import cn.hutool.core.collection.CollUtil;
	4	import cn.hutool.core.lang.Assert;
	5	import cn.hutool.core.map.MapUtil;
	6	import cn.hutool.core.util.ObjectUtil;
	7	import cn.hutool.core.util.StrUtil;
	8	import cn.hutool.extra.spring.SpringUtil;
	9	import cn.hutool.poi.excel.ExcelUtil;
	10	import com.alibaba.excel.annotation.ExcelProperty;
	11	import com.alibaba.excel.write.handler.SheetWriteHandler;
	12	import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
	13	import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
03e8ac	14	import com.iailab.framework.common.core.KeyValue;
J	15	import com.iailab.framework.dict.core.DictFrameworkUtils;
	16	import com.iailab.framework.excel.core.annotations.ExcelColumnSelect;
	17	import com.iailab.framework.excel.core.function.ExcelColumnSelectFunction;
e7c126	18	import lombok.extern.slf4j.Slf4j;
H	19	import org.apache.poi.hssf.usermodel.HSSFDataValidation;
	20	import org.apache.poi.ss.usermodel.*;
	21	import org.apache.poi.ss.util.CellRangeAddressList;
	22
	23	import java.lang.reflect.Field;
03e8ac	24	import java.util.*;
e7c126	25
H	26	import static com.iailab.framework.common.util.collection.CollectionUtils.convertList;
	27
	28	/**
	29	* 基于固定 sheet 实现下拉框
	30	*
	31	* @author HUIHUI
	32	*/
	33	@Slf4j
	34	public class SelectSheetWriteHandler implements SheetWriteHandler {
	35
	36	/**
	37	* 数据起始行从 0 开始
	38	* 约定：本项目第一行有标题所以从 1 开始如果您的 Excel 有多行标题请自行更改
	39	*/
	40	public static final int FIRST_ROW = 1;
	41	/**
	42	* 下拉列需要创建下拉框的行数，默认两千行如需更多请自行调整
	43	*/
	44	public static final int LAST_ROW = 2000;
	45
	46	private static final String DICT_SHEET_NAME = "字典sheet";
	47
	48	/**
	49	* key: 列 value: 下拉数据源
	50	*/
	51	private final Map<Integer, List<String>> selectMap = new HashMap<>();
	52
03e8ac	53	private static Boolean ifSetSelect;
J	54
	55	public SelectSheetWriteHandler(Class<?> head, Boolean selectFlag) {
	56	ifSetSelect = selectFlag;
e7c126	57	// 加载下拉数据获取接口
H	58	Map<String, ExcelColumnSelectFunction> beansMap = SpringUtil.getBeanFactory().getBeansOfType(ExcelColumnSelectFunction.class);
	59	if (MapUtil.isEmpty(beansMap)) {
	60	return;
	61	}
03e8ac	62	List<Field> fields = new ArrayList<>();
J	63	for (Class<?> c = head; c != null; c = c.getSuperclass()) {
	64	Collections.addAll(fields, c.getDeclaredFields());
	65	}
e7c126	66	// 解析下拉数据
H	67	int colIndex = 0;
03e8ac	68	for (Field field : fields) {
e7c126	69	if (field.isAnnotationPresent(ExcelColumnSelect.class)) {
H	70	ExcelProperty excelProperty = field.getAnnotation(ExcelProperty.class);
	71	if (excelProperty != null && excelProperty.index() != -1) {
03e8ac	72	getSelectDataList(excelProperty.index(), field);
J	73	}else{
	74	getSelectDataList(colIndex, field);
e7c126	75	}
H	76	}
	77	colIndex++;
	78	}
	79	}
	80
	81	/**
	82	* 获得下拉数据，并添加到 {@link #selectMap} 中
	83	*
	84	* @param colIndex 列索引
	85	* @param field 字段
	86	*/
	87	private void getSelectDataList(int colIndex, Field field) {
	88	ExcelColumnSelect columnSelect = field.getAnnotation(ExcelColumnSelect.class);
	89	String dictType = columnSelect.dictType();
	90	String functionName = columnSelect.functionName();
	91	Assert.isTrue(ObjectUtil.isNotEmpty(dictType) \|\| ObjectUtil.isNotEmpty(functionName),
	92	"Field({}) 的 @ExcelColumnSelect 注解，dictType 和 functionName 不能同时为空", field.getName());
	93
	94	// 情况一：使用 dictType 获得下拉数据
	95	if (StrUtil.isNotEmpty(dictType)) { // 情况一：字典数据（默认）
	96	selectMap.put(colIndex, DictFrameworkUtils.getDictDataLabelList(dictType));
	97	return;
	98	}
	99
	100	// 情况二：使用 functionName 获得下拉数据
	101	Map<String, ExcelColumnSelectFunction> functionMap = SpringUtil.getApplicationContext().getBeansOfType(ExcelColumnSelectFunction.class);
	102	ExcelColumnSelectFunction function = CollUtil.findOne(functionMap.values(), item -> item.getName().equals(functionName));
	103	Assert.notNull(function, "未找到对应的 function({})", functionName);
	104	selectMap.put(colIndex, function.getOptions());
	105	}
	106
	107	@Override
	108	public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
	109	if (CollUtil.isEmpty(selectMap)) {
	110	return;
	111	}
	112	// 1. 获取相应操作对象
	113	DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper(); // 需要设置下拉框的 sheet 页的数据验证助手
	114	Workbook workbook = writeWorkbookHolder.getWorkbook(); // 获得工作簿
	115	List<KeyValue<Integer, List<String>>> keyValues = convertList(selectMap.entrySet(), entry -> new KeyValue<>(entry.getKey(), entry.getValue()));
	116	keyValues.sort(Comparator.comparing(item -> item.getValue().size())); // 升序不然创建下拉会报错
03e8ac	117	if (ifSetSelect){
J	118	for (KeyValue<Integer, List<String>> keyValue : keyValues) {
	119	/起始行、终止行、起始列、终止列起始行为1即表示表头不设置*/
	120	CellRangeAddressList addressList = new CellRangeAddressList(FIRST_ROW, LAST_ROW, keyValue.getKey(), keyValue.getKey());
	121	/设置下拉框数据*/
	122	DataValidationConstraint constraint = helper.createExplicitListConstraint(keyValue.getValue().toArray(new String[0]));
	123	DataValidation dataValidation = helper.createValidation(constraint, addressList);
	124	if (dataValidation instanceof HSSFDataValidation) {
	125	dataValidation.setSuppressDropDownArrow(false);
	126	} else {
	127	dataValidation.setSuppressDropDownArrow(true);
	128	dataValidation.setShowErrorBox(true);
e7c126	129	}
03e8ac	130	// 2.2 阻止输入非下拉框的值
J	131	dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
	132	dataValidation.createErrorBox("提示", "此值不存在于下拉选择中！");
	133	writeSheetHolder.getSheet().addValidationData(dataValidation);
e7c126	134	}
03e8ac	135	}else{
J	136	// 2. 创建数据字典的 sheet 页
	137	Sheet dictSheet = workbook.createSheet(DICT_SHEET_NAME);
	138	for (KeyValue<Integer, List<String>> keyValue : keyValues) {
	139	int rowLength = keyValue.getValue().size();
	140	// 2.1 设置字典 sheet 页的值，每一列一部字典项
	141	for (int i = 0; i < rowLength; i++) {
	142	Row row = dictSheet.getRow(i);
	143	if (row == null) {
	144	row = dictSheet.createRow(i);
	145	}
	146	row.createCell(keyValue.getKey()).setCellValue(keyValue.getValue().get(i));
	147	}
	148	// 2.2 设置单元格下拉选择
	149	setColumnSelect(writeSheetHolder, workbook, helper, keyValue);
	150	}
e7c126	151	}
H	152	}
	153
	154	/**
	155	* 设置单元格下拉选择
	156	*/
	157	private static void setColumnSelect(WriteSheetHolder writeSheetHolder, Workbook workbook, DataValidationHelper helper,
	158	KeyValue<Integer, List<String>> keyValue) {
	159	// 1.1 创建可被其他单元格引用的名称
	160	Name name = workbook.createName();
	161	String excelColumn = ExcelUtil.indexToColName(keyValue.getKey());
	162	// 1.2 下拉框数据来源 eg:字典sheet!$B1:$B2
	163	String refers = DICT_SHEET_NAME + "!$" + excelColumn + "$1:$" + excelColumn + "$" + keyValue.getValue().size();
	164	name.setNameName("dict" + keyValue.getKey()); // 设置名称的名字
	165	name.setRefersToFormula(refers); // 设置公式
	166
	167	// 2.1 设置约束
	168	DataValidationConstraint constraint = helper.createFormulaListConstraint("dict" + keyValue.getKey()); // 设置引用约束
	169	// 设置下拉单元格的首行、末行、首列、末列
	170	CellRangeAddressList rangeAddressList = new CellRangeAddressList(FIRST_ROW, LAST_ROW,
	171	keyValue.getKey(), keyValue.getKey());
	172	DataValidation validation = helper.createValidation(constraint, rangeAddressList);
	173	if (validation instanceof HSSFDataValidation) {
	174	validation.setSuppressDropDownArrow(false);
	175	} else {
	176	validation.setSuppressDropDownArrow(true);
	177	validation.setShowErrorBox(true);
	178	}
	179	// 2.2 阻止输入非下拉框的值
	180	validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
	181	validation.createErrorBox("提示", "此值不存在于下拉选择中！");
	182	// 2.3 添加下拉框约束
	183	writeSheetHolder.getSheet().addValidationData(validation);
	184	}
	185
	186	}