VBA и Python для автоматизации Excel и MS Office +11



Поводом для заметки послужила статья на Хабре, в которой автор описывал, как он решал на Python задачу сбора и анализа метаданных из файлов Excel.


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


VBA и Python


VBA (Visual Basic for Applications), де-факто, самый популярный язык для автоматизации Microsoft Office. Доступен из коробки, помимо Excel, работает в PowerPoint, Outlook, Access, Project и других приложениях.


Если задать вопрос: «Какой язык программирования выбрать первым», то где-то в 90% всех случаев будет предложен Python. На практике здесь может быть и любой другой язык, но, исходя из популярности языка и своего опыта, буду сравнивать с ним.


В общем виде можно описать ситуацию через подобный график:



Детального сравнения не будет, рассмотрим основные killer-фичи, в ситуации, когда junior-программист/офисный сотрудник хочет автоматизировать что-либо, связанное с MS Office, и у него есть возможность выбора между языками.


Если в силу разных причин возможности выбора нет, то и сравнивать нечего.


В пользу VBA



  • Отличная работа с объектной моделью Excel и других приложений MS Office. Написание кода на VBA для большинства внутренних операций тривиально. У Python, в сравнении с VBA, поддержка объектной модели Office очень слабая.
  • Поддержка разных форматов MS Office. Самая большая проблема для внешних языков — это работа с разными форматами файлов MS Office. Например, xls, xlsx, xlsm файлы могут требовать разных библиотек, так как каждая хорошо работает только со своим форматом файла. Для VBA — это все "файл Excel", работа с которыми в целом одинаково хороша.
  • Работа с MS Exchange. Если необходимо обеспечить работу с корпоративной почтой/календарем на Exchange, то далеко не каждом языке есть нормальная библиотека для работы протоколом Exchange. В VBA это решается относительно просто через использование в макросе объектной модели MS Outlook.
  • Легкая установка и дистрибуция. К уже установленному офису не надо ничего устанавливать. Чтобы коллега мог воспользоваться программой, достаточно передать ему файл с макросом. Легко сделать надстройку, которая позволит "установить" модель макроса в фон офиса.
  • Интерактивность внутри приложений MS Office. Внутри офисных программ можно как просто поставить кнопки запуска макросов, так и (чуть сложнее) сделать целый отдельный UI. Сюда же относится написание своих формул в Excel и то, что макросы могут воздействовать на объекты внутри документов Office в реальном времени.
  • Запись макросов. Удобный инструмент, который позволяет записать действия человека в готовый код, для последующего редактирования использования.

В пользу Python (и других внешних языков программирования)



  • Приятный синтаксис и синтаксический сахар. Если коротко, то VBA не отличается выразительностью и удобством. Это вопрос личного вкуса, но для меня Python намного удобнее.
  • Богатая экосистема библиотек. Огромный выбор готовых библиотек для работы с внешним миром. Пытаться сделать на VBA программу, взаимодействующую с каким-нибудь внешним API, та еще боль. Занимательно, что как раз для работы с файлами Office библиотеки того же Python — откровенно "на троечку".
  • Хорошие средства разработки. Можно выбрать из огромного выбор программ, которые облегчают процесс разработки. Стандартный редактор VBA из Office предлагает очень бедный функционал и, в сравнении с альтернативами из мира Python, откровенно неудобен. Писать код VBA в внешнем редакторе, а потом копировать внутрь офиса для отладки — тоже неудобно.
  • Скорость работы. Не проверял скорость однопоточной работы, но, предположу, что в случае однопоточной работы преимущество будет за Python. В любом случае, достаточно тривиально организуется многопоточная обработка данных/файлов, что позволяет говорить в большей достижимой скорости.

Кейсы



Далее приведены конкретные задачи, которые я сам решал или автоматизировал, и когда мне надо было выбрать стек: VBA или Python. Для каждой задачи указан выбранный стек и даны пояснения почему:


  • Задача: Программа для проверки всех файлов Excel в директории на предмет наличия скрытых листов
    • Мой выбор: VBA. Причины: простота работы с разными форматами файлов Excel и отсутствие внешних взаимодействий.
  • Задача: Сервис, который должен был позволить пользователю с мобильного устройства конвертировать файлы PowerPoint в PDF для просмотра
    • Сервис был реализован как почтовый бот, на адрес которого пользователь может переслать файлы Office, а в ответ пользователю по почте приходит ответ с файлами PDF.
    • Мой выбор: Логика VBA + Python для мониторинга
      • Во-первых, внутренние функции гарантированно сохраняли PDF, аутентичный файлу PowerPoint (внешние библиотеки плохо справляются с рендером PowerPoint).
      • Во-вторых, реализация почтового бота, как макроса в MS Outlook решала проблемы работы с корпоративной Exchange почтой. Так, в Python нет нормальной библиотеки для работы с MS Exchange.
      • Python использовался для организации мониторинга работы сервиса и нотификации о возможных проблемах
  • Задача: Программа для объединения файлов Powerpoint с "подстрочником" (текстом для докладчика) в файл для печати
    • Мой выбор: VBA. Задача решалась через конвертацию двух файлов в PDF и их объединением с Riffle Shuffle. Так как важно качество конвертации в PDF, то использовались встроенные функции офиса для экспорта в PDF.
  • Задача: Дана база данных по предприятиям отрасли в виде карточек-отдельных файлов html, которые надо отфильтровать и объединить в 1 файл Excel для расчета ряда показателей.
    • Мой выбор: Python.
      • Чтобы вытаскивать из html карточек данные пригодилась библиотека для парсинга html BeautifulSoup.
      • Excel-файл создает программа, поэтому мы сами можем решать, какую аналитику рассчитывать уже в нем, а какую еще на стадии подготовки данных в Python.
  • Задача: Перевести весь текст в презентации PowerPoint на другой язык машинным переводчиком
    • Мой выбор: VBA. Важно было аккуратно работать с текстом во внутренних объектах файла PowerPoint. Для перевода использовался API от Яндекса, так как он бесплатен для небольших объемов и прост в подклюении. Например, API переводчика Bing я так и не смог заставить работать в VBA, так как там для работы нужен OAuth со своими заморочками. Если бы пришлось работать с Bing, то, наверное, я бы делал сервис-посредник на Python.
  • Задача: По заданным биржевым тикерам брать данные из API с финансовыми показателями (API выдает сформированный по запросу CSV файл) и считать на их основе ряд бенчмарков для анализа
    • Мой выбор: Python. Хотя API — простой (не требует какой-либо подписи запросов или авторизации) и выдает данные в CSV, выбран Python, так как нет причин выбирать VBA, а на Python писать удобнее.
  • Задача: Ведение базы поручений, рассылка уведомлений исполнителям, генерация отчета для печати
    • Здесь я выбирал очень долго, так как есть много альтернатив:
      • Сторонняя готовая система поручений
      • База данных с каким-то обработчиком
      • Access
      • Excel
    • Мой выбор: VBA
      • Во-первых, Excel сам по себе является готовым UI для работы
      • Во-вторых, VBA решает задачу работы с корпоративным Exchange через подключение MS Outlook
      • В-третьих, это решение делалось с прицелом на коллег, которым Excel был более понятен, чем что-либо совсем новое

Заключение


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

Вы можете помочь и перевести немного средств на развитие сайта

Теги:



Комментарии (15):

  1. KamAdm
    /#19745402

    Не убедительно.
    Пишу на vbscript или HTA. На VBA не программирую, потому что не установлен Microsoft Office.
    Так вот для подключения к внешним API не вызывает сильных проблем: COM-сервера, WMI, библиотека ADO и DOM — все это можно использовать и в VBA.

    • kaleman
      /#19745628

      В Vbscript нет отладчика. Это решает все. Редактор VBA в Office это по-сути обрезанная версия Visual Studio 6. И есть хороший отладчик. Без него любой более-менее сложный проект не написать. Поэтому я пользуюсь только VBA.

      • KamAdm
        /#19746462

        Может красивого и нет, но сообщения об ошибке в такой-то строке при исполнении выскакивает.

        • try1975
          /#19746940

          да есть же cscript //X scriptfile.vbs

          • KamAdm
            /#19747352

            Да есть, не отрицаю, но некоторые вещи в консоли не пашут.

      • kovserg
        /#19749504

        vbsedit посмотрите там и редактор и отладчик есть.

        • KamAdm
          /#19749924

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

    • Busla
      /#19749160

      А почему не jscript? — все API те же, плюс на 99% тот же javascript, опыт работы на котором можно переиспользовать в массе областей. Ну и без призрачных перспектив один только try-catch дорогого стоит.

      • KamAdm
        /#19749912

        Пишу конечно jscript, не без этого иначе не поймут сотоварищи. Просто как-то изначально случилось, что первые скрипты писал на vbscript на работе, так и продолжил. Часто приходиться тупа собирать новый скрипт из кусков старого, просто копипаст и все.
        Очень в своё время поразила работа скриптового червя I love you, на его основе написал скрипт, который из большого текстового файла по заданным параметрам искал кусок необходимого текста и кидал на печать. Ну как кидал, формировал файл и батником отправлял в lpt порт матричного принтера.

    • korygin
      /#19750240

      Я вам больше скажу, при использовании соответствующей библиотеки в Python вы получаете доступ ко всем возможностям VBA)

  2. dom1n1k
    /#19746036 / +1

    Больше десятка лет назад у меня была задача экспортировать текстовые данные из Autocad в Excel. Выбор стоял между Lisp и VBA, оба языка я знал на уровне пары обзорных лекций в университете. Потыкавшись в Лиспе день или два, я понял, что выбора на самом деле у меня нет. На VBA получилось почти без проблем, работало нормально. (к чему это? просто музыкой навеяло)

  3. try1975
    /#19746132

    cscript //X scriptfile.vbs — в качестве отладчика VBScript прекрасно работает Visual Studio

  4. SLASH_CyberPunk
    /#19746238

    Не согласен с графиком сложности разработки, есть какие-то аргументы?

  5. BasiC2k
    /#19747964 / +1

    На самом деле нужно выбирать инструмент исходя из основной логики программы. Если нужно работать с объектами Office — лучше использовать VBA. Если Office используется только для вывода результата, то лучше использовать то, что ближе по технологии к объекту обработки.
    С графиком тоже не согласен. Если возникают задачи, которые на VBA сложно решить, я использую VSTO. Вся мощь NET в поддержку.

  6. uaggster
    /#19749366

    > Задача: Дана база данных по предприятиям отрасли в виде карточек-отдельных файлов html, которые надо отфильтровать и объединить в 1 файл Excel для расчета ряда показателей.
    Не знаю, как там всё было устроено, но, возможно, задачу можно решить вообще без программирования, используя надстройку PowerQuery (не путать с PowerPivot).