数据验证

Microsoft Excel 提供了一些很好的功能来自动筛选或验证工作表数据。

数据验证是设置与在工作表上输入的数据有关的规则的能力。例如，使用验证来确保标记为 DATE 的列仅包含日期，或者另一列仅包含数字。您甚至可以确保标记为 DATE 的列仅包含特定范围内的日期。通过数据验证，您可以控制在工作表的单元格中输入的内容。 Aspose.Cells 完全支持 Microsoft Excel 的数据验证和自动筛选功能。本文介绍如何使用 Microsoft Excel 中的功能，以及如何使用 Aspose.Cells 对其进行编码。

数据验证类型和执行

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

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

使用 Microsoft Excel 进行数据验证

使用 Microsoft Excel 创建验证：

1. 在工作表中，选择要应用验证的单元格。

2. 来自数据菜单，选择验证. 显示验证对话框。

3. 点击设置选项卡并输入设置，如下所示。

   数据验证设置

使用 Aspose.Cells 进行数据验证

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

• 类型：表示验证类型，可以通过使用中的预定义值之一来指定验证类型枚举。
• 操作员：表示要在验证中使用的运算符，可以通过使用中的预定义值之一来指定运算符类型枚举。
• 公式1：表示与数据验证的第一部分关联的值或表达式。
• 方程式2：表示与数据验证的第二部分关联的值或表达式。

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

数据验证类型

数据验证允许您将业务规则构建到每个单元格中，以便不正确的条目会导致错误消息。业务规则是管理业务运作方式的政策和程序。 Aspose.Cells 支持所有重要类型的数据验证。

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

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

编程示例：整数数据验证

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

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(WholeNumberDataValidation.class) + "data/";

// Instantiating an Workbook object
Workbook workbook = new Workbook();
WorksheetCollection worksheets = workbook.getWorksheets();

// Accessing the Validations collection of the worksheet
Worksheet worksheet = worksheets.get(0);

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

ValidationCollection validations = worksheet.getValidations();

// Creating a Validation object
int index = validations.add(area);
Validation validation = validations.get(index);

// Setting the validation type to whole number
validation.setType(ValidationType.WHOLE_NUMBER);

// Setting the operator for validation to Between
validation.setOperator(OperatorType.BETWEEN);

// Setting the minimum value for the validation
validation.setFormula1("10");

// Setting the maximum value for the validation
validation.setFormula2("1000");

// Saving the Excel file
workbook.save(dataDir + "WNDValidation_out.xls");

// Print message
System.out.println("Process completed successfully");

编程示例：十进制数据验证

通过这种类型的验证，用户可以将十进制数字输入到已验证的单元格中。在示例中，限制用户只能输入十进制值，验证区域为 A1:A10。

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(DecimalDataValidation.class) + "data/";

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

// Create a worksheet and get the first worksheet.
Worksheet ExcelWorkSheet = workbook.getWorksheets().get(0);

// Specify the validation area of cells.
CellArea area = new CellArea();
area.StartRow = 0;
area.StartColumn = 0;
area.EndRow = 9;
area.EndColumn = 0;

// Obtain the existing Validations collection.
ValidationCollection validations = ExcelWorkSheet.getValidations();

// Create a validation object adding to the collection list.
int index = validations.add(area);
Validation validation = validations.get(index);

// Set the validation type.
validation.setType(ValidationType.DECIMAL);

// Specify the operator.
validation.setOperator(OperatorType.BETWEEN);

// Set the lower and upper limits.
validation.setFormula1("10");
validation.setFormula2("1000");

// Set the error message.
validation.setErrorMessage("Please enter a valid integer or decimal number");

// Save the workbook.
workbook.save(dataDir + "DDValidation_out.xls");

// Print message
System.out.println("Process completed successfully");

编程示例：列表数据验证

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

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

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(ListDataValidation.class) + "data/";

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

// Get the first worksheet.
Worksheet ExcelWorkSheet = workbook.getWorksheets().get(0);

// Add a new worksheet and access it.
int sheetIndex = workbook.getWorksheets().add();
Worksheet worksheet2 = workbook.getWorksheets().get(sheetIndex);

// Create a range with name in the second worksheet.
Range range = worksheet2.getCells().createRange(0, 4, 4, 4);
range.setName("MyRange");

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

// Specify the validation area of cells.
CellArea area = new CellArea();
area.StartRow = 0;
area.StartColumn = 0;
area.EndRow = 4;
area.EndColumn = 0;

// Obtain the existing Validations collection.
ValidationCollection validations = ExcelWorkSheet.getValidations();

// Create a validation object adding to the collection list.
int index = validations.add(area);
Validation validation = validations.get(index);

// Set the validation type.
validation.setType(ValidationType.LIST);

// Set the in cell drop down.
validation.setInCellDropDown(true);

// Set the formula1.
validation.setFormula1("=MyRange");

// Enable it to show error.
validation.setShowError(true);

// Set the alert type severity level.
validation.setAlertStyle(ValidationAlertType.STOP);

// Set the error title.
validation.setErrorTitle("Error");

// Set the error message.
validation.setErrorMessage("Please select a color from the list");

// Save the excel file.
workbook.save(dataDir + "LDValidation_out.xls");

// Print message
System.out.println("Process completed successfully");

编程示例：日期数据验证

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

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(DateDataValidation.class) + "data/";

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

// Obtain the cells of the first worksheet.
Cells cells = workbook.getWorksheets().get(0).getCells();

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

// Wrap the text.
Style style = cells.get("A1").getStyle();
style.setTextWrapped(true);
cells.get("A1").setStyle(style);

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

// Set a collection of CellArea which contains the data validation
// settings.
CellArea area = new CellArea();

area.StartRow = 0;
area.StartColumn = 1;
area.EndRow = 0;
area.EndColumn = 1;

// Get the validations collection.
ValidationCollection validations = workbook.getWorksheets().get(0).getValidations();

// Add a new validation.
int i = validations.add(area);
Validation validation = validations.get(i);

// Set the data validation type.
validation.setType(ValidationType.DATE);

// Set the operator for the data validation
validation.setOperator(OperatorType.BETWEEN);

// Set the value or expression associated with the data validation.
validation.setFormula1("1/1/1970");

// The value or expression associated with the second part of the data
// validation.
validation.setFormula2("12/31/1999");

// Enable the error.
validation.setShowError(true);

// Set the validation alert style.
validation.setAlertStyle(ValidationAlertType.STOP);

// Set the title of the data-validation error dialog box
validation.setErrorTitle("Date Error");

// Set the data validation error message.
validation.setErrorMessage("Enter a Valid Date");

// Set and enable the data validation input message.
validation.setInputMessage("Date Validation Type");
validation.setIgnoreBlank(true);
validation.setShowInput(true);

// Save the excel file.
workbook.save(dataDir + "DDValidation_out.xls", FileFormatType.EXCEL_97_TO_2003);

// Print message
System.out.println("Process completed successfully");

编程示例：时间数据验证

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

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(TimeDataValidation.class) + "data/";

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

// Obtain the cells of the first worksheet.
Cells cells = workbook.getWorksheets().get(0).getCells();

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

// Wrap the text.
Style style = cells.get("A1").getStyle();
style.setTextWrapped(true);
cells.get("A1").setStyle(style);

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

// Set a collection of CellArea which contains the data validation
// settings.
CellArea area = new CellArea();

area.StartRow = 0;
area.StartColumn = 1;
area.EndRow = 0;
area.EndColumn = 1;

// Get the validations collection.
ValidationCollection validations = workbook.getWorksheets().get(0).getValidations();

// Add a new validation.
int i = validations.add(area);
Validation validation = validations.get(i);

// Set the data validation type.
validation.setType(ValidationType.TIME);

// Set the operator for the data validation
validation.setOperator(OperatorType.BETWEEN);

// Set the value or expression associated with the data validation.
validation.setFormula1("09:00");

// The value or expression associated with the second part of the data
// validation.
validation.setFormula2("11:30");

// Enable the error.
validation.setShowError(true);

// Set the validation alert style.
validation.setAlertStyle(ValidationAlertType.INFORMATION);

// Set the title of the data-validation error dialog box.
validation.setErrorTitle("Time Error");

// Set the data validation error message.
validation.setErrorMessage("Enter a Valid Time");

// Set and enable the data validation input message.
validation.setInputMessage("Time Validation Type");
validation.setIgnoreBlank(true);
validation.setShowInput(true);

// Save the excel file.
workbook.save(dataDir + "TDValidation_out.xls", FileFormatType.EXCEL_97_TO_2003);

// Print message
System.out.println("Process completed successfully");

编程示例：文本长度数据验证

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

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the documents directory.
String dataDir = Utils.getSharedDataDir(TextLengthDataValidation.class) + "data/";

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

// Obtain the cells of the first worksheet.
Cells cells = workbook.getWorksheets().get(0).getCells();

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

// Wrap the text.
Style style = cells.get("A1").getStyle();
style.setTextWrapped(true);
cells.get("A1").setStyle(style);

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

// Set a collection of CellArea which contains the data validation
// settings.
CellArea area = new CellArea();

area.StartRow = 0;
area.StartColumn = 1;
area.EndRow = 0;
area.EndColumn = 1;

// Get the validations collection.
ValidationCollection validations = workbook.getWorksheets().get(0).getValidations();

// Add a new validation.
int i = validations.add(area);
Validation validation = validations.get(i);

// Set the data validation type.
validation.setType(ValidationType.TEXT_LENGTH);

// Set the operator for the data validation.
validation.setOperator(OperatorType.LESS_OR_EQUAL);

// Set the value or expression associated with the data validation.
validation.setFormula1("5");

// Enable the error.
validation.setShowError(true);

// Set the validation alert style.
validation.setAlertStyle(ValidationAlertType.WARNING);

// Set the title of the data-validation error dialog box.
validation.setErrorTitle("Text Length Error");

// Set the data validation error message.
validation.setErrorMessage(" Enter a Valid String");

// Set and enable the data validation input message.
validation.setInputMessage("TextLength Validation Type");
validation.setIgnoreBlank(true);
validation.setShowInput(true);

// Save the excel file.
workbook.save(dataDir + "TLDValidation_out.xls", FileFormatType.EXCEL_97_TO_2003);

// Print message
System.out.println("Process completed successfully");

数据验证规则

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

示例数据验证规则.xlsx

示例代码

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// Instantiate the workbook from sample Excel file
Workbook workbook = new Workbook(srcDir + "sampleDataValidationRules.xlsx");

// Access the first worksheet
Worksheet worksheet = workbook.getWorksheets().get(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.getCells().get("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
System.out.println("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
System.out.println("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
System.out.println("Is 30 a Valid Value for this Cell: " + cell.getValidationValue());

// Enter large number 12345678901 inside this cell
// Since it is not between 1 and 999999999999, it should pass the validation again
Cell cell2 = worksheet.getCells().get("D1");
cell2.putValue(12345678901l);

// Check if number 12345678901 satisfies the Data Validation rule applied on this cell
System.out.println("Is 12345678901 a Valid Value for this Cell: " + cell2.getValidationValue());

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

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

示例数据验证规则.xlsx

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
Workbook book = new Workbook(srcDir + "sampleValidation.xlsx");
Worksheet sheet = book.getWorksheets().get("Sheet1");
Cells cells  = sheet.getCells();
Cell a2 = cells.get("A2");
Validation va2 = a2.getValidation();
if(va2.getInCellDropDown()) {
    System.out.println("A2 is a dropdown");
} else {
    System.out.println("A2 is NOT a dropdown");
}
Cell b2 = cells.get("B2");
Validation vb2 = b2.getValidation();
if(vb2.getInCellDropDown()) {
    System.out.println("B2 is a dropdown");
} else {
    System.out.println("B2 is NOT a dropdown");
}
Cell c2 = cells.get("C2");
Validation vc2 = c2.getValidation();
if(vc2.getInCellDropDown()) {
    System.out.println("C2 is a dropdown");
} else {
    System.out.println("C2 is NOT a dropdown");
}

将 CellArea 添加到现有验证

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

下面的代码片段演示了使用[Validation.AddAreaCellArea cellArea, bool checkIntersection, bool checkEdge)](https://reference.aspose.com/cells/java/com.aspose.cells/Validation#addArea(com.aspose.cells.CellArea,%20boolean,%20boolean)添加新方法单元格区域对现有的验证.

// For complete examples and data files, please go to https://github.com/aspose-cells/Aspose.Cells-for-Java
// The path to the directories.
String sourceDir = Utils.Get_SourceDirectory();
String outputDir = Utils.Get_OutputDirectory();

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

// Access first worksheet.
Worksheet worksheet = workbook.getWorksheets().get(0);

// Accessing the Validations collection of the worksheet
Validation validation = worksheet.getValidations().get(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 值是否满足数据验证规则