C#/VB.NET 设置 Excel 数据验证/数据有效性

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";

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);

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)