| 
| 
spire.xls 支持的数据验证类型：
- 数字验证
- 序列验证
- 文本长度验证
- 时间验证
- 日期验证
- 自定义验证
数字验证
validation numbervalidation = sheet.range["c1"].datavalidation;
//整数验证
numbervalidation.allowtype = celldatatype.integer;
//小数验证
//numbervalidation.allowtype = celldatatype.decimal;
//限制输入1-10之间的整数
numbervalidation.compareoperator = validationcomparisonoperator.between;
numbervalidation.formula1 = "1";
numbervalidation.formula2 = "10";
序列验证
validation listvalidation = sheet.range["c2"].datavalidation;
listvalidation.values = new string[] { "销售", "人力资源", "研发", "财务" };
文本长度验证
validation txtlengthvalidation = sheet.range["c3"].datavalidation;
txtlengthvalidation.allowtype = celldatatype.textlength;
txtlengthvalidation.compareoperator = validationcomparisonoperator.between;
//限制输入的文本长度在1-5之间
txtlengthvalidation.formula1 = "1";
txtlengthvalidation.formula2 = "5";
时间验证
validation timevalidation = sheet.range["c4"].datavalidation;
timevalidation.allowtype = celldatatype.time;
timevalidation.compareoperator = validationcomparisonoperator.between;
//限制输入时间在00.00到24.00之间
timevalidation.formula1 = "00.00";
timevalidation.formula2 = "24.00";
日期验证
validation datevalidation = sheet.range["c5"].datavalidation;
datevalidation.allowtype = celldatatype.date;
datevalidation.compareoperator = validationcomparisonoperator.between;
//限制输入日期在2016/5/10到2017/5/10之间
datevalidation.datetime1 = new datetime(2016, 5, 10);
datevalidation.datetime2 = new datetime(2017, 5, 10);
自定义验证
validation customvalidation = sheet.range["c6"].datavalidation;
customvalidation.allowtype = celldatatype.user;
customvalidation.formula1 = "=a1>10";
完整代码
c#
workbook workbook = new workbook();
worksheet sheet = workbook.worksheets[0];
//数字验证
validation numbervalidation = sheet.range["c1"].datavalidation;
//整数验证
numbervalidation.allowtype = celldatatype.integer;
//小数验证
//numbervalidation.allowtype = celldatatype.decimal;
numbervalidation.compareoperator = validationcomparisonoperator.between;
numbervalidation.formula1 = "1";
numbervalidation.formula2 = "10";
//设置错误提示信息
numbervalidation.alertstyle = alertstyletype.stop;
numbervalidation.showerror = true;
numbervalidation.errortitle = "error";
numbervalidation.errormessage = "请输入1-10之间的整数";
//序列验证
validation listvalidation = sheet.range["c2"].datavalidation;
listvalidation.values = new string[] { "销售", "人力资源", "研发", "财务" };
listvalidation.issuppressdropdownarrow = false;
//设置错误提示信息
listvalidation.alertstyle = alertstyletype.stop;
listvalidation.showerror = true;
listvalidation.errortitle = "error";
listvalidation.errormessage = "请从序列中选择一个项目";
//文本长度验证
validation txtlengthvalidation = sheet.range["c3"].datavalidation;
txtlengthvalidation.allowtype = celldatatype.textlength;
txtlengthvalidation.compareoperator = validationcomparisonoperator.between;
txtlengthvalidation.formula1 = "1";
txtlengthvalidation.formula2 = "5";
//设置错误提示信息
txtlengthvalidation.alertstyle = alertstyletype.stop;
txtlengthvalidation.showerror = true;
txtlengthvalidation.errortitle = "error";
txtlengthvalidation.errormessage = "输入的文本长度应该在1-5之间";
//时间验证
validation timevalidation = sheet.range["c4"].datavalidation;
timevalidation.allowtype = celldatatype.time;
timevalidation.compareoperator = validationcomparisonoperator.between;
timevalidation.formula1 = "00.00";
timevalidation.formula2 = "24.00";
//设置错误提示信息
timevalidation.alertstyle = alertstyletype.stop;
timevalidation.showerror = true;
timevalidation.errortitle = "error";
timevalidation.errormessage = "输入的时间应该在00.00到24.00之间";
//日期验证
validation datevalidation = sheet.range["c5"].datavalidation;
datevalidation.allowtype = celldatatype.date;
datevalidation.compareoperator = validationcomparisonoperator.between;
datevalidation.datetime1 = new datetime(2016, 5, 10);
datevalidation.datetime2 = new datetime(2017, 5, 10);
//设置错误提示信息
datevalidation.alertstyle = alertstyletype.stop;
datevalidation.showerror = true;
datevalidation.errortitle = "error";
datevalidation.errormessage = "输入的日期应该在2016/5/10到2017/5/10之间";
//自定义验证
validation customvalidation = sheet.range["c6"].datavalidation;
customvalidation.allowtype = celldatatype.user;
customvalidation.formula1 = "=a1>10";
//设置错误提示信息
customvalidation.alertstyle = alertstyletype.stop;
customvalidation.showerror = true;
customvalidation.errortitle = "error";
customvalidation.errormessage = "无法输入！a1的数据小于10";
//保存文档
workbook.savetofile("output.xlsx",fileformat.version2013);
vb.net
dim workbook as new workbook()
dim sheet as worksheet = workbook.worksheets(0)
'数字验证
dim numbervalidation as validation = sheet.range("c1").datavalidation
'整数验证
numbervalidation.allowtype = celldatatype.[integer]
'小数验证
'numbervalidation.allowtype = celldatatype.decimal;
numbervalidation.compareoperator = validationcomparisonoperator.between
numbervalidation.formula1 = "1"
numbervalidation.formula2 = "10"
'设置错误提示信息
numbervalidation.alertstyle = alertstyletype.[stop]
numbervalidation.showerror = true
numbervalidation.errortitle = "error"
numbervalidation.errormessage = "请输入1-10之间的整数"
'序列验证
dim listvalidation as validation = sheet.range("c2").datavalidation
listvalidation.values = new string() {"销售", "人力资源", "研发", "财务"}
listvalidation.issuppressdropdownarrow = false
'设置错误提示信息
listvalidation.alertstyle = alertstyletype.[stop]
listvalidation.showerror = true
listvalidation.errortitle = "error"
listvalidation.errormessage = "请从序列中选择一个项目"
'文本长度验证
dim txtlengthvalidation as validation = sheet.range("c3").datavalidation
txtlengthvalidation.allowtype = celldatatype.textlength
txtlengthvalidation.compareoperator = validationcomparisonoperator.between
txtlengthvalidation.formula1 = "1"
txtlengthvalidation.formula2 = "5"
'设置错误提示信息
txtlengthvalidation.alertstyle = alertstyletype.[stop]
txtlengthvalidation.showerror = true
txtlengthvalidation.errortitle = "error"
txtlengthvalidation.errormessage = "输入的文本长度应该在1-5之间"
'时间验证
dim timevalidation as validation = sheet.range("c4").datavalidation
timevalidation.allowtype = celldatatype.time
timevalidation.compareoperator = validationcomparisonoperator.between
timevalidation.formula1 = "00.00"
timevalidation.formula2 = "24.00"
'设置错误提示信息
timevalidation.alertstyle = alertstyletype.[stop]
timevalidation.showerror = true
timevalidation.errortitle = "error"
timevalidation.errormessage = "输入的时间应该在00.00到24.00之间"
'日期验证
dim datevalidation as validation = sheet.range("c5").datavalidation
datevalidation.allowtype = celldatatype.[date]
datevalidation.compareoperator = validationcomparisonoperator.between
datevalidation.datetime1 = new datetime(2016, 5, 10)
datevalidation.datetime2 = new datetime(2017, 5, 10)
'设置错误提示信息
datevalidation.alertstyle = alertstyletype.[stop]
datevalidation.showerror = true
datevalidation.errortitle = "error"
datevalidation.errormessage = "输入的日期应该在2016/5/10到2017/5/10之间"
'自定义验证
dim customvalidation as validation = sheet.range("c6").datavalidation
customvalidation.allowtype = celldatatype.user
customvalidation.formula1 = "=a1>10"
'设置错误提示信息
customvalidation.alertstyle = alertstyletype.[stop]
customvalidation.showerror = true
customvalidation.errortitle = "error"
customvalidation.errormessage = "无法输入！a1的数据小于10"
'保存文档
workbook.savetofile("output.xlsx", fileformat.version2013)
