Python for Excel

Chapter 8. Excel File Manipulation with Reader and Writer Packages

This chapter introduces you to OpenPyXL, XlsxWriter, pyxlsb, xlrd, and xlwt: these are the packages that can read and write Excel files and are used by pandas under the hood when you call the read_excel or to_excel functions. Using the reader and writer packages directly allows you to create more complex Excel reports as well as fine-tune the reading process. Also, should you ever work on a project where you only need to read and write Excel files without the need for the rest of the pandas functionality, installing the full NumPy/pandas stack would probably be overkill. We’ll start this chapter by learning when to use which package and how their syntax works before looking at a few advanced topics, including how to work with big Excel files and how to combine pandas with the reader and writer packages to improve the styling of DataFrames. To conclude, we will pick up the case study from the beginning of the last chapter again and enhance the Excel report by formatting the table and adding a chart. Like the last chapter, this chapter does not require an installation of Excel, which means that all code samples run on Windows, macOS, and Linux.

The Reader and Writer Packages

The reader and writer landscape can be a bit overwhelming: we are going to look at no less than six packages in this section as almost every Excel file type requires a different package. The fact that each package uses a different syntax that ...