EasyExcel导出文件添加单元格输入约束

应用场景:账单在导入已收的时候,收款方式需要固定,这时就需要定义一个单元格下拉选,且只能选择下拉框中指定的值。
导出效果


限制效果:

实现方式:

实现SheetWriteHandler过滤器

SheetWriteHandler过滤器(处理器)的作用就是可以让我们自定义单元格的样式,我们常用的是其中的afterSheetCreate()方法,在该方法中,我们可以对当前所在的单元格进行样式的设置。

public class ProfessionSheetWriteHandler implements SheetWriteHandler {
    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
    }
}

其中关键在于DataValidationHelper 实例,中文意思为 验证助手

DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();

DataValidationHelper 是一个接口,其中定义了各种验证接口

public interface DataValidationHelper {
    // 创建公式列表约束
    DataValidationConstraint createFormulaListConstraint(String var1);
    // 创建显式列表约束
    DataValidationConstraint createExplicitListConstraint(String[] var1);
    // 创建数值约束
    DataValidationConstraint createNumericConstraint(int var1, int var2, String var3, String var4);
    // 创建文本长度约束
    DataValidationConstraint createTextLengthConstraint(int var1, String var2, String var3);
    // 创建十进制约束
    DataValidationConstraint createDecimalConstraint(int var1, String var2, String var3);
    // 创建整数制约束
    DataValidationConstraint createIntegerConstraint(int var1, String var2, String var3);
    // 创建日期制约束
    DataValidationConstraint createDateConstraint(int var1, String var2, String var3, String var4);
    // 创建时间约束
    DataValidationConstraint createTimeConstraint(int var1, String var2, String var3);
    // 创建自定义约束
    DataValidationConstraint createCustomConstraint(String var1);
    //  创建验证
    DataValidation createValidation(DataValidationConstraint var1, CellRangeAddressList var2);
}

定义约束下拉框,且只能是下拉跨所拥有的值

public class BillSheetWriteHandler implements SheetWriteHandler {
    /**
     * v1 - v2 前两个值为几行到几行  v3 -v4 后两个值为几列到几列
     */
    private CellRangeAddressList cellRangeAddressList;
    /**
     * 下拉表的值
     */
    private String[] value;
    /**
     * 是否设置下拉选
     */
    private Boolean state;

    public BillSheetWriteHandler(CellRangeAddressList cellRangeAddressList, String[] value, Boolean state) {
        this.cellRangeAddressList = cellRangeAddressList;
        this.value = value;
        this.state = state;
    }

    @Override
    public void beforeSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {

    }

    /**
     * 定义下拉框
     */
    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Sheet sheet = writeSheetHolder.getSheet();
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        workbook.createCellStyle().setLocked(true);
        if (state) {
            DataValidationHelper helper = sheet.getDataValidationHelper();
            if (value.length == 0) {
                throw new ServiceWarnException(BillExcelConstant.DROP_DOWN_ARRAY);
            }
            DataValidationConstraint constraint = helper.createExplicitListConstraint(value);
            // 指定行列约束
            DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
            // 阻止输入非下拉选项的值
            dataValidation.setErrorStyle(DataValidation.ErrorStyle.STOP);
            dataValidation.setShowErrorBox(true);
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.createErrorBox(BillExcelConstant.CREATE_ERROR_BOX_HINT, BillExcelConstant.CREATE_ERROR_BOX_INCONSISTENT);
            sheet.addValidationData(dataValidation);
        }
        // 目前不生效
        sheet.createFreezePane(0, 1, 0, 1);
        // 默认筛选条件
        sheet.setAutoFilter(CellRangeAddress.valueOf("1:1"));

    }
}

使限制生效:

// 声明单元格出现列及行
CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
WriteSheet mainSheet = EasyExcel.writerSheet(i, sheetName.get(i)).head(classType).registerWriteHandler(new BillSheetWriteHandler(cellRangeAddressList, value, state)).build();

阿拉伯数字核心验证器:
定义整数约束,且在指定范围内

// 创建数值约束
DataValidationConstraint integerConstraint = helper.createNumericConstraint(DataValidationConstraint.ValidationType.INTEGER, DataValidationConstraint.OperatorType.BETWEEN, "1", "9999999");
// 创建验证
DataValidation validation = helper.createValidation(integerConstraint, HeaderVerify.getSkillCellRangeAddressList());
// 阻止输入非下拉选项的值
validation.setErrorStyle(DataValidation.ErrorStyle.STOP);
validation.setShowErrorBox(true);
validation.setSuppressDropDownArrow(true);
validation.createErrorBox("提示", "必须输入数字,且上下限为1~999999");

任何方法中涉及validationType和OperatorType参数时,需要根据DataValidationConstraint.ValidationType|DataValidationConstraint.OperatorType这两个接口定义的规则常量值来设置

源码如下:

public static final class ValidationType {
    public static final int ANY = 0;
    public static final int INTEGER = 1;
    public static final int DECIMAL = 2;
    public static final int LIST = 3;
    public static final int DATE = 4;
    public static final int TIME = 5;
    public static final int TEXT_LENGTH = 6;
    public static final int FORMULA = 7;

    private ValidationType() {
    }
}

    public static final class OperatorType {
        public static final int BETWEEN = 0;
        public static final int NOT_BETWEEN = 1;
        public static final int EQUAL = 2;
        public static final int NOT_EQUAL = 3;
        public static final int GREATER_THAN = 4;
        public static final int LESS_THAN = 5;
        public static final int GREATER_OR_EQUAL = 6;
        public static final int LESS_OR_EQUAL = 7;
        public static final int IGNORED = 0;

        private OperatorType() {
        }

        public static void validateSecondArg(int comparisonOperator, String paramValue) {
            switch (comparisonOperator) {
                case 0:
                case 1:
                    if (paramValue == null) {
                        throw new IllegalArgumentException("expr2 must be supplied for 'between' comparisons");
                    }
                default:
            }
        }
    }

另外创建日期约束时需要使用 'Date(1999, 1, 1)' 公式

DataValidationConstraint dateConstraint = helper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN,"Date(1999, 1, 1)","Date(2023, 12, 1)","yyyy-MM-dd");
暂无评论

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇