How to set up Excel data validation in Java

Data validation is a function under the data function group in the Excel 2013 version. In versions before Excel 2013, including Excel 2010 and Excel 2007, it is called data validity. Data input can be effectively standardized by setting data validation in excel tables. When setting the data type, you can set the verification number (number range/number type), date, text length, etc. The following uses Java program code to demonstrate the setting method and results of data verification.

Tool: Free Spire. jar file into the java program; or download and import through maven.

Refer to the following Jar import effect:

Java

Example (for reference)

import com.spire.xls.*;

public class DataValidation {
    public static void main(String[] args) {
        //创建Workbook对象
        Workbook workbook = new Workbook();

        //获取第一个工作表
        Worksheet sheet = workbook.getWorksheets().get(0);

        //在单元格B3中设置数字验证-仅允许输入1到100之间的数
        sheet.getCellRange("B2").setText("请输入1-100之间的数：");
        CellRange rangeNumber = sheet.getCellRange("B3");
        rangeNumber.getDataValidation().setCompareOperator(ValidationComparisonOperator.Between);
        rangeNumber.getDataValidation().setFormula1("1");
        rangeNumber.getDataValidation().setFormula2("100");
        rangeNumber.getDataValidation().setAllowType(CellDataType.Decimal);
        rangeNumber.getDataValidation().setErrorMessage("Please input correct number!");
        rangeNumber.getDataValidation().setShowError(true);
        rangeNumber.getCellStyle().setKnownColor(ExcelColors.Color21);

        //在单元格B6中设置日期验证-仅允许输入1/1/1970到12/31/1970之间的日期
        sheet.getCellRange("B5").setText("请输入1/1/1970-12/31/1970之间的日期：");
        CellRange rangeDate = sheet.getCellRange("B6");
        rangeDate.getDataValidation().setAllowType(CellDataType.Date);
        rangeDate.getDataValidation().setCompareOperator(ValidationComparisonOperator.Between);
        rangeDate.getDataValidation().setFormula1("1/1/1970");
        rangeDate.getDataValidation().setFormula2("12/31/1970");
        rangeDate.getDataValidation().setErrorMessage("Please input correct date!");
        rangeDate.getDataValidation().setShowError(true);
        rangeDate.getDataValidation().setAlertStyle(AlertStyleType.Warning);
        rangeDate.getCellStyle().setKnownColor(ExcelColors.Color16);

        //在单元格B9设置字符长度验证-仅允许输入5个字符以内的文本
        sheet.getCellRange("B8").setText("请输入不超过5个字符的文本：");
        CellRange rangeTextLength = sheet.getCellRange("B9");
        rangeTextLength.getDataValidation().setAllowType(CellDataType.TextLength);
        rangeTextLength.getDataValidation().setCompareOperator(ValidationComparisonOperator.LessOrEqual);
        rangeTextLength.getDataValidation().setFormula1("5");
        rangeTextLength.getDataValidation().setErrorMessage("Enter a Valid String!");
        rangeTextLength.getDataValidation().setShowError(true);
        rangeTextLength.getDataValidation().setAlertStyle(AlertStyleType.Stop);
        rangeTextLength.getCellStyle().setKnownColor(ExcelColors.Color14);

        //在单元格B12设置数字验证-仅允许输入大于等于18的整数
        sheet.getCellRange("B11").setText("请输入大于等于18的整数：");
        CellRange rangeinteger = sheet.getCellRange("B12");
        rangeinteger.getDataValidation().setAllowType(CellDataType.Integer);
        rangeinteger.getDataValidation().setCompareOperator(ValidationComparisonOperator.GreaterOrEqual);
        rangeinteger.getDataValidation().setFormula1("18");
        rangeinteger.getDataValidation().setErrorMessage("Enter a Valid String!");
        rangeinteger.getDataValidation().setShowError(true);
        rangeinteger.getDataValidation().setAlertStyle(AlertStyleType.Stop);
        rangeinteger.getCellStyle().setKnownColor(ExcelColors.LightGreen1);

        //第二列自适应宽度
        sheet.autoFitColumn(2);

        //保存文档
        workbook.saveToFile("DataValidation.xlsx", ExcelVersion.Version2016);
    }
}

Data verification setting effect:

The above is the detailed content of How to set up Excel data validation in Java. For more information, please follow other related articles on the PHP Chinese website!