数据验证

Microsoft Excel提供了一些很好的自动筛选或验证工作表数据的功能。Aspose.Cells for Python via .NET完全支持Microsoft Excel的数据验证和自动筛选功能。本文解释了如何在Microsoft Excel中使用这些功能，并介绍了如何使用Aspose.Cells for Python via .NET进行编码。

数据验证类型和执行

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

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

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

Microsoft Excel中的数据验证

要使用Microsoft Excel创建验证：

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

Aspose.Cells for Python Excel Library Data Validation

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

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

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

数据验证类型

ValidationType 枚举具有以下成员:

成员名称	描述
ANY_VALUE	表示任何类型的值。
WHOLE_NUMBER	表示整数的验证类型。
DECIMAL表示小数的验证类型。
LIST表示下拉列表的验证类型。
DATE	用于日期的验证类型。
TIME表示时间的验证类型。
TEXT_LENGTH	表示文本长度的验证类型。
CUSTOM表示自定义验证类型。

整数数据验证

使用这种类型的验证，用户只能在验证的单元格中输入指定范围内的整数。接下来的代码示例显示了如何实现 WholeNumber 验证类型。该示例创建了与我们在 Microsoft Excel 中创建的相同数据验证使用 Aspose.Cells for Python via .NET。

from aspose.cells import CellArea, OperatorType, ValidationType, Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  Create a workbook object.
workbook = Workbook()
#  Create a worksheet and get the first worksheet.
ExcelWorkSheet = workbook.worksheets[0]
#  Accessing the Validations collection of the worksheet
validations = workbook.worksheets[0].validations
#  Create Cell Area
ca = CellArea()
ca.start_row = 0
ca.end_row = 0
ca.start_column = 0
ca.end_column = 0
#  Creating a Validation object
validation = validations[validations.add(ca)]
#  Setting the validation type to whole number
validation.type = ValidationType.WHOLE_NUMBER
#  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
area = CellArea()
area.start_row = 0
area.end_row = 1
area.start_column = 0
area.end_column = 1
#  Adding the cell area to Validation
validation.add_area(area)
#  Save the workbook.
workbook.save("output.out.xls")

列表数据验证

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

在这里重要的是，将 Validation.in_cell_drop_down 属性设置为 true。

from aspose.cells import CellArea, OperatorType, ValidationAlertType, ValidationType, Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  Create a workbook object.
workbook = Workbook()
#  Get the first worksheet.
worksheet1 = workbook.worksheets[0]
#  Add a new worksheet and access it.
i = workbook.worksheets.add()
worksheet2 = workbook.worksheets[i]
#  Create a range in the second worksheet.
range = worksheet2.cells.create_range("E1", "E4")
#  Name the range.
range.name = "MyRange"
#  Fill different cells with data in the range.
worksheet2.cells.get("E1").value = "Blue"
worksheet2.cells.get("E2").value = "Red"
worksheet2.cells.get("E3").value = "Green"
worksheet2.cells.get("E4").value = "Yellow"
#  Get the validations collection.
validations = worksheet1.validations
#  Create Cell Area
ca = CellArea()
ca.start_row = 0
ca.end_row = 0
ca.start_column = 0
ca.end_column = 0
#  Create a new validation to the validations list.
validation = validations[validations.add(ca)]
#  Set the validation type.
validation.type = ValidationType.LIST
#  Set the operator.
validation.operator = OperatorType.NONE
#  Set the in cell drop down.
validation.in_cell_drop_down = True
#  Set the formula1.
validation.formula1 = "=MyRange"
#  Enable it to show error.
validation.show_error = True
#  Set the alert type severity level.
validation.alert_style = ValidationAlertType.STOP
#  Set the error title.
validation.error_title = "Error"
#  Set the error message.
validation.error_message = "Please select a color from the list"
#  Specify the validation area.
area = CellArea()
area.start_row = 0
area.end_row = 4
area.start_column = 0
area.end_column = 0
#  Add the validation area.
validation.add_area(area)
#  Save the Excel file.
workbook.save("output.out.xls")

日期数据验证

使用此类型的验证，用户可以在验证单元格中输入符合指定范围或特定条件的日期值。在此示例中，用户被限制只能输入1970年至1999年之间的日期。这里，验证区域是B1单元格。

from aspose.cells import CellArea, OperatorType, ValidationAlertType, ValidationType, Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  Create a workbook.
workbook = Workbook()
#  Obtain the cells of the first worksheet.
cells = workbook.worksheets[0].cells
#  Put a string value into the A1 cell.
cells.get("A1").put_value("Please enter Date b/w 1/1/1970 and 12/31/1999")
#  Set row height and column width for the cells.
cells.set_row_height(0, float(31))
cells.set_column_width(0, float(35))
#  Get the validations collection.
validations = workbook.worksheets[0].validations
#  Create Cell Area
ca = CellArea()
ca.start_row = 0
ca.end_row = 0
ca.start_column = 0
ca.end_column = 0
#  Add a new 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.show_error = True
#  Set the validation alert style.
validation.alert_style = ValidationAlertType.STOP
#  Set the title of the data-validation error dialog box
validation.error_title = "Date Error"
#  Set the data validation error message.
validation.error_message = "Enter a Valid Date"
#  Set and enable the data validation input message.
validation.input_message = "Date Validation Type"
validation.ignore_blank = True
validation.show_input = True
#  Set a collection of CellArea which contains the data validation settings.
cellArea = CellArea()
cellArea.start_row = 0
cellArea.end_row = 0
cellArea.start_column = 1
cellArea.end_column = 1
#  Add the validation area.
validation.add_area(cellArea)
#  Save the Excel file.
workbook.save("output.out.xls")

时间数据验证

使用此类型的验证，用户可以在验证单元格中输入符合指定范围或特定条件的时间值。在此示例中，用户被限制只能输入上午09:00至11:30之间的时间。这里，验证区域是B1单元格。

from aspose.cells import CellArea, OperatorType, ValidationAlertType, ValidationType, Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  Create a workbook.
workbook = Workbook()
#  Obtain the cells of the first worksheet.
cells = workbook.worksheets[0].cells
#  Put a string value into A1 cell.
cells.get("A1").put_value("Please enter Time b/w 09:00 and 11:30 'o Clock")
#  Set the row height and column width for the cells.
cells.set_row_height(0, float(31))
cells.set_column_width(0, float(35))
#  Get the validations collection.
validations = workbook.worksheets[0].validations
#  Create Cell Area
ca = CellArea()
ca.start_row = 0
ca.end_row = 0
ca.start_column = 0
ca.end_column = 0
#  Add a new 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.show_error = True
#  Set the validation alert style.
validation.alert_style = ValidationAlertType.INFORMATION
#  Set the title of the data-validation error dialog box.
validation.error_title = "Time Error"
#  Set the data validation error message.
validation.error_message = "Enter a Valid Time"
#  Set and enable the data validation input message.
validation.input_message = "Time Validation Type"
validation.ignore_blank = True
validation.show_input = True
#  Set a collection of CellArea which contains the data validation settings.
cellArea = CellArea()
cellArea.start_row = 0
cellArea.end_row = 0
cellArea.start_column = 1
cellArea.end_column = 1
#  Add the validation area.
validation.add_area(cellArea)
#  Save the Excel file.
workbook.save("output.out.xls")

文本长度数据验证

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

from aspose.cells import CellArea, OperatorType, ValidationAlertType, ValidationType, Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  Create a new workbook.
workbook = Workbook()
#  Obtain the cells of the first worksheet.
cells = workbook.worksheets[0].cells
#  Put a string value into A1 cell.
cells.get("A1").put_value("Please enter a string not more than 5 chars")
#  Set row height and column width for the cell.
cells.set_row_height(0, float(31))
cells.set_column_width(0, float(35))
#  Get the validations collection.
validations = workbook.worksheets[0].validations
#  Create Cell Area
ca = CellArea()
ca.start_row = 0
ca.end_row = 0
ca.start_column = 0
ca.end_column = 0
#  Add a new validation.
validation = validations[validations.add(ca)]
#  Set the data validation type.
validation.type = ValidationType.TEXT_LENGTH
#  Set the operator for the data validation.
validation.operator = OperatorType.LESS_OR_EQUAL
#  Set the value or expression associated with the data validation.
validation.formula1 = "5"
#  Enable the error.
validation.show_error = True
#  Set the validation alert style.
validation.alert_style = ValidationAlertType.WARNING
#  Set the title of the data-validation error dialog box.
validation.error_title = "Text Length Error"
#  Set the data validation error message.
validation.error_message = " Enter a Valid String"
#  Set and enable the data validation input message.
validation.input_message = "TextLength Validation Type"
validation.ignore_blank = True
validation.show_input = True
#  Set a collection of CellArea which contains the data validation settings.
cellArea = CellArea()
cellArea.start_row = 0
cellArea.end_row = 0
cellArea.start_column = 1
cellArea.end_column = 1
#  Add the validation area.
validation.add_area(cellArea)
#  Save the Excel file.
workbook.save("output.out.xls")

数据验证规则

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

sampleDataValidationRules.xlsx

from aspose.cells import Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
#  Instantiate the workbook from sample Excel file
workbook = Workbook("sample.xlsx")
#  Access the first 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 = worksheet.cells.get("C1")
#  Enter 3 inside this cell
#  Since it is not between 10 and 20, it should fail the validation
cell.put_value(3)
#  Check if number 3 satisfies the Data Validation rule applied on this cell
print("Is 3 a Valid Value for this Cell: "  + str(cell.get_validation_value()))
#  Enter 15 inside this cell
#  Since it is between 10 and 20, it should succeed the validation
cell.put_value(15)
#  Check if number 15 satisfies the Data Validation rule applied on this cell
print("Is 15 a Valid Value for this Cell: "  + str(cell.get_validation_value()))
#  Enter 30 inside this cell
#  Since it is not between 10 and 20, it should fail the validation again
cell.put_value(30)
#  Check if number 30 satisfies the Data Validation rule applied on this cell
print("Is 30 a Valid Value for this Cell: "  + str(cell.get_validation_value()))

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

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

sampleValidation.xlsx

from aspose.cells import Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
book = Workbook("sampleValidation.xlsx")
sheet = book.worksheets.get("Sheet1")
cells = sheet.cells
a2 = cells.get("A2")
va2 = a2.get_validation()
if va2.in_cell_drop_down:
    print("A2 is a dropdown")
else:
    print("A2 is NOT a dropdown")
b2 = cells.get("B2")
vb2 = b2.get_validation()
if vb2.in_cell_drop_down:
    print("B2 is a dropdown")
else:
    print("B2 is NOT a dropdown")
c2 = cells.get("C2")
vc2 = c2.get_validation()
if vc2.in_cell_drop_down:
    print("C2 is a dropdown")
else:
    print("C2 is NOT a dropdown")

为现有验证添加CellArea

可能存在这样的情况，您可能希望通过使用Validation.add_area(cell_area)向现有的Validation添加CellArea。当您使用Validation.add_area(cell_area)添加CellArea时，Aspose.Cells将检查所有现有区域，以查看新区域是否已经存在。如果文件具有大量验证，这会导致性能下降。为了克服这个问题，API提供了Validation.add_area(cell_area, check_intersection, check_edge)方法。checkIntersection参数指示是否检查给定区域与现有验证区域的交集。将其设置为false将禁用对其他区域的检查。checkEdge参数指示是否检查应用区域。如果新区域成为左上角区域，则内部设置将被重新构建。如果您确定新区域不是左上角区域，可以将此参数设置为false。

以下代码片段演示了如何使用Validation.add_area(cell_area, check_intersection, check_edge)方法向现有的Validation添加新的CellArea。

from aspose.cells import CellArea, Workbook

#  For complete examples and data files, please go to https:# github.com/aspose-cells/Aspose.Cells-for-.NET
workbook = Workbook("ValidationsSample.xlsx")
#  Access first worksheet.
worksheet = workbook.worksheets[0]
#  Accessing the Validations collection of the worksheet
validation = worksheet.validations[0]
#  Create your cell area.
cellArea = CellArea.create_cell_area("D5", "E7")
#  Adding the cell area to Validation
validation.add_area(cellArea, False, False)
#  Save the output workbook.
workbook.save("ValidationsSample_out.xlsx")

源和输出的Excel文件已附上供参考。

源文件

输出文件

高级主题

• 获取ODS文件中的单元格验证
• 获取应用于单元格的验证
• 验证单元格值是否满足数据验证规则