EasyExecl导出模板,实现动态下拉列

矫情吗；* 2023-01-10 14:48 102阅读 0赞

### 1.需要效果. ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70][]

### 2.pom.xml  依赖jar包 ###

<dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>easyexcel</artifactId>
                <version>2.0.2</version>
            </dependency>

### 3.自定义注解 ###

/**
     * 自定义注解标记导出excel的下拉数据集
     *  Author xianyu
     *  Date  2021-01-20
     */
    @Documented
    // 作用在字段上
    @Target(ElementType.FIELD)
    // 运行时有效
    @Retention(RetentionPolicy.RUNTIME)
    public @interface DropDownSetField {
        // 固定下拉内容
        String[] source() default {};
    
        String name() default "";
    }

### 4.实体类添加 easyexecl注释 和自定义注释 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 1][]

### 5.逻辑处理 ###

@Override
        public void downTemplate(HttpServletResponse response) {
            try {
                // 获取该类声明的所有字段
                Field[] fields = PersonModel.class.getDeclaredFields();
                // 响应字段对应的下拉集合
                Map<Integer, String[]> map = new HashMap<>();
                Field field = null;
                // 循环判断哪些字段有下拉数据集，并获取
                for (int i = 0; i < fields.length; i++) {
                    field = fields[i];
                    // 解析注解信息
                    DropDownSetField dropDownSetField = field.getAnnotation(DropDownSetField.class);
                    if (null != dropDownSetField) {
                        String name = dropDownSetField.name();
                        if (!StringUtils.isEmpty(name)) {
                            //这里简单处理,直接写死了 , 可以从库里查字典值,然后赋值 ,这里name是注解上的key值,匹配
                            if (name.equals(Constants.OLD_CATEGORY)) {  // 老人类别 普通老人 2->孤寡老人 3->低保老人 4->残疾老人 5->空巢老人
                                List<String> list = new ArrayList<>();
                                list.add("普通老人");list.add("孤寡老人");list.add("低保老人");
                                list.add("残疾老人");list.add("空巢老人");
                                String[] params = list.toArray(new String[list.size()]);
                                insertMap(map, params, dropDownSetField, i);
                            }
                            if (name.equals(Constants.SEX)) { //当前栏为 销售方式 栏,处理 销售方式 下拉框
                                List<String> list = new ArrayList<>();
                                list.add("男性");
                                list.add("女性");
                                String[] params = list.toArray(new String[list.size()]);
                                insertMap(map, params, dropDownSetField, i);
                            }
                            if (name.equals(Constants.CHANGE_TYPE)) { //当前栏为 异动状态: 1->正常 2->迁出 3->去世
                                List<String> list = new ArrayList<>();
                                list.add("正常");
                                list.add("迁出");
                                list.add("去世");
                                String[] params = list.toArray(new String[list.size()]);
                                insertMap(map, params, dropDownSetField, i);
                            }
                        }else {
                            insertMap(map,null,dropDownSetField,i);
                        }
    
                    }
                }
                //文件以流形式返回前端下载
                String fileName="lrmb.xlsx";
                OutputStream fileOutputStream = null;
                response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
                response.setContentType("application/x-download");
                response.setCharacterEncoding("UTF-8");
                response.addHeader("Pargam", "no-cache");
                response.addHeader("Cache-Control", "no-cache");
                response.flushBuffer();
                fileOutputStream = response.getOutputStream();
                ExcelWriter excelWriter = EasyExcel.write(fileOutputStream, PersonModel.class)
                        .registerWriteHandler(new ProductCellWriteHandler(map)).build();
                WriteSheet sheet = EasyExcel.writerSheet(0, "老人模板").build();
                excelWriter.write(null, sheet);
                excelWriter.finish();
                fileOutputStream.flush();
                fileOutputStream.close();
            }catch (Exception e){
                e.printStackTrace();
                throw new GlobalException("下载模板失败!");
            }
        }
    
    
    
        //插入到map中
        private void insertMap(Map<Integer, String[]> map, String[] params, DropDownSetField dropDownSetField, int i) {
            String[] sources = ResoveDropAnnotationUtil.resove(dropDownSetField, params);
            if (null != sources && sources.length > 0) {
                map.put(i, sources);
            }
        }

### 6.工具类 ###

public class ResoveDropAnnotationUtil {
    
        private static String productCategoryName = "productCategoryName"; //商品分类名
        private static String salesMode = "salesMode"; //销售方式
        private static String pms_sales_mode = "pms_sales_mode"; //销售方式字典key
        private static String format = "format"; //规格
        private static String supplier = "supplier";  //供应商
        private static String pms_supplier = "pms_supplier"; //供应商字典key
        private static String productUnit = "productUnit"; //商品单位
        private static String pms_product_unit = "pms_product_unit"; //商品单位字典key
        private static String productBrand = "productBrand"; //商品品牌
        private static String pms_product_brand = "pms_product_brand"; //商品品牌字典key
        
    
        public static String[] resove(DropDownSetField dropDownSetField, String[] strings) {
            if (!Optional.ofNullable(dropDownSetField).isPresent()) {
                return null;
            }
    
            // 获取固定下拉信息
            String[] source = dropDownSetField.source();
            if (null != source && source.length > 0) {
                return source;
            }
    
            if (null != strings && strings.length > 0) {
                try {
                    String[] dynamicSource = strings;
                    if (null != dynamicSource && dynamicSource.length > 0) {
                        return dynamicSource;
                    }
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
            return null;
        }
        
    
        //插入到map中
        private void insertMap(Map<Integer, String[]> map, String[] params, DropDownSetField dropDownSetField, int i) {
            String[] sources = ResoveDropAnnotationUtil.resove(dropDownSetField, params);
            if (null != sources && sources.length > 0) {
                map.put(i, sources);
            }
        }
    }

7.监听器

package com.zhzh.utils.execl;
    
    import com.alibaba.excel.write.handler.SheetWriteHandler;
    import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
    import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
    import org.apache.poi.ss.usermodel.DataValidation;
    import org.apache.poi.ss.usermodel.DataValidationConstraint;
    import org.apache.poi.ss.usermodel.DataValidationHelper;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.util.CellRangeAddressList;
    import org.springframework.stereotype.Component;
    
    import java.util.Map;
    
    @Component
    public class ProductCellWriteHandler implements SheetWriteHandler {
    
        private Map<Integer,String[]> map = null;
    
        public ProductCellWriteHandler(Map<Integer,String[]> map){
            this.map = map;
        }
    
        @Override
        public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        }
    
        @Override
        public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
            // 这里可以对cell进行任何操作
            Sheet sheet = writeSheetHolder.getSheet();
            DataValidationHelper helper = sheet.getDataValidationHelper();
            // k 为存在下拉数据集的单元格下表 v为下拉数据集
            map.forEach((k, v) -> {
                // 下拉列表约束数据
                DataValidationConstraint constraint = helper.createExplicitListConstraint(v);
                // 设置下拉单元格的首行 末行 首列 末列
                CellRangeAddressList rangeList = new CellRangeAddressList(1, 65536, k, k);
                // 设置约束
                DataValidation validation = helper.createValidation(constraint, rangeList);
                // 阻止输入非下拉选项的值
                validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
                validation.setShowErrorBox(true);
                validation.setSuppressDropDownArrow(true);
                validation.createErrorBox("提示","此值与单元格定义格式不一致");
                // validation.createPromptBox("填写说明：","填写内容只能为下拉数据集中的单位，其他单位将会导致无法入仓");
                sheet.addValidationData(validation);
            });
        }
    }

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70]: /images/20221119/3e35f41e56314a92bba33005c1ed3c51.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYW5nc2hlbmdxaWFuZzE2OA_size_16_color_FFFFFF_t_70 1]: /images/20221119/dcf503d1841142b9891455e0cdbbc146.png