Примените валидацию данных в Excel с использованием C#

Валидация данных в Excel обеспечивает ввод пользователями только допустимых данных в рабочие листы. При разработке форм, сборе данных или создании финансовых моделей валидация данных помогает поддерживать структуру и минимизировать ошибки пользователей. В этом посте мы покажем вам, как применить валидацию данных в Excel с помощью C# программно.

Эта статья охватывает следующие темы:

• Библиотека проверки данных Excel на C#
• Создайте выпадающий список в Excel с помощью C#
• Ограничить ввод целых чисел в Excel с помощью C#
• Валидация диапазона дат в Excel с использованием C#
• Формульная проверка данных в Excel
• Обрабатывайте недействительные данные с помощью предупреждений об ошибках в Excel
• Бесплатные ресурсы

C# Библиотека валидации данных Excel

Проверка данных гарантирует, что пользователи вводят допустимый и ожидаемый ввод в электронные таблицы Excel. Мы будем использовать Aspose.Cells for .NET API для добавления различных типов проверки данных в Excel. Он предоставляет мощный и гибкий подход к определению правил, таких как выпадающие списки, числовые диапазоны, ограничения по датам и пользовательские формулы, не требуя при этом Microsoft Excel.

Установите Aspose.Cells for .NET через NuGet пакет в вашем проекте:

PM> Install-Package Aspose.Cells

Затем импортируйте соответствующее пространство имен:

using Aspose.Cells;

Создать проверку выпадающего списка в Excel с использованием C#

Вы можете ограничить ввод пользователя заранее определенным набором опций, создавая проверку с помощью раскрывающегося списка в Excel. Это обеспечивает согласованные значения, что особенно полезно в формах или шаблонах.

Следуйте этим шагам, чтобы создать выпадающий список в Excel с использованием C#:

1. Создайте новый объект Workbook.
2. Получите доступ к первому worksheet с помощью его индекса.
3. Определите целевую CellArea для валидации.
4. Получите доступ к коллекции проверок работного листа.
5. Добавьте новую валидацию, используя метод Add().
6. Установите тип проверки на ValidationType.List.
7. Определите параметры выпадающего списка, используя свойство Formula1 (разделенные запятыми).
8. Добавьте область ячейки в проверку, используя метод AddArea().
9. Сохраните книгу, чтобы сгенерировать проверенный файл Excel.

Вот пример кода на C#, который реализует эти шаги:

// Создайте новую книгу Excel
var workbook = new Workbook();

// Получите доступ к первому листу в workbook
var sheet = workbook.Worksheets[0];

// Определите целевую область ячейки для валидации (A1 в данном случае)
var area = new CellArea
{
    StartRow = 0,     // Row index starts from 0 (Row 1)
    EndRow = 0,       // Apply to a single row
    StartColumn = 0,  // Column index starts from 0 (Column A)
    EndColumn = 0     // Apply to a single column
};

// Получите доступ к коллекции проверок рабочей таблицы
var validations = sheet.Validations;

// Добавьте новую запись проверки для указанной области ячеек.
int index = validations.Add(area);
var validation = validations[index];

// Установите тип проверки на выпадающий список
validation.Type = ValidationType.List;

// Определите разрешенные элементы списка (разделенные запятыми значения)
validation.Formula1 = "Red,Green,Blue";

// Установите входное сообщение, которое отображается при выборе ячейки.
validation.InputMessage = "Select a color from the list.";

// Установите сообщение об ошибке, которое отображается, если введены недопустимые данные.
validation.ErrorMessage = "Only Red, Green, or Blue are allowed.";

// Включите отображение сообщения об ошибке
validation.ShowError = true;

// Примените заданную область к проверке
validation.AddArea(area);

// Сохраните книгу в указанном пути файла
workbook.Save("dropdown-validation.xlsx");

Ограничение на целые числа в Excel с использованием C#

Валидация целых чисел гарантирует, что пользователи вводят только допустимые целые значения в Excel, что идеально подходит для полей количества, ввода возраста или бюджетных таблиц, где десятичные числа или текст недопустимы.

Давайте скажем, что вы хотите ограничить ячейку так, чтобы она принимала только числа от 1 до 100. Без этого правила пользователи могут случайно вводить недопустимые значения, такие как 150 или abc, что приведет к ошибкам расчетов или логическим проблемам в вашей электронной таблице.

С помощью Aspose.Cells for .NET вы можете программно навязать это правило проверки в C# без необходимости вручную настраивать его в Excel.

Следующий фрагмент кода показывает, как ограничить пользователей вводить только разрешенные значения:

using Aspose.Cells;

// Создайте новую книгу Excel
var workbook = new Workbook();

// Доступ к первому листу в книге рабочих таблиц
var sheet = workbook.Worksheets[0];

// Определите целевую область ячейки — B2 (строка 1, столбец 1)
var area = new CellArea
{
    StartRow = 1,
    EndRow = 1,
    StartColumn = 1,
    EndColumn = 1
};

// Получите доступ к коллекции валидаций рабочего листа
var validations = sheet.Validations;

// Добавить новую валидацию и получить её индекс
int index = validations.Add(area);

// Получите объект валидации, используя индекс
var validation = validations[index];

// Установите тип проверки на Целое число (разрешены только целые числа)
validation.Type = ValidationType.WholeNumber;

// Установите оператор на "Между"
validation.Operator = OperatorType.Between;

// Определите допустимый диапазон: от 1 до 100
validation.Formula1 = "1";
validation.Formula2 = "100";

// Установите сообщение об ошибке, которое появляется при вводе недопустимых данных
validation.ErrorMessage = "Enter a number between 1 and 100.";

// Включите отображение предупреждения об ошибке, когда валидация не проходит
validation.ShowError = true;

// (Необязательно, если ранее не использовалась Add(area)) Явно добавьте область в валидацию
validation.AddArea(area);

// Сохраните книгу в файл
workbook.Save("numbers-validation.xlsx");

Примените проверку диапазона дат в Excel с помощью C#

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

Например, представьте, что вы создаете шаблон расписания проекта и хотите ограничить пользователей вводить даты только в пределах 2024 года. Позволяя пользователям вводить дату вне этого диапазона (например, 2023 или 2025), это может нарушить формулы или создать несоответствующие записи.

Aspose.Cells for .NET упрощает применение проверок дат к конкретным ячейкам, так что пользователи могут вводить только те даты, которые соответствуют вашим критериям.

Следующий фрагмент кода демонстрирует, как обеспечить возможность вводить только даты между 1 января 2024 года и 31 декабря 2024 года. Все, что выходит за рамки диапазона, будет вызывать ошибку, что поможет вам поддерживать более чистые и точные данные в вашей таблице.

using Aspose.Cells;

// Создайте новую книгу Excel
var workbook = new Workbook();

// Получите доступ к первому листу в рабочей книге
var sheet = workbook.Worksheets[0];

// Определите область ячейки для применения проверки — C3 (строка 2, столбец 2)
var area = new CellArea
{
    StartRow = 2,
    EndRow = 2,
    StartColumn = 2,
    EndColumn = 2
};

// Получите доступ к коллекции проверок рабочего листа.
var validations = sheet.Validations;

// Добавьте новую валидацию и получите ее индекс.
int index = validations.Add(area);

// Получите объект валидации
var validation = validations[index];

// Установите тип валидации на Дата
validation.Type = ValidationType.Date;

// Установите оператор на "Между" (начальная и конечная даты)
validation.Operator = OperatorType.Between;

// Укажите допустимый диапазон дат: 1 января 2024 года по 31 декабря 2024 года
validation.Formula1 = "2024-01-01";
validation.Formula2 = "2024-12-31";

// Установите сообщение об ошибке для отображения, когда дата вне диапазона.
validation.ErrorMessage = "Date must be within the year 2024.";

// Включить отображение предупреждения об ошибке
validation.ShowError = true;

// Перепривяжите область, чтобы убедиться, что валидация правильно привязана.
validation.AddArea(area);

// Сохраните рабочую книгу в указанном пути.
workbook.Save("date-validation.xlsx");

Используйте валидацию на основе формул в Excel с использованием C#

Иногда простых выпадающих списков или фиксированных диапазонов чисел недостаточно, особенно когда ваши правила зависят от значений в других ячейках. С помощью валидации на основе формул вы можете определить пользовательские правила, используя формулы в стиле Excel. Эти правила могут ссылаться на другие ячейки и динамически оценивать, является ли ввод допустимым. Например, вы можете захотеть убедиться, что значение в ячейке B1 всегда больше значения в A1. Это распространенный сценарий в сравнении цен, оценочных листах или последовательностях дат.

Aspose.Cells for .NET полностью поддерживает эту функцию и позволяет вам определять валидацию с использованием пользовательских формул, так же как в Excel.

Следующий образец кода демонстрирует, как применять валидацию на основе формул в Excel с использованием C#.

using Aspose.Cells;

// Создайте новую книгу Excel
var workbook = new Workbook();

// Получите доступ к первому листу в рабочей книге
var sheet = workbook.Worksheets[0];

// Определите область ячейки для валидации — B1 (строка 0, столбец 1)
var area = new CellArea
{
    StartRow = 0,
    EndRow = 0,
    StartColumn = 1,
    EndColumn = 1
};

// Получите коллекцию валидаций листов расчетов
var validations = sheet.Validations;

// Добавьте новую проверку в коллекцию и получите ее индекс.
int index = validations.Add(area);

// Получите объект валидации по индексу
var validation = validations[index];

// Установите тип валидации на Пользовательский (используется для правил на основе формул)
validation.Type = ValidationType.Custom;

// Установите пользовательскую формулу: B1 должно быть больше A1
validation.Formula1 = "=B1>A1";

// Определите сообщение об ошибке, которое отображается, когда проверка не проходит.
validation.ErrorMessage = "Value in B1 must be greater than A1.";

// Включите отображение уведомления об ошибке при неверном вводе.
validation.ShowError = true;

// Добавьте область явно, чтобы гарантировать, что она покрыта проверкой.
validation.AddArea(area);

// Сохраните книгу в указанном файловом пути
workbook.Save("formula-validation.xlsx");

Настройка входных и сообщений об ошибках в Excel с использованием C#

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

Aspose.Cells for .NET позволяет вам настраивать полезные сообщения ввода и предупреждения об ошибках, которые появляются, когда пользователи выбирают ячейку или вводят неверные данные. Эти сообщения улучшают пользовательский опыт, снижают путаницу и делают ваши шаблоны Excel более профессиональными.

Например, когда пользователь щелкает на ячейку, вы можете показать подсказку, такую как

`Допускаются только значения от 1 до 100.”

И если они введут неправильное значение, Excel может отобразить диалоговое окно с сообщением:

`Недопустимый ввод: Пожалуйста, введите число от 1 до 100.”

Вы также можете настроить, как Excel реагирует на недействительный ввод, выбрав, блокировать ли пользователя полностью (Stop), разрешить им продолжить с предупреждением (Warning) или просто отображать информационное сообщение (Information).

Следуйте этим шагам, чтобы настроить уведомления о валидации с использованием C#:

1. После настройки правила валидации установите InputTitle и InputMessage, чтобы отображать текст помощи, когда пользователь выбирает ячейку.
2. Определите ErrorTitle и ErrorMessage, чтобы объяснить, что пошло не так, если валидация не удалась.
3. Выберите AlertStyle — варианты включают Stop, Warning и Information.
4. Установите ShowError в true, чтобы включить предупреждение о валидации.
5. Сохраните рабочую книгу.

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

var workbook = new Workbook();
var sheet = workbook.Worksheets[0];

// Настройте область валидации — примените к ячейке C1
var area = new CellArea
{
    StartRow = 0,
    EndRow = 0,
    StartColumn = 2, // Column C = 2
    EndColumn = 2
};

// Добавить валидацию
int index = sheet.Validations.Add(area);
var validation = sheet.Validations[index];

validation.Type = ValidationType.Custom;

// Эта формула всегда оценивается как ЛОЖЬ
validation.Formula1 = "=FALSE";

// Настройте сообщения ввода и ошибки
validation.InputTitle = "Input Restricted";
validation.InputMessage = "Try entering anything to see the validation.";
validation.ErrorTitle = "Invalid Input";
validation.ErrorMessage = "You triggered this validation error successfully!";
validation.AlertStyle = ValidationAlertType.Stop;
validation.ShowError = true;
validation.ShowInput = true;

// Примените валидацию к области
validation.AddArea(area);

// Сохраните проверенную книгу.
workbook.Save("D:\\Files\\validated_with_alerts.xlsx");

Они предупреждения улучшают удобство использования и явно направляют пользователей о том, что вводить.

Попробуйте бесплатно

Получите бесплатную временную лицензию, чтобы протестировать все возможности Aspose.Cells for .NET без каких-либо ограничений по функциональности.

Бесплатные ресурсы

Узнайте больше об автоматизации и валидации Excel, используя эти ресурсы:

• Документация Aspose.Cells
• Онлайн инструменты
• API Reference
• Блог статьи

Заключение

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

Если у вас есть какие-либо вопросы или отзывы, пожалуйста, не стесняйтесь размещать их на нашем free support forum.Мы будем рады помочь!

См. также

• Объединение почты из Excel
• Фильтровать пустые и непустые ячейки в Excel
• Создайте диаграмму Ганта в Excel
• Как заблокировать ячейки в Excel
• Добавить изображение в Excel
• Удалите пустые строки и столбцы в Excel
• Добавьте расчетное поле в сводной таблице