lesson1Title

lesson2Title

lesson3Title

lesson4Title

lesson5Title

lesson6Title

lesson7Title

lesson8Title

lesson9Title

lesson10Title

automationIntroOtChapter1Title

from openpyxl import Workbook
from openpyxl.chart import LineChart, Reference

# Create a new workbook
wb = Workbook()

# Select the active sheet
ws = wb.active

# Enter data (e.g., monthly sales)
data = [
    ['Month', 'Sales'],
    ['January', 30],
    ['February', 40],
    ['March', 50],
    ['April', 20],
    ['May', 60],
]

for row in data:
    ws.append(row)

# Create a chart
line_chart = LineChart()

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

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

# Do not include title in data
line_chart.add_data(data, titles_from_data=False)
line_chart.set_categories(cats)
line_chart.title = "Monthly Sales"
line_chart.x_axis.title = "Month"
line_chart.y_axis.title = "Sales"

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

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

# 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

```python title="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).

 

### 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

```python title="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.

```python title="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.

```python title="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.