Python openpyxl: Cells, Rows, Columns, and Worksheets

python openpyxl cells rows columns and worksheet operations requires a clear understanding of the core syntax, runtime behavior, and practical implementation patterns demonstrated in the examples below.

Working with Excel files in Python often means manipulating cells, rows, columns, and worksheets programmatically. The openpyxl library provides a straightforward API for these tasks, whether you are generating reports, transforming data, or updating existing spreadsheets. This article focuses on the core operations you need to read and write Excel workbooks efficiently.

Accessing Cells Directly

The most basic operation is reading or writing a single cell. openpyxl gives you two equivalent ways to address a cell: by its coordinate string (like "A1") or by row and column numbers. Both approaches return a Cell object whose value attribute holds the data.

from openpyxl import load_workbook

wb = load_workbook("example.xlsx")
ws = wb.active

# Access by coordinate
cell_a1 = ws["A1"]
print(cell_a1.value)

# Access by row and column (1-based)
cell_b2 = ws.cell(row=2, column=2)
cell_b2.value = "New value"

# Save changes
wb.save("example.xlsx")

When writing a value, you can assign directly to cell.value or pass the value to the cell() method as a second argument. The cell() method is especially useful when you need to compute row and column indices dynamically, for example in a loop.

A common mistake is forgetting that openpyxl uses 1-based indexing. ws.cell(row=0, column=1) raises an IndexError. Always start from 1.

Iterating Over Rows and Columns

Real-world data rarely fits in a single cell. You often need to process entire rows or columns. openpyxl provides iter_rows() and iter_cols() to iterate over rectangular ranges, returning tuples of Cell objects.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# Fill a small grid
for row in range(1, 4):
    for col in range(1, 4):
        ws.cell(row=row, column=col, value=row * col)

# Iterate over all rows in the used range
for row in ws.iter_rows(min_row=1, max_row=3, min_col=1, max_col=3):
    for cell in row:
        print(cell.value, end=" ")
    print()

# Iterate over columns instead
for col in ws.iter_cols(min_row=1, max_row=3, min_col=1, max_col=3):
    print([cell.value for cell in col])

You can also access a full row or column directly using the rows and columns properties, but those return generators that include empty cells up to the worksheet dimension. For sparse data, iter_rows with explicit bounds is more efficient.

If you only need values and not Cell objects, pass values_only=True to iter_rows(). This returns tuples of raw values, which is faster and more memory-efficient when you don't need formatting or style information.

Worksheet Management

A workbook can contain multiple worksheets. You can create, rename, and remove them as needed. The active property returns the currently selected sheet, but you can also access sheets by name or index.

from openpyxl import Workbook

wb = Workbook()
ws1 = wb.active
ws1.title = "Data"

# Create a new worksheet
ws2 = wb.create_sheet("Summary")

# Access by name
ws = wb["Data"]

# List all sheet names
print(wb.sheetnames)

# Remove a sheet
wb.remove(ws2)

When you create a new workbook, it automatically contains one worksheet. If you need to start with a blank slate, you can remove the default sheet and add your own, but it's usually easier to rename the default sheet.

Be careful when removing sheets: wb.remove(ws) expects a worksheet object, not a name. If you have the name, use wb.remove(wb[name]).

Inserting and Deleting Rows and Columns

openpyxl supports inserting and deleting rows and columns at arbitrary positions. This is useful when you need to restructure a spreadsheet programmatically, for example to add a header row or remove a column that is no longer needed.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.append(["A", "B", "C"])
ws.append([1, 2, 3])
ws.append([4, 5, 6])

# Insert a new row at the top (row 1)
ws.insert_rows(1)
ws["A1"] = "Header"

# Insert a column at position 2
ws.insert_cols(2)
ws["B1"] = "New Column"

# Delete the second row
ws.delete_rows(2)

# Delete the first column
ws.delete_cols(1)

wb.save("restructured.xlsx")

Inserting a row shifts existing rows down; inserting a column shifts columns to the right. The insert_rows and insert_cols methods accept an idx parameter (1-based) and an optional amount to insert multiple rows or columns at once. Similarly, delete_rows and delete_cols remove rows or columns and shift the remaining content.

One important side effect: formulas that reference moved cells are not automatically updated by openpyxl. If your workbook contains formulas, you may need to recalculate them after structural changes, either manually or by re-evaluating the workbook in Excel.

Merging and Unmerging Cells

Merging cells is a common way to create headers that span multiple columns or rows. openpyxl provides merge_cells() and unmerge_cells() methods that take a range string like "A1:C1".

from openpyxl import Workbook

wb = Workbook()
ws = wb.active

# Merge a range of cells
ws.merge_cells("A1:C1")
ws["A1"] = "Merged Header"

# Unmerge the same range
ws.unmerge_cells("A1:C1")

After merging, only the top-left cell retains its value; the other cells become None and are not accessible as separate cells. When you unmerge, the previously merged cells revert to individual cells, but the values that were in the non-top-left cells are lost. If you need to preserve data, copy it before merging.

Merging can also cause issues with iteration. When you use iter_rows(), merged cells appear as None for the non-top-left positions, which can break data processing. Consider whether merging is truly necessary or if you can achieve the visual effect with formatting alone.

Performance Considerations for Large Workbooks

openpyxl loads an entire workbook into memory by default. For large files (tens of thousands of rows), this can consume significant RAM and slow down operations. When you only need to write data, you can use the write_only mode, which streams rows to disk instead of keeping them in memory.

from openpyxl import Workbook

wb = Workbook(write_only=True)
ws = wb.create_sheet("Large Data")

# Append rows one at a time
for i in range(100000):
    ws.append([i, i * 2, i * 3])

wb.save("large.xlsx")

In write_only mode, you cannot read or modify cells after writing; you can only append rows. This is ideal for generating large export files where the data is produced sequentially.

For reading large workbooks, consider using read_only mode in load_workbook. This loads the workbook in a streaming fashion, allowing you to iterate over rows without holding the entire file in memory. However, read_only mode has limitations: you cannot modify the workbook, and some features like merged cells are not fully supported.

from openpyxl import load_workbook

wb = load_workbook("large.xlsx", read_only=True)
ws = wb.active

for row in ws.iter_rows(values_only=True):
    # process each row
    pass

wb.close()  # release the file handle

Always close a read_only workbook when you are done to free system resources. In both modes, the trade-off is between memory usage and flexibility. For interactive or small-file scenarios, the default mode is simpler and more capable.

When performance matters, avoid repeatedly accessing cells by coordinate in a loop. Instead, use iter_rows() or iter_cols() to process ranges in bulk. This reduces the overhead of cell lookups and improves cache locality, especially when dealing with thousands of cells.