esayExcel自定义注解导出带下拉框

编程曹贼

已于 2022-09-25 14:07:44 修改

阅读量1.8k

点赞数 2

分类专栏：工具 Java # Java基础文章标签： java spring

于 2022-09-24 19:45:18 首次发布

本文链接：https://blog.csdn.net/lxhJava15/article/details/127029750

版权

工具同时被 3 个专栏收录

7 篇文章 2 订阅

订阅专栏

Java

2 篇文章 0 订阅

订阅专栏

Java基础

2 篇文章 0 订阅

订阅专栏

注解

package com.baidu.activitidemo.annotation;

import com.baidu.activitidemo.handler.ExcelSelectedHandler;

import java.lang.annotation.*;

/**
 * 设置列的下拉选择值, 需要配合{@link ExcelSelectedHandler}使用
 *
 * @author li
 */
@Target(ElementType.FIELD)
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelSelected {

    /**
     * 一般使用此属性的值进行下拉值的填充
     *
     * @return {@link String[]}
     */
    String[] value() default {};

    /**
     * 慎用此属性，建议使用value
     * 此处值应为一个SpEL，返回值需要是一个数组或集合
     * 此表达式若有值(无论是否调用是否成功)，则会忽略value属性
     * 使用示例：#root.getBean('userService')?.getSelectedList('2')
     * 此处的使用示例中的 #root指的是applicationContext
     *
     * @return {@link String}
     */
    String dynamicAccess() default "";

    /**
     * 下拉起始行
     * -1表示表头的下一行，代码自动获取
     *
     * @return int
     */
    int firstRow() default -1;

    /**
     * 下拉结束行
     * -1表示使用默认值1000
     *
     * @return int
     */
    int lastRow() default -1;
}

Handler

package com.baidu.activitidemo.handler;

import com.alibaba.excel.annotation.ExcelIgnore;
import com.alibaba.excel.annotation.ExcelProperty;
import com.alibaba.excel.write.handler.SheetWriteHandler;
import com.alibaba.excel.write.metadata.holder.WriteSheetHolder;
import com.alibaba.excel.write.metadata.holder.WriteWorkbookHolder;
import com.baidu.activitidemo.annotation.ExcelSelected;
import com.baidu.activitidemo.config.SpringContextUtil;
import lombok.Data;
import org.apache.poi.hssf.usermodel.HSSFDataValidation;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.expression.Expression;
import org.springframework.expression.spel.standard.SpelExpressionParser;
import org.springframework.expression.spel.support.StandardEvaluationContext;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;

import java.lang.reflect.Field;
import java.util.Arrays;
import java.util.List;
import java.util.Map;
import java.util.TreeMap;

/**
 * 设置列的下拉选择值, 需要配合注解{@link ExcelSelected}使用
 * eg：EasyExcel.write(fileName, Person.class).registerWriteHandler(new ExcelRemarkHandler(Person.class))
 *
 * @author lxh
 * @date 2022/9/24 8:59
 */
@Data
public class ExcelSelectedHandler<T> implements SheetWriteHandler {
    /** 解析后的下拉数据 */
    private Map<Integer, ExcelSelectedResolve> excelSelectedResolveMap;

    /** spring表达式解析器 */
    private SpelExpressionParser spelExpressionParser;

    /** 标准上下文 */
    private StandardEvaluationContext standardEvaluationContext;

    /** 默认不在新sheet页中设置下拉值 */
    private boolean inNewSheetFlag = false;

    /** 当前最大规格 */
    private Integer currentMaxLength;

    /** 允许最大规格 */
    public Integer allowMaxLength = 200;

    /** 默认下拉的行数:1000 */
    private int defaultRowNumber;

    /** 需要解析的类 */
    private Class<T> clazz;

    /** 字母 */
    private static final char[] alphabet = new char[]{'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L',
            'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'};

    /**
     * excel选择处理程序,默认下拉行数：1000
     *
     * @param clazz clazz
     */
    public ExcelSelectedHandler(Class<T> clazz) {
        this(clazz, 1000);
    }

    public ExcelSelectedHandler(Class<T> clazz, Integer defaultRowNumber) {
        Assert.notNull(defaultRowNumber, "defaultRowNumber must not be null");
        this.clazz = clazz;
        this.defaultRowNumber = defaultRowNumber;
        //解析注解
        this.analysisAnnotation();
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        if (excelSelectedResolveMap == null || excelSelectedResolveMap.isEmpty()) {
            return;
        }

        //获取最多字符的组的总字符数
        if (currentMaxLength == null) {
            currentMaxLength = excelSelectedResolveMap.values()
                    .stream()
                    .map(excelSelectedResolve -> StringUtils.arrayToDelimitedString(excelSelectedResolve.selectedValues, ""))
                    .map(s -> s.length())
                    .max(Integer::compareTo)
                    .orElse(Integer.MAX_VALUE);

        }

        //若总字符数大于最大范围，则强制在新sheet页中设置下拉值
        if (currentMaxLength > allowMaxLength) {
            this.inNewSheetFlag = true;
        }

        if (inNewSheetFlag) {
            //新sheet页中设置下拉值
            this.setSelectedValuesOnNewSheet(writeSheetHolder.getSheet());
        } else {
            //使用最简单当前sheet页设置
            this.setSelectedValues(writeSheetHolder.getSheet());
        }

    }

    /**
     * 设置下拉值
     *
     * @param sheet 表
     */
    private void setSelectedValues(Sheet sheet) {
        //这里可以对cell进行任何操作
        DataValidationHelper helper = sheet.getDataValidationHelper();
        excelSelectedResolveMap.forEach((column, v) -> {
            // 设置下拉列表的行： 首行，末行，首列，末列
            CellRangeAddressList rangeList = new CellRangeAddressList(v.getFirstRow(), v.getLastRow(), column, column);
            // 设置下拉列表的值
            DataValidationConstraint constraint = helper.createExplicitListConstraint(v.getSelectedValues());
            // 设置约束
            this.setConstraint(sheet, helper, rangeList, constraint);
        });
    }

    /**
     * 设置下拉值，在新sheet中
     *
     * @param sheet 表
     */
    private void setSelectedValuesOnNewSheet(Sheet sheet) {
        Workbook workbook = sheet.getWorkbook();
        DataValidationHelper helper = sheet.getDataValidationHelper();
        // 数据字典的sheet页
        long currentTimeMillis = System.currentTimeMillis();
        String dictSheetName = "字典sheet_" + currentTimeMillis;
        Sheet dictSheet = workbook.createSheet(dictSheetName);
        // 设置隐藏
        workbook.setSheetHidden(workbook.getSheetIndex(dictSheet), true);
        excelSelectedResolveMap.forEach((column, v) -> {
            // 设置下拉单元格的首行、末行、首列、末列
            CellRangeAddressList rangeAddressList = new CellRangeAddressList(v.getFirstRow(), v.lastRow, column, column);
            String[] selectedValues = v.getSelectedValues();
            // 设置字典sheet页的值 每一列一个字典项
            for (int i = 0; i < selectedValues.length; i++) {
                Row row = dictSheet.getRow(i);
                if (row == null) {
                    row = dictSheet.createRow(i);
                }
                row.createCell(column).setCellValue(selectedValues[i]);
            }
            String excelColumn = this.getExcelColumn(column);
            // 下拉框数据来源 eg:字典sheet!$B1:$B2
            String refers = dictSheetName + "!$" + excelColumn + "$1:$" + excelColumn + "$" + selectedValues.length;
            // 创建可被其他单元格引用的名称
            Name name = workbook.createName();
            // 设置名称的名字
            String constraintName = "dict_" + currentTimeMillis + "_" + column;
            name.setNameName(constraintName);
            // 设置公式
            name.setRefersToFormula(refers);
            // 设置引用约束
            DataValidationConstraint constraint = helper.createFormulaListConstraint(constraintName);
            this.setConstraint(sheet, helper, rangeAddressList, constraint);

        });
    }

    /**
     * 设置约束
     *
     * @param sheet            表
     * @param helper           助手
     * @param rangeAddressList 范围地址列表
     * @param constraint       约束
     */
    private void setConstraint(Sheet sheet, DataValidationHelper helper, CellRangeAddressList rangeAddressList, DataValidationConstraint constraint) {
        // 设置约束
        DataValidation validation = helper.createValidation(constraint, rangeAddressList);
        if (validation instanceof HSSFDataValidation) {
            validation.setSuppressDropDownArrow(false);
        } else {
            validation.setSuppressDropDownArrow(true);
            validation.setShowErrorBox(true);
        }
        // 阻止输入非下拉框的值
        validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
        validation.createErrorBox("提示", "请输入下拉选项中的内容");
        // 添加下拉框约束
        sheet.addValidationData(validation);
    }


    /**
     * 将数字列转化成为字母列
     *
     * @param num
     * @return
     */
    private String getExcelColumn(int num) {
        String column = "";
        int len = alphabet.length - 1;
        int first = num / len;
        int second = num % len;
        if (num <= len) {
            column = alphabet[num] + "";
        } else {
            column = alphabet[first - 1] + "";
            if (second == 0) {
                column = column + alphabet[len] + "";
            } else {
                column = column + alphabet[second - 1] + "";
            }
        }
        return column;

    }

    /**
     * 解析注释
     *
     * @return {@link List}<{@link ExcelSelectedResolve}>
     */
    private Map<Integer, ExcelSelectedResolve> analysisAnnotation() {
        Map<Integer, ExcelSelectedResolve> map = new TreeMap<>();

        //getDeclaredFields(): 返回全部声明的属性；getFields(): 返回public类型的属性
        Field[] fields = clazz.getDeclaredFields();
        //获取表头最大的行数
        int tableHeadMaxRowNumber = Arrays.stream(fields)
                .map(field -> field.getAnnotation(ExcelProperty.class).value().length)
                .max(Integer::compareTo)
                .orElse(Integer.valueOf("0"));

        //解析字段的注解信息
        for (int i = 0; i < fields.length; i++) {
            Field field = fields[i];
            //判断是否跳过
            ExcelIgnore ignore = field.getAnnotation(ExcelIgnore.class);
            if (ignore != null) {
                continue;
            }

            //解析注解信息
            ExcelSelected selected = field.getAnnotation(ExcelSelected.class);
            if (selected != null) {
                ExcelSelectedResolve resolve = new ExcelSelectedResolve();
                String dynamicAccess = selected.dynamicAccess();

                if (dynamicAccess == null || dynamicAccess.isEmpty()) {
                    //直接获取注解中的值
                    resolve.setSelectedValues(selected.value());
                } else {
                    //调用SpEL解析器
                    resolve.setSelectedValues(this.getSelectedValuesFormApplicationContext(dynamicAccess));
                }
                if (resolve.getSelectedValues() == null || resolve.getSelectedValues().length <= 0) {
                    continue;
                }

                //解析注解信息
                ExcelProperty property = field.getAnnotation(ExcelProperty.class);
                if (property != null && property.index() >= 0) {
                    resolve.setColumn(property.index());
                } else {
                    resolve.setColumn(i);
                }

                //设置起始行,若都没执行，***则使用int初始化的默认值0***
                if (selected.firstRow() != -1) {
                    resolve.setFirstRow(selected.firstRow());
                } else if (property != null) {
                    int currentRowNumber = property.value().length;
                    //length属性为元素个数,此处需转换算成index
                    int rowNumberResult = (currentRowNumber >= tableHeadMaxRowNumber ? currentRowNumber : tableHeadMaxRowNumber) - 1;
                    resolve.setFirstRow(rowNumberResult);
                }

                //若自定义了结束行,则使用自定义的值
                if (selected.lastRow() != -1) {
                    resolve.setLastRow(selected.lastRow());
                } else {
                    //否则:使用将当前下拉框的首行+默认值(默认1000,可通过构造函数进行设置)
                    resolve.setLastRow(defaultRowNumber + resolve.firstRow);
                }

                //将参数放入集合
                map.put(resolve.getColumn(), resolve);
            }
        }

        this.excelSelectedResolveMap = map;

        return map;

    }

    /**
     * 调用上下文中的方法，动态获取下拉值
     *
     * @param dynamicAccess 动态访问
     * @return {@link String[]}
     */
    private String[] getSelectedValuesFormApplicationContext(String dynamicAccess) {
        if (spelExpressionParser == null) {
            this.spelExpressionParser = new SpelExpressionParser();
        }

        if (standardEvaluationContext == null) {
            standardEvaluationContext = new StandardEvaluationContext(SpringContextUtil.getApplicationContext());
        }

        Expression expression = spelExpressionParser.parseExpression(dynamicAccess);
        Object value = expression.getValue(standardEvaluationContext);
        if (value instanceof String[]) {
            return ((String[]) value);
        }
        if (value instanceof List) {
            List<Object> valueList = (List) value;
            String[] values = valueList.stream().map(Object::toString).toArray(String[]::new);
            return values;
        }

        return null;
    }

    @Data
    private class ExcelSelectedResolve {

        /** 列号 */
        private Integer column;

        /** 选择值 */
        private String[] selectedValues;

        /**
         * 下拉起始行
         */
        private int firstRow;

        /**
         * 下拉结束行
         */
        private int lastRow;
    }
}

spring上下文工具

package com.baidu.activitidemo.config;

import org.springframework.beans.BeansException;
import org.springframework.context.ApplicationContext;
import org.springframework.context.ApplicationContextAware;
import org.springframework.stereotype.Component;

/**
 * spring上下文工具
 * @author lxh
 * @date 2022/9/24 13:17
 */

@Component
public class SpringContextUtil implements ApplicationContextAware {
    private static ApplicationContext applicationContext;

    @Override
    public void setApplicationContext(ApplicationContext applicationContext) throws BeansException {
        SpringContextUtil.applicationContext = applicationContext;
    }


    /**
     * 获取ApplicationContext
     *
     * @return {@link ApplicationContext}
     */
    public static ApplicationContext getApplicationContext() {
        return applicationContext;
    }

    /**
     * 通过class获取Bean
     *
     * @param clazz clazz
     * @return {@link T}
     */
    public static <T> T getBean(Class<T> clazz) {
        return applicationContext.getBean(clazz);
    }


    /**
     * 通过name以及class获取Bean
     *
     * @param name  名字
     * @param clazz clazz
     * @return {@link T}
     */
    public static <T> T getBean(String name, Class<T> clazz) {
        return applicationContext.getBean(name, clazz);
    }

}

使用示例

package com.baidu.activitidemo.entity;

import com.alibaba.excel.annotation.ExcelProperty;
import com.baidu.activitidemo.annotation.ExcelSelected;
import lombok.Data;

import java.time.LocalDateTime;

/**
 * @author lxh
 * @date 2022/7/24 10:32
 */
@Data
public class Person {
    @ExcelProperty("用户名")
    private String userName;

    @ExcelProperty(value = "年龄")
    private String userAge;

    @ExcelSelected(value = {"code_001", "code_002"})
    @ExcelProperty(value = "编号")
    private String code;

    @ExcelProperty("创建时间")
    private LocalDateTime createTime;
}

@Test
void contextLoads() {
    List<Person> list = new ArrayList<>();
    list.add(new Person());
    String fileName = "D:\\temp\\" + System.currentTimeMillis() + ".xlsx";

    EasyExcel.write(fileName, Person.class)
            .registerWriteHandler(new ExcelSelectedHandler(Person.class))
            .sheet("模板")
            .doWrite(list);

}

大部分从网上搬的

主要来源：

Easyexcel生成excel并通过自定义注解实现下拉框以及动态下拉框(将数据库中的数据显示在excel下拉框中)EasyExcel在项目中的应用-在web中导出带下拉框和批注的excel文件