JAVA POI导出带动态下拉框EXCEL模板

先看看效果，前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) throws     Exception{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 (attr.combo().length > 0)  setHSSFValidation(sheet, attr.combo(), 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 sx10List=fjsx10Dao.selectList(wrapper);List sx11List=fjsx11Dao.selectList(wrapper);List sx12List=fjsx12Dao.selectList(wrapper);List  plList=daleiDao.selectList(wrapper);MyExcel 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  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);}

本文来自互联网用户投稿，文章观点仅代表作者本人，不代表本站立场，不承担相关法律责任。如若转载，请注明出处。 如若内容造成侵权/违法违规/事实不符，请点击【内容举报】进行投诉反馈！