Использование Python для добавления различных правил проверки данных в Excel

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

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

Обзор инструмента и подготовка среды

Преимущества Spire.XLS for Python

Spire.XLS for Python — это профессиональный компонент для обработки Excel на Python, ключевые преимущества которого:

1. Не требует установки Microsoft Excel, WPS или других офисных приложений, работает независимо;
2. Поддерживает полный спектр операций: создание, редактирование, форматирование, проверку данных, вычисление формул, конвертацию файлов Excel;
3. Простой синтаксис, низкий порог входа, подходит для сценариев автоматизированной отчетности, проверки данных, пакетной обработки;
4. Совместим с основными версиями Excel (2016/2019/365), высокая совместимость файлов.

Установка библиотеки

Установка зависимости одной командой pip, выполните в терминале:

pip install spire.xls

Полный код реализации функций

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

from spire.xls import *

from spire.xls.common import *

# Создание объекта рабочей книги

workbook = Workbook()

# Получение первого рабочего листа

sheet = workbook.Worksheets[0]

# Ввод описания типов проверки

sheet.Range["B2«].Text = «Проверка числа:»

sheet.Range["B4«].Text = «Проверка даты:»

sheet.Range["B6«].Text = «Проверка длины текста:»

sheet.Range["B8«].Text = «Проверка списка:»

sheet.Range["B10«].Text = «Проверка времени:»

# 1. Проверка целочисленного диапазона (1-10)

rangeNumber = sheet.Range["C2«]

rangeNumber.DataValidation.AllowType = CellDataType.Integer

rangeNumber.DataValidation.CompareOperator = ValidationComparisonOperator.Between

rangeNumber.DataValidation.Formula1 = «1»

rangeNumber.DataValidation.Formula2 = «10»

rangeNumber.DataValidation.InputMessage = «Введите число от 1 до 10»

rangeNumber.Style.KnownColor = ExcelColors.Gray25Percent

# 2. Проверка диапазона дат (весь 2022 год)

rangeDate = sheet.Range["C4«]

rangeDate.DataValidation.AllowType = CellDataType.Date

rangeDate.DataValidation.CompareOperator = ValidationComparisonOperator.Between

rangeDate.DataValidation.Formula1 = «01/01/2022»

rangeDate.DataValidation.Formula2 = «31/12/2022»

rangeDate.DataValidation.InputMessage = «Введите дату от 01/01/2022 до 31/12/2022»

rangeDate.Style.KnownColor = ExcelColors.Gray25Percent

# 3. Проверка длины текста (максимум 5 символов)

rangeTextLength = sheet.Range["C6«]

rangeTextLength.DataValidation.AllowType = CellDataType.TextLength

rangeTextLength.DataValidation.CompareOperator = ValidationComparisonOperator.LessOrEqual

rangeTextLength.DataValidation.Formula1 = «5»

rangeTextLength.DataValidation.InputMessage = «Введите текст не длиннее 5 символов»

rangeTextLength.Style.KnownColor = ExcelColors.Gray25Percent

# 4. Проверка выпадающим списком (фиксированные варианты)

rangeList = sheet.Range["C8«]

rangeList.DataValidation.Values = [«США», «Канада», «Великобритания», «Германия»]

rangeList.DataValidation.IsSuppressDropDownArrow = False

rangeList.DataValidation.InputMessage = «Выберите элемент из списка»

rangeList.Style.KnownColor = ExcelColors.Gray25Percent

# 5. Проверка временного диапазона (9:00-12:00)

rangeTime = sheet.Range["C10«]

rangeTime.DataValidation.AllowType = CellDataType.Time

rangeTime.DataValidation.CompareOperator = ValidationComparisonOperator.Between

rangeTime.DataValidation.Formula1 = «9:00»

rangeTime.DataValidation.Formula2 = «12:00»

rangeTime.DataValidation.InputMessage = «Введите время от 9:00 до 12:00»

rangeTime.Style.KnownColor = ExcelColors.Gray25Percent

# Автоподбор ширины второго столбца

sheet.AutoFitColumn(2)

# Фиксация ширины третьего столбца для полного отображения текста подсказки

sheet.Columns[2].ColumnWidth = 20

# Сохранение сгенерированного файла Excel

workbook.SaveToFile("output/DataValidation.xlsx", ExcelVersion.Version2016)

Построчный разбор ключевых функций

Базовая логика инициализации

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

Детальное описание пяти правил проверки данных

1) Проверка целочисленного диапазона

Ограничивает ячейку C2 только целыми числами от 1 до 10. Тип проверки задается через CellDataType.Integer, диапазон — через Between. Также настраивается всплывающая подсказка для направления пользователя. Серый фон ячейки помогает визуально выделить ячейки с активной проверкой.

2) Проверка диапазона дат

Для сценариев ввода дат ячейка C4 ограничена датами с 01.01.2022 по 31.12.2022. Это широко применимо для отчетных дат, регистрационных дат, статистических периодов и исключает ввод недопустимых дат.

3) Проверка длины текста

Ограничивает ввод в C6 текстом длиной ≤ 5 символов. Подходит для коротких кодов, аббревиатур, порядковых номеров и других кратких текстов, предотвращая нарушение верстки таблицы и ошибки в статистике из-за слишком длинного текста.

4) Проверка выпадающим списком

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

5) Проверка временного диапазона

Ограничивает ячейку C10 временем с 9:00 до 12:00. Подходит для учета рабочего времени, времени встреч, периодов обслуживания клиентов — точно стандартизирует формат и диапазон времени.

Оптимизация стиля таблицы

Автоподбор ширины столбца через AutoFitColumn и фиксация ширины столбца через ColumnWidth обеспечивают полное отображение текстов подсказок, повышая эстетику и читаемость таблицы в соответствии с офисными стандартами отчетности.

Описание результата работы

После выполнения кода в папке output проекта создается файл DataValidation.xlsx. При открытии файла вы увидите:

1. Ячейки C2/C4/C6/C8/C10 имеют серый фон, обозначая ячейки с проверкой;
2. При выделении соответствующей ячейки появляется предустановленная подсказка;
3. При вводе данных, не соответствующих правилам (например, 11 в C2 или 6 символов в C6), Excel автоматически блокирует ввод и выводит сообщение об ошибке;
4. В ячейке C8 можно нажать стрелку для выбора предустановленного варианта без ручного ввода.

Расширение бизнес-сценариев

Пять правил проверки, реализованных в этой статье, покрывают большинство офисных сценариев и могут быть гибко адаптированы под бизнес-требования:

1. Финансовая отчетность : проверка диапазона числовых значений и дат для исключения аномальных данных;
2. Кадровые формы : выбор отдела и должности из списка, проверка длины текста для табельных номеров;
3. Учет рабочего времени : проверка времени прихода и ухода, проверка даты отметки;
4. Сбор данных : унификация стандартов ввода, снижение затрат на ручную проверку.

Заключение

Автоматизация проверки данных в Excel с помощью Spire.XLS for Python полностью решает проблемы низкой эффективности, несогласованности и подверженности ошибкам при ручной настройке правил. Рассмотренные в статье пять ключевых правил проверки (целые числа, даты, длина текста, выпадающий список, время) отличаются простотой настройки и высокой применимостью.

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