Python для Excel вместо VBA
Обновлено: 04.07.2024
Этот сайт содержит указатели на лучшую доступную информацию о работе с файлами Excel на языке программирования Python.
Чтение и запись файлов Excel
Существуют пакеты Python для работы с файлами Excel, которые будут работать на любой платформе Python и не требуют использования Windows или Excel. Они быстрые, надежные и с открытым исходным кодом:
openpyxl
Рекомендуемый пакет для чтения и записи файлов Excel 2010 (например, .xlsx)
xlsxwriter
Альтернативный пакет для записи данных, форматирования информации и, в частности, диаграмм в формате Excel 2010 (например: .xlsx)
pyxlsb
Этот пакет позволяет читать файлы Excel в формате xlsb.
pylightxl
Этот пакет позволяет читать файлы xlsx и xlsm и записывать файлы xlsx.
Этот пакет предназначен для чтения данных и информации о форматировании из старых файлов Excel (например, .xls)
Этот пакет предназначен для записи данных и информации о форматировании в старые файлы Excel (например, .xls)
xlutils
В этом пакете собраны утилиты, для которых требуются как xlrd, так и xlwt , включая возможность копировать, изменять или фильтровать существующие файлы Excel.
Примечание. В целом, эти варианты использования теперь покрываются openpyxl!
Написание надстроек Excel
Для написания надстроек Excel на Python можно использовать следующие продукты. В отличие от пакетов чтения и записи, для них требуется установка Microsoft Excel.
PyXLL
PyXLL — это коммерческий продукт, который позволяет писать надстройки Excel на языке Python без использования VBA. Функции Python могут быть представлены в виде функций рабочего листа (UDF), макросов, меню и панелей инструментов ленты.
xlwings
xlwings — это библиотека с открытым исходным кодом для автоматизации Excel с помощью Python вместо VBA, которая работает в Windows и macOS: вы можете вызывать Python из Excel и наоборот и писать пользовательские функции в Python (только для Windows). xlwings PRO — это коммерческое дополнение с дополнительными функциями.
Список рассылки/дискуссионная группа
Существует группа Google, посвященная работе с файлами Excel в Python, включая перечисленные выше библиотеки, а также управлению приложением Excel через COM.
Коммерческое развитие
Следующие компании могут предоставить разработку коммерческого программного обеспечения и консультации, а также являются специалистами по работе с файлами Excel в Python:
Все, что вы можете написать на VBA, можно сделать на Python. На этой странице содержится информация, которая поможет вам перевести код VBA на Python.
Обратите внимание, что объектная модель Excel является частью Excel и задокументирована Microsoft. Классы и методы из этого API, использованные в этой документации, не являются частью PyXLL, поэтому дополнительные сведения об их использовании см. в документации по объектной модели Excel.
Объектная модель Excel ¶
При программировании на VBA вы взаимодействуете с объектной моделью Excel. Например, при написании
Вы создаете объект Range и вызываете для него метод Select. Объект Range является частью объектной модели Excel.
Большая часть того, что люди говорят о VBA в Excel, на самом деле является объектной моделью Excel, а не самим языком VBA. Как только вы поймете, как взаимодействовать с объектной моделью Excel из Python, замена вашего кода VBA на код Python станет простой задачей.
Объектная модель Excel хорошо задокументирована Microsoft как часть справочника по Office VBA.
Первое препятствие, с которым люди часто сталкиваются при написании макросов Excel на Python, — это поиск документации по классам Excel Python. Как только вы поймете, что объектная модель одинакова для Python и VBA, вы увидите, что классы, задокументированные в справочнике по Office VBA, — это те же самые классы, которые вы используете в Python, и поэтому вы можете использовать ту же документацию, даже если код примера может быть написан на VBA.
Доступ к объектной модели Excel в Python ¶
Объектная модель Excel доступна для всех языков, использующих COM. В Python есть несколько пакетов, упрощающих вызов COM-интерфейсов. Если вы ничего не знаете о COM, вам не о чем беспокоиться, поскольку вам не нужно вызывать COM API Excel из Python.
Объектом верхнего уровня в объектной модели Excel является объект приложения. Он представляет приложение Excel, и доступ ко всем другим объектам осуществляется через этот объект.
PyXLL предоставляет вспомогательную функцию xl_app для извлечения объекта приложения Excel. По умолчанию используется пакет Python win32com, который является частью пакета pywin32 [1].
Если у вас еще не установлен пакет pywin32, вы можете сделать это с помощью pip :
Или, если вы используете Anaconda, вы можете использовать conda :
Вы можете использовать xl_app для доступа к объекту приложения Excel из макроса Excel.В следующем примере показано, как переписать образец кода Macro1 VBA из раздела выше.
Обратите внимание, что в VBA есть неявный объект, связанный с тем, где был написан VBA Sub (макрос). Обычно код VBA записывается непосредственно на листе, и лист подразумевается в различных вызовах. В приведенном выше примере Macro1 Range на самом деле является методом на листе, на котором был написан макрос. В Python вместо этого нам нужно явно получить текущий активный лист.
Вы можете вызывать Excel с помощью объектной модели Excel из макросов и функций меню, а также использовать подмножество функций Excel из функций рабочего листа, где необходимо проявлять больше осторожности, поскольку функции вызываются во время процесса вычислений Excel.< /p>
Вы можете снять эти ограничения, вызвав функцию PyXLL schedule_call, чтобы запланировать вызов функции Python таким образом, чтобы вы могли безопасно использовать объектную модель Excel. Например, невозможно обновить значения ячеек рабочего листа с помощью функции рабочего листа, но можно запланировать вызов с помощью schedule_call, и этот вызов обновит рабочий лист после того, как Excel завершит вычисления.
Для тестирования также может быть полезно вызвать Excel из командной строки Python (или блокнота Jupyter). Это также можно сделать с помощью xl_app , и в этом случае будет возвращен первый найденный открытый экземпляр Excel.
Вы можете попробовать сделать это напрямую, используя win32com, а не xl_app . Однако мы не советуем этого делать при вызове кода Python из Excel, так как он может вернуть экземпляр Excel, отличный от ожидаемого.
Различия между VBA и Python ¶
Чувствительность к регистру ¶
Python чувствителен к регистру. Это означает, что фрагменты кода, такие как r.Value и r.value, различны (обратите внимание на заглавную букву V в первом случае. В VBA они будут обрабатываться одинаково, но в Python вы должны обратить внимание на регистр, который вы используете в своем коде. .
Если что-то работает не так, как ожидалось, проверьте файл журнала PyXLL. Любые неперехваченные исключения будут регистрироваться там, и если вы попытались получить доступ к свойству, используя неправильный регистр, вы, вероятно, увидите исключение AttributeError.
Вызов методов ¶
В Python скобки ( () ) всегда используются при вызове метода. В VBA они могут быть опущены. Пренебрежение добавлением круглых скобок в Python приведет к тому, что метод не будет вызываться, поэтому важно знать, какие атрибуты класса являются методами (и поэтому должны вызываться), а какие — свойствами (значения которых доступны по ссылке).
Например, метод Select для типа Range является методом и поэтому должен вызываться с круглыми скобками в Python, но в VBA они могут быть и обычно опускаются.
Аргументы ключевых слов могут передаваться как в VBA, так и в Python, но в аргументах ключевых слов Python используется = вместо :=, используемого в VBA.
Для доступа к свойствам скобки не требуются, и это приведет к неожиданным результатам! Например, свойство range.Value вернет значение диапазона. Добавление () к нему попытается вызвать это значение, и, поскольку значение не будет вызываться, это приведет к ошибке.
Именованные аргументы ¶
В VBA именованные аргументы передаются с использованием Name := Value . В Python синтаксис немного отличается и используется только знак равенства. Еще одно важное отличие состоит в том, что VBA не чувствителен к регистру, в отличие от Python. Это относится к именам аргументов, а также к именам методов и свойств.
В VBA вы можете написать
Если вы посмотрите документацию для Application.InputBox, вы увидите, что имена аргументов имеют другой регистр, и на самом деле это «Prompt» и «Type». В Python вы не сможете ошибиться в регистре, как в VBA.
В Python этот же метод будет вызываться как
Свойства ¶
И VBA, и Python поддерживают свойства. Доступ к свойству из объекта одинаков в обоих языках. Например, чтобы получить свойство ActiveSheet из объекта Application, вы должны сделать в VBA следующее:
В Python используется идентичный синтаксис:
Свойства с аргументами ¶
В VBA различие между методами и свойствами несколько размыто, поскольку свойства в VBA могут принимать аргументы. В Python свойство никогда не принимает аргументы. Чтобы обойти эту разницу, в классах Excel win32com есть методы Get и Set для свойств, которые принимают аргументы, в дополнение к свойству.
Свойство Range.Offset — это пример свойства, которое принимает необязательные аргументы. При вызове без аргументов он просто возвращает тот же объект Range. Чтобы вызвать его с аргументами в Python, необходимо использовать метод GetOffset вместо свойства Offset.
Следующий код активирует ячейку на три столбца справа и на три строки ниже активной ячейки на Листе1:
Чтобы преобразовать это в Python, мы должны внести следующие изменения:
- Замените свойство Offset на метод GetOffset, чтобы передать аргументы.
- Замените rowOffset и columnOffsetRowOffset и ColumnOffset, как указано в документации Range.Offset.
- Вызовите метод Activate, добавив круглые скобки в обоих местах, где он используется.
Вы можете задаться вопросом, что произойдет, если вы будете использовать свойство Offset в Python? Как вы можете уже ожидать, это потерпит неудачу, но, возможно, не так, как вы могли бы подумать.
Если бы вы вызвали xl.ActiveCell.Offset(RowOffset=3, ColumnOffset=3), результатом было бы то, что параметр RowOffset недействителен. На самом деле происходит то, что при оценке xl.ActiveCell.Offset свойство Offset возвращает Range, эквивалентный ActiveCell, и что Затем вызывается Range.
Range имеет метод по умолчанию. В Python это означает, что класс Range является вызываемым, и его вызов вызывает метод по умолчанию.
Методом по умолчанию для Range является Item, поэтому этот фрагмент кода фактически эквивалентен xl.ActiveCell.Offset.Item(RowOffset=3, ColumnOffset=3 ). Метод Item не ожидает аргумента RowOffset, поэтому он не работает таким образом.
Неявные объекты и «С» ¶
При написании кода VBA код обычно пишется «на» объекте, таком как рабочая книга или лист. Этот объект используется неявно при написании кода VBA.
При использовании оператора With..End в VBA целью оператора With становится неявный объект.
Если свойство не найдено в текущем неявном объекте (например, указанном в операторе «With..End»), то пробуется следующий (например, рабочий лист, с которым связана подпрограмма). Наконец, неявно используется объект приложения Excel.
В Python нет неявного объекта, и объект, на который вы хотите сослаться, должен быть указан явно.
Например, следующий код VBA выбирает диапазон и изменяет ширину столбца.
Чтобы написать один и тот же код на Python, на каждый объект нужно ссылаться явно.
Индексирование коллекций ¶
VBA использует круглые скобки ( () ) для вызова методов и для индексации коллекций.
В Python квадратные скобки ( [] ) используются для индексации коллекций.
При индексировании коллекций Excel следует соблюдать осторожность, поскольку Excel использует смещение индекса, равное 1, тогда как Python использует 0. Это означает, что для получения первого элемента в обычной коллекции Python вы должны использовать индекс 0, но при доступе к коллекциям из объектная модель Excel, которую вы бы использовали 1.
Перечисления и постоянные значения ¶
При записи значения перечисления VBA напрямую доступны в глобальной области видимости. Например, вы можете написать
В Python эти значения перечисления доступны как константы в пакете win32com.client.constants. Приведенный выше код будет переписан на Python следующим образом
Excel и многопоточность ¶
В VBA все всегда выполняется в основном потоке Excel. В Python у нас есть поддержка многопоточности, и иногда для выполнения длительной задачи вам может потребоваться запустить код в фоновом потоке.
Стандартный модуль потоков Python — это удобный способ запуска кода в фоновом потоке Python. Однако мы должны быть осторожны с тем, как мы обратимся к Excel из фонового потока. Поскольку VBA не имеет возможности использовать потоки, объекты Excel не написаны таким образом, чтобы их можно было использовать в разных потоках. Попытка сделать это может привести к серьезным проблемам и даже к сбою Excel!
Чтобы иметь возможность работать с несколькими потоками и при этом выполнять обратный вызов в Excel, в PyXLL есть функция schedule_call. Это используется для планирования выполнения функции Python в основном потоке Excel таким образом, чтобы можно было безопасно использовать объекты Excel. Всякий раз, когда вы работаете с потоками и вам нужно использовать API Excel, вы должны использовать schedule_call .
Например, вы можете использовать макрос Excel, чтобы запустить длительную задачу, а когда эта задача будет завершена, записать результат обратно в Excel. Вместо того, чтобы записывать результат обратно в Excel из фонового потока, используйте вместо этого schedule_call.
Примечания по отладке ¶
В редактор Excel VBA встроена функция отладки, поэтому вы можете выполнять код пошагово и видеть, что происходит на каждом этапе.
При написании кода Python иногда бывает проще написать код вне Excel в Python IDE, прежде чем адаптировать его для вызова из Excel в качестве макроса, функции меню и т. д.
Вызывая свой код из Excel, помните, что любые неперехваченные исключения будут напечатаны в файле журнала PyXLL, поэтому вы всегда должны искать в первую очередь, что происходит не так.
Если вы обнаружите, что вам необходимо выполнять пошаговый код Python во время его выполнения в Excel, вам понадобится Python IDE, поддерживающая удаленную отладку.Удаленная отладка — это то, как отладчики подключаются к внешнему процессу, который они не запускали сами.
Инструкции по отладке кода Python, работающего в Excel, можно найти в этой записи блога Отладка надстройки Python для Excel.
В настоящее время многие компании переходят с Excel на Python — высокоуровневый язык программирования общего назначения, созданный голландским программистом Гвидо ван Россумом. Сегодня все больше разработчиков программного обеспечения считают Python достойной заменой Excel из-за преимуществ, которые может предложить первый.
Обзор
- Переход с Excel на Python может быть оправдан возможностью последнего выполнять сложные вычисления и алгоритмы.
- Python проще в изучении и освоении, в отличие от Excel, который включает персонализированный язык, известный как VBA, сложный в освоении и использовании.
- Переход с Excel на Python позволяет пользователям пользоваться различными преимуществами, такими как платформа кодирования с открытым исходным кодом, множество добровольцев и бесплатные библиотеки.
Использование Excel и Python
Excel Основы Excel - формулы для финансов Вы ищете ускоренный курс Excel? Получите бесплатное обучение Excel для карьеры в области корпоративных финансов и инвестиционно-банковской деятельности от Института корпоративных финансов. является распространенным инструментом для анализа данных и обычно используется для проведения аналитических операций в финансовой сфере. Однако Excel имеет тенденцию быть более сложным, поскольку требует применения VBA. С VBA сложно работать, и они затрудняют работу с Excel при выполнении нескольких операций во время анализа данных.
Python как язык программирования предлагает различные преимущества по сравнению с Excel. Это язык программирования с открытым исходным кодом, в котором принимают участие многочисленные участники, которые добровольно предоставляют регулярные обновления кода и улучшают его функциональность.
Наоборот, Excel — это платное программное обеспечение, которое предоставляет обновления программы только тем, кто купил приложение, что ограничивает его использование. Python также поставляется с большим количеством предустановленных библиотек, что экономит время разработчиков, которым в противном случае пришлось бы создавать проекты с нуля.
Функциональные интеграции
Хорошее программное обеспечение для анализа данных должно быть способно интегрироваться с другим аналитическим и неаналитическим программным обеспечением. Python хорошо подходит под это описание, поскольку он хорошо интегрируется с другими программами. Пользователи могут импортировать и экспортировать различные типы форматов файлов в Python.
Например, Python совместим с синтаксисом SQL и даже может запускать его в своей среде для извлечения данных и таблиц в свою среду. Среда Python также эффективна при автоматизации таких задач, как импорт данных и запись проанализированных данных в функции Excel или CSV для анализа данных.
Переход с Excel на Python может быть оправдан с точки зрения функциональной интеграции. Во-первых, Python удобен для пользователя, и как начинающие, так и опытные аналитики могут с легкостью использовать этот язык. Excel использует VBA Глоссарий VBA Этот словарь VBA является полезным руководством для всех, кто хочет научиться использовать VBA в моделировании Excel. Просмотрите каждый из терминов и определений в приведенном ниже словаре VBA, чтобы изучить основы, прежде чем пройти курс CFI по финансовому моделированию VBA. язык, представляющий собой персонализированную платформу, использующую макросы для автоматизации задач анализа данных.
Использование макросов для автоматизации задач сложнее, чем автоматизация задач в среде Python. Кроме того, тот факт, что Python можно легко интегрировать с другими программами, делает его более подходящим для анализа данных.
Чтобы узнать больше о внутренней работе Python, ознакомьтесь с курсом CFI «Машинное обучение для финансов — основы Python»!
Совместимость кода
Код анализа данных можно сохранить в виде скриптов для повторного использования и дальнейшей обработки. Код Python воспроизводим и совместим, что делает его пригодным для дальнейшей обработки другими участниками, работающими над независимыми проектами. В отличие от языка VBA, используемого в Excel, анализ данных с помощью Python более четкий и обеспечивает лучший контроль версий.
Еще лучше Python обеспечивает согласованность и точность выполнения кода. Другие пользователи могут воспроизвести исходный код и по-прежнему получать плавное выполнение на том же уровне, что и исходный код. Возможность воспроизводить код делает Python более эффективным, чем Excel, поскольку пользователи могут обойти начальный процесс написания кода и начать с уже функционирующей платформы.
Масштабируемость и эффективность
Ученые, работающие с данными, предпочитают Python Excel из-за его способности обрабатывать большие наборы данных, а также включать машинное обучение и моделирование. При обработке больших объемов данных Excel занимает больше времени для завершения вычислений по сравнению с Python.Когда данные загружаются в две программы одновременно, Excel будет отставать от Python, поскольку он не предназначен для обработки больших объемов данных.
Кроме того, в Excel требуется больше времени для импорта данных, созданных в других аналитических программах. Это может быть еще медленнее, когда объем данных, импортируемых в электронную таблицу, огромен. Python восполняет этот пробел, так как это более эффективный инструмент для импорта и экспорта данных в различных форматах, что делает его идеальным для извлечения данных.
По сравнению с Excel, Python лучше подходит для обработки конвейеров данных, автоматизации задач и выполнения сложных вычислений. Кроме того, он поставляется с широким набором инструментов и библиотек для манипулирования.
Python и Excel в организациях
Python считается более эффективным инструментом анализа данных для сложных вычислений и больших объемов данных. Однако в целом Excel по-прежнему более популярен, чем Python, и его использует большое количество людей, занимающихся финансовым анализом.
Хотя Excel не идеален для обработки больших объемов данных, он является более удобным инструментом для организаций с небольшими объемами данных, требующих простых вычислений. Python, с другой стороны, более эффективен, чем Excel, когда организация обрабатывает большие объемы данных, которые требуют автоматизации для получения результатов в течение короткого периода времени.
Дополнительные ресурсы
Чтобы продолжать учиться и продвигаться по карьерной лестнице, вам будут полезны следующие ресурсы CFI:
Excel является частью MS Office.
VBA — это язык программирования в MS Office.
Python — это язык программирования, разрабатываемый с начала 90-х годов.
В отличие от VBA, Python является языком программирования с открытым исходным кодом.
Количество разработчиков Python намного превышает количество разработчиков VBA.
Python имеет множество подбиблиотек, которые могут дополнять возможности MS-Office.
Python может выполнять вычисления с неопределенно большими числами, в отличие, например, от Excel.
Я изучил XLwings, чтобы оценить его ценность для связывания Python и Excel.
XLwings создает собственную подбиблиотеку Python (xlwings.py), в которой переписывает все параметры Excel на Python.
Я не считаю это плодотворным путем.
Я попытался разработать альтернативу, в которой:
- Python может быть запущен из/посредством Excel
- аргументы из Excel могут быть переданы в код Python
- результат Python будет сохранен в форме, управляемой для Excel: csv-файл или графический файл,
- файл Excel связан с выводом Python запросами,
- пользователь может просматривать/адаптировать код Python в Excel.
Преимущества моего подхода:
- нет необходимости в отдельной библиотеке Python,
- способ использования вывода Python происходит в Excel, а не "жестко запрограммирован" в коде Python
- Python будет использоваться только для того, что не может сделать Excel: Python является дополнением к Excel
- использование вывода Python в Excel является гибким
- использование событий Excel устраняет кнопки на листе и делает изменения мгновенные
- вкладка Python на ленте содержит все (т.е. очень немногие) кнопки для пересчета/обновления/обновления
- не требуется никаких специальных надстроек
- можно просмотреть Python-код /адаптировано в пользовательской форме в Excel: специальный редактор Python не требуется
- доступны все опции в Excel (актуальные и в будущем); не нужно ждать реализации новых опций в библиотеке Python
Excel как графический интерфейс Python
Параметры VBA в Excel предоставляют средства для создания пользовательской формы VBA как графического пользовательского интерфейса (GUI) для Python.
Python не имеет встроенного графического интерфейса.
Библиотека Python, с помощью которой можно создать графический интерфейс, мягко говоря, не очень удобна для пользователя.
Так почему бы не использовать встроенные графические параметры пользовательской формы VBA?
Я интегрировал примеры "Фибоначчи", "моделирование", "график" и "база данных" в книгу "Python GUI.xlsb".
В веб-примере в качестве графического интерфейса используются сводная таблица и слайсеры; в этом случае пользовательская форма неприменима.
Пример UDF предназначен только для рабочих листов.
Как только рабочая книга откроется, пользовательская форма будет показана.
Пользовательская форма содержит многостраничную страницу с 5 вкладками.
Весь код Python был интегрирован в 1 файл Python: «Excel.py».
Функции, которые будут вызываться из Excel, имеют отдельные имена в файле Python.
Если Python требует загрузки дополнительных библиотек, он сделает это, как только функция будет вызвана.
Таким образом, память не будет загружена библиотеками, которые функция не использует.
Код Python записывает результаты в CSV-файл или графический файл.
Иногда код VBA читает csv-файл напрямую.
В некоторых случаях CSV-файл будет прочитан таблицей запросов.
Таблицы запросов имеют "собственный" очень скрытый лист ("база данных" и "моделирование").
– массив Фибоначчи;
– моделирование методом Монте-Карло;
– использование музыкальной базы данных;
– просмотр веб-страниц и анализ этих данных в сводной таблице; : графическое представление вычислений, которых нет в Excel
- использование Python в пользовательских функциях
Установите Python, чтобы запустить примеры.
На данный момент у Python есть 2 «разновидности»: Python 2.x и 3.x.
X — это номер версии.
Примеры на этой странице работают в обеих версиях, кроме «базы данных».
Для базы данных требуется Python 3.x.
Если Python был установлен правильно, вы можете использовать "py -2" для запуска Python 2.x и "py -3" для запуска Python 3.x.
В примерах используется этот способ запуска Python.
Примеры
ZIP-файл содержит пример.
Каждый Zip-файл состоит из
- файла Excel *.xlsb,
- файла Python *.py,
- файла *.csv
Каждый файл Excel содержит
вкладку «Python» на ленте,
кнопку редактирования Py для отображения/изменения кода Python,
кнопку(и) для запуска макросов
Примечание. Имейте в виду: пользовательская форма показывает код Python только в виде обычного текста.
Пользовательская форма не является редактором Python с аксессуарами.
Шаги
Разархивируйте zip-файл в отдельный собственный каталог.
Откройте файл Excel.
Кнопки на вкладке Python запускают макросы, запускающие код Python.
Результат будет записан в рабочий лист.
При первом запуске код может быть несколько медленнее, так как библиотеки должны быть загружены в память.
Введите в ячейку A2, сколько чисел Фибоначчи вы хотите сгенерировать.
Код VBA в событии worksheet_change запускает код Python.
Числа Фибоначчи появляются в столбце B.
Введите в ячейки C2 и D2, какие числа будут составлять двойную сумму: 2*(C2+D2)
Код VBA в событии worksheet_change запускает код Python.
Результат будет показан в ячейке B1.
Вы можете переключаться между вычислениями с помощью кнопок "Двойной" и "Фибоначчи" на вкладке Python на ленте.
Файл Excel содержит 1 таблицу запросов (в ячейке B1) для нескольких вычислений Python.
Использование Python для вычисления двойной суммы лучше, чем Excel, только для больших чисел, которые Excel не может обработать.
Посмотрите, например, на результат для 600 чисел Фибоначчи.
c_00 = ''
a, b = 0, 1
для него в диапазоне (n):
a, b = b, a + b
c_00 += str(a) +'\n'
open(n_csv,'w').write(c_00)
Введите параметры моделирования в диапазоне B1:B7.
Нажмите кнопку «Обновить» на вкладке Python на ленте.
Запрос в скрытом рабочем листе «данные» считывает результаты вычислений в Python.
График на листе «Моделирование» использует запрос в качестве источника данных.
Обновление таблицы запросов означает обновление графика.
При открытии книги подключение Querytable будет адаптировано к текущему расположению CSV-файла.
Код VBA для запуска Python находится в модуле кода листа Sheet1.
Вы можете переместить таблицу на листе 1 в любое место на листе.
Кнопка "Редактировать Py" на вкладке Python показывает код Python.
Вы можете проверить, изменить и сохранить код.
Файл Python
импортировать numpy как np
n_3=np.log(1 + float(n_3))
n_4=float(n_4)
n_6=float(float(n_1)/n_2)
цена = np.zeros((n_2 + 1, n_0))
процентили = np.zeros((n_2 + 1, 3))
цена[0,:] = n_5
процентили[0,:] = n_5
для t в диапазоне (1, n_2 + 1):
rand_nums = np.random.randn(n_0)
price[t,:] = price[t-1,:] * np.exp((n_3 - 0,5 * n_4**2) * n_6 + n_4 * rand_nums * np.sqrt(n_6))
процентили[t,:] = np.percentile(price[t,:], perc_selection)
sy=[t* n_6 для t в диапазоне (0,n_2+1)]
open(n_csv,'w').write('')
для (it,it1,it2) в zip(процентили,цена,sy):
open(n_csv,'a').write(str(it[0])+"," +str(it[1])+"," + str(it[2]) +','+ str(it1[0]) +','+str(it2) +'\n')
Zip-файл содержит:
- файл Excel database.xlsb
- файл Python database.py
- файлы csv combo.csv и list.csv
- базу данных Python chinook .sqlite
Код Python использует библиотеку Python sqlite .
Файл содержит 2 таблицы запросов
Таблица запросов 'combo' в скрытом листе 'source' .
Он содержит перечень всех музыкальных жанров в базе данных.
Таблица запросов подключена к CSV-файлу 'combo.csv'.
Файл combo.csv содержит результаты функции 'combo' в файле Python 'database.py'.
Эта функция фильтрует музыкальные жанры в базе данных 'chinook.sqlite'.
Поле со списком «C_00» на листе «список воспроизведения» заполняется запрашиваемым «комбо» на листе «источник».
Таблица запроса 'список' на листе 'список воспроизведения' .
Он содержит все треки в базе данных, которые соответствуют выбранному музыкальному жанру в поле со списком на листе 'плейлист'.
Таблица запросов подключена к CSV-файлу 'list.csv'
Список файлов.csv содержит результаты функции «список» в файле Python «database.py».
Эта функция фильтрует треки в базе данных «chinook.sqlite», которые соответствуют выбранному музыкальному жанру в Combobox «C_00» на листе «playlist». ' .
Открытие книги адаптирует подключение Querytables к текущему местоположению CSV-файла.
Используйте кнопку "combo" на вкладке "Python" на ленте, чтобы обновить музыкальные жанры в Combobox "C_00" .
Кнопка "Плейлист" на вкладке Python позволяет выбрать подходящие треки.
Более разумно запускать код Python после выбора в Combobox 'C_00' .
Вы найдете этот код в событии 'C_00_change'.
Кнопка "Редактировать Py" на вкладке Python показывает код Python.
Вы можете проверить, изменить и сохранить код.
Файл Python
Код VBA для запуска Python находится в модуле кода листа Sheet1.
Код Python иллюстрирует 2 метода передачи SQL-строки в Python:
1. SQL-строка "жестко закодирована" в коде Python: функция "список"
2. SQL-строка передается в качестве аргумента из Excel в код Python: функция 'combo'
cursor = sqlite3.connect(n_db, detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES).cursor()
"""
ВЫБЕРИТЕ
t.Name КАК Трек, alb.Название КАК Альбом, art.Name КАК Исполнитель, t.Композитор
FROM PlaylistTrack pt
INNER JOIN Track t ON pt.TrackId = t.TrackId
INNER JOIN Альбом alb ON t.AlbumId = alb.AlbumId
INNER JOIN Искусство исполнителя ON alb.ArtistId = art.ArtistId
ГДЕ PlaylistId = ?< br />""", (n_sql,))
open(n_csv,'w').write(','.join(it[0] для него в cursor.description)+'\n')
open(n_csv,' a').write('\n'.join(','.join(str(it1).replace(',',' ') для it1 в нем) для него в курсоре))
cursor = sqlite3.connect(n_db, detect_types=sqlite3.PARSE_DECLTYPES | sqlite3.PARSE_COLNAMES).cursor()
open(n_csv,'w').write('\n'.join(it[1 ] для этого в cursor.execute(n_sql)))
Этот файл не является примером XLwings.
URL содержит прибл. 417000 записей на момент написания этого кода.
Если URL-адрес больше не существует, вы можете заменить его в макросе VBA «M_refresh».
Код Python использует «запросы» библиотеки Python.
Лист "данные" содержит запрашиваемые "данные" .
Запрос связан с файлом data.csv.
Код Python записывает результаты "веб-скрейпинга" в файл "data.csv".
Данные листа можно сделать невидимыми.
Код события в ThisWorkbook_Open адаптирует соединение Querytable к расположению CSV-файла data.csv .
Сводная таблица содержит сводную таблицу.
"Источником данных" этой сводной таблицы является таблица запросов в листе "данные".
Каждый раз, когда веб-данные обновляются, сводная таблица также будет обновляться.
Кнопка «Обновить» на вкладке Python обновляет веб-данные.
Макрос VBA 'M_refresh' для запуска Python находится в модуле кода Sheet1.
Кнопка "Редактировать Py" на вкладке Python показывает код Python.
Вы можете проверить, изменить и сохранить код.
Лучший Python
Код Python использует 2 библиотеки Python:
- Numpy для математических расчетов,
- Matplotlib для графического представления математических результатов.
Введите на листе 'streamplot' в ячейку B1 значение параметра (целое число) для расчета в Numpy.
Запустите код Python, нажав кнопку "Рисовать" на вкладке Python на ленте.
В результате код Python создает графический файл 'graph.jpg'.
Макрос VBA 'M_plot' добавляет графический файл на рабочий лист.
Макрос VBA "M_plot" находится в макромодуле листа "streamplot".
Кнопка "Редактировать Py" на вкладке Python показывает код Python.
Вы можете проверить, изменить и сохранить код.
Файл Python
импортировать numpy как np
импортировать matplotlib.pyplot как plt
fig, ax = plt.subplots(figsize=(6, 4))
strm = ax.streamplot(X, Y, U, V, color=U, linewidth=2, cmap=plt.cm .autumn)
fig.colorbar(strm.lines)
Один из примеров XLwings — рабочий лист с пользовательскими функциями.
Комбинация UDF и Python не очень удачна.
Пользовательские функции будут пересчитываться чаще, чем это необходимо.
Даже если изменение происходит в другой открытой книге.
Я думаю, что было бы лучше использовать метод, реализованный в примере Фибоначчи.
В этом случае перерасчет происходит только в том случае, если одна из составляющих расчета была изменена.
Все остальные расчеты будут оставлены в покое.
Это значительно экономит время вычислений и делает код более быстрым и надежным.
Я настоятельно рекомендую не сочетать UDF и Python.
Тем не менее в иллюстративных целях я переписал пример с XLwings, чтобы показать, как можно добиться тех же результатов без XLwings.
ZIP-файл содержит:
- файл Excel UDF.xlsb
- 4 файла csv
- файл Python udf.py
Файл 'UDF.xlsb' содержит 4 таблицы запросов: 'data_001 . данные_004'.
Эти запросы связаны с 4 файлами CSV 'data_001.csv . data_004.csv'
Три таблицы запросов являются частью листа 'UDF'; querytable 'data_001' находится в скрытом листе 'data'.
Код события workbook_Open адаптирует соединения между CSV-файлами и таблицами запросов.
Пользовательские функции могут быть только частью макромодуля; в данном случае модуль 'python_udf' .
Пользовательские функции будут запускаться при каждом изменении на листе. Вот почему вкладка Python на ленте не содержит кнопки для запуска макроса.
Кнопка "Редактировать Py" на вкладке Python показывает код Python.
Вы можете проверить, изменить и сохранить код.
Пример XLwings
Первая пользовательская функция в примере XLwings извлекает workbook.name.
Это кажется довольно надуманным: формула Excel =cell("filename") тоже это понимает.
Я бы предпочел ограничить использование Python случаями, которые не поддерживает Excel.
Если вы предпочитаете UDF, я бы предложил этот (намного быстрее):
Файл Python udf.py содержит 4 функции: 'double_sum, add_one, matrix_mult и correl' .
Функция 'correl' использует библиотеку Python Pandas (часть дистрибутива Anaconda).
Мне не удалось заставить "коррел" работать.
Другие функции используют только стандартный код Python.
Обратите внимание, что результат будет записан рядом с UDF-формулой на листе.
Файл Python
импортировать панд как pd
def double_sum(x, y, n_csv):
open(n_csv,'w').write('\n'.join(','.join(str(sn[x][y]*sp[x][y]) для y в диапазоне( len(sn))) для x в диапазоне(len(sn))))
Читайте также: