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;
}

步骤五：导出模板

    @Override
    public 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);
    }