Of course! Here is a comprehensive guide to the openpyxl API for Python, covering the most common tasks from basic file operations to advanced features like styling and formulas.

What is openpyxl?

openpyxl is a Python library for reading and writing Excel 2010+ (.xlsx) files. It's powerful, widely used, and allows you to manipulate spreadsheets programmatically. It does not support the older .xls format.

Installation

First, you need to install the library using pip:

pip install openpyxl

Core Concepts: The Workbook, Worksheet, and Cell

Before diving into the API, understand the three main objects:

• Workbook: Represents the entire Excel file. It can contain multiple sheets.
• Worksheet: Represents a single sheet within a workbook (e.g., "Sheet1").
• Cell: Represents a single cell in a worksheet (e.g., A1).

Basic Operations: Reading and Writing

A. Creating a New Workbook and Writing Data

You start by creating a Workbook object. By default, it contains one sheet named "Sheet1".

from openpyxl import Workbook
# Create a new workbook
wb = Workbook()
# Select the active sheet (the one created by default)
ws = wb.active
# You can rename the sheet= "Sales Report"
# Write data to cells
ws['A1'] = "Product"
ws['B1'] = "Q1 Sales"
ws['C1'] = "Q2 Sales"
ws['A2'] = "Laptop"
ws['B2'] = 1200
ws['C2'] = 1350
ws['A3'] = "Mouse"
ws['B3'] = 500
ws['C3'] = 600
# Save the workbook to a file
wb.save("sales_report.xlsx")

B. Reading an Existing Workbook

To read an existing file, you use load_workbook.

from openpyxl import load_workbook
# Load an existing workbook
wb = load_workbook("sales_report.xlsx")
# Select a sheet by its name
ws = wb["Sales Report"]
# Read data from a cell
product_name = ws['A2'].value
q1_sales = ws['B2'].value
print(f"Product: {product_name}, Q1 Sales: {q1_sales}")
# You can also use cell coordinates (row, column)
# ws.cell(row=2, column=1) is the same as ws['A2']
q2_sales = ws.cell(row=2, column=3).value
print(f"Q2 Sales: {q2_sales}")
# Iterate over rows
# The `iter_rows` method is very useful for processing data
print("\n--- Reading all data ---")
for row in ws.iter_rows(min_row=1, max_row=4, min_col=1, max_col=3):
    for cell in row:
        print(f"Cell {cell.coordinate} has value: {cell.value}")

Manipulating Worksheets

You can add, delete, and copy sheets within a workbook.

from openpyxl import Workbook
wb = Workbook()
ws1 = wb.active= "First Sheet"
# Add a new sheet at the end
ws2 = wb.create_sheet("Second Sheet")
# Add a new sheet at a specific position (index 0 means the first sheet)
ws0 = wb.create_sheet("Zeroth Sheet", 0)
# You can also copy a sheet
ws_copy = wb.copy_worksheet(ws1)
ws_copy.title = "Copy of First Sheet"
# Delete a sheet
# wb.remove(ws2) # Note: wb.remove() takes the worksheet object, not its name
# Save to see the changes
wb.save("manipulated_sheets.xlsx")

Manipulating Cell Data and Formulas

Writing Formulas

You write formulas just like any other string, but they must start with an equals sign ().

from openpyxl import Workbook
wb = Workbook()
ws = wb.active= "Formulas"
ws['A1'] = "10"
ws['A2'] = "20"
ws['A3'] = "30"
# Write a formula to sum A1, A2, and A3
ws['A4'] = "=SUM(A1:A3)"
# Write another formula
ws['B1'] = "=A1 * 2"
ws['B2'] = "=A2 * 2"
wb.save("formulas.xlsx")

When you open formulas.xlsx in Excel, cell A4 will display 60, and B1 and B2 will display 20 and 40, respectively.

Data Types

openpyxl tries to infer the data type of a cell's value. You can also set it explicitly if needed, though it's often not necessary.

ws['A1'] = "Hello"       # String
ws['A2'] = 123           # Integer
ws['A3'] = 3.14159       # Float
ws['A4'] = True          # Boolean
ws['A5'] = None          # Empty cell

Styling Cells and Worksheets

This is where openpyxl shines. You can apply rich formatting to make your reports look professional.

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Border, Side, Alignment
wb = Workbook()
ws = wb.active= "Styling"
# --- 1. Font Styling ---
header_font = Font(name='Calibri', size=14, bold=True, color="FF0000") # Red color
ws['A1'].font = header_font
ws['A1'] = "Header with Style"
# --- 2. Fill and Alignment ---
header_fill = PatternFill(start_color="FFFF00", end_color="FFFF00", fill_type="solid")
center_alignment = Alignment(horizontal="center", vertical="center")
ws['A2'].fill = header_fill
ws['A2'].alignment = center_alignment
ws['A2'] = "Centered Yellow Cell"
# --- 3. Borders ---
# Define a thin border
thin_border = Border(
    left=Side(style='thin'),
    right=Side(style='thin'),
    top=Side(style='thin'),
    bottom=Side(style='thin')
)
# Apply borders to a range of cells
for row in ws['A3:D5']:
    for cell in row:
        cell.border = thin_border
ws['A3'] = "Data with borders"
ws['B3'] = "in a range"
ws['C3'] = "from A3 to D5"
# --- 4. Number Formatting ---
# You can format numbers to appear as currency, dates, etc.
# Note: This changes how the number is displayed, not its underlying value.
ws['A6'] = 1234.567
ws['A6'].number_format = '$#,##0.00' # Displays as $1,234.57
ws['B6'] = "2025-10-27"
ws['B6'].number_format = 'MM/DD/YYYY' # Displays as 10/27/2025
wb.save("styled_workbook.xlsx")

Merging and Unmerging Cells

You can merge a range of cells into a single, larger cell.

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
ws.merge_cells('A1:C1') # Merge cells from A1 to C1
ws['A1'] = "This is a merged cell spanning 3 columns"
ws['A1'].alignment = Alignment(horizontal="center")
# To unmerge, use unmerge_cells
# ws.unmerge_cells('A1:C1')
wb.save("merged_cells.xlsx")

Inserting Images

You can insert images into your worksheet.

from openpyxl import Workbook
from openpyxl.drawing.image import Image
wb = Workbook()
ws = wb.active
# Make sure the 'logo.png' file exists in the same directory
try:
    img = Image('logo.png')
    # Add the image to cell E5
    # The coordinates are the top-left cell of the image
    ws.add_image(img, 'E5')
except FileNotFoundError:
    print("Could not find 'logo.png'. Please create a dummy image file to test this.")
wb.save("images.xlsx")

Adjusting Row and Column Dimensions

You can set the height of rows and the width of columns.

from openpyxl import Workbook
wb = Workbook()
ws = wb.active
# Set row height (in points)
# Row 1 will be 30 points high
ws.row_dimensions[1].height = 30
# Set column width (in terms of the number of characters)
# Column A will be 20 characters wide
ws.column_dimensions['A'].width = 20
ws['A1'] = "This column is wider"
ws['B1'] = "This row is taller"
wb.save("dimensions.xlsx")

Performance Tips for Large Files

When working with very large Excel files (e.g., 50,000+ rows), memory usage can become an issue. openpyxl has a special "read-only" and "write-only" mode to help.

Read-Only Mode

This mode loads only the data and formulas, not styles or other formatting, drastically reducing memory.

from openpyxl import load_workbook
# Use read_only=True
wb = load_workbook(filename='large_file.xlsx', read_only=True)
ws = wb.active
# You can still read data
for row in ws.iter_rows(values_only=True):
    print(row)
# You MUST close the workbook when done!
wb.close()

Write-Only Mode

This mode is for creating new files with a lot of data. It avoids storing the entire workbook in memory.

from openpyxl import Workbook
from openpyxl.worksheet.write_only import WriteOnlyWorksheet
wb = Workbook(write_only=True)
ws = wb.create_sheet()
# In write-only mode, you can't access cells directly
# You must append rows
ws.append(['Header 1', 'Header 2', 'Header 3'])
for i in range(10000):
    ws.append([i, f"Item {i}", i * 2])
wb.save("large_write_only_file.xlsx")

This API guide covers the most essential features of openpyxl. For more advanced details, always refer to the official openpyxl documentation.