Python for Excel, 2nd Edition

Chapter 8. Excel Automation

This chapter kicks off Part IV, which is about xlwings. While Python in Excel turns Excel into a Jupyter notebook, xlwings aims to replace VBA with Python. Accordingly, xlwings allows you to automate the Excel application, run macros, and create custom functions. To do this, xlwings relies on the Python environment that we installed in Chapter 2. You can therefore work offline and use any third-party package that you want.

We’ll start this chapter with the basics like reading and writing cell values. After learning how xlwings works with pandas DataFrames, charts, and pictures, we’ll put these skills into practice with a reporting case study. The last section teaches you how to make your scripts performant as well as work around missing functionality. This chapter requires you to run the code samples on either Windows or macOS, as they depend on a local installation of Microsoft Excel.

Getting Started with xlwings

The main goal of xlwings is to serve as a drop-in replacement for VBA, allowing you to interact with Excel from Python. Since Excel’s grid is the perfect layout to display data structures like lists, NumPy arrays, and pandas DataFrames, one of xlwings’ core features is to make reading from and writing to Excel as easy as possible. I’ll start this section by using Excel as a data viewer—​this is useful when you are interacting with DataFrames in a Jupyter notebook. Next, I’ll walk you through the Excel object model, and to wrap this section ...