数据验证

数据验证类型和执行

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

Microsoft Excel 支持许多不同类型的数据验证。 每种类型用于控制输入到单元格或单元格范围中的数据类型。 下面的代码片段说明了如何验证:

  • 数字是整数,即它们没有小数部分。
  • 十进制数遵循正确的结构。代码示例定义了一组单元格应具有两个小数位。
  • 值受限于值列表。列表验证定义了可应用于单元格或单元格范围的单独值列表。
  • 日期在特定范围内。
  • 时间在特定范围内。
  • 文本在给定的字符长度内。

Microsoft Excel中的数据验证

要使用Microsoft Excel创建验证:

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

Aspose.Cells中的数据验证

数据验证是一项强大的功能,可验证输入工作表的信息。借助数据验证,开发人员可以为用户提供选择列表,限制数据输入为特定类型或大小等功能。 在Aspose.Cells中,每个Worksheet类都有一个Validations属性,该属性表示一组Validation对象。要设置验证,设置一些Validation类的属性,如下所示:

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

当配置了 Validation 对象的属性后,开发人员可以使用 CellArea 结构来存储有关使用创建的验证进行验证的单元格范围的信息。

数据验证类型

ValidationType 枚举具有以下成员:

成员名称 描述
AnyValue 表示任何类型的值。
WholeNumber 表示整数的验证类型。
Decimal 表示十进制数字的验证类型。
List 表示下拉列表的验证类型。
Date 表示日期的验证类型。
Time 表示时间的验证类型。
TextLength 表示文本长度的验证类型。
Custom 表示自定义验证类型。
整数数据验证

使用这种类型的验证,用户只能输入指定范围内的整数到被验证的单元格中。接下来的代码示例展示了如何实现整数验证类型。示例创建了同样的数据验证,用Aspose.Cells来创建,就像我们以上使用Microsoft Excel创建的一样。

// 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。

在这里重要的是,将 Validation.InCellDropDown 属性设置为 true

// 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之间的时间。这里,验证区域是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可以用来获取验证结果。以下示例演示了使用不同值的此功能。可以从以下链接下载示例文件进行测试:

sampleDataValidationRules.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());

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

正如我们所见,可以在单元格中实施许多类型的验证。如果要检查验证是否为下拉列表,可以使用Validation.InCellDropDown属性来测试。以下示例代码演示了如何使用此属性。测试样本文件可从以下链接下载:

sampleValidation.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 cellArea)向现有的Validation添加CellArea。当您使用Validation.AddArea(CellArea cellArea)添加CellArea时,Aspose.Cells将检查所有现有区域,以查看新区域是否已经存在。如果文件具有大量验证,这会导致性能下降。为了克服这个问题,API提供了Validation.AddAreaCellArea cellArea, bool checkIntersection, bool checkEdge)方法。checkIntersection参数指示是否检查给定区域与现有验证区域的交集。将其设置为false将禁用对其他区域的检查。checkEdge参数指示是否检查应用区域。如果新区域成为左上角区域,则内部设置将被重新构建。如果您确定新区域不是左上角区域,可以将此参数设置为false

以下代码片段演示了如何使用Validation.AddAreaCellArea cellArea, bool checkIntersection, bool checkEdge)方法向现有的Validation添加新的CellArea

// 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文件已附上供参考。

源文件

输出文件

高级主题