Read Excel Files with Python OpenPyXL

OpenPyXL can read data from Excel .xlsx workbooks directly in Python. We can open a workbook, select a worksheet, read individual cells, iterate through rows or columns, inspect worksheet dimensions and process the values.

The main function used to open an existing workbook is load_workbook().

Open an Excel Workbook with load_workbook() 🔝

Import load_workbook() and pass the path of the Excel file.

from openpyxl import load_workbook

wb=load_workbook(
    'student.xlsx'
)

ws=wb['student']

print(
    ws['A1'].value
)

wb.close()

wb represents the workbook and ws represents one worksheet inside it.

Windows File Path

A raw string is convenient for Windows paths:

file_path=r'D:\testing3\openpyxl\student.xlsx'

wb=load_workbook(
    file_path
)

The r prefix prevents backslashes in the Windows path from being interpreted as Python escape sequences.

Get Worksheet Names and Select a Sheet 🔝

Use sheetnames to see the worksheets available in a workbook.

from openpyxl import load_workbook

wb=load_workbook(
    'student.xlsx'
)

print(
    wb.sheetnames
)

wb.close()

If the workbook contains a worksheet named student, select it with:

ws=wb['student']

The active worksheet can also be selected:

ws=wb.active

Using the worksheet name is usually clearer when the workbook contains several sheets.

Read an Individual Cell Value 🔝

There are two common ways to access a cell.

Using Excel Cell Coordinates

value=ws['C2'].value

print(value)

Using cell(row, column)

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

print(value)

Row and column numbers are 1-based, so:

row=1, column=1  # A1
row=2, column=3  # C2

Read a Range of Excel Cells 🔝

A rectangular worksheet range can be accessed directly.

for row in ws['A1:C5']:
    for cell in row:
        print(
            cell.value,
            end=' '
        )

    print()

For larger or dynamically defined ranges, iter_rows() is generally more convenient.

Read Rows with iter_rows() 🔝

iter_rows() returns rows from a selected worksheet area.

ws.iter_rows(
    min_row=None,
    max_row=None,
    min_col=None,
    max_col=None,
    values_only=False
)

The row and column indexes used here are 1-based.

Read A1:C5

from openpyxl import load_workbook

wb=load_workbook(
    'student.xlsx'
)

ws=wb['student']

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

wb.close()

With values_only=True, each generated row contains values instead of Cell objects.

For example:

('id', 'name', 'class')
(1, 'John Deo', 'Four')
(2, 'Max Ruin', 'Three')
(3, 'Arnold', 'Three')
(4, 'Krish Star', 'Four')

Cell Objects vs Values Only

Without values_only=True:

for row in ws.iter_rows(
    min_row=1,
    max_row=2
):
    for cell in row:
        print(
            cell.coordinate,
            cell.value
        )

This is useful when you need properties such as the cell coordinate, style or data type.

When only the stored data is required, values_only=True keeps the code simpler.

Skip the Header Row 🔝

If row 1 contains column headings, start from row 2.

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

(1, 'John Deo', 'Four', 75, 'female')
(2, 'Max Ruin', 'Three', 85, 'male')
(3, 'Arnold', 'Three', 55, 'male')
(4, 'Krish Star', 'Four', 60, 'female')
(5, 'John Mike', 'Four', 60, 'female')

To continue through the remaining used rows, omit max_row:

for row in ws.iter_rows(
    min_row=2,
    values_only=True
):
    print(row)

Read Columns with iter_cols() 🔝

A normal OpenPyXL worksheet provides iter_cols() for iterating vertically through columns.

ws.iter_cols(
    min_col=None,
    max_col=None,
    min_row=None,
    max_row=None,
    values_only=False
)

Read Only the Name Column

In the sample file, column 2 contains student names.

from openpyxl import load_workbook

wb=load_workbook(
    'student.xlsx'
)

ws=wb['student']

for column in ws.iter_cols(
    min_row=2,
    max_row=6,
    min_col=2,
    max_col=2,
    values_only=True
):
    print(column)

wb.close()

('John Deo', 'Max Ruin', 'Arnold', 'Krish Star', 'John Mike')

To include the header, start from row 1:

for column in ws.iter_cols(
    min_row=1,
    max_row=6,
    min_col=2,
    max_col=2,
    values_only=True
):
    print(column)

('name', 'John Deo', 'Max Ruin', 'Arnold', 'Krish Star', 'John Mike')

Use read_only=True for Large Excel Files 🔝

For a large workbook that only needs to be read, use read_only=True.

from openpyxl import load_workbook

wb=load_workbook(
    'student.xlsx',
    read_only=True
)

ws=wb['student']

for row in ws.iter_rows(
    values_only=True
):
    print(row)

wb.close()

Read-only mode uses lazy loading, so it is useful when a workbook is too large to load fully into memory.

iter_cols() and Read-Only Mode

iter_cols() is not provided by the read-only worksheet implementation. If column-wise iteration is required, either load the workbook normally or read rows and select the required element from each row.

Read Column B While Staying in Read-Only Mode

for row in ws.iter_rows(
    min_row=2,
    min_col=2,
    max_col=2,
    values_only=True
):
    print(
        row[0]
    )

This keeps the memory benefit of read-only mode while retrieving values from one column.

Check Worksheet Dimensions in Read-Only Mode

Read-only mode depends on the worksheet dimensions stored in the Excel file.

print(
    ws.calculate_dimension()
)

A result such as:

A1:E36

indicates the apparent used range.

If a workbook was created by software that stored incorrect worksheet dimensions, OpenPyXL also provides:

ws.reset_dimensions()

This is mainly relevant to unusual files where the reported read-only dimensions are clearly incorrect.

Read Excel Formulas or Cached Formula Results 🔝

By default, OpenPyXL returns the formula expression stored in a formula cell.

For example, if F2 contains:

=SUM(B2:E2)

then:

wb=load_workbook(
    'report.xlsx'
)

ws=wb.active

print(
    ws['F2'].value
)

returns the formula text.

Use data_only=True

wb=load_workbook(
    'report.xlsx',
    data_only=True
)

ws=wb.active

print(
    ws['F2'].value
)

With data_only=True, OpenPyXL returns the cached result previously stored in the workbook instead of the formula expression.

Find the Number of Rows and Columns 🔝

max_row and max_column return the highest row and column indexes reported as part of the worksheet's used area.

print(
    ws.max_row
)

print(
    ws.max_column
)

For the sample student worksheet:

36
5

This means the highest used row index is 36 and the highest used column index is 5.

Get the Worksheet Range

print(
    ws.calculate_dimension()
)

A1:E36

Read Data from the Last Worksheet Row 🔝

The value in column A of the last reported worksheet row can be read with:

last_value=ws.cell(
    row=ws.max_row,
    column=1
).value

print(
    last_value
)

Read the Complete Last Row

last_row=next(
    ws.iter_rows(
        min_row=ws.max_row,
        max_row=ws.max_row,
        values_only=True
    )
)

print(
    last_row
)

Convert Excel Rows to a Python List 🔝

Because iter_rows() is iterable, the returned rows can be converted into a list.

data=list(
    ws.iter_rows(
        min_row=2,
        values_only=True
    )
)

print(
    data[:3]
)

[(1, 'John Deo', 'Four', 75, 'female'),
 (2, 'Max Ruin', 'Three', 85, 'male'),
 (3, 'Arnold', 'Three', 55, 'male')]

For very large workbooks, avoid converting the entire worksheet to a list because doing so loads all selected rows into memory. Process the generator row by row instead.

Convert Excel Rows to Dictionaries 🔝

Using the first worksheet row as field names makes each subsequent record easier to access by column name.

rows=ws.iter_rows(
    values_only=True
)

headers=next(
    rows
)

for row in rows:
    record=dict(
        zip(
            headers,
            row
        )
    )

    print(
        record
    )

{'id': 1, 'name': 'John Deo', 'class': 'Four', 'mark': 75, 'gender': 'female'}

Read One Field by Header Name

print(
    record['name']
)

John Deo

This can be more readable than repeatedly using numeric positions such as row[3].

Find the Row with the Maximum Mark 🔝

The mark is stored in the fourth column, so its tuple index is 3.

rows=ws.iter_rows(
    min_row=2,
    values_only=True
)

valid_rows=[
    row
    for row in rows
    if isinstance(
        row[3],
        (int, float)
    )
]

if valid_rows:
    top_student=max(
        valid_rows,
        key=lambda row: row[3]
    )

    print(
        top_student
    )

Using the key argument lets Python compare the mark field while returning the complete student row.

Calculate the Average Mark 🔝

marks=[]

for row in ws.iter_rows(
    min_row=2,
    values_only=True
):
    mark=row[3]

    if isinstance(
        mark,
        (int, float)
    ):
        marks.append(
            mark
        )

if marks:
    average=sum(
        marks
    ) / len(
        marks
    )

    print(
        'Average mark:',
        average
    )

For a very large worksheet, the average can also be calculated without storing every mark in a list.

total=0
count=0

for row in ws.iter_rows(
    min_row=2,
    values_only=True
):
    mark=row[3]

    if isinstance(
        mark,
        (int, float)
    ):
        total += mark
        count += 1

if count:
    average=total / count

    print(
        'Average mark:',
        average
    )

This second version uses constant extra memory regardless of the number of worksheet rows.

Handle Common Errors When Reading Excel Files 🔝

File Does Not Exist

from openpyxl import load_workbook

try:
    wb=load_workbook(
        'student.xlsx'
    )

except FileNotFoundError:
    print(
        'Excel file was not found.'
    )

Worksheet Does Not Exist

try:
    ws=wb['student']

except KeyError:
    print(
        'Worksheet was not found.'
    )

Check Sheet Names Before Selecting One

print(
    wb.sheetnames
)

Trying to Use iter_cols() in read_only Mode

A read-only worksheet is optimized for streaming rows and does not provide the normal worksheet's iter_cols() interface.

Either use normal mode:

wb=load_workbook(
    'student.xlsx',
    read_only=False
)

or keep read-only mode and select the required column through iter_rows().

Trying to Read Legacy .xls Files

OpenPyXL is intended for modern Excel workbook formats such as .xlsx. A legacy binary .xls file should be converted or read using a library that supports that format.

Summary of Reading Excel with OpenPyXL 🔝

• Use load_workbook() to open an existing Excel workbook.
• Use wb.sheetnames to see available worksheet names.
• Select a worksheet with wb['sheet_name'] or wb.active.
• Read a cell with ws['A1'].value or ws.cell(row=1, column=1).value.
• Row and column numbers passed to cell() are 1-based.
• Use iter_rows() to process worksheet rows efficiently.
• Use values_only=True when only cell values are required.
• Use min_row=2 to skip a header stored in row 1.
• Use iter_cols() for column-wise iteration on a normal worksheet.
• iter_cols() is not available on the optimized read-only worksheet.
• Use read_only=True for large workbooks that only need to be read.
• Read-only mode uses lazy loading and the workbook should be explicitly closed.
• Use data_only=True to read a cached formula result instead of formula text.
• data_only=True does not calculate Excel formulas.
• max_row and max_column report the highest worksheet row and column indexes in the apparent used range.
• calculate_dimension() returns a range such as A1:E36.
• Rows can be converted into tuples, lists or dictionaries depending on how the data will be processed.
• For large files, process generators row by row instead of converting the entire worksheet into a list.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: