EasyExcel自定义动态下拉框（附加业务对象转换功能）
全文直接复制粘贴即可，测试无误
一、注解类
1、ExcelSelected.java
设置下拉框
@Documented
@Target({ElementType.FIELD})//用此注解用在属性上。
@Retention(RetentionPolicy.RUNTIME)//注解不仅被保存到class文件中，jvm加载class文件之后，仍然存在；
public @interface ExcelSelected {/*** 固定下拉内容*/String[] source() default {};/*** 方式二：提供动态下拉选项的类*/Class<? extends WhExcelDynamicSelect>[] sourceClass() default {};/*** 设置下拉框的起始行，默认为第二行*/int firstRow() default 1;/*** 设置下拉框的结束行，默认为最后一行*/int lastRow() default 0x10000;
}
2、TextColumn.java
设置单元格为文本格式
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
public @interface TextColumn {int index(); // 列索引
}
二、Controller类
分为导入数据与导出模板
@Tag(name = "xxxx管理")
@RestController
@RequestMapping("/api/v1/project/people")
@RequiredArgsConstructor
@Slf4j
public class WhProjectPeopleController {private final WhProjectPeopleService whProjectPeopleService;@GetMapping("/exportDownload")@Operation(summary = "导出excel模板")public void downloadExcel(HttpServletResponse response) throws IOException {//获取输入流，原始模板位置String name = "人员信息模板";//假如以中文名下载的话，设置下载文件名称String filename = "人员信息模板.xlsx";//转码，免得文件名中文乱码filename = URLEncoder.encode(filename, "UTF-8");//设置文件下载头response.addHeader("Content-Disposition", "attachment;filename=" + filename);response.setContentType("multipart/form-data");ExcelUtils.writeExcel(response, filename, name, WhProjectPeopleVO.class, null);}@PostMapping("/importExcel")@Operation(summary = "导入人员数据")public Result importExcel(MultipartFile file, HttpServletRequest request) throws IOException {if (file.isEmpty()) {throw new SzException("excel文件内容为空！");}List<WhProjectPeopleVO> vos = ExcelUtils.read(file, WhProjectPeopleVO.class);if (CollectionUtils.isEmpty(vos)){throw new SzException("导入数据失败！");}Map<String,Integer> result = whProjectPeopleService.importExcel(vos);return Result.success(result);}}三、Service方法
向excel导入数据
@Override@Transactionalpublic Map<String, Integer> importExcel(List<WhProjectPeopleVO> vos) {Map<String, Integer> params = new HashMap<>();if (CollectionUtils.isEmpty(vos)) {throw new SzException("数据导入失败！");}log.info("导入数据：{}" + JSON.toJSONString(vos));int count = 0;List<WhProjectPeople> peoples = whProjectPeopleConverter.voListToEntity(vos);for (WhProjectPeople people : peoples) {WhProjectPeopleForm whProjectPeopleForm = whProjectPeopleConverter.entityToForm(people);saveProjectPeople(whProjectPeopleForm);count++;params.put("insert", count);}return params;}
}
四、工具类
1、ExcelUtils.java
public class ExcelUtils {/*** 将列表以 Excel 响应给前端** @param response 响应* @param filename 文件名* @param sheetName Excel sheet 名* @param head Excel head 头* @param data 数据列表* @param <T> 泛型，保证 head 和 data 类型的一致性* @throws IOException 写入失败的情况*/public static <T> void writeExcel(HttpServletResponse response, String filename, String sheetName,Class<T> head, List<T> data) throws IOException {CellStyleStrategy cellStyleStrategy =new CellStyleStrategy(new WriteCellStyle());Map<Integer, WhExcelSelectedResolve> selectedMap = resolveSelectedAnnotation(head);// 输出 ExcelEasyExcel.write(response.getOutputStream(), head)// 不要自动关闭，交给 Servlet 自己处理.autoCloseStream(false)//设置表头和填充内容的样式.registerWriteHandler(cellStyleStrategy)//设置填充内容单元格格式为文本格式.registerWriteHandler(new CustomSheetWriteHandler()).registerWriteHandler(new SelectedSheetWriteHandler(selectedMap)).sheet(sheetName).doWrite(data);// 设置 header 和 contentType。写在最后的原因是，避免报错时，响应 contentType 已经被修改了response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));response.setContentType("application/vnd.ms-excel;charset=UTF-8");}/*** 将列表以 Excel 响应给前端** @param response 响应* @param filename 文件名* @param sheetName Excel sheet 名* @param head Excel head 头* @param data 数据列表* @param <T> 泛型，保证 head 和 data 类型的一致性* @throws IOException 写入失败的情况*/public static <T> void write(HttpServletResponse response, String filename, String sheetName,Class<T> head, List<T> data) throws IOException {CellStyleStrategy cellStyleStrategy =new CellStyleStrategy(new WriteCellStyle());// 输出 ExcelEasyExcel.write(response.getOutputStream(), head).autoCloseStream(false) // 不要自动关闭，交给 Servlet 自己处理.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy()) // 基于 column 长度，自动适配。最大 255 宽度.registerWriteHandler(cellStyleStrategy).sheet(sheetName).doWrite(data);// 设置 header 和 contentType。写在最后的原因是，避免报错时，响应 contentType 已经被修改了response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(filename, "UTF-8"));response.setContentType("application/vnd.ms-excel;charset=UTF-8");}public static <T> List<T> read(MultipartFile file, Class<T> head) throws IOException {return EasyExcel.read(file.getInputStream(), head, null)// 不要自动关闭，交给 Servlet 自己处理.autoCloseStream(false).doReadAllSync();}/*** 解析表头类中的下拉注解* @param head 表头类* @param <T> 泛型* @return Map<下拉框列索引, 下拉框内容> map*/private static <T> Map<Integer, WhExcelSelectedResolve> resolveSelectedAnnotation(Class<T> head) {Map<Integer, WhExcelSelectedResolve> selectedMap = new HashMap<>();// getDeclaredFields(): 返回全部声明的属性；getFields(): 返回public类型的属性Field[] fields = head.getDeclaredFields();for (int i = 0; i < fields.length; i++){Field field = fields[i];// 解析注解信息ExcelSelected selected = field.getAnnotation(ExcelSelected.class);ExcelProperty property = field.getAnnotation(ExcelProperty.class);if (selected != null) {WhExcelSelectedResolve excelSelectedResolve = new WhExcelSelectedResolve();String[] source = excelSelectedResolve.resolveSelectedSource(selected);if (source != null && source.length > 0){excelSelectedResolve.setSource(source);excelSelectedResolve.setFirstRow(selected.firstRow());excelSelectedResolve.setLastRow(selected.lastRow());if (property != null && property.index() >= 0){selectedMap.put(property.index(), excelSelectedResolve);} else {selectedMap.put(i, excelSelectedResolve);}}}}return selectedMap;}}
2、SpringContextUtil.java
@Component
public class SpringContextUtil implements ApplicationContextAware {/*** 获取ApplicationContext*/@Getterprivate static ApplicationContext applicationContext;@Overridepublic void setApplicationContext(ApplicationContext applicationContext) throws BeansException {SpringContextUtil.applicationContext = applicationContext;}/*** 通过class获取Bean*/public static <T> T getBean(Class<T> clazz) {return applicationContext.getBean(clazz);}/*** 通过name以及class获取Bean*/public static <T> T getBean(String name, Class<T> clazz) {return applicationContext.getBean(name, clazz);}
}
五、转换器
1、WhProjectExcelConverter.java
项目id与项目名称互相转化
public class WhProjectExcelConverter implements Converter<String> {private final WhProjectService whProjectService = SpringContextUtil.getBean(WhProjectService.class);private final List<WhProject> list = whProjectService.list();@Overridepublic Class<?> supportJavaTypeKey() {//实体类对象属性类型return String.class;}/*** 将单元格中数据转化为java对象属性* @param context* @return* @throws Exception*/@Overridepublic String convertToJavaData(ReadConverterContext<?> context) throws Exception {//创建集合用于存储项目id和名称对应关系Map<String,String> mapToJavaData = new HashMap<>();//遍历项目列表将项目id和名称放入mapfor (WhProject sysDeptEntity : list) {mapToJavaData.put(sysDeptEntity.getProjectName(),sysDeptEntity.getId());}//从cellData中读取数据 转换为实体类中的对象数值return mapToJavaData.get(context.getReadCellData().getStringValue());}/*** 将java对象转化为excel单元格数据* @param context* @return* @throws Exception*/@Overridepublic WriteCellData<?> convertToExcelData(WriteConverterContext<String> context) throws Exception {Map<String,String> mapToExcelData = new HashMap<>();for (WhProject sysDeptEntity : list) {mapToExcelData.put(sysDeptEntity.getId(),sysDeptEntity.getProjectName());}//将java属性转换为excel对应属性类型return new WriteCellData<>(mapToExcelData.get(context.getValue()));}
}
2、WhWorkTypeExcelConverter.java
int类型type值与String类型type名称相互转化
public class WhWorkTypeExcelConverter implements Converter<Integer> {private final WhDictItemService whDictItemService = SpringContextUtil.getBean(WhDictItemService.class);private final List<WhDictItem> list = whDictItemService.listByCode(WORK_TYPE);@Overridepublic Class<?> supportJavaTypeKey() {//实体类对象属性类型return Integer.class;}/*** 将单元格中数据转化为java对象属性* @param context* @return* @throws Exception*/@Overridepublic Integer convertToJavaData(ReadConverterContext<?> context) throws Exception {//创建集合用于存储部门id和名称对应关系Map<String,String> mapToJavaData = new HashMap<>();//遍历数据字典将属性名称和属性值放入mapfor (WhDictItem sysDeptEntity : list) {mapToJavaData.put(sysDeptEntity.getItemName(),sysDeptEntity.getItemValue());}//从cellData中读取数据 转换为实体类中的对象数值return Integer.valueOf(mapToJavaData.get(context.getReadCellData().getStringValue()));}/*** 将java对象转化为excel单元格数据* @param context* @return* @throws Exception*/@Overridepublic WriteCellData<?> convertToExcelData(WriteConverterContext<Integer> context) throws Exception {Map<String,String> mapToExcelData = new HashMap<>();for (WhDictItem sysDeptEntity : list) {mapToExcelData.put(sysDeptEntity.getItemValue(),sysDeptEntity.getItemName());}//将java属性转换为excel对应属性类型return new WriteCellData<>(mapToExcelData.get(context.getValue()));}
}
六、Handler类
1、SelectedSheetWriteHandler.java
设置下拉列表相关
@Data
public class SelectedSheetWriteHandler implements SheetWriteHandler {/*** 构建下拉选的map*/private final Map<Integer, WhExcelSelectedResolve> selectedMap;private final int columnSelectMaxLength = 255;@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {// 这里可以对cell进行任何操作Sheet sheet = writeSheetHolder.getSheet();DataValidationHelper helper = sheet.getDataValidationHelper();selectedMap.forEach((k, v) -> {// 设置下拉列表的行： 首行，末行，首列，末列CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), k, k);// 设置下拉列表的值DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getSource());// 设置约束DataValidation validation = helper.createValidation(constraint, rangeList);// 阻止输入非下拉选项的值validation.setErrorStyle(DataValidation.ErrorStyle.STOP);validation.setShowErrorBox(true);validation.setSuppressDropDownArrow(true);validation.createErrorBox("提示", "请输入下拉选项中的内容");sheet.addValidationData(validation);});}
}
2、CustomSheetWriteHandler.java
设置文本格式、表格式
public class CustomSheetWriteHandler implements SheetWriteHandler {// 设置100列columnprivate static final Integer COLUMN = 100;@Overridepublic void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {}@Overridepublic void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {// 获取带有 @TextColumn 注解的列索引Map<Integer, String> textColumns = getTextColumns();SXSSFSheet sxssfSheet = (SXSSFSheet) writeSheetHolder.getSheet();for (int i = 0; i < COLUMN; i++) {if (textColumns.containsKey(i)) {// 设置为文本格式CellStyle cellStyle = writeWorkbookHolder.getCachedWorkbook().createCellStyle();// 49为文本格式cellStyle.setDataFormat((short) 49);// i为列，一整列设置为文本格式sxssfSheet.setDefaultColumnStyle(i, cellStyle);}}}private Map<Integer, String> getTextColumns() {Map<Integer, String> textColumns = new HashMap<>();Class<?> dataClass = WhProjectPeopleVO.class;Field[] fields = dataClass.getDeclaredFields();for (Field field : fields) {if (field.isAnnotationPresent(TextColumn.class)) {int columnIndex = field.getAnnotation(TextColumn.class).index();textColumns.put(columnIndex, field.getName());}}return textColumns;}
}
七、SelectedResolve类
@Data
@Slf4j
public class WhExcelSelectedResolve {/*** 下拉内容*/private String[] source;/*** 设置下拉框的起始行，默认为第二行*/private int firstRow;/*** 设置下拉框的结束行，默认为最后一行*/private int lastRow;public String[] resolveSelectedSource(ExcelSelected excelSelected) {if (excelSelected == null) {return null;}// 获取固定下拉框的内容String[] source = excelSelected.source();if (source.length > 0) {return source;}// 获取动态下拉框的内容Class<? extends WhExcelDynamicSelect>[] classes = excelSelected.sourceClass();if (classes.length > 0) {try {WhExcelDynamicSelect excelDynamicSelect = classes[0].newInstance();String[] dynamicSelectSource = excelDynamicSelect.getSource();if (dynamicSelectSource != null && dynamicSelectSource.length > 0) {return dynamicSelectSource;}} catch (InstantiationException | IllegalAccessException e) {log.error("解析动态下拉框数据异常", e);}}return null;}
}
八、实体类
@Data
@Schema(description = "项目随行人员表")
@HeadRowHeight(30)
@ContentRowHeight(18)
public class WhProjectPeopleVO {@ExcelIgnore@Schema(description = "id")private String id;@ExcelIgnore@Schema(description = "人员类型 0随行人员1负责人 默认0")private Integer type;@ExcelProperty(value = "姓名", index = 0)@Schema(description = "姓名")private String name;@ColumnWidth(20)@TextColumn(index = 1)@ExcelProperty(value = "身份证号", index = 1)@Schema(description = "身份证号")private String identityId;@ColumnWidth(20)@ExcelProperty(value = "手机号", index = 2)@Schema(description = "手机号")private String mobile;@ColumnWidth(20)@ExcelSelected(sourceClass = WhProjectSelect.class)@ExcelProperty(value = "所属项目", index = 3, converter = WhProjectExcelConverter.class)@Schema(description = "项目id")private String projectId;@ExcelIgnore@Schema(description = "微信openId")private String openId;@ExcelIgnore@Schema(description = "微信unionId")private String unionId;@ExcelIgnore@Schema(description = "开始时间")private Date startTime;@ExcelIgnore@Schema(description = "结束时间")private Date endTime;@ExcelIgnore@Schema(description = "项目名称")private String projectName;@ExcelIgnore@Schema(description = "最近入场时间")private Date lastArrivalTime;@ExcelSelected(sourceClass = WhWorkTypeSelect.class)@ExcelProperty(value = "工种", index = 4, converter = WhWorkTypeExcelConverter.class)@Schema(description = "工种")private Integer workType;
}
测试结果
