In this post, I review Automating Excel with Python by Michael Driscoll. From the introduction chapter of this book:

The purpose of this book is to help you learn how to use Python to work with Excel. You will be using a package called OpenPyXL to create, read, and edit Excel documents with Python. While the focus of this book will be on OpenPyXL, you will also learn about other Python packages that you can use to interact with Excel using the Python programming language.

Book details🔗

Automating Excel with Python book cover

Book cover


My very first job assignment (at a semiconductor company) required me to use spreadsheets for tabulating results of various experiments, adding charts, etc. I used to manually copy-paste the results generated from a Perl script. There were multiple sheets and my work was complicated enough to require multiple months of refinement, feature modifications, etc. Not sure if a library like OpenPyXL existed back then, but I think I should've at least asked/searched ways to automate the spreadsheet process.

Going through this book felt like someone wrote a book just for that project, albeit 13 years late. Here's a rough list of features that would've helped me:

  • Creating xlsx files with multiple sheets
  • Adding data
  • Formatting cells based on a known equation
  • Creating charts

Instructions and examples were clear and easy to follow. Snapshots were also shown for all the examples, so you can check if you've followed along as expected. While the book is best suited if you have MS Excel, most of the examples worked for me on LibreOffice Calc. Only the charts had major differences — some types weren't supported and x/y axis label/data were problematic as shown below:

Bar Chart in Excel

Bar Chart in Excel (snapshot from the book)

Bar Chart in Calc

Bar Chart in LibreOffice Calc (what I got on my machine)

Apart from the openpyxl module, the author also briefly covered how you can use pandas, xlsxwriter and gspread (for working with Google sheets). Some features were presented at the end as Appendix chapters.

Table of Contents🔗

  • Introduction
  • Chapter 1 - Setting Up Your Machine
  • Chapter 2 - Reading Spreadsheets with OpenPyXL
  • Chapter 3 - Creating a Spreadsheet with OpenPyXL
  • Chapter 4 - Styling Cells
  • Chapter 5 - Conditional Formatting
  • Chapter 6 - Creating Charts
  • Chapter 7 - Chart Types
  • Chapter 8 - Converting CSV to Excel
  • Chapter 9 - Using Pandas with Excel
  • Chapter 10 - Python and Google Sheets
  • Chapter 11 - XlsxWriter
  • Appendix A - Cell Comments
  • Appendix B - Print Settings Basics
  • Appendix C - Formulas

Feedback and Reviews🔗

All in all, I would highly recommend this book for those wanting to use Python for automating spreadsheets. I'd request you to post reviews after going through the book (they help us indie authors a lot). And please do contact the author to let him know your feedback or if you have any clarifications.

Happy learning :)