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

首先需要创建一个类来实现 SheetWriteHandler,重写beforeSheetCreateafterSheetCreate方法,约束校验主要在 afterSheetCreate 方法中实现

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

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

1、获得 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);
}

2、例1:定义约束下拉框,且只能是下拉跨所拥有的值

// 创建显式列表约束
DataValidationConstraint validationConstraint = helper.createExplicitListConstraint(HeaderVerify.getContributionProfession());
// 创建验证
DataValidation var1 = helper.createValidation(validationConstraint, HeaderVerify.getContributionCellRangeAddressList2());
// 阻止输入非下拉选项的值
var1.setErrorStyle(DataValidation.ErrorStyle.STOP);
var1.setShowErrorBox(true);
var1.setSuppressDropDownArrow(true);
var1.createErrorBox("提示", "此值与单元格定义格式不一致");

3、例2:定义整数约束,且在指定范围内

// 创建数值约束
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");

4、最后需要添加创建的验证对象

writeSheetHolder.getSheet().addValidationData(dataValidation);
暂无评论

发送评论 编辑评论


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