xlwings

Python Principles

• Style Guide (PEP-8): https://www.python.org/dev/peps/pep-0008/#introduction
• Zen of Python (PEP-20): https://www.python.org/dev/peps/pep-0020/
• import antigravity

Introduction

• Excel is great for visualization, but large datasets are cumbersome
• xlwings is a free and open-source add-in for Python
• xlwings provides access to Python’s libraries
• Different from a dll
• Docs are here: https://docs.xlwings.org/en/stable/api.html

What you will need for this session:

• Pycharm
• https://github.com/devalbo/xlwings-xlamples

Installation

At the command line:

• pip install xlwings --upgrade
• xlwings addin install

In Excel:

• Alt+F11 to bring up VBA Editor
• Tools->References - Make sure xlwings is ticked

The Basics

• Using Python to control Excel - demo.py

Performance matters?

loop.py

Invert control

From Excel, use Python as a VBA substitute or replacement

• Ensure Developer tab is enabled in Excel: https://docs.xlwings.org/en/stable/addin.html#installation
• Execute Python functionality from VBA code
• Expose Python methods to Excel as UDF
• myxl.py and myxl.xlsm

Integrating Data with Other Applications

• Pandas - https://pandas.pydata.org/
• See data_exports.py - call macro from data_exports.xlsm

Back and forth with Python calling macros

• xlwings quickstart macro_example
• macro_example.py