Automating 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.

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

However, for handling large datasets or repetitive tasks, Python is far more efficient.

No need to fully understand the code yet!

Click the green ▶︎ Run button in the code editor to explore the environment first, then skim through the code explanations below 🙂

How to Skim Through the Code

openpyxl is a library that allows Python to create and modify Excel files.

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

Curious about how the code works in a bigger project? Check out the following details.

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 this code, `wb` refers to the workbook (Excel file), and ws refers to the worksheet (Excel sheet).

<br />

### 2. Defining Data

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

```python title="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 code above 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 code above 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 check the work done in code on the CodeFriends practice screen, please make sure to specify the file name as output_file!

Through this simple Excel task automation example, we have seen what kind of content can be learned throughout this course.

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