Python for Excel

Chapter 10. 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 Anaconda Prompt or an editor like VS Code to run the scripts. This is most likely not the case if your tools are used by 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. How you achieve that with xlwings is the topic of this chapter. I’ll start by showing you the shortest path to run Python code from Excel before looking at the challenges of deploying xlwings tools—this will also allow us to have a more detailed look at the available settings 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 with xlwings

The frontend is the part of an application that a user sees and interacts with. Other common names for frontend are graphical user interface (GUI) or just user interface (UI). When I ask xlwings users why they are creating their tool with Excel rather than building a modern web application, what I usually hear is this: “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 have to use a half-baked web interface. I’ll start this section ...