JAVA POI导出带动态下拉框EXCEL模板-白红宇

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

阅读量：2386 次

发布时间：2019-05-10

本文共 6683 字，大约阅读时间需要 22 分钟。

先看看效果，前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
   
     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);    }