数据验证

Microsoft Excel 提供了一些很好的功能来自动筛选或验证工作表数据。Aspose.Cells 完全支持 Microsoft Excel 的数据验证和自动筛选功能。本文介绍如何使用 Microsoft Excel 中的功能，以及如何使用 Aspose.Cells 对其进行编码。

数据验证类型和执行

数据验证是设置与在工作表上输入的数据有关的规则的能力。例如，使用验证来确保标记为 DATE 的列仅包含日期，或者另一列仅包含数字。您甚至可以确保标记为 DATE 的列仅包含特定范围内的日期。通过数据验证，您可以控制在工作表的单元格中输入的内容。

Microsoft Excel 支持多种不同类型的数据验证。每种类型用于控制将什么类型的数据输入单元格或单元格范围。下面的代码片段说明了如何验证这一点：

• Numbers 是整数，即没有小数部分。
• 小数遵循正确的结构。代码示例定义了一系列单元格应该有两个小数位。
• 值仅限于值列表。列表验证定义可应用于单元格或单元格区域的单独值列表。
• 日期在特定范围内。
• 时间在特定范围内。
• 文本在给定的字符长度内。

使用 Microsoft Excel 进行数据验证

使用 Microsoft Excel 创建验证：

1. 在工作表中，选择要应用验证的单元格。
2. 来自数据菜单，选择验证.将显示验证对话框。
3. 点击设置选项卡并输入设置。

使用 Aspose.Cells 进行数据验证

数据验证是一项强大的功能，用于验证输入到工作表中的信息。通过数据验证，开发人员可以为用户提供选择列表，将数据条目限制为特定类型或大小等。 在 Aspose.Cells 中，每个工作表类有一个验证代表集合的属性验证对象。要设置验证，请设置一些验证类的属性如下：

• 类型——表示验证类型，可以通过使用中的预定义值之一来指定验证类型枚举。
• 运算符 - 表示要在验证中使用的运算符，可以通过使用中的预定义值之一来指定运算符类型枚举。
• Formula1 – 表示与数据验证的第一部分关联的值或表达式。
• Formula2 – 表示与数据验证的第二部分关联的值或表达式。

当。。。的时候验证对象的属性已经配置好，开发者可以使用单元格区域结构来存储有关将使用创建的验证进行验证的单元格范围的信息。

数据验证类型

这验证类型枚举有以下成员：

成员名字	描述
任意值	表示任何类型的值。
完整的号码	表示整数的验证类型。
十进制	表示十进制数的验证类型。
列表	表示下拉列表的验证类型。
日期	表示日期的验证类型。
时间	表示时间的验证类型。
文本长度	表示文本长度的验证类型。
风俗	表示自定义验证类型。

整数数据验证

通过这种类型的验证，用户只能将指定范围内的整数输入到已验证的单元格中。下面的代码示例展示了如何实现 WholeNumber 验证类型。该示例使用我们在上面使用 Microsoft Excel 创建的 Aspose.Cells 创建相同的数据验证。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
    System.IO.Directory.CreateDirectory(dataDir);

// Create a workbook object.
Workbook workbook = new Workbook();

// Create a worksheet and get the first worksheet.
Worksheet ExcelWorkSheet = workbook.Worksheets[0];

// Accessing the Validations collection of the worksheet
ValidationCollection validations = workbook.Worksheets[0].Validations;

// Create Cell Area
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 0;
ca.StartColumn = 0;
ca.EndColumn = 0;

// Creating a Validation object
Validation validation = validations[validations.Add(ca)];

// Setting the validation type to whole number
validation.Type = ValidationType.WholeNumber;

// Setting the operator for validation to Between
validation.Operator = OperatorType.Between;

// Setting the minimum value for the validation
validation.Formula1 = "10";

// Setting the maximum value for the validation
validation.Formula2 = "1000";

// Applying the validation to a range of cells from A1 to B2 using the
// CellArea structure
CellArea area;
area.StartRow = 0;
area.EndRow = 1;
area.StartColumn = 0;
area.EndColumn = 1;

// Adding the cell area to Validation
validation.AddArea(area);


// Save the workbook.
workbook.Save(dataDir + "output.out.xls");

列表数据验证

这种类型的验证允许用户从下拉列表中输入值。它提供了一个列表：一系列包含数据的行。在该示例中，添加了第二个工作表以保存列表源。用户只能从列表中选择值。验证区域是第一个工作表中的单元格区域 A1:A5。

在这里设置是很重要的验证.InCellDropDown财产给真的.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
    System.IO.Directory.CreateDirectory(dataDir);

// Create a workbook object.
Workbook workbook = new Workbook();

// Get the first worksheet.
Worksheet worksheet1 = workbook.Worksheets[0];

// Add a new worksheet and access it.
int i = workbook.Worksheets.Add();
Worksheet worksheet2 = workbook.Worksheets[i];

// Create a range in the second worksheet.
Range range = worksheet2.Cells.CreateRange("E1", "E4");

// Name the range.
range.Name = "MyRange";

// Fill different cells with data in the range.
range[0, 0].PutValue("Blue");
range[1, 0].PutValue("Red");
range[2, 0].PutValue("Green");
range[3, 0].PutValue("Yellow");

// Get the validations collection.
ValidationCollection validations = worksheet1.Validations;

// Create Cell Area
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 0;
ca.StartColumn = 0;
ca.EndColumn = 0;

// Create a new validation to the validations list.
Validation validation = validations[validations.Add(ca)];

// Set the validation type.
validation.Type = Aspose.Cells.ValidationType.List;

// Set the operator.
validation.Operator = OperatorType.None;

// Set the in cell drop down.
validation.InCellDropDown = true;

// Set the formula1.
validation.Formula1 = "=MyRange";

// Enable it to show error.
validation.ShowError = true;

// Set the alert type severity level.
validation.AlertStyle = ValidationAlertType.Stop;

// Set the error title.
validation.ErrorTitle = "Error";

// Set the error message.
validation.ErrorMessage = "Please select a color from the list";

// Specify the validation area.
CellArea area;
area.StartRow = 0;
area.EndRow = 4;
area.StartColumn = 0;
area.EndColumn = 0;

// Add the validation area.
validation.AddArea(area);

// Save the Excel file.
workbook.Save(dataDir + "output.out.xls");

日期数据验证

通过这种类型的验证，用户可以在指定范围内或满足特定条件的日期值输入到已验证的单元格中。在示例中，用户只能输入 1970 到 1999 之间的日期。这里，验证区域是 B1 单元格。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
    System.IO.Directory.CreateDirectory(dataDir);

// Create a workbook.
Workbook workbook = new Workbook();

// Obtain the cells of the first worksheet.
Cells cells = workbook.Worksheets[0].Cells;

// Put a string value into the A1 cell.
cells["A1"].PutValue("Please enter Date b/w 1/1/1970 and 12/31/1999");

// Set row height and column width for the cells.
cells.SetRowHeight(0, 31);
cells.SetColumnWidth(0, 35);

// Get the validations collection.
ValidationCollection validations = workbook.Worksheets[0].Validations;

// Create Cell Area
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 0;
ca.StartColumn = 0;
ca.EndColumn = 0;

// Add a new validation.
Validation validation = validations[validations.Add(ca)];

// Set the data validation type.
validation.Type = ValidationType.Date;

// Set the operator for the data validation
validation.Operator = OperatorType.Between;

// Set the value or expression associated with the data validation.
validation.Formula1 = "1/1/1970";

// The value or expression associated with the second part of the data validation.
validation.Formula2 = "12/31/1999";

// Enable the error.
validation.ShowError = true;

// Set the validation alert style.
validation.AlertStyle = ValidationAlertType.Stop;

// Set the title of the data-validation error dialog box
validation.ErrorTitle = "Date Error";

// Set the data validation error message.
validation.ErrorMessage = "Enter a Valid Date";

// Set and enable the data validation input message.
validation.InputMessage = "Date Validation Type";
validation.IgnoreBlank = true;
validation.ShowInput = true;

// Set a collection of CellArea which contains the data validation settings.
CellArea cellArea;
cellArea.StartRow = 0;
cellArea.EndRow = 0;
cellArea.StartColumn = 1;
cellArea.EndColumn = 1;

// Add the validation area.
validation.AddArea(cellArea);

// Save the Excel file.
workbook.Save(dataDir + "output.out.xls");

时间数据验证

通过这种类型的验证，用户可以在指定范围内或满足某些条件的时间输入已验证的单元格。在示例中，用户只能输入 09:00 到 11:30 AM 之间的时间。这里，验证区域是 B1 单元格。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

// Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
    System.IO.Directory.CreateDirectory(dataDir);

// Create a workbook.
Workbook workbook = new Workbook();

// Obtain the cells of the first worksheet.
Cells cells = workbook.Worksheets[0].Cells;

// Put a string value into A1 cell.
cells["A1"].PutValue("Please enter Time b/w 09:00 and 11:30 'o Clock");


// Set the row height and column width for the cells.
cells.SetRowHeight(0, 31);
cells.SetColumnWidth(0, 35);

// Get the validations collection.
ValidationCollection validations = workbook.Worksheets[0].Validations;

// Create Cell Area
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 0;
ca.StartColumn = 0;
ca.EndColumn = 0;

// Add a new validation.
Validation validation = validations[validations.Add(ca)];

// Set the data validation type.
validation.Type = ValidationType.Time;

// Set the operator for the data validation.
validation.Operator = OperatorType.Between;

// Set the value or expression associated with the data validation.
validation.Formula1 = "09:00";

// The value or expression associated with the second part of the data validation.
validation.Formula2 = "11:30";

// Enable the error.
validation.ShowError = true;

// Set the validation alert style.
validation.AlertStyle = ValidationAlertType.Information;

// Set the title of the data-validation error dialog box.
validation.ErrorTitle = "Time Error";

// Set the data validation error message.
validation.ErrorMessage = "Enter a Valid Time";

// Set and enable the data validation input message.
validation.InputMessage = "Time Validation Type";
validation.IgnoreBlank = true;
validation.ShowInput = true;

// Set a collection of CellArea which contains the data validation settings.
CellArea cellArea;
cellArea.StartRow = 0;
cellArea.EndRow = 0;
cellArea.StartColumn = 1;
cellArea.EndColumn = 1;

// Add the validation area.
validation.AddArea(cellArea);

// Save the Excel file.
workbook.Save(dataDir + "output.out.xls");

文本长度数据验证

通过这种类型的验证，用户可以将指定长度的文本值输入到已验证的单元格中。在示例中，用户被限制输入不超过 5 个字符的字符串值。验证区域是 B1 单元格。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

  // Create directory if it is not already present.
bool IsExists = System.IO.Directory.Exists(dataDir);
if (!IsExists)
    System.IO.Directory.CreateDirectory(dataDir);

// Create a new workbook.
Workbook workbook = new Workbook();

// Obtain the cells of the first worksheet.
Cells cells = workbook.Worksheets[0].Cells;

// Put a string value into A1 cell.
cells["A1"].PutValue("Please enter a string not more than 5 chars");


// Set row height and column width for the cell.
cells.SetRowHeight(0, 31);
cells.SetColumnWidth(0, 35);

// Get the validations collection.
ValidationCollection validations = workbook.Worksheets[0].Validations;

// Create Cell Area
CellArea ca = new CellArea();
ca.StartRow = 0;
ca.EndRow = 0;
ca.StartColumn = 0;
ca.EndColumn = 0;

// Add a new validation.
Validation validation = validations[validations.Add(ca)];

// Set the data validation type.
validation.Type = ValidationType.TextLength;

// Set the operator for the data validation.
validation.Operator = OperatorType.LessOrEqual;

// Set the value or expression associated with the data validation.
validation.Formula1 = "5";

// Enable the error.
validation.ShowError = true;

// Set the validation alert style.
validation.AlertStyle = ValidationAlertType.Warning;

// Set the title of the data-validation error dialog box.
validation.ErrorTitle = "Text Length Error";

// Set the data validation error message.
validation.ErrorMessage = " Enter a Valid String";

// Set and enable the data validation input message.
validation.InputMessage = "TextLength Validation Type";
validation.IgnoreBlank = true;
validation.ShowInput = true;

// Set a collection of CellArea which contains the data validation settings.
CellArea cellArea;
cellArea.StartRow = 0;
cellArea.EndRow = 0;
cellArea.StartColumn = 1;
cellArea.EndColumn = 1;

// Add the validation area.
validation.AddArea(cellArea);

// Save the Excel file.
workbook.Save(dataDir + "output.out.xls");

数据验证规则

实施数据验证后，可以通过在单元格中分配不同的值来检查验证。Cell.GetValidationValue可用于获取验证结果。以下示例使用不同的值演示了此功能。示例文件可以从以下链接下载进行测试：

示例数据验证规则.xlsx

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// The path to the documents directory.
string dataDir = RunExamples.GetDataDir(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
// Instantiate the workbook from sample Excel file
Workbook workbook = new Workbook(dataDir+ "sample.xlsx");

// Access the first worksheet
Worksheet worksheet = workbook.Worksheets[0];

// Access Cell C1
// Cell C1 has the Decimal Validation applied on it.
// It can take only the values Between 10 and 20
Cell cell = worksheet.Cells["C1"];

// Enter 3 inside this cell
// Since it is not between 10 and 20, it should fail the validation
cell.PutValue(3);

// Check if number 3 satisfies the Data Validation rule applied on this cell
Console.WriteLine("Is 3 a Valid Value for this Cell: " + cell.GetValidationValue());

// Enter 15 inside this cell
// Since it is between 10 and 20, it should succeed the validation
cell.PutValue(15);

// Check if number 15 satisfies the Data Validation rule applied on this cell
Console.WriteLine("Is 15 a Valid Value for this Cell: " + cell.GetValidationValue());

// Enter 30 inside this cell
// Since it is not between 10 and 20, it should fail the validation again
cell.PutValue(30);

// Check if number 30 satisfies the Data Validation rule applied on this cell
Console.WriteLine("Is 30 a Valid Value for this Cell: " + cell.GetValidationValue());

检查单元格中的验证是否为下拉列表

正如我们所见，可以在一个单元格中实现多种类型的验证。如果你想检查验证是否下拉，验证.InCellDropDown属性可用于对此进行测试。以下示例代码演示了此属性的用法。可以从以下链接下载用于测试的示例文件：

示例验证.xlsx

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
Workbook book = new Workbook(sourceDir + "sampleValidation.xlsx");
Worksheet sheet = book.Worksheets["Sheet1"];
Cells cells = sheet.Cells;
Cell a2 = cells["A2"];
Validation va2 = a2.GetValidation();
if (va2.InCellDropDown)
{
    Console.WriteLine("A2 is a dropdown");
}
else
{
    Console.WriteLine("A2 is NOT a dropdown");
}
Cell b2 = cells["B2"];
Validation vb2 = b2.GetValidation();
if (vb2.InCellDropDown)
{
    Console.WriteLine("B2 is a dropdown");
}
else
{
    Console.WriteLine("B2 is NOT a dropdown");
}
Cell c2 = cells["C2"];
Validation vc2 = c2.GetValidation();
if (vc2.InCellDropDown)
{
    Console.WriteLine("C2 is a dropdown");
}
else
{
    Console.WriteLine("C2 is NOT a dropdown");
}

将 CellArea 添加到现有验证

在某些情况下，您可能想要添加单元格区域对现有的验证.当你添加单元格区域使用Validation.AddArea(CellArea 单元格区域)Aspose.Cells 检查所有现有区域，看新区域是否已经存在。如果文件有大量验证，这会影响性能。为了克服这个问题，API 提供了Validation.AddAreaCellArea cellArea, bool checkIntersection, bool checkEdge)方法。这检查交叉路口参数指示是否检查给定区域与现有验证区域的交集。将其设置为错误的将禁用其他区域的检查。这检查边缘参数表示是否勾选应用区域。如果新区域成为左上角区域，则内部设置将被重建。如果您确定新区域不是左上角区域，您可以将此参数设置为错误的.

下面的代码片段演示了使用Validation.AddAreaCellArea cellArea, bool checkIntersection, bool checkEdge)添加新方法单元格区域对现有的验证.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-.NET
// directories
string SourceDir = RunExamples.Get_SourceDirectory();
string outputDir = RunExamples.Get_OutputDirectory();

Workbook workbook = new Workbook(SourceDir + "ValidationsSample.xlsx");

// Access first worksheet.
Worksheet worksheet = workbook.Worksheets[0];

// Accessing the Validations collection of the worksheet
Validation validation = worksheet.Validations[0];

// Create your cell area.
CellArea cellArea = CellArea.CreateCellArea("D5", "E7");

// Adding the cell area to Validation
validation.AddArea(cellArea, false, false);

// Save the output workbook.
workbook.Save(outputDir + "ValidationsSample_out.xlsx");

附上源文件和输出 excel 文件以供参考。

源文件

输出文件

推进主题

• 在 ODS 文件中获取 Cell 验证
• 在 Cell 上应用验证
• 验证 Cell 值是否满足数据验证规则