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

Microsoft Excel предоставляет некоторые хорошие функции для автоматической фильтрации или валидации данных листа.

Валидация данных – это возможность установить правила ввода данных на листе. Например, используйте валидацию, чтобы гарантировать, что столбец с меткой DATE содержит только даты, или что другой столбец содержит только числа. Вы можете даже убедиться, что столбец с меткой DATE содержит только даты в определенном диапазоне. С помощью валидации данных вы можете контролировать, что вводится в ячейки на листе. Aspose.Cells полностью поддерживает функции валидации данных и автофильтрации Microsoft Excel. В этой статье объясняется, как использовать функции в Microsoft Excel и как их программир…</cel…

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

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

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

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

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

1. В листе выберите ячейки, к которым вы хотите применить проверку.

2. В меню Данные выберите Проверка. Отображается диалоговое окно проверки.

3. Нажмите вкладку Настройка и введите настройки, как показано ниже. 

   Настройки проверки данных

Проверка данных в Aspose.Cells

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

• Тип: представляет тип проверки, который может быть указан с использованием одного из предопределенных значений в перечислении ТипПроверки.
• Оператор: представляет оператор, который будет использоваться при проверке, который может быть указан с использованием одного из предопределенных значений в перечислении ТипОператора.
• Формула1: представляет значение или выражение, связанное с первой частью проверки данных.
• Формула2: представляет значение или выражение, связанное со второй частью проверки данных.

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

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

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

Перечисление ТипПроверки имеет следующие элементы:

Название элемента	Описание
ANY_VALUE	Обозначение значения любого типа.
WHOLE_NUMBER	Обозначение типа проверки для целых чисел.
DECIMAL	Обозначение типа проверки для десятичных чисел.
LIST	Обозначение типа проверки для выпадающего списка.
DATE	Обозначение типа проверки для дат.
TIME	Обозначение типа проверки для времени.
TEXT_LENGTH	Обозначение типа проверки для длины текста.
CUSTOM	Обозначение типа проверки.

Пример программирования: Проверка данных целого числа

С этим типом проверки пользователи могут вводить только целые числа в указанном диапазоне в проверяемых ячейках. Приведенные далее примеры кода показывают, как реализовать тип проверки ЦЕЛОЕ_ЧИСЛО. В примере создается та же проверка данных, используя Aspose.Cells, что и мы создали с помощью Microsoft Excel выше.

// 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 на первом листе.

Здесь важно установить свойство Validation.setInCellDropDown в true.

// 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 утра. Здесь область проверки – ячейка 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() можно использовать, чтобы получить результат проверки. В следующем примере демонстрируется это свойство с различными значениями. Образец файла можно загрузить по следующей ссылке для тестирования:

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

Проверьте, является ли проверка в ячейке выпадающим списком

Как мы видели, существует множество типов проверок, которые могут быть реализованы внутри ячейки. Если вы хотите проверить, является ли проверка выпадающим списком или нет, можно использовать свойство Validation.InCellDropDown, чтобы проверить это. В следующем примере демонстрируется использование этого свойства. Образец файла для тестирования можно загрузить по следующей ссылке:

sampleDataValidationRules.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

Возможно, появятся случаи, когда вы захотите добавить CellArea к существующей Validation. Когда вы добавляете CellArea с помощью Validation.AddArea(CellArea cellArea), Aspose.Cells проверяет все существующие области, чтобы увидеть, не существует ли новая область уже. Если файл содержит большое количество проверок, это влияет на производительность. Чтобы это преодолеть, API предоставляет метод Validation.AddAreaCellArea cellArea, bool checkIntersection, bool checkEdge). Параметр checkIntersection указывает, нужно ли проверять пересечение данной области с существующими областями проверки. Установка его на false отключит проверку других областей. Параметр checkEdge указывает, нужно ли проверять применяемые области. Если новая область становится верхней левой областью, внутренние настройки перестраиваются. Если вы уверены, что новая область не является верхней левой областью, вы можете установить этот параметр как false.

В следующем сниппете кода демонстрируется использование метода Validation.AddAreaCellArea cellArea, bool checkIntersection, bool checkEdge) для добавления новой CellArea к существующей Validation.

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