| | |
| | | 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; |
| | | |
| | |
| | | |
| | | /** |
| | | * 数据起始行从 0 开始 |
| | | * |
| | | * 约定:本项目第一行有标题所以从 1 开始如果您的 Excel 有多行标题请自行更改 |
| | | */ |
| | | public static final int FIRST_ROW = 1; |
| | |
| | | */ |
| | | 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++; |
| | | } |
| | |
| | | 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); |
| | | } |
| | | } |
| | | } |
| | | |