Automating Excel with Python

The first step in learning to automate Excel with Python is to learn how to read an Excel spreadsheet.

In this lesson, you will learn how to do the following tasks:

• Open a spreadsheet

• Read specific cells

• Read cells from a specific row

• Read cells from a specific column

• Read cells from multiple rows or columns

• Read cells from a range

• Read all cells in all sheets

Code examples are available on GitHub: https://github.com/driscollis/automating_excel_with_python/tree/main/02_reading_spreadsheets

Learn how to create Excel spreadsheets with Python and OpenPyXL.

In this lesson, you will learn how to use OpenPyXL to do the following:

• Create a spreadsheet

• Write to a spreadsheet

• Add and remove sheets

• Insert and delete rows and columns

• Edit cell data

• Create merged cells

OpenPyXL gives you the ability to style your cells in many different ways. Styling cells will give your spreadsheets pizazz! Your spreadsheets can have some pop and zing to them that will help differentiate them from others. However, don’t go overboard! Your spreadsheet would look messy if every cell had a different font and color.

You should use the skills that you learn in this chapter sparingly. You’ll still have beautiful spreadsheets that you can share with your colleagues. If you would like to learn more about what styles OpenPyXL supports, you should check out their documentation.

In this lesson, you will learn about the following:

• Working with fonts

• Setting the alignment

• Adding a border

• Changing the cell background-color

• Inserting images into cells

• Styling merged cells

• Using a built-in style

• Creating a custom named style

Microsoft Excel has the concept of conditional formatting. You apply conditional formatting to a range of cells. You can set up rules for the formatting, such as changing the background color of a row if it contains a specific string. You can also set cell formatting based on a formula-type condition. There are many different ways to set up conditional formatting using OpenPyXL.

Microsoft Excel supports three different types of conditional formatting:

• Builtins – combines specific rules with predefined cell styles

• Standard – combines specific rules with custom formatting

• Custom – uses custom formulae to apply custom formats using different styles.

This lesson will focus on built-in formats. In the package’s documentation, you can read more about OpenPyXL’s support for Excel’s conditional formatting.

You will learn about the following in this lesson:

• Builtin formats

• Working with ColorScales

• Adding IconSets

• Creating a DataBar

• Using DifferentialStyles

Let’s get started by learning about built-in formats!

OpenPyXL supports many different kinds of charts. These charts are an excellent way to convey information to the user and help them understand the data in the spreadsheet. The most common charts include bar, pie, and line charts.

This lesson’s focus is to familiarize you with how to create a chart and edit its many different parts. When you create a chart, you must define the type of chart you want to use, such as BarChart or LineChart. You will also need to provide the data the chart uses, called a Reference.

In this lesson, you will learn how to:

• Add Titles to a chart

• Change axis orientation

• Modify chart layout

• Change the chart size

• Use Styles

• Create Chartsheets

Let’s get started by creating your first chart!

There are many different kinds of charts that you can create using OpenPyXL. These different chart types give you many different ways to visualize and display your data to the user. You can help users better understand their data by putting it into a chart.

In this lesson, you will learn how to create the following chart types:

• Area Charts

• Bar Charts

• Bubble Charts

• Line Charts

• Scatter Charts

• Pie Charts

• Doughnut Charts

• Radar Charts

• Surface Charts

You won’t learn every idiosyncrasy with every chart, but you will learn how to create the most supported chart types. Doing so will allow you to visualize your data in many different ways. Once you have learned about all the different types, you can experiment with them and see which ones work best for your audience.

WARNING: Some chart examples in this chapter do not work in non-Excel spreadsheets such as LibreOffice. Also, some examples may vary slightly in color or icon shape between Microsoft Excel versions or platforms (i.e. Mac vs Windows)

The first chart type you will learn about is the area chart!

You will need to work with many common file types as a software developer. One such format is the CSV file. CSV stands for “Comma-Separated Values” and is a text file format that uses a comma as a delimiter to separate values from one another. Each row is its record and each value is its field. Most CSV files have records that are all the same length.

Microsoft Excel opens CSV files with no problem. You can open one with Excel and then save it in an Excel format. The purpose of this lesson is to teach you the following concepts:

• Converting a CSV file to Excel

• Converting an Excel spreadsheet to CSV

You will use Python to convert from one file type to the other.

OpenPyXL is not the only tool you can use to read and write Excel spreadsheets. One popular package used by the scientific Python community that can read and write Excel spreadsheets is Pandas.

If you look in the Pandas documentation, you will discover that it uses the following Python packages for reading and writing Excel:

• xlrd – Reading Excel

• xlwt – Writing Excel (xls)

• xlsxwriter – Writing Excel

• OpenPyXL – Reading and Writing (xlsx)

• pyxlsb – Reading xlsb files

Pandas doesn’t have Excel file manipulation built-in. Instead, Pandas is off-loading that functionality to one or more external Python packages. When you search for reading and writing Excel files, you will discover that Pandas is a popular choice. The reason is that Pandas is incredibly popular for data analysis, and it provides a thin wrapper around other packages.

Pandas makes working with Excel easier for some developers because they already use Pandas and don’t need to switch to another tool.

In this lesson, you will learn how to use Pandas to do the following:

• Read Excel spreadsheets

• Read multiple Excel worksheets

• Write DataFrames to Excel

• Convert CSV to Excel with Pandas

Your first step is to install Pandas so you can start using it to read and write Excel!

Thanks so much for purchasing this course. You made it to the end. At this point, you can now use Python to automate Excel spreadsheets!

If you completed the entire course, you should be able to do all of the following

• Read and write Excel spreadsheets

• Style cells

• Change fonts

• Add borders

• Insert images

• Add charts

• Apply conditional formatting

• Add Sparklines

• Convert between multiple formats (CSV to Excel)

• Load Excel into Pandas for data analysis