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.

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.

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

Copy
# Change sheet name
ws.title = "MySheet"

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

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.

Copy
# 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')

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

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

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

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

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.

Copy
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