Skip to main content
Practice

Exploring Key Methods of openpyxl

The Workbook object represents the entire Excel file.

Typically, after loading an Excel file with the load_workbook function, the loaded workbook is stored in the wb variable (short for Workbook).

The Sheet object represents each sheet (tab) within the Excel file.

Typically, a specific sheet from the workbook is stored in the ws variable (short for Worksheet), allowing you to manipulate the sheet's data.

In this lesson, we will learn about the key methods of openpyxl.


active Method​

The active method is used to get the currently active sheet from the workbook object, as in wb.active.

When you open an Excel file, the first sheet you see is the active sheet.

Example of using the active method
from openpyxl import load_workbook

# Load the Excel file
wb = load_workbook('input_file.xlsx')

# Get the currently active sheet
ws = wb.active

print(ws.title)  # Print the name of the active sheet

You can use the title attribute to set or change the name of a sheet.

Example of using the title attribute
# Change sheet name
ws.title = "MySheet"

print(ws.title)  # Print the new name of the sheet

create_sheet Method​

The create_sheet method creates a new sheet.

By default, the newly created sheet is added at the end, but you can also add it at a specific position.

Example of using the create_sheet method
# Add a sheet at the end
new_sheet = wb.create_sheet("NewSheet")

# Add a sheet at the first position
first_sheet = wb.create_sheet("FirstSheet", 0)

wb.save('output_file.xlsx')

rows Method​

The rows method retrieves all rows in the sheet. This allows you to easily process sheet data row by row.

Example of using the rows method
# Iterate over all rows in the sheet and print values
for row in ws.rows:
    for cell in row:
        print(cell.value)

columns Method​

The columns method retrieves all columns in the sheet. Similar to the rows method, you can process data column by column.

Example of using the columns method
# Iterate over all columns in the sheet and print values
for col in ws.columns:
    for cell in col:
        print(cell.value)

copy_worksheet Method​

The copy_worksheet method copies an existing sheet to create a new sheet.

Using this method allows you to quickly duplicate sheets with similar structures.

Example of using the copy_worksheet method
from openpyxl import load_workbook

wb = load_workbook('input_file.xlsx')

# Get the currently active sheet
ws = wb.active

print(ws.title)  # Print the name of the active sheet

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.