Валидация данных

Microsoft Excel предоставляет некоторые хорошие функции автофильтрации или проверки данных на листе таблицы. Aspose.Cells для Python via .NET полностью поддерживает проверку данных и функции автофильтрации Microsoft Excel. В этой статье объясняется, как использовать эти функции в Microsoft Excel и как написать код для их использования с помощью Aspose.Cells для Python via .NET.

Типы проверки данных и выполнение

Проверка данных - это возможность устанавливать правила, касающиеся ввода данных на листе таблицы. Например, использовать проверку для обеспечения того, что столбец с подписью DATE содержит только даты, или что другой столбец содержит только числа. Вы даже можете обеспечить, что столбец с подписью DATE содержит только даты в определенном диапазоне. С помощью проверки данных вы можете контролировать то, что вводится в ячейки на листе таблицы.

Microsoft Excel поддерживает ряд различных типов проверки данных. Каждый тип используется для контроля типа данных, вводимого в ячейку или диапазон ячеек. Ниже приведены фрагменты кода, иллюстрирующие проверку, что:

• Числа являются целыми, то есть у них нет десятичной части.
• Десятичные числа соответствуют правильной структуре. Приведенный пример кода определяет, что диапазон ячеек должен иметь два десятичных знака.
• Значения ограничены списком значений. Проверка списка определяет отдельный список значений, которые можно применить к ячейке или диапазону ячеек.
• Даты находятся в определенном диапазоне.
• Время находится в определенном диапазоне.
• Текст имеет определенную длину.

Проверка данных в Microsoft Excel

Для создания проверок с помощью Microsoft Excel:

1. В листе выберите ячейки, к которым вы хотите применить проверку.
2. Из меню Данные выберите Проверку. Диалоговое окно проверки будет отображено.
3. Нажмите вкладку Настройки и введите настройки.

Проверка данных с использованием библиотеки Aspose.Cells для Python Excel

Проверка данных - мощная функция для проверки введенной информации в таблицы. С помощью проверки данных разработчики могут предоставлять пользователям список выбора, ограничивать ввод данных определенного типа или размера и т. д. В Aspose.Cells для Python via .NET, у каждого объекта Worksheet есть свойство validations, которое представляет собой коллекцию объектов Validation. Чтобы настроить проверку, задайте некоторые свойства класса Validation следующим образом:

• type – представляет тип проверки, который можно указать, используя одно из предопределенных значений в перечислении ValidationType.
• Оператор – представляет оператор, который следует использовать в проверке, который можно указать, используя одно из предопределенных значений в перечислении OperatorType.
• formula1 – представляет значение или выражение, связанное с первой частью проверки данных.
• formula2 – представляет значение или выражение, связанное со второй частью проверки данных.

Когда свойства объекта Validation были настроены, разработчики могут использовать структуру CellArea, чтобы хранить информацию о диапазоне ячеек, который будет проверен с использованием созданной проверки.

Типы проверки данных

Перечисление ValidationType имеет следующие члены:

Название элемента	Описание
ANY_VALUE	Обозначает значение любого типа.
WHOLE_NUMBER	Обозначает тип проверки для целых чисел.
DECIMAL	Обозначает тип проверки для десятичных чисел.
LIST	Обозначает тип проверки для выпадающего списка.
DATE	Обозначает тип проверки для дат.
TIME	Обозначает тип проверки для времени.
TEXT_LENGTH	Обозначает тип проверки для длины текста.
ПОЛЬЗОВАТЕЛЬСКИЙ	Обозначает тип пользовательской проверки.

Проверка данных на целые числа

С этим типом проверки пользователи могут вводить только целые числа в указанном диапазоне в проверяемые ячейки. Приведены примеры кода, показывающие, как реализовать тип проверки на целые числа. В примере создается такая же проверка данных, используя Aspose.Cells для Python via .NET, которую мы создали с помощью Microsoft Excel выше.

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

Может возникнуть случаи, когда вы хотите добавить CellArea к существующему Validation. Когда вы добавляете CellArea при помощи Validation.add_area(cell_area), 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) для добавления нового CellArea к существующему Validation.

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
• Получить примененную проверку данных к ячейке
• Проверьте, что значение ячейки удовлетворяет правилам проверки данных