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

1.需要效果.

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注释 和自定义注释

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