Python for Excel

Chapter 9. Excel Automation

So far, we’ve learned how to replace typical Excel tasks with pandas (Part II) and how to use Excel files both as a data source as well as a file format for your reports (Part III). This chapter kicks off Part IV, where we switch away from manipulating Excel files with the reader and writer packages and begin automating the Excel application with xlwings.

The main use case of xlwings is to build interactive applications where Excel spreadsheets act as the user interface, allowing you to call Python by clicking a button or calling a user-defined function—that’s the type of functionality that isn’t covered by the reader and writer packages. But that doesn’t mean that xlwings can’t be used to read and write files, as long as you are on either macOS or Windows and have Excel installed. One advantage that xlwings has in this area is the ability to truly edit Excel files, in all formats, without changing or losing any of the existing content or formatting. Another advantage is that you can read the cell values from an Excel workbook without the need to save it first. It can, however, also make perfect sense to use an Excel reader/writer package and xlwings together, as we will see when we pick up the reporting case study from Chapter 7 one more time.

I’ll start this chapter by introducing you to the Excel object model as well as xlwings: we’ll first learn the basics like connecting to a workbook or reading and writing cell values before digging a bit deeper ...