Python Openpyxl: Create, Load, Read, Write Excel Files

python openpyxl create load read and write excel files requires a clear understanding of the core syntax, runtime behavior, and practical implementation patterns demonstrated in the examples below.

When you need to create, load, read, and write Excel files in Python, openpyxl is the library that handles .xlsx files without requiring Excel to be installed. It gives you a structured API for manipulating workbooks, sheets, and cells, making it a standard choice for automation, data pipelines, and reporting scripts. This article walks through the core operations you will use in practice, from opening an existing file to building a new one from scratch, with attention to the details that trip up developers new to the library.

Installing openpyxl

openpyxl is a third-party package, so you need to install it before importing it. Use pip in your environment:

pip install openpyxl

If you work with virtual environments or dependency files, add openpyxl to your requirements.txt or use a package manager like Poetry or uv. The library supports Python 3.8 and later, and it does not depend on Excel itself. Once installed, you can import it as openpyxl and access the main functions and classes.

Loading an Existing Workbook

To read or modify an existing Excel file, use load_workbook(). This function accepts a file path and returns a Workbook object. By default, it loads the workbook in read-write mode, which means you can change cells and save the file again.

from openpyxl import load_workbook

wb = load_workbook("sales.xlsx")
print(wb.sheetnames)  # ['Sheet1', 'Summary']

The sheetnames property gives you a list of all sheet names in the order they appear. To access a specific sheet, use the workbook as a mapping with the sheet name as the key:

ws = wb["Sheet1"]

If you need to work with the active sheet without knowing its name, use wb.active. This is useful when the file has a single sheet or you do not care which sheet is selected.

ws = wb.active

Keep in mind that load_workbook() reads the entire file into memory. For very large files, you can pass read_only=True to load the workbook in a read-only mode that streams cell data from disk, which we will cover later.

Creating a New Workbook

Creating a new workbook starts with the Workbook() constructor. It returns a workbook with a single sheet named "Sheet" by default. You can rename that sheet or create additional ones as needed.

from openpyxl import Workbook

wb = Workbook()
ws = wb.active
ws.title = "Report"

To add another sheet, use create_sheet(). You can specify a title and an optional index to control where the sheet appears in the tab order.

ws2 = wb.create_sheet("Data", 0)  # inserts at the first position

When you are done building the workbook, save it with save(). The file extension must be .xlsx; openpyxl does not support the older .xls format.

wb.save("report.xlsx")

If the file already exists, save() overwrites it without warning. Make sure you have the appropriate file permissions in the target directory.

Reading Data from Cells

Reading a single cell is straightforward: access the cell by its coordinate string or use the cell() method with row and column numbers. The returned Cell object has a value attribute that holds the cell's content.

value = ws["A1"].value
# or
value = ws.cell(row=1, column=1).value

Cells that are empty return None. This is important to remember when you iterate over ranges, because you will often need to skip None values.

To read a range of cells, you can iterate over rows or columns. The iter_rows() method returns a generator of row tuples, and iter_cols() does the same for columns. Both accept min_row, max_row, min_col, and max_col to define the bounds.

for row in ws.iter_rows(min_row=1, max_row=5, min_col=1, max_col=3):
    for cell in row:
        print(cell.coordinate, cell.value)

If you only need the values without cell objects, set values_only=True:

for row in ws.iter_rows(min_row=1, max_row=5, min_col=1, max_col=3, values_only=True):
    print(row)

This returns tuples of plain values, which is convenient for data processing. To get all rows in the sheet, use ws.iter_rows() without bounds; it will iterate over the entire used range.

Writing Data to Cells

Writing a value to a cell is as simple as assigning to the cell's value attribute. You can write strings, numbers, dates, booleans, and even formulas. openpyxl automatically infers the data type from the Python object you assign.

ws["A1"] = "Product"
ws["B1"] = 42
ws["C1"] = 3.14
ws["D1"] = True

For writing multiple rows of data, you can use the append() method, which adds a row at the bottom of the current used range. Each argument or list item becomes a cell in that row.

rows = [
    ["Product", "Price", "Units"],
    ["Widget", 19.99, 100],
    ["Gadget", 29.99, 50],
]
for row in rows:
    ws.append(row)

append() is efficient for building a sheet from a list of lists, and it automatically moves the active cell to the next row. If you need to write to a specific cell, use direct assignment or the cell() method with value:

ws.cell(row=2, column=2, value=19.99)

Remember that cells are 1-indexed: row 1, column 1 is A1. Mixing up the indexing is a common source of off-by-one errors.

Formatting Cells and Sheets

Formatting in openpyxl is done through style objects that you assign to cells. The most common styles are Font, PatternFill, Alignment, and Border. You import them from openpyxl.styles.

from openpyxl.styles import Font, PatternFill, Alignment

header_font = Font(bold=True, size=12)
header_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid")
center_align = Alignment(horizontal="center")

for cell in ws[1]:  # first row
    cell.font = header_font
    cell.fill = header_fill
    cell.alignment = center_align

You can also adjust column widths and row heights to make the sheet readable. Column widths are set in character units, and row heights in points.

ws.column_dimensions["A"].width = 20
ws.row_dimensions[1].height = 30

For more advanced styling, you can apply a number format to cells, such as currency or percentage. The number_format attribute accepts Excel format strings.

ws["B2"].number_format = "$#,##0.00"

Formatting is applied at the cell level, so you often need to loop over ranges to style a table consistently. You can also merge cells with merge_cells() and unmerge with unmerge_cells().

ws.merge_cells("A1:C1")
ws["A1"] = "Sales Summary"

Working with Formulas and Dates

openpyxl supports Excel formulas as strings. When you assign a string that starts with =, the library stores it as a formula, and Excel evaluates it when the file is opened. For example:

ws["C2"] = "=A2*B2"

You can also use built-in functions like SUM, AVERAGE, or IF. However, openpyxl does not evaluate formulas itself; it only writes the formula text. If you read the cell value later in Python, you will get the formula string, not the computed result. To get the computed value, you need to open the file in Excel or use a library like formulas or LibreOffice to calculate it.

Dates are handled through Python's datetime module. Assign a datetime object to a cell, and openpyxl will serialize it as an Excel date. To read it back, the cell's value will be a datetime object as well.

from datetime import datetime

ws["A1"] = datetime(2025, 3, 15)

By default, the cell will have a date number format. If you want to control the display, set the number_format to something like "YYYY-MM-DD".

Performance Considerations for Large Files

When working with large Excel files, memory usage becomes a real concern. The default mode loads the entire workbook into memory, which can consume hundreds of megabytes for files with many rows. openpyxl provides two modes to mitigate this: read-only and write-only.

Read-Only Mode

Load a workbook with read_only=True to stream cell data from disk without building the full in-memory model. This is ideal for reading large files where you only need to extract values.

wb = load_workbook("large.xlsx", read_only=True)
ws = wb.active
for row in ws.iter_rows(values_only=True):
    # process row
    pass
wb.close()

In read-only mode, you cannot modify cells, and some features like formatting are not fully available. You must also close the workbook with close() to release the file handle.

Write-Only Mode

For writing large amounts of data, use write_only=True when creating a workbook. This mode writes rows to disk as you append them, avoiding the memory spike of holding all cells in RAM.

wb = Workbook(write_only=True)
ws = wb.create_sheet()
ws.append(["Product", "Price"])
for i in range(100000):
    ws.append([f"Item{i}", i])
wb.save("large_output.xlsx")
```n
In write-only mode, you cannot read cells or apply most formatting. The sheet is created empty, and you must use `append()` to add rows. This mode is significantly faster for bulk data insertion.

Choosing between normal, read-only, and write-only modes depends on your workflow. For typical scripts that handle small to medium files, the default mode is simplest. For files with tens of thousands of rows or more, consider the streaming modes to keep memory usage predictable.

## Common Pitfalls and How to Avoid Them

Several mistakes trip up developers new to openpyxl. Knowing them in advance saves debugging time.

### Forgetting the .xlsx Extension

`save()` does not add an extension automatically. If you pass `"output"` instead of `"output.xlsx"`, you will get a file that Excel cannot open. Always include the extension in your file path.

### Confusing Sheet Names

Sheet names are case-sensitive. If you try to access `ws = wb["sheet1"]` but the sheet is named `"Sheet1"`, you will get a `KeyError`. Verify the exact names using `wb.sheetnames`.

### Modifying Cells in Read-Only Mode

If you load a workbook with `read_only=True` and try to assign a value to a cell, openpyxl raises an `AttributeError` because the cell objects are read-only. Use the normal mode for editing, or create a new workbook and copy data over.

### Overwriting Existing Files

`save()` overwrites the target file silently. If you need to preserve the original, save to a different path or create a backup before saving.

### Data Type Confusion

openpyxl stores numbers as `int` or `float`, but it may read a cell with a numeric-looking string as a string if the source file stored it that way. When processing data, explicitly convert values if you expect a particular type. For example, `int(cell.value)` will fail if the value is `None` or a non-numeric string, so check the type first.

### Formulas Not Evaluated

As mentioned earlier, openpyxl does not compute formula results. If you read a cell that contains a formula, you get the formula string, not the calculated value. This is a common surprise when you expect the result. If you need calculated values, you must open the file in an application that evaluates formulas or use a separate calculation engine.

By understanding these behaviors and choosing the appropriate mode for your task, you can work with Excel files in Python reliably and efficiently. openpyxl gives you fine-grained control over workbook structure and cell content, making it a solid foundation for reporting and data exchange workflows.