先看看效果，前4个均是从数据库查询出来的下拉选择：
步骤一：写EXCEL 注解：
import java.lang.annotation.Retention;import java.lang.annotation.RetentionPolicy;import java.lang.annotation.Target;@Retention(RetentionPolicy.RUNTIME) @Target( { java.lang.annotation.ElementType.FIELD }) public @interface ExcelVOAttribute {/** * 导出到Excel中的名字. */ public abstract String name(); /** * 配置列的名称,对应A,B,C,D.... */ public abstract String column(); /** * 提示信息 */ public abstract String prompt() default ""; /** * 设置只能选择不能输入的列内容. */ public abstract String[] combo() default {}; /** * 是否导出,应对需求:有时我们需要导出一份模板,这是标题需要但内容需要用户手工填写. */ public abstract boolean isExport() default true; /** * 是否应用到导入模板上*/ public abstract boolean isUsedInImport() default true; /** * 是否应用到导出上*/ public abstract boolean isUsedInExport() default true; }
步骤二：写实体类：
@Data@TableName("rst_cycleset")public class Cycleset {@TableId(type = IdType.AUTO)private Long id;// @ExcelVOAttribute(name = "经典分类", column = "A",isUsedInExport=false,isUsedInImport=false, combo = { } )private String sx10dm; //经典分类代码@TableField(exist = false)@ExcelVOAttribute(name = "经典分类", column = "A")private String sx10mc;//@ExcelVOAttribute(name = "产品系列", column = "B",isUsedInExport=false, combo = { } )private String sx11dm; //产品系列代码@TableField(exist = false)@ExcelVOAttribute(name = "产品系列", column = "B" )private String sx11mc;//@ExcelVOAttribute(name = "大类属性", column = "C",isUsedInExport=false, combo = { } )private String sx12dm; //大类属性代码@TableField(exist = false)@ExcelVOAttribute(name = "大类属性", column = "C")private String sx12mc;//@ExcelVOAttribute(name = "产品品类", column = "D",isUsedInExport=false, combo = { } )private String pldm; //品类代码@TableField(exist = false)@ExcelVOAttribute(name = "产品品类", column = "D" )private String plmc;@ExcelVOAttribute(name = "周期起始", column = "E" )private String cycleFrom;@ExcelVOAttribute(name = "周期截止", column = "F")private String cycleTo;@ExcelVOAttribute(name = "冻结天数", column = "G" )private Integer frozenDays;@ExcelVOAttribute(name = "投单货量", column = "H" )private Integer deliveryVolume;}
步骤三：利用反射动态修改注解
public void setClassExcelAttribute(Class clazz,String declareField,String memberKey,Object memberValue) throws Exception{Field declaredField = clazz.getDeclaredField(declareField);declaredField.setAccessible(true);ExcelVOAttribute annotation = declaredField.getAnnotation(ExcelVOAttribute.class);if (annotation != null) {InvocationHandler ih = Proxy.getInvocationHandler(annotation);Field memberValuesField = ih.getClass().getDeclaredField("memberValues");memberValuesField.setAccessible(true);Map memberValues = (Map) memberValuesField.get(ih);memberValues.put(memberKey, memberValue);}}public String[] getClassFieldValueList(Class clazz,String declareField,List list) throwsException{String[] strArray=new String[list.size()];int index=0;for(Object object:list){Field declaredField = clazz.getDeclaredField(declareField);declaredField.setAccessible(true);String str=declaredField.get(object).toString();//System.out.println(str);strArray[index++]=str;}return strArray;}
步骤四：根据注解加入EXCEL 下拉校验
//...for (int i = 0; i < fields.size(); i++) { Field field = fields.get(i); ExcelVOAttribute attr = field.getAnnotation(ExcelVOAttribute.class); int col = getExcelCol(attr.column());// 获得列号 cell = row.createCell(col);// 创建列 cell.setCellType(HSSFCell.CELL_TYPE_STRING);// 设置列中写入内容为String类型 cell.setCellValue(attr.name()); style=getHeadCellStyler(workbook);// 如果设置了提示信息则鼠标放上去提示. if (!attr.prompt().trim().equals("")) setHSSFPrompt(sheet, "", attr.prompt(), 3, 3000, col, col); // 如果设置了combo属性则本列只能选择或者输入下拉中的内容 if (bo().length > 0) setHSSFValidation(sheet, bo(), col,workbook);cell.setCellStyle(style); } //...//由于下列是不确定是所以不能保证不超过255故用隐藏页来放置下列信息private HSSFSheet setHSSFValidation(HSSFSheet sheet, String[] textlist, int col,HSSFWorkbook wb) {//获取所有sheet页个数int sheetTotal = wb.getNumberOfSheets();String hiddenSheetName = "hiddenSheet" + sheetTotal;HSSFSheet hiddenSheet = wb.createSheet(hiddenSheetName);Row row;//写入下拉数据到新的sheet页中for (int i = 0; i < textlist.length; i++) {row = hiddenSheet.createRow(i);Cell cell = row.createCell(0);cell.setCellValue(textlist[i]);}//获取新sheet页内容String strFormula = hiddenSheetName + "!$A$1:$A$65535"; //hiddenSheetName + ! 定位到用来加载列的新的sheet页，后面则是A列的1-65535为有效性List条件DVConstraint constraint = DVConstraint.createFormulaListConstraint(strFormula);// 设置数据有效性加载在哪个单元格上,四个参数分别是：起始行、终止行、起始列、终止列CellRangeAddressList regions = new CellRangeAddressList(0,65535, col, col);// 数据有效性对象DataValidation validation = new HSSFDataValidation(regions,constraint);sheet.addValidationData(validation);//将新建的sheet页隐藏掉wb.setSheetHidden(sheetTotal, true);return sheet;}
步骤五：导出模板
@Overridepublic void downLoadCycleSetTemplate(HttpServletResponse response) throws Exception {QueryWrapper wrapper=new QueryWrapper();List<EZFjsx10> sx10List=fjsx10Dao.selectList(wrapper);List<EZFjsx11> sx11List=fjsx11Dao.selectList(wrapper);List<EZFjsx12> sx12List=fjsx12Dao.selectList(wrapper);List<EZDalei> plList=daleiDao.selectList(wrapper);MyExcel<Cycleset> me = new MyExcel<>(Cycleset.class);String[] sx10mcArray=me.getClassFieldValueList(EZFjsx10.class,"sxmc", sx10List);String[] sx11mcArray=me.getClassFieldValueList(EZFjsx11.class,"sxmc", sx11List);String[] sx12mcArray=me.getClassFieldValueList(EZFjsx12.class,"sxmc", sx12List);String[] plmcArray=me.getClassFieldValueList(EZDalei.class,"dlmc", plList);Class<? extends Cycleset> clazz = new Cycleset().getClass();me.setClassExcelAttribute( clazz,"sx10mc","combo",sx10mcArray);me.setClassExcelAttribute( clazz,"sx11mc","combo",sx11mcArray);me.setClassExcelAttribute( clazz,"sx12mc","combo",sx12mcArray);me.setClassExcelAttribute( clazz,"plmc","combo",plmcArray);me.exportExcelFormat("周期设置导入模板", "周期设置", response);}
