automating-excel-with-python-review/index.html

<!doctype html><html lang=en><head><meta content="IE=edge" http-equiv=X-UA-Compatible><meta content="text/html; charset=utf-8" http-equiv=content-type><meta content="width=device-width,initial-scale=1.0,maximum-scale=1" name=viewport><title>Automating Excel with Python - book review</title><link href=https://learnbyexample.github.io/atom.xml rel=alternate title=RSS type=application/atom+xml><script src=https://cdnjs.cloudflare.com/ajax/libs/slideout/1.0.1/slideout.min.js></script><link href=https://learnbyexample.github.io/site.css rel=stylesheet><meta content="Automating Excel with Python - book review" property=og:title><meta content=website property=og:type><meta content="For those wanting to use Python for automating spreadsheets" property=og:description><meta content=https://learnbyexample.github.io/automating-excel-with-python-review/ property=og:url><meta content=https://learnbyexample.github.io/images/automating_excel/automating_excel_with_python.jpg property=og:image><meta content=655 property=og:image:width><meta content=843 property=og:image:height><meta content=summary_large_image property=twitter:card><meta content=@learn_byexample property=twitter:site><link href=https://learnbyexample.github.io/favicon.svg rel=icon><link rel="shortcut icon" href=https://learnbyexample.github.io/favicon.png><body><div class=container><div class=mobile-navbar id=mobile-navbar><div class=mobile-header-logo><a class=logo href=/>learnbyexample</a></div><div class="mobile-navbar-icon icon-out"><span></span><span></span><span></span></div></div><nav class="mobile-menu slideout-menu slideout-menu-left" id=mobile-menu><ul class=mobile-menu-list><li class=mobile-menu-item><a href=https://learnbyexample.github.io/books> Books </a><li class=mobile-menu-item><a href=https://learnbyexample.github.io/mini> Mini </a><li class=mobile-menu-item><a href=https://learnbyexample.github.io/tips> Tips </a><li class=mobile-menu-item><a href=https://learnbyexample.github.io/tags> Tags </a><li class=mobile-menu-item><a href=https://learnbyexample.github.io/about> About </a></ul></nav><header id=header><div class=logo><a href=https://learnbyexample.github.io>learnbyexample</a></div><nav class=menu><ul><li><a href=https://learnbyexample.github.io/books> Books </a><li><a href=https://learnbyexample.github.io/mini> Mini </a><li><a href=https://learnbyexample.github.io/tips> Tips </a><li><a href=https://learnbyexample.github.io/tags> Tags </a><li><a href=https://learnbyexample.github.io/about> About </a></ul></nav></header><main><div class=content id=mobile-panel><div class=post-toc id=post-toc><h2 class=post-toc-title>Contents</h2><div class="post-toc-content always-active"><nav id=TableOfContents><ul><li><a class=toc-link href=https://learnbyexample.github.io/automating-excel-with-python-review/#book-details>Book details</a><li><a class=toc-link href=https://learnbyexample.github.io/automating-excel-with-python-review/#review>Review</a><li><a class=toc-link href=https://learnbyexample.github.io/automating-excel-with-python-review/#table-of-contents>Table of Contents</a><li><a class=toc-link href=https://learnbyexample.github.io/automating-excel-with-python-review/#feedback-and-reviews>Feedback and Reviews</a></ul></nav></div></div><article class=post><header class=post__header><h1 class=post__title><a href=https://learnbyexample.github.io/automating-excel-with-python-review/>Automating Excel with Python - book review</a></h1><div class=post__meta><span class=post__time>2022-01-11</span></div></header><div class=post-content><p>In this post, I review <strong>Automating Excel with Python</strong> by <a href=https://www.blog.pythonlibrary.org/>Michael Driscoll</a>. From the introduction chapter of this book:<blockquote><p>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.</blockquote><span id=continue-reading></span><h2 id=book-details>Book details<a aria-label="Anchor link for: book-details" class=zola-anchor href=#book-details>🔗</a></h2><p align=center><img alt="Automating Excel with Python book cover" src=/images/automating_excel/automating_excel_with_python.jpg><p align=center>Book cover<ul><li><a href=https://www.amazon.com/dp/B09M5551W2>Amazon</a> — Paperback, Kindle<li><a href=https://driscollis.gumroad.com/l/openpyxl>Gumroad</a> — PDF, EPUB, Mobi<li><a href=https://leanpub.com/openpyxl>Leanpub</a> — PDF, EPUB, Mobi<li><a href=https://github.com/driscollis/automating_excel_with_python>GitHub</a> — code examples and sample spreadsheets used in the book<li><a href=https://www.goodreads.com/book/show/59974445-automating-excel-with-python>Goodreads</a> — book reviews</ul><h2 id=review>Review<a aria-label="Anchor link for: review" class=zola-anchor href=#review>🔗</a></h2><p>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.<p>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:<ul><li>Creating <code>xlsx</code> files with multiple sheets<li>Adding data<li>Formatting cells based on a known equation<li>Creating charts</ul><p>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:<p align=center><img alt="Bar Chart in Excel" height=400px src=/images/automating_excel/bar_chart_excel.png width=600px><p align=center>Bar Chart in Excel (snapshot from the book)<p align=center><img alt="Bar Chart in Calc" height=400px src=/images/automating_excel/bar_chart_calc.png width=600px><p align=center>Bar Chart in LibreOffice Calc (what I got on my machine)</p><br><p>Apart from the <code>openpyxl</code> module, the author also briefly covered how you can use <code>pandas</code>, <code>xlsxwriter</code> and <code>gspread</code> (for working with Google sheets). Some features were presented at the end as Appendix chapters.<h2 id=table-of-contents>Table of Contents<a aria-label="Anchor link for: table-of-contents" class=zola-anchor href=#table-of-contents>🔗</a></h2><ul><li>Introduction<li>Chapter 1 - Setting Up Your Machine<li>Chapter 2 - Reading Spreadsheets with OpenPyXL<li>Chapter 3 - Creating a Spreadsheet with OpenPyXL<li>Chapter 4 - Styling Cells<li>Chapter 5 - Conditional Formatting<li>Chapter 6 - Creating Charts<li>Chapter 7 - Chart Types<li>Chapter 8 - Converting CSV to Excel<li>Chapter 9 - Using Pandas with Excel<li>Chapter 10 - Python and Google Sheets<li>Chapter 11 - XlsxWriter<li>Appendix A - Cell Comments<li>Appendix B - Print Settings Basics<li>Appendix C - Formulas</ul><h2 id=feedback-and-reviews>Feedback and Reviews<a aria-label="Anchor link for: feedback-and-reviews" class=zola-anchor href=#feedback-and-reviews>🔗</a></h2><p>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.<p>Happy learning :)</div><div class=post-footer><div class=post-tags><a href=https://learnbyexample.github.io/tags/python/>#python</a><a href=https://learnbyexample.github.io/tags/excel/>#excel</a><a href=https://learnbyexample.github.io/tags/openpyxl/>#openpyxl</a><a href=https://learnbyexample.github.io/tags/pandas/>#pandas</a><a href=https://learnbyexample.github.io/tags/book-review/>#book-review</a></div><hr color=#e6e6e6><div class=post-nav><p><a class=previous href=https://learnbyexample.github.io/duplicates-irrespective-field-order/>← Removing duplicates irrespective of field order</a><br><p><a class=next href=https://learnbyexample.github.io/wild-ride-2021/>2021 was a wild ride →</a><br></div><hr color=#e6e6e6><p>📰 Use <a href=https://learnbyexample.github.io/atom.xml>this link</a> for the Atom feed. <br> ✅ Follow me on <a href=https://twitter.com/learn_byexample>Twitter</a>, <a href=https://github.com/learnbyexample>GitHub</a> and <a href=https://www.youtube.com/c/learnbyexample42>Youtube</a> for interesting tech nuggets. <br> 📧 Subscribe to <a href=https://learnbyexample.gumroad.com/l/learnbyexample-weekly>learnbyexample weekly</a> for programming resources, tips, tools, free ebooks and more (free newsletter, delivered every Friday).<hr color=#e6e6e6></div></article></div></main></div><script src=https://learnbyexample.github.io/even.js></script>