Automate Excel Tasks with Python

So, after learning Python coding, what kind of results can you produce?

In this lesson, we will introduce how to handle Excel(.xlsx) files with Python code.

Typing directly into the Excel program is faster when entering simple data.

However, for entering and modifying large-scale data or performing repetitive tasks, handling Excel tasks with Python code is much more efficient.

You don't need to understand all the code yet!

Press the ▶︎ Run button marked in green on the code editor to get a practical feel for the environment first, then lightly read through the code explanations below 🙂

Light Code Explanation

openpyxl is a library used to create or modify Excel files with Python code.

A library is a collection of pre-written code that performs specific functions, allowing you to use necessary features without writing all the code from scratch.

If you're curious about how the code works on a larger scale, please refer to the following content.

1. Creating an Excel File

Reading an Excel File

# Import the Workbook function from the openpyxl library
from openpyxl import Workbook

# Create an Excel file using the imported Workbook function
wb = Workbook()

# Select the active sheet
ws = wb.active

In the above code, wb refers to the workbook (Excel file), and ws refers to the worksheet (Excel sheet).

2. Defining Data

Data to be displayed in Excel can be defined as follows:

Data to Input into Excel

data = [
    ['Month', 'Sales'],
    ['January', 30],
    ['February', 40],
    ['March', 50],
    ['April', 20],
    ['May', 60],
]

For reference, data enclosed in square brackets ([ ]) is called a list in Python.

More detailed information on this will be covered in the Python introductory lesson.

The above code example defines the data to be handled by Excel directly within the Python code, but a more common method is to read the data from externally stored .xlsx and .csv files on the computer.

3. Inputting Data

Entering Data into Excel

# Insert data using a loop
for row in data:
    ws.append(row)

The above code inputs the Month and Sales data into the created worksheet (ws) using a loop.

In programming, a loop refers to code that repeatedly performs the same task.

4. Creating a Chart

Based on the data to be displayed in Excel, create a Line Chart.

For clarity in this lesson, we drew a chart with simple data consisting of five items from January to May.

However, when handling large-scale data, using Python coding allows you to process the data much more efficiently.

Visualizing Data with a Chart

# Import chart-related functions from the openpyxl library
from openpyxl.chart import LineChart, Reference

# Create a chart
line_chart = LineChart()

# Reference the chart data (from B2 to B6)
data = Reference(ws, min_col=2, min_row=2, max_row=6)

# Reference the X-axis (category) labels (from A2 to A6)
cats = Reference(ws, min_col=1, min_row=2, max_row=6)

...(rest of code)...

# Add the chart to the sheet (at cell D1)
ws.add_chart(line_chart, "A10")

5. Saving the File

Based on the work done so far, create an .xlsx file to save the work.

Saving the Excel File

# Save the Excel file
wb.save("output_file.xlsx")

Note: To verify the contents of the work done by the code in the CodeFriends exercise screen, be sure to name the file output_file!

Through this simple Excel task automation example, we have seen what content can be learned through the lesson.

In the next lesson, we will introduce how to handle PPT files with Python.