java excel 设置数据验证-k8凯发天生赢家

本文介绍如何使用spire.xls for java在excel单元格中设置数据验证。

import com.spire.xls.*;
public class applydatavalidation {
    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.gray25percent);
        //在单元格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.gray25percent);
        //在单元格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.gray25percent);
        //第二列自适应宽度
        sheet.autofitcolumn(2);
        //保存文档
        workbook.savetofile("output/datavalidation.xlsx", excelversion.version2016);
    }
}