Создание интерфейсных книг Excel для инструментов Python
На конференции Build 2016 Microsoft объявила, что 1,2 миллиарда человек по всему миру используют Excel [1]. В том же году предполагаемое население Земли составляло 7,4 миллиарда [2].
Это 16,2% всех людей на Земле.
Согласно отчету за 2019 год [3], Python может похвастаться всего 8,2 миллионами активных разработчиков, что составляет 0,001% населения Земли.
Имея в виду эти цифры, нам может быть полезно поощрять большее взаимодействие между Excel и Python, открывая шлюзы для множества новых пользователей для инструментов, созданных на Python.
Возможности внешнего интерфейса Excel для Python огромны. В этой статье мы рассмотрим, как это сделать, и реализуем «типичную» таблицу настройки финансового Excel.
Сначала инструмент, потом Excel
Практически в каждом сценарии, который я могу придумать, удобнее сначала создать Python-часть инструмента. Однако мы должны сохранять гибкость в форматировании «входных данных» инструмента.
Я имею в виду, что если мы, например, читаем один или два листа CSV / Excel с помощью Pandas - для первого прототипа мы можем полагаться на заданный набор имен столбцов.
Но если в коде тысячи строк, мы полагаемся на одни и те же жестко запрограммированные значения, мы столкнемся с проблемами, когда попытаемся сделать эти имена входных столбцов динамическими с помощью Excel.
Итак, на начальных этапах создания прототипа, если таблицы Excel еще нет, используйте начальную часть кода, чтобы переименовать метки столбцов в их внутренние (и, надеюсь, более информативные) имена меток:
Позже это будет заменено на наши сопоставления листов Excel.
Внешний интерфейс Excel
После того, как инструмент Python был встроен в более существенный прототип, нам пора приступить к созданию внешнего интерфейса Excel. Во-первых, мы должны решить, какие переменные можно изменить на листе Excel.
Всегда создавайте инструменты такого типа с учетом того, что формат входных данных изменится.
В зависимости от того, где вы работаете, и от инструмента, который вы разрабатываете, это либо очень важно, либо нет. Некоторые процессы просто четко определены, и форматирование данных вряд ли изменится.
Но я всегда ошибаюсь, проявляя осторожность и добавляя больше, а не меньше гибкости через интерфейс Excel. Только не усложняйте.
Использование внутренней системы именования и разрешение пользователю Excel указывать сопоставления столбцов - отличный пример сохранения гибкости. Теперь, вместо того, чтобы полагаться на жестко заданные имена столбцов, пользователь Excel может настраивать эти сопоставления, даже не касаясь Python.
Сопоставления
Центральным элементом интеграции является словарь mappings. Это займет вкладку Excel, содержащую «настройки» инструмента (я обычно называю его «Картографирование»).
Чтобы заполнить словарь сопоставлений, нам нужны функции для чтения вкладки сопоставлений Excel. Для этого мы используем openpyxl.
Мы можем прочитать значение из заданной ячейки в Excel следующим образом:
Используя этот метод, мы можем теперь начать заполнение нашего mappings словаря. Мы скорректируем приведенный выше код, чтобы добавить локальный путь к книге Excel «tool_setup».
Предположим также, что изначально активный лист может не быть листом сопоставления, и в случае добавления, удаления или перемещения вкладок мы используем анализ списка, чтобы найти индекс вкладки «Сопоставление»:
Теперь мы можем добавить несколько сопоставлений:
mappings = {}
mappings['Amount'] = ws["E4"].value
mappings['Term'] = ws["E5"].value
Сохраняя гибкость
В случае добавления или удаления строк на нашей вкладке сопоставления Excel этот подход создаст неверный mapping словарь. Чтобы этого избежать, мы используем функцию search_col. Это будет итеративно искать каждую ячейку в столбце, пока не найдет ячейку, содержащую нужное нам значение (или превышающую строку limit).
На этом этапе search_col возвращает столбец и строку, содержащие данные, которые мы хотели найти.
Это позволяет нам искать таблицу сопоставлений столбцов, выполнив поиск в столбце B слова «Внутренний», например:
search_col(ws, 'B', 'Internal')
[Out]: ('B', 12)
Отсюда мы можем создать цикл для добавления сопоставлений из столбца C в столбец E в наш mappings словарь. Увидев две или более пустых ячеек, мы можем быть уверены, что таблица сопоставления закончилась, и, таким образом, мы можем выйти из цикла:
После выполнения этого фрагмента кода у нас будет словарь Python mappings , который выглядит так:
{
'Loan ID': 'loan identifier',
'Product': 'product type',
...
'Initial Fees': 'init fees'
}
Если мы также хотим ввести другие переменные, например путь к файлу, который на скриншотах листа сопоставления показан как data/loanbook.csv. Мы просто находим строку, содержащую ‘ Filepath’, и извлекаем соответствующее значение в столбце D:
row, _ = search_col(ws, 'C', 'Filepath')
mappings['filepath'] = ws[f'D{row}].value
Интеграция
Последний шаг также самый простой - интеграция этих новых имен столбцов в наши сценарии Python.
Давайте воспользуемся приведенным выше листом сопоставления, чтобы прочитать наши данные и преобразовать метки входных столбцов во внутренние метки.
data = pd.read_csv(mappings['Filepath'])
Перед преобразованием меток входных столбцов во внутренние метки мы должны поменять местами пары ключ-значение на пары значение-ключ.
# invert the dictionary
inv_mappings = {mappings[key]: key for key in mappings}
Хотя для этого простого примера может показаться более удобным сделать это при построении mappings словаря. Для более сложных инструментов я всегда считал, что лучше поддерживать формат сопоставления внутренний: внешний, который мы здесь используем. Тем не менее, эта деталь остается на ваше усмотрение.
Наконец, преобразуйте метки ввода во внутренние метки:
data.rename(inv_mappings, axis=1, inplace=True)
Здесь мы можем добавить больше гибкости. Чтобы избежать возможных опечаток в начале / конце пробела или в нижнем / верхнем регистре, мы переписываем эту часть кода:
Еще одна необязательная часть, которую я хотел бы включить. Когда мы показываем внутренние подписи столбцов на листе Excel, они пишутся с заглавной буквы и содержат нормальный интервал. Однако, как личное предпочтение, я поддерживаю внутреннее форматирование snake_case, конвертируя:
"Loan ID" -> "loan_id" "Initial Rate" -> "initial_rate"
Я видел бесчисленное количество офисов, в которых много Excel, которые могли бы сэкономить сотни часов, потраченных на установку флажков, ввод значений или ожидание, пока модели Excel обработают даже самые маленькие наборы данных.
Хотя эпоха автоматизации и машинного обучения быстро автоматизирует многие тяжелые области Excel, Excel быстро никуда не денется.
На данный момент многие отрасли могут получить огромные выгоды за счет более тесной интеграции между самым быстрорастущим языком программирования в мире и наиболее часто используемым в мире программным обеспечением.
Спасибо за прочтение!
использованная литература
[1] Дж. Осборн, Build 2016: самые важные новости первого и второго дня (2016), techradar
[2] Всемирный банк, Общая численность населения (2019 г.), Открытые данные Всемирного банка.
[3] М. Карраз, Дж. Стичбери, С. Шерманс, П. Крокер, К. Коракитис, К. Воскоглу, Developer Economis: состояние нации разработчиков (2019), SlashData
Говоря о преодолении разрыва между различными технологиями, я написал статью о расширении возможностей SQL с помощью Python. Посмотрите здесь:
