Previous topicNext topic
Help > 开发指南 > Excel > API > 主要对象 >
数据验证

数据验证可确保单元格仅接受有效数据。当用户输入无效数据时，可能会显示一条消息，说明值无效的原因和/或如何更正错误。

DataValidation 接口包含数据验证规则信息 - 规则生效的范围、ValidationType 以及验证数据时可以使用的其他选项。

DataValidationCollection 集合包含工作表中的所有数据验证规则。通过 Worksheet.DataValidations 属性访问集合。

添加新的数据验证规则

使用 DataValidationCollection.Add 方法添加新的数据验证规则。

下表列出了可用于指定验证类型的 API。

API 成员 任务
DataValidationType.List 将数据限制为列表中的预定义项。
DataValidationType.WholeNumber
DataValidationType.Decimal
DataValidationOperator.Between		 限制指定范围之外的号码。
DataValidationType.Time
DataValidationOperator.LessThanOrEqual		 将日期和时间限制在特定时间范围之外。
DataValidationType.TextLength
DataValidationOperator.Equal		 限制文本字符数。
DataValidationType.Custom 根据其他单元格中的公式或值验证数据。
InputTitle
InputMessage
ShowInputMessage		 创建一条消息，说明可以在单元格中输入哪些数据。
ErrorTitle
ErrorMessage
ErrorStyle
ShowErrorMessage		 创建一条消息，当用户键入不正确的数据时显示该消息。
DataValidationCollection.Validate 检查单元格是否包含有效数据。
DataValidationCollection.GetInvalidCells 检索不符合数据验证条件的单元格。

下面的代码片段演示了如何创建和应用不同的验证条件：

注意

用于指定数据验证类型的列表的最大长度。列表验证类型或自定义验证条件为 255 个字符。当列表或条件超过此值时， 将引发无效操作异常。

Vb.Net
Dim workbook As New DevExpress.Spreadsheet.Workbook()
workbook.LoadDocument("D:\DataValidation.xlsx")
Dim worksheet As DevExpress.Spreadsheet.Worksheet = workbook.Worksheets(0)
worksheet("C1").SetValue(Date.Now)
worksheet("C1").NumberFormat = "mmm/d/yyyy h:mm"

' 将数据输入限制为10到20之间的整数。
worksheet.DataValidations.Add(worksheet("B1"), DevExpress.Spreadsheet.DataValidationType.WholeNumber, DevExpress.Spreadsheet.DataValidationOperator.Between, 10, 20)

' 将数据输入限制为限制范围内的数字。
Dim validation As DevExpress.Spreadsheet.DataValidation = worksheet.DataValidations.Add(worksheet("F4:F11"), DevExpress.Spreadsheet.DataValidationType.Decimal, DevExpress.Spreadsheet.DataValidationOperator.Between, 10, 40)

'使用工作表公式计算的条件限制数据输入。
worksheet.DataValidations.Add(worksheet("B4:B11"), DevExpress.Spreadsheet.DataValidationType.Custom, "=AND(ISNUMBER(B4),LEN(B4)=5)")

'将数据输入限制为3个符号。
worksheet.DataValidations.Add(worksheet("D4:D11"), DevExpress.Spreadsheet.DataValidationType.TextLength, DevExpress.Spreadsheet.DataValidationOperator.Equal, 3)

'将数据输入限制为代码中指定的下拉列表中的值。
'请注意，代码中的列表应始终使用逗号分隔条目，
'但UI中的列表是使用区域性特定的列表分隔符显示的。
worksheet.DataValidations.Add(worksheet("A4:A11"), DevExpress.Spreadsheet.DataValidationType.List, "PASS, FAIL")

'将数据输入限制为从工作表中获取的下拉列表中的值。
worksheet.DataValidations.Add(worksheet("E4:E11"), DevExpress.Spreadsheet.DataValidationType.List, DevExpress.Spreadsheet.ValueObject.FromRange(worksheet("H4:H9").GetRangeWithAbsoluteReference()))

'将数据输入限制在指定时间之前。
worksheet.DataValidations.Add(worksheet("C1"), DevExpress.Spreadsheet.DataValidationType.Time, DevExpress.Spreadsheet.DataValidationOperator.LessThanOrEqual, Date.Now)

'突出显示数据验证范围。
worksheet("H4:H9").FillColor = Color.LightGray
Dim MyColorScheme() As Integer = { &HFFC4C4, &HFFD9D9, &HFFF6F6, &HFFECEC, &HE9D3D3, &HFFDFC4, &HFFDAE9}
For i As Integer = 0 To worksheet.DataValidations.Count - 1
    worksheet.DataValidations(i).Range.FillColor = Color.FromArgb(MyColorScheme(i))
Next i

C# 
DevExpress.Spreadsheet.Workbook workbook = new DevExpress.Spreadsheet.Workbook();
workbook.LoadDocument(@"D:\DataValidation.xlsx");
DevExpress.Spreadsheet.Worksheet worksheet = workbook.Worksheets[0];
worksheet["C1"].SetValue(DateTime.Now);
worksheet["C1"].NumberFormat = "mmm/d/yyyy h:mm";

// 将数据输入限制为10到20之间的整数。
worksheet.DataValidations.Add(worksheet["B1"], DevExpress.Spreadsheet.DataValidationType.WholeNumber, DevExpress.Spreadsheet.DataValidationOperator.Between, 10, 20);

// 将数据输入限制为限制范围内的数字。
DevExpress.Spreadsheet.DataValidation validation = worksheet.DataValidations.Add(worksheet["F4:F11"], DevExpress.Spreadsheet.DataValidationType.Decimal, DevExpress.Spreadsheet.DataValidationOperator.Between, 10, 40);

// 使用工作表公式计算的条件限制数据输入。
worksheet.DataValidations.Add(worksheet["B4:B11"], DevExpress.Spreadsheet.DataValidationType.Custom, "=AND(ISNUMBER(B4),LEN(B4)=5)");

// 将数据输入限制为3个符号。
worksheet.DataValidations.Add(worksheet["D4:D11"], DevExpress.Spreadsheet.DataValidationType.TextLength, DevExpress.Spreadsheet.DataValidationOperator.Equal, 3);

// 将数据输入限制为代码中指定的下拉列表中的值。
// 请注意，代码中的列表应始终使用逗号分隔条目，
// 但UI中的列表是使用区域性特定的列表分隔符显示的。
worksheet.DataValidations.Add(worksheet["A4:A11"], DevExpress.Spreadsheet.DataValidationType.List, "PASS, FAIL");

// 将数据输入限制为从工作表中获取的下拉列表中的值。
worksheet.DataValidations.Add(worksheet["E4:E11"], DevExpress.Spreadsheet.DataValidationType.List, DevExpress.Spreadsheet.ValueObject.FromRange(worksheet["H4:H9"].GetRangeWithAbsoluteReference()));

// 将数据输入限制在指定时间之前。
worksheet.DataValidations.Add(worksheet["C1"], DevExpress.Spreadsheet.DataValidationType.Time, DevExpress.Spreadsheet.DataValidationOperator.LessThanOrEqual, DateTime.Now);

// 突出显示数据验证范围。
worksheet["H4:H9"].FillColor = Color.LightGray;
int[] MyColorScheme = new[] { 0xFFC4C4, 0xFFD9D9, 0xFFF6F6, 0xFFECE, 0xE9D3D3, 0xFFDFC4, 0xFFDAE9 };
for (int i = 0; i <= worksheet.DataValidations.Count - 1; i++)
    worksheet.DataValidations[i].Range.FillColor = Color.FromArgb(MyColorScheme[i]);


获取数据验证规则

使用 DataValidationCollection.GetDataValidation 方法检索指定单元格的数据验证规则。DataValidationCollection.GetDataValidations 方法获取指定范围内或满足特定条件的单元格的数据验证条目。

Vb.Net
Dim workbook As New DevExpress.Spreadsheet.Workbook()
workbook.LoadDocument("D:\DataValidation.xlsx")
Dim worksheet As DevExpress.Spreadsheet.Worksheet = workbook.Worksheets(0)
'添加数据验证。
worksheet.DataValidations.Add(worksheet("D4:D11"), DevExpress.Spreadsheet.DataValidationType.TextLength, DevExpress.Spreadsheet.DataValidationOperator.Equal, 3)
worksheet.DataValidations.Add(worksheet("E4:E11"), DevExpress.Spreadsheet.DataValidationType.List, ValueObject.FromRange(worksheet("H4:H9").GetRangeWithAbsoluteReference()))

'获取与特定单元格关联的数据验证条目。
worksheet.DataValidations.GetDataValidation(worksheet.Cells("E4")).Criteria = ValueObject.FromRange(worksheet("H4:H5"))

'获取指定范围的数据验证项。
Dim myValidation = worksheet.DataValidations.GetDataValidations(worksheet("D4:E11")).Where(Function(d) d.ValidationType = DataValidationType.TextLength).SingleOrDefault()
If myValidation IsNot Nothing Then
    myValidation.Criteria = 4
End If

'获取符合特定条件的数据验证条目。
For Each d In worksheet.DataValidations.GetDataValidations(DevExpress.Spreadsheet.DataValidationType.TextLength, DevExpress.Spreadsheet.DataValidationOperator.Equal, 4, DevExpress.Spreadsheet.ValueObject.Empty)
    '更改条件运算符。
    '范围D4:D11应包含超过4个字符的文本。
    d.Operator = DevExpress.Spreadsheet.DataValidationOperator.GreaterThan
Next d

'突出显示数据验证范围。
Dim MyColorScheme() As Integer = { &HFFC4C4, &HFFD9D9, &HFFF6F6, &HFFECEC, &HE9D3D3 }
For i As Integer = 0 To worksheet.DataValidations.Count - 1
    worksheet.DataValidations(i).Range.FillColor = Color.FromArgb(MyColorScheme(i))
Next i

C# 
DevExpress.Spreadsheet.Workbook workbook = new DevExpress.Spreadsheet.Workbook();
workbook.LoadDocument(@"D:\DataValidation.xlsx");
DevExpress.Spreadsheet.Worksheet worksheet = workbook.Worksheets[0];
// 添加数据验证。
worksheet.DataValidations.Add(worksheet["D4:D11"], DevExpress.Spreadsheet.DataValidationType.TextLength, DevExpress.Spreadsheet.DataValidationOperator.Equal, 3);
worksheet.DataValidations.Add(worksheet["E4:E11"], DevExpress.Spreadsheet.DataValidationType.List, ValueObject.FromRange(worksheet["H4:H9"].GetRangeWithAbsoluteReference()));

// 获取与特定单元格关联的数据验证条目。
worksheet.DataValidations.GetDataValidation(worksheet.Cells["E4"]).Criteria = ValueObject.FromRange(worksheet["H4:H5"]);

// 获取指定范围的数据验证项。
var myValidation = worksheet.DataValidations.GetDataValidations(worksheet["D4:E11"]).Where(d => d.ValidationType == DataValidationType.TextLength).SingleOrDefault();
if (myValidation != null)
    myValidation.Criteria = 4;

// 获取符合特定条件的数据验证条目。
foreach (var d in worksheet.DataValidations.GetDataValidations(DevExpress.Spreadsheet.DataValidationType.TextLength, DevExpress.Spreadsheet.DataValidationOperator.Equal, 4, DevExpress.Spreadsheet.ValueObject.Empty))
    // 更改条件运算符。
    // 范围D4:D11应包含超过4个字符的文本。
    d.Operator = DevExpress.Spreadsheet.DataValidationOperator.GreaterThan;

// 突出显示数据验证范围。
int[] MyColorScheme = new[] { 0xFFC4C4, 0xFFD9D9, 0xFFF6F6, 0xFFECE, 0xE9D3D3 };
for (int i = 0; i <= worksheet.DataValidations.Count - 1; i++)
    worksheet.DataValidations[i].Range.FillColor = Color.FromArgb(MyColorScheme[i]);


删除验证规则

若要取消特定规则，请将其从集合中删除。若要删除所有数据验证规则，请清除集合。

Vb.Net
Dim workbook As New DevExpress.Spreadsheet.Workbook()
workbook.LoadDocument("D:\DataValidation.xlsx")
Dim worksheet As DevExpress.Spreadsheet.Worksheet = workbook.Worksheets(0)
'添加数据验证。
worksheet.DataValidations.Add(worksheet("D4:D11"), DevExpress.Spreadsheet.DataValidationType.TextLength, DevExpress.Spreadsheet.DataValidationOperator.Equal, 3)
worksheet.DataValidations.Add(worksheet("E4:E11"), DevExpress.Spreadsheet.DataValidationType.List, ValueObject.FromRange(worksheet("H4:H9").GetRangeWithAbsoluteReference()))

'按索引删除数据验证。
worksheet.DataValidations.RemoveAt(1)

'突出显示数据验证范围。
Dim MyColorScheme() As Integer = { &HFFC4C4, &HFFD9D9, &HFFF6F6, &HFFECEC, &HE9D3D3 }
For i As Integer = 0 To worksheet.DataValidations.Count - 1
    worksheet.DataValidations(i).Range.FillColor = Color.FromArgb(MyColorScheme(i))
Next i

C# 
DevExpress.Spreadsheet.Workbook workbook = new DevExpress.Spreadsheet.Workbook();
workbook.LoadDocument(@"D:\DataValidation.xlsx");
DevExpress.Spreadsheet.Worksheet worksheet = workbook.Worksheets[0];
// 添加数据验证。
worksheet.DataValidations.Add(worksheet["D4:D11"], DevExpress.Spreadsheet.DataValidationType.TextLength, DevExpress.Spreadsheet.DataValidationOperator.Equal, 3);
worksheet.DataValidations.Add(worksheet["E4:E11"], DevExpress.Spreadsheet.DataValidationType.List, ValueObject.FromRange(worksheet["H4:H9"].GetRangeWithAbsoluteReference()));

//按索引删除数据验证。
worksheet.DataValidations.RemoveAt[1];

// 突出显示数据验证范围。
int[] MyColorScheme = new[] { 0xFFC4C4, 0xFFD9D9, 0xFFF6F6, 0xFFECE, 0xE9D3D3 };
for (int i = 0; i <= worksheet.DataValidations.Count - 1; i++)
    worksheet.DataValidations[i].Range.FillColor = Color.FromArgb(MyColorScheme[i]);


从数据验证中排除单元格区域

访问规则生效的范围，调用 CellRange.Exclude 方法，并将要排除的范围作为参数，然后将使用 Exclude 方法返回的范围重新分配给 Range 属性。

Vb.Net
Dim workbook As New DevExpress.Spreadsheet.Workbook()
workbook.LoadDocument("D:\DataValidation.xlsx")
Dim worksheet As DevExpress.Spreadsheet.Worksheet = workbook.Worksheets(0)

Dim validations = worksheet.DataValidations.GetDataValidations(worksheet.Selection)

For Each validation As DevExpress.Spreadsheet.DataValidation In validations
    Dim updatedRange As DevExpress.Spreadsheet.CellRange = validation.Range.Exclude(worksheet.Selection)
    If updatedRange IsNot Nothing Then
        validation.Range = updatedRange
    Else
        worksheet.DataValidations.Remove(validation)
    End If
Next

C# 
DevExpress.Spreadsheet.Workbook workbook = new DevExpress.Spreadsheet.Workbook();
workbook.LoadDocument(@"D:\DataValidation.xlsx");
DevExpress.Spreadsheet.Worksheet worksheet = workbook.Worksheets[0];
var validations = worksheet.DataValidations.GetDataValidations(worksheet.Selection);

foreach (DevExpress.Spreadsheet.DataValidation validation in validations)
{
    DevExpress.Spreadsheet.CellRange updatedRange = validation.Range.Exclude(worksheet.Selection);
    if (updatedRange != null)
    {
        validation.Range = updatedRange;
    }
    else
    {
        worksheet.DataValidations.Remove(validation);
    }   
}