Python for Excel, 2nd Edition

Chapter 9. Python-Powered Excel Tools

In the last chapter, we learned how to write Python scripts to automate Microsoft Excel. While this is very powerful, the user must feel comfortable using either the Terminal, VS Code, or Jupyter notebooks to run the scripts. This is most likely not the case if you give your tools to business users. For them, you’ll want to hide away the Python part so that the Excel tool feels like a normal macro-enabled workbook again. This chapter shows you how to accomplish that with xlwings. I’ll start by showing you the shortest path to run Python code in Excel before looking at the challenges of deploying xlwings tools—​this will also allow us to have a more detailed look at the configuration that xlwings offers. Like the last chapter, this chapter requires you to have an installation of Microsoft Excel on either Windows or macOS.

Using Excel as Frontend

The frontend is the part of an application that users see and interact with. Other common names for the frontend are graphical user interface (GUI) or just user interface (UI). When I ask xlwings users why they choose Excel over a modern web app, the answer is usually along the following lines: “Excel is the interface that our users are familiar with.” Relying on spreadsheet cells allows the users to provide inputs quickly and intuitively, making them often more productive than if they had to use a half-baked web interface. I’ll start this section by introducing you to the xlwings add-in and the xlwings ...