Automate Your Mundane Excel Reporting with Python | by Nik Piepenbreier | May, 2022

Let’s break down what we did here:

1. In Section 1, we first loaded the path where the files are saved and used the os.list() function to get a list of all the files contained in that folder
2. In Section 2, we first created an empty DataFrame then looped over each file, loaded it into a DataFrame and appended it to our combined DataFrame
3. Finally, we saved the file to its own Excel file

That was easy, wasn’t it? There are many different ways to accomplish this task, and that’s the beauty of Python!

Now that our data is loaded, let’s learn how to summarize our data with Pandas.

In this section, you’ll learn how to use Pandas to create a summary table! For this, we’ll use the aptly-named Pandas .pivot_table() function.

The function is designed to feel familiar with pivot tables in Excel. With this, we can figure out how we’d like to summarize our data. Let’s say we wanted to figure out what the total amount sold by each Salesperson was. We could write the following:

In the code above, we used the Pandas .pivot_table() function to create a pivot table. Much of this should feel similar to an Excel pivot table. By default, however, Pandas will use an aggregation function of 'mean', so we need to set it to 'sum'.

In this section, you’ll learn how to add descriptive header rows to your Excel reports, in order to make them more print-ready. For this, we’ll start using the Openpyxl library.

Openpyxl works by loading the workbook into memory. From there, you can access different attributes and objects within it, such as worksheets and the cells within these worksheets.

The key difference here is that you’re working directly with the workbook. With Pandas, we simply saved to the workbook. (I’ll admit there’s much more to this, but it’s a good way of thinking about it.)

In the code above:

1. We loaded a workbook object, wb. From there, we accessed the worksheet.
2. We were able to manipulate the worksheet by using the .insert_rows() method to insert three rows.
3. These rows were added at index 0 (the first row) and included three rows. We assigned helpful values to two cells.
4. Finally, we saved the workbook using the Openpyxl .save() method.

In this section, we’ll take a look at adding a chart to the Excel file. One of the great things about the Openpyxl library is that we can create Excel-based charts, that remain dynamic to the data.

In order to make our chart dynamic, we need to create Reference() objects which, as the name might imply, hold references to places in our workbook.

We’ll be creating a bar chart and affixing both data and categories to the workbook. Let’s take a look at how we can do this:

Let’s break down what the code above does:

1. We set two Referenceobjects: one for our data and one for our categories. These Reference objects will tie our graph to specific cells, allowing them to remain dynamic.
2. We add a BarChartobject. To the chart, we use the .add_data() and .set_categories() methods to pass in our Reference objects.
3. Finally, we add the chart to specific anchor point on our worksheet.

In this final section, we’ll take a look at how we can style our workbook using OpenPyxl. OpenPyxl can add styles to cells in an Excel workbook based on the styles that exist in Excel.

This means that we can add styles like Title and currency. Let’s see how this works, using the .style attribute:

In the example above, we used the .style attribute to assign different styles to different cells. The reason we use the for loop in cells 5–6 is that OpenPyxl doesn’t let you assign styles to ranges, so we assign them one at a time.

Great! You’ve automated your boring Excel reporting! Now, what should you look at next? I’d recommend thinking about other elements that you may need to automate. For example:

• How can you add names to worksheets?
• How can you email your resulting file automatically?
• How can you style values as tables?

Thanks for reading all the way through the tutorial! I hope that you enjoyed it and that you learned something. Using Python to automate your work can be a really rewarding thing to learn. If you want to learn more about it, please consider subscribing to my YouTube channel by clicking here.

Let’s break down what we did here:

1. In Section 1, we first loaded the path where the files are saved and used the os.list() function to get a list of all the files contained in that folder
2. In Section 2, we first created an empty DataFrame then looped over each file, loaded it into a DataFrame and appended it to our combined DataFrame
3. Finally, we saved the file to its own Excel file

That was easy, wasn’t it? There are many different ways to accomplish this task, and that’s the beauty of Python!

Now that our data is loaded, let’s learn how to summarize our data with Pandas.

In this section, you’ll learn how to use Pandas to create a summary table! For this, we’ll use the aptly-named Pandas .pivot_table() function.

The function is designed to feel familiar with pivot tables in Excel. With this, we can figure out how we’d like to summarize our data. Let’s say we wanted to figure out what the total amount sold by each Salesperson was. We could write the following:

In the code above, we used the Pandas .pivot_table() function to create a pivot table. Much of this should feel similar to an Excel pivot table. By default, however, Pandas will use an aggregation function of 'mean', so we need to set it to 'sum'.

In this section, you’ll learn how to add descriptive header rows to your Excel reports, in order to make them more print-ready. For this, we’ll start using the Openpyxl library.

Openpyxl works by loading the workbook into memory. From there, you can access different attributes and objects within it, such as worksheets and the cells within these worksheets.

The key difference here is that you’re working directly with the workbook. With Pandas, we simply saved to the workbook. (I’ll admit there’s much more to this, but it’s a good way of thinking about it.)

In the code above:

1. We loaded a workbook object, wb. From there, we accessed the worksheet.
2. We were able to manipulate the worksheet by using the .insert_rows() method to insert three rows.
3. These rows were added at index 0 (the first row) and included three rows. We assigned helpful values to two cells.
4. Finally, we saved the workbook using the Openpyxl .save() method.

In this section, we’ll take a look at adding a chart to the Excel file. One of the great things about the Openpyxl library is that we can create Excel-based charts, that remain dynamic to the data.

In order to make our chart dynamic, we need to create Reference() objects which, as the name might imply, hold references to places in our workbook.

We’ll be creating a bar chart and affixing both data and categories to the workbook. Let’s take a look at how we can do this:

Let’s break down what the code above does:

1. We set two Referenceobjects: one for our data and one for our categories. These Reference objects will tie our graph to specific cells, allowing them to remain dynamic.
2. We add a BarChartobject. To the chart, we use the .add_data() and .set_categories() methods to pass in our Reference objects.
3. Finally, we add the chart to specific anchor point on our worksheet.

In this final section, we’ll take a look at how we can style our workbook using OpenPyxl. OpenPyxl can add styles to cells in an Excel workbook based on the styles that exist in Excel.

This means that we can add styles like Title and currency. Let’s see how this works, using the .style attribute:

In the example above, we used the .style attribute to assign different styles to different cells. The reason we use the for loop in cells 5–6 is that OpenPyxl doesn’t let you assign styles to ranges, so we assign them one at a time.

Great! You’ve automated your boring Excel reporting! Now, what should you look at next? I’d recommend thinking about other elements that you may need to automate. For example:

• How can you add names to worksheets?
• How can you email your resulting file automatically?
• How can you style values as tables?

Thanks for reading all the way through the tutorial! I hope that you enjoyed it and that you learned something. Using Python to automate your work can be a really rewarding thing to learn. If you want to learn more about it, please consider subscribing to my YouTube channel by clicking here.