说明
核心方法就是：resolveAnnotation和buildSheetWriteHandler ，解析注解参数，设置下拉
StringUtils中添加了一个转换方法。
ExcelUtils中为exportExcel公用方法中添加了入参，添加是否带有下拉框的判断
Controller导出方法无变化
ExcelUtil.java代码
/**
* 导出excel
*
* @param list 导出数据集合
* @param sheetName 工作表的名称
* @param clazz 实体类
* @param response 响应体
* @author: 安贺
* @createDate: 2023/05/05 09:23:03
* @version: 1.0.0
*/
public static void exportExcel(List list, String sheetName, Class clazz, HttpServletResponse response) {
try {
resetResponse(sheetName, response);
ServletOutputStream os = response.getOutputStream();
exportExcel(list, sheetName, clazz, false,true, os);
} catch (IOException e) {
throw new RuntimeException("导出Excel异常");
}
}
/**
* 导出excel
*
* @param list 导出数据集合
* @param sheetName 工作表的名称
* @param clazz 实体类
* @param merge 是否合并单元格
* @param withSelected 带有下拉
* @param os 输出流
* @author: 安贺
* @createDate: 2023/05/05 09:24:32
* @version: 1.0.0
*/
public static void exportExcel(List list, String sheetName, Class clazz, boolean merge,
boolean withSelected,
OutputStream os) {
ExcelWriterSheetBuilder builder = EasyExcel.write(os, clazz)
.autoCloseStream(false)
// 自动适配
.registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
// 大数值自动转换 防止失真
.registerConverter(new ExcelBigNumberConvert())
.sheet(sheetName);
if(withSelected){
builder.registerWriteHandler(buildSheetWriteHandler(resolveAnnotation(clazz)));
}
if (merge) {
// 合并处理器
builder.registerWriteHandler(new CellMergeStrategy(list, true));
}
builder.doWrite(list);
}
/**
* 解析实体类属性上的 ExcelDictFormat 注解，返回一个 map，
* 其中键为属性在 Excel 表格中的索引位置，值为 ExcelDictFormat 注解。
*
* @param head 实体类
* @return {@link Map }
* @author: 安贺
* @createDate: 2023/05/05 10:56:24
* @version: 1.0.0
*/
private static Map resolveAnnotation(Class head) {
Map indexSelectMap = new HashMap(16);
//反射获取属性
IntStream.range(0, head.getDeclaredFields().length).forEach(i -> {
Field field = head.getDeclaredFields()[i];
ExcelDictFormat excelDictFormat = field.getAnnotation(ExcelDictFormat.class);
if (excelDictFormat == null) {
return;
}
ExcelProperty property = field.getAnnotation(ExcelProperty.class);
indexSelectMap.put(
//如果使用原生easyExcel注解声明了excel展示的索引位置，填充map的key为声明索引位置
Optional.ofNullable(property)
.map(ExcelProperty::index)
.filter(index -> index >= 0)
//否则使用当前类的属性的索引下标
//为什么要i-1呢，因为实体类中有序列化id，影响展示下拉时的列。
.orElse(i - 1),
excelDictFormat);
});
return indexSelectMap;
}
/**
* 为excel创建下拉框
*
* @param indexSelectMap 指数查询地图
* @return {@link SheetWriteHandler }
* @author: 安贺
* @createDate: 2023/05/05 10:50:22
* @version: 1.0.0
*/
private static SheetWriteHandler buildSheetWriteHandler(Map indexSelectMap) {
return new SheetWriteHandler() {
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Sheet sheet = writeSheetHolder.getSheet();
DataValidationHelper helper = sheet.getDataValidationHelper();
indexSelectMap.forEach((k, v) -> {
String type = v.dictType();
//读取内容转表达式 (如: 0=男,1=女,2=未知)
List values;
//这里就是确定读取表达式还是字典类型
if (StringUtils.isBlank(type)) {
values = StringUtils.str2List(v.readConverterExp());
}else {
values = SpringUtils.getBean(DictService.class).getDictDataList(type);
}
//填充excel下拉内容、非法值提示操作
if (CollUtil.isNotEmpty(values)) {
CellRangeAddressList rangeList = new CellRangeAddressList(v.firstRow(), v.lastRow(), k, k);
DataValidationConstraint constraint = helper.createExplicitListConstraint(values.toArray(new String[0]));
DataValidation validation = helper.createValidation(constraint, rangeList);
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.setShowErrorBox(true);
validation.setSuppressDropDownArrow(true);
validation.createErrorBox("提示", "请输入下拉选项中的内容哦");
sheet.addValidationData(validation);
}
});
}
};
}StringUtils.java代码
/**
* str2列表
*
* @param readConverterExp 读变换器实验
* @return {@link List }
* @author: 安贺
* @createDate: 2023/05/05 10:04:36
* @version: 1.0.0
*/
public static List str2List(String readConverterExp) {
String[] arr = readConverterExp.split(",");
List strings = new ArrayList(arr.length);
for (String s : arr) {
strings.add(s.split("=")[1]);
}
return strings;
}SysUserController.java代码
/**
* 下载导入模板
*/
@PostMapping("/importTemplate")
public void importTemplate(HttpServletResponse response) throws IOException {
ExcelUtil.exportExcel(new ArrayList(), "用户数据", SysUserImportVo.class, response);
}SysUserImportVo.java代码
package com.bjjdlh.system.domain.vo;
import com.alibaba.excel.annotation.ExcelProperty;
import com.bjjdlh.common.excel.annotation.ExcelDictFormat;
import com.bjjdlh.common.excel.convert.ExcelDictConvert;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.io.Serializable;
/**
* 用户对象导入VO
*
* @author Lion Li
*/
@Data
@NoArgsConstructor
public class SysUserImportVo implements Serializable {
private static final long serialVersionUID = 1L;
/**
* 用户ID
*/
@ExcelProperty(value = "用户序号")
private Long userId;
/**
* 部门ID
*/
@ExcelProperty(value = "部门编号")
private Long deptId;
/**
* 用户账号
*/
@ExcelProperty(value = "登录名称")
private String userName;
/**
* 用户昵称
*/
@ExcelProperty(value = "用户名称")
private String nickName;
/**
* 用户邮箱
*/
@ExcelProperty(value = "用户邮箱")
private String email;
/**
* 手机号码
*/
@ExcelProperty(value = "手机号码")
private String phonenumber;
/**
* 用户性别，测试导出下拉框，读取自定义下拉菜单
*/
@ExcelProperty(value = "用户性别", converter = ExcelDictConvert.class)
@ExcelDictFormat(readConverterExp = "0=不男不女,1=女,2=未知")
private String sex;
/**
* 帐号状态（0正常 1停用）
*/
@ExcelProperty(value = "帐号状态", converter = ExcelDictConvert.class)
@ExcelDictFormat(dictType = "sys_normal_disable")
private String status;
}ExcelDictFormat.java
package com.an.common.annotation;
import com.an.common.utils.StringUtils;
import java.lang.annotation.*;
/**
* 字典格式化
*
* @author Lion Li
*/
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelDictFormat {
/**
* 如果是字典类型，请设置字典的type值 (如: sys_user_sex)
*/
String dictType() default "";
/**
* 读取内容转表达式 (如: 0=男,1=女,2=未知)
*/
String readConverterExp() default "";
/**
* 分隔符，读取字符串组内容
*/
String separator() default StringUtils.SEPARATOR;
/**
* 设置下拉框的起始行，默认为第二行
*/
int firstRow() default 1;
/**
* 设置下拉框的结束行，默认为最后一行
*/
int lastRow() default 0x10000;
}
