工具类在底部，先上代码。
静态下拉值设置
public class ActiveGifts {private String lpbarcode;@ExcelProperty(value = "礼品名称") private String lpname;@ExcelProperty(value = "是否可用") @ExcelDropdown(value = {"Y", "N"}, isAllowOtherValue = true) private String isvalid;}
动态下拉值设置
java8版本
实体
public class ActiveGifts {private String lpbarcode;@ExcelProperty(value = "礼品名称") private String lpname;@ExcelProperty(value = "是否可用") @ExcelDropdown(value = {"Y", "N"}, isAllowOtherValue = true) private String isvalid;}
示例方法：动态修改为：是，否
public void downloadTemplate(HttpServletResponse response) { try { ExcelImportUtils<ActiveGifts> activeGiftsExcelImportUtils = new ExcelImportUtils<>(ActiveGifts.class); String[] strings = new String[1]; strings[0] = "isvalid"; Object[] values = new Object[strings.length]; values[0] = new String[]{"是", "否"}; //动态修改ExcelDropdown注解的值 activeGiftsExcelImportUtils.setExcelDropdownValue(strings, values); activeGiftsExcelImportUtils.downLoad(response,new ArrayList<>(),"文件名", "sheet名"); } catch (Exception e) { e.printStackTrace(); throw new ServiceException("下载失败"); }}
java9及以上版本
实体
去掉这行：@ExcelDropdown(value = {"Y", "N"}, isAllowOtherValue = true)
public class ActiveGifts {private String lpbarcode;@ExcelProperty(value = "礼品名称") private String lpname;@ExcelProperty(value = "是否可用") private String isvalid;}
示例方法
public void downloadTemplate(HttpServletResponse response) {
try {
ExcelImportUtils<ActiveGifts> activeGiftsExcelImportUtils = new ExcelImportUtils<>(ActiveGifts.class);
//动态设置下拉的值
activeGiftsExcelImportUtils.setDropDownValue("isvalid", new String[]{"是", "否"});
activeGiftsExcelImportUtils.downLoad(response, new ArrayList<>(), "文件名", "sheet名");
} catch (Exception e) {
e.printStackTrace();
throw new ServiceException("下载失败");
}
}
工具类
AnnotationUtil
@Data
@Accessors(chain = true)
@ToString
public class AnnotationUtil<T> {
public Class<T> clazz;
public AnnotationUtil(Class<T> clazz) {
this.clazz = clazz;
}
/**
* 动态修改对象属性上某个注解的属性值，通过getClazz()方法可以得到修改后的class
*
* @param fieldName 对象属性名称
* @param annotationClass 注解class
* @param attrName 注解属性名称
* @param attrValue 注解属性值
* @return 本工具类实例
* @throws Exception 异常
*/
public AnnotationUtil updateAnnoAttrValue(String fieldName, Class<? extends Annotation> annotationClass, String attrName, Object attrValue) throws Exception {
Field[] declaredFields = this.clazz.getDeclaredFields();
if (null != declaredFields && declaredFields.length != 0) {
for (int i = 0; i < declaredFields.length; i++) {
Field declaredField = declaredFields[i];
if (fieldName.equals(declaredField.getName())) {
InvocationHandler invocationHandler = Proxy.getInvocationHandler(declaredField.getAnnotation(annotationClass));
Field hField = invocationHandler.getClass().getDeclaredField("memberValues");
hField.setAccessible(true);
Map memberValues = (Map) hField.get(invocationHandler);
memberValues.put(attrName, attrValue);
break;
}
}
}
return this;
}
/**
* !!!!!!!!!! java版本过高用不了，须在启动类中添加配置 参考：https://blog.csdn.net/wenxuankeji/article/details/140672099
* 动态修改对象属性上某个注解的属性值，通过getClazz()方法可以得到修改后的class
* @author ljd
* @date 2024/12/4
* @param fieldNames 字段数组
* @param annotationClass 注解
* @param attrNames 属性名数组-和字段依次匹配
* @param attrValues 属性值数组-和字段依次匹配
* @return
* @throws Exception
*/
public AnnotationUtil updateAnnoAttrValue(String[] fieldNames, Class<? extends Annotation> annotationClass, String[] attrNames, Object[] attrValues) throws Exception {
if (fieldNames == null || fieldNames.length == 0 || attrNames == null || attrValues == null) {
throw new Exception("参数错误！");
}
Field[] declaredFields = this.clazz.getDeclaredFields();
if (null != declaredFields && declaredFields.length != 0) {
HashMap<String, Field> map = new HashMap<>();
for (int i = 0; i < declaredFields.length; i++) {
Field declaredField = declaredFields[i];
map.put(declaredField.getName(), declaredField);
}
for (int j = 0; j < fieldNames.length; j++) {
if (!map.containsKey(fieldNames[j])) {
throw new Exception("字段名错误");
}
if(map.get(fieldNames[j]).getAnnotation(annotationClass) == null){
throw new Exception("该属性上无此注解");
}
InvocationHandler invocationHandler = Proxy.getInvocationHandler(map.get(fieldNames[j]).getAnnotation(annotationClass));
Field hField = invocationHandler.getClass().getDeclaredField("memberValues");
hField.setAccessible(true);
Map memberValues = (Map) hField.get(invocationHandler);
memberValues.put(attrNames[j], attrValues[j]);
}
}
return this;
}
}
ExcelDropdown
@Documented
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Inherited
public @interface ExcelDropdown {
String[] value() default {}; //下拉列表
boolean isAllowOtherValue() default false; //是否允许设置其他的值。false：只能是下拉列表的值；true：允许列表之外的值
}
ExcelImportUtils
@Slf4j
public class ExcelImportUtils<T extends Object> {
private Class<T> clazz;
// 对应列的下拉列表
Map<Integer, Map<String, Object>> mapDropDown = new HashMap<>();
//对应日期位置列表
List<Integer> dateList = new ArrayList<>();
public ExcelImportUtils(Class<T> clazz) {
this.clazz = clazz;
}
//导入
public List<T> excelImport(InputStream stream) throws Exception {
List<T> list = new ArrayList<>();
EasyExcel.read(stream, Object.class, new AnalysisEventListener<T>() {
@SneakyThrows
@Override
public void invoke(T o, AnalysisContext analysisContext) {
list.add(o);
}
@Override
public void doAfterAllAnalysed(AnalysisContext analysisContext) {
log.debug("导入完成！");
}
}).sheet().doRead();
return list;
}
/**
* 下载
*
* @param res 响应
* @param data 下载的数据
* @param fileName 文件名
* @param sheetName 表名
* @throws Exception
*/
public void downLoad(HttpServletResponse res, List<T> data, String fileName, String sheetName) throws Exception {
setMapDropDown(this.clazz);
DropdownWriteHandler dropdownWriteHandler = new DropdownWriteHandler();
EasyExcelFactory.write(getOutputStream(fileName, res), this.clazz).sheet(sheetName).registerWriteHandler(dropdownWriteHandler).doWrite(data);
}
//响应头封装
private static OutputStream getOutputStream(String fileName, HttpServletResponse response) throws Exception {
fileName = URLEncoder.encode(fileName, "UTF-8");
response.setContentType("application/vnd.ms-excel");
response.setCharacterEncoding("utf8");
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
return response.getOutputStream();
}
//自定义处理器：单元格下拉列表格式
class DropdownWriteHandler implements SheetWriteHandler {
@Override
public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
}
@Override
public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
Sheet sheet = writeSheetHolder.getSheet();
//开始设置下拉框
DataValidationHelper helper = sheet.getDataValidationHelper();//设置下拉框
for (Map.Entry<Integer, Map<String, Object>> entry : mapDropDown.entrySet()) {
/***起始行、终止行、起始列、终止列**/
CellRangeAddressList addressList = new CellRangeAddressList(1, 100000, entry.getKey(), entry.getKey()); // 检查的区域
/***设置下拉框数据**/
DataValidationConstraint constraint = helper.createExplicitListConstraint((String[]) entry.getValue().get("val"));
DataValidation dataValidation = helper.createValidation(constraint, addressList);
/***处理Excel兼容性问题**/
if (dataValidation instanceof XSSFDataValidation) {
dataValidation.setSuppressDropDownArrow(true); // 验证输入数据是否真确
dataValidation.setShowErrorBox(true); // 输入无效值时是否显示错误框
dataValidation.setShowPromptBox(true); // 设置无效值时 是否弹出提示框
dataValidation.createPromptBox("温馨提示", "只能选择列表中的值!!!"); // 设置无效值时的提示框内容
sheet.addValidationData(dataValidation);
} else {
dataValidation.setSuppressDropDownArrow(false);
}
sheet.addValidationData(dataValidation);
}
/***时间格式校验**/
for (int i : dateList) {
DataValidationConstraint constraint2 = helper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN, "Date(1900, 1, 1)", "Date(2100, 12, 31)", "yyyy/MM/dd");
DataValidation dataValidation2 = helper.createValidation(constraint2, new CellRangeAddressList(1, 100000, i, i));
//校验时间
dataValidation2.setSuppressDropDownArrow(true); // 验证输入数据是否真确
dataValidation2.setShowErrorBox(true); // 输入无效值时是否显示错误框
dataValidation2.setShowPromptBox(true); // 设置无效值时 是否弹出提示框
dataValidation2.createPromptBox("温馨提示", "请输入[yyyy-MM-dd]格式日期!!!"); // 设置无效值时的提示框内容
sheet.addValidationData(dataValidation2);
}
//下面定时样式的
Row row = sheet.getRow(0);
if (row != null) {
Workbook workbook = writeWorkbookHolder.getWorkbook();
row.setHeight((short) 500);
for (int i = 0; i < row.getLastCellNum(); i++) {
sheet.setColumnWidth(i, 5000);
Cell cell = row.getCell(i);
cell.setCellStyle(setStyle(workbook));
}
row.setHeight((short) (205 * 7));
}
}
//设置单元格样式
public CellStyle setStyle(Workbook wb) {
Font dataFont = wb.createFont();
dataFont.setColor(new HSSFColor().getIndex());
dataFont.setFontName("宋体");
dataFont.setFontHeight((short) 240);
dataFont.setBold(true);
dataFont.setFontHeightInPoints((short) 10);
CellStyle dataStyle = wb.createCellStyle();
dataStyle.setFont(dataFont);
dataStyle.setWrapText(true);
dataStyle.setVerticalAlignment(VerticalAlignment.CENTER);
dataStyle.setAlignment(HorizontalAlignment.CENTER);
return dataStyle;
}
}
/**
* 设置下拉值
* （提供该方法，主要解决java版本过高不能动态修改ExcelDropdown的value值的问题）
*
* @param field 字段名
* @param strings 列表值
* @author ljd
* @date 2024/12/5
*/
public void setDropDownValue(String field, String[] strings) {
Map<Integer, Map<String, Object>> mapDropDown = new HashMap<>();
HashMap<String, Object> stringObjectHashMap = new HashMap<>();
stringObjectHashMap.put("val", strings);
Field[] fields = clazz.getDeclaredFields();
//用于标志是excel的哪个列
int flag = -1;
for (int i = 0; i < fields.length; i++) {
ExcelProperty annotation = fields[i].getAnnotation(ExcelProperty.class);
if (annotation != null) {
flag = flag + 1;
}
if (fields[i].getName().equals(field)) {
mapDropDown.put(flag, stringObjectHashMap);
}
}
this.mapDropDown = mapDropDown;
}
//下拉列表
private void setMapDropDown(Class clazz) throws Exception {
Field[] fields = clazz.getDeclaredFields();
if (fields == null) {
throw new Exception("属性为空");
}
//用于标记是第几个注解
int annotationCount = -1;
for (int i = 0; i < fields.length; i++) {
Field field = fields[i];
//DateTimeFormat注解
if (field.isAnnotationPresent(DateTimeFormat.class)) {
int index = field.getAnnotation(ExcelProperty.class).index(); //位置
dateList.add(index);
}
//判断这个字段上是否有相应的注解信息（ExcelDropdown.class）
if (field.isAnnotationPresent(ExcelDropdown.class) && field.isAnnotationPresent(ExcelProperty.class)) {
// int index = field.getAnnotation(ExcelProperty.class).index(); //位置
String[] values = field.getAnnotation(ExcelDropdown.class).value(); //下拉列表的value内容
boolean allowOtherValue = field.getAnnotation(ExcelDropdown.class).isAllowOtherValue(); //下拉列表的isAllowOtherValue值
Map<String, Object> map = new HashMap<>();
map.put("val", values);
map.put("isAllow", allowOtherValue);
mapDropDown.put(++annotationCount, map);
}
}
}
//设置对应属性的@ExcelDropdown注解的对应属性的对应值。
// 如：fieldNames[0] 对应 attrNames[0] 对应 attrValues[0]， 否则出错。
public void setExcelDropdownValue(String[] fieldNames, Object[] attrValues) throws Exception {
AnnotationUtil<T> s = new AnnotationUtil<T>(this.clazz);
String[] attrNames = new String[fieldNames.length]; //ExcelDropdown注解只有value属性
for (int i = 0; i < attrNames.length; i++) {
attrNames[i] = "value";
}
//更新字段上ExcelDropdown注解的value属性的值
s.updateAnnoAttrValue(fieldNames, ExcelDropdown.class, attrNames, attrValues);
}
}
前端接收并下载
发送请求并处理res
//下载模板
const downloadTemp = () => {
activeGiftsApi.activeGiftsDownloadTemp().then((res) => {
let blob = new Blob([res.data], { type: 'application/vnd.ms-excel;charset=utf-8' })
let filename = '活动礼品导入模板'
let url = window.URL.createObjectURL(blob)
let aLink = document.createElement('a')
aLink.style.display = 'none'
aLink.href = url
aLink.setAttribute('download', filename)
document.body.appendChild(aLink)
aLink.click()
document.body.removeChild(aLink)
window.URL.revokeObjectURL(url)
})
}