Workbook and Worksheet Objects

Workbook Object

A Workbook is the top-level object in OpenPyXL, representing a complete Excel file. Each Excel file is a Workbook object that can contain multiple Worksheets.

Creating a New Workbook

from openpyxl import Workbook

# Create a new workbook
wb = Workbook()

# A worksheet named "Sheet" is automatically created by default
ws = wb.active

# Save the workbook
wb.save('new_workbook.xlsx')

Opening an Existing Workbook

from openpyxl import load_workbook

# Open an existing Excel file
wb = load_workbook('existing_file.xlsx')

# Open in read-only mode (suitable for large files)
wb = load_workbook('large_file.xlsx', read_only=True)

# Open while preserving formulas (default calculates formula values)
wb = load_workbook('file_with_formulas.xlsx', data_only=False)

Tip: For large Excel files, using read_only=True can significantly reduce memory usage and loading time.

Common Workbook Properties and Methods

# Get all worksheet names
sheet_names = wb.sheetnames
print(sheet_names)  # ['Sheet1', 'Sheet2', 'Sheet3']

# Get active worksheet
active_sheet = wb.active

# Create new worksheet
new_sheet = wb.create_sheet('NewSheet')
new_sheet_at_index = wb.create_sheet('FirstSheet', 0)  # Create at specified position

# Get specified worksheet
sheet = wb['Sheet1']

# Delete worksheet
wb.remove(sheet)
# Or
del wb['Sheet1']

# Copy worksheet
source_sheet = wb['Sheet1']
target_sheet = wb.copy_worksheet(source_sheet)

# Save workbook
wb.save('output.xlsx')

# Close workbook (release resources)
wb.close()

Worksheet Object

A Worksheet is a child object of a Workbook, representing a worksheet in an Excel file. Each worksheet contains a grid of cells organized in rows and columns.

Accessing Worksheets

# Access by name
ws = wb['Sheet1']

# Get active worksheet
ws = wb.active

# Iterate through all worksheets
for sheet in wb:
    print(sheet.title)

Worksheet Properties

# Get worksheet title
title = ws.title

# Modify worksheet title
ws.title = 'Sales Data'

# Get maximum row and column numbers
max_row = ws.max_row
max_col = ws.max_column

# Get worksheet dimensions (range containing data)
dimensions = ws.dimensions  # Example: 'A1:D10'

# Set worksheet tab color
ws.sheet_properties.tabColor = "1072BA"  # Blue

Worksheet Operations

# Insert rows
ws.insert_rows(1)  # Insert one row before row 1
ws.insert_rows(5, 3)  # Insert 3 rows before row 5

# Delete rows
ws.delete_rows(1)  # Delete row 1
ws.delete_rows(5, 3)  # Delete 3 rows starting from row 5

# Insert columns
ws.insert_cols(1)  # Insert one column before column 1
ws.insert_cols(3, 2)  # Insert 2 columns before column 3

# Delete columns
ws.delete_cols(1)  # Delete column 1
ws.delete_cols(3, 2)  # Delete 2 columns starting from column 3

# Move cell range
ws.move_range("A1:D10", rows=2, cols=3)  # Move down 2 rows, right 3 columns

Freeze Panes

# Freeze first row
ws.freeze_panes = 'A2'

# Freeze first column
ws.freeze_panes = 'B1'

# Freeze first row and first column
ws.freeze_panes = 'B2'

# Unfreeze
ws.freeze_panes = None

Print Settings

# Set print area
ws.print_area = 'A1:D20'

# Set print title rows
ws.print_title_rows = '1:1'  # Print first row on every page

# Set print title columns
ws.print_title_cols = 'A:A'  # Print first column on every page

# Set page orientation
ws.page_setup.orientation = ws.ORIENTATION_LANDSCAPE  # Landscape

# Set paper size
ws.page_setup.paperSize = ws.PAPERSIZE_A4

# Set scaling
ws.page_setup.fitToPage = True
ws.page_setup.fitToHeight = 1
ws.page_setup.fitToWidth = 1

Note: Deleting rows or columns affects formula references. Make sure to back up important data before deletion.

Worksheet Protection

OpenPyXL supports worksheet protection to prevent unauthorized modifications.

# Protect worksheet
ws.protection.sheet = True
ws.protection.password = 'secret'

# Set protection options
ws.protection.enable()
ws.protection.set_password('secret')

# Allow specific operations
ws.protection.formatCells = False  # Allow cell formatting
ws.protection.formatColumns = False  # Allow column formatting
ws.protection.formatRows = False  # Allow row formatting
ws.protection.insertColumns = False  # Allow column insertion
ws.protection.insertRows = False  # Allow row insertion
ws.protection.deleteColumns = False  # Allow column deletion
ws.protection.deleteRows = False  # Allow row deletion

# Unprotect
ws.protection.sheet = False

Practical Tips

1. Batch Create Worksheets

months = ['January', 'February', 'March', 'April']
for month in months:
    wb.create_sheet(month)

2. Rename Worksheets

# Batch rename worksheets
for i, sheet in enumerate(wb.worksheets, 1):
    sheet.title = f'Sheet_{i}'

3. Check if Worksheet Exists

if 'Sales' in wb.sheetnames:
    ws = wb['Sales']
else:
    ws = wb.create_sheet('Sales')

Best Practices:

• Use meaningful worksheet names, avoid default "Sheet1", "Sheet2"
• For large files, use read_only mode to improve performance
• Save workbooks regularly to avoid data loss
• Use with statement to automatically manage file resources

📚 Workbook and Worksheet Objects