Reading Excel Files with Python's xlrd Library

To manipulate Excel files in Python, the xlrd library is commonly used for reading operations, while xlwt handles writing. This guide covers the fundamental usage of xlrd.

Installation

Note that recent versions of xlrd no longer support .xlsx files. To work with both .xls and .xlsx formats, install version 1.2.0:

pip uninstall xlrd
pip install xlrd==1.2.0

Opening Workbooks

To open an Excel file:

import xlrd

# Using raw string for Windows paths
file_path = r'C:\data\workbook.xlsx'
workbook = xlrd.open_workbook(file_path)

Alternatively, use forward slashes:

workbook = xlrd.open_workbook('C:/data/workbook.xlsx')

Accessing Worksheets

Multiple methods exist to retrieve worksheet objects:

# By index (first sheet)
sheet1 = workbook.sheets()[0]

# By index position
sheet2 = workbook.sheet_by_index(1)

# By name
sheet3 = workbook.sheet_by_name('DataSheet')

# Get all sheet names
all_names = workbook.sheet_names()

# Check if sheet exists (by name or index)
exists = workbook.sheet_loaded('DataSheet')  # True/False
exists = workbook.sheet_loaded(0)           # True/False

Working with Rows and Columns

Retrieve dimensions and content:

# Get dimensions
row_count = sheet3.nrows  # Effective rows
col_count = sheet3.ncols  # Effective columns

# Get data from specific row
row_data = sheet3.row_values(1, start_colx=0, end_colx=None)
# or simpler:
row_data = sheet3.row_values(1)

# Get data from specific column
col_data = sheet3.col_values(0, start_rowx=0, end_rowx=None)
# or simpler:
col_data = sheet3.col_values(0)

Retrieve cell objects and their types:

# Get row cell objects
row_cells = sheet3.row(0)

# Get column cell objects
col_cells = sheet3.col(1)

# Get slice of row cells (columns 1-3)
row_slice = sheet3.row_slice(1, 1, 3)

# Get slice of column cells (rows 1-3)
col_slice = sheet3.col_slice(1, 1, 3)

# Get row cell types
row_types = sheet3.row_types(1)

# Get column cell types
col_types = sheet3.col_types(1)

Iterate through all rows:

for row in sheet3.get_rows():
    for cell in row:
        print(cell.value)

Accessing Cell Data

Retrieve individual cell values and types:

# Get cell value (row 0, col 0)
value = sheet3.cell(0, 0).value
# Alternative method
value = sheet3.cell_value(0, 0)

# Get cell type
cell_type = sheet3.cell_type(0, 0)

Understanding Cell Types

xlrd recognizes these cell types:

0: Empty
1: String (text)
2: Number
3: Date
4: Boolean
5: Error

Special handling for dates (type 3):

if sheet3.cell_type(0, 0) == 3:
    date_tuple = xlrd.xldate_as_tuple(sheet3.cell_value(0, 0), workbook.datemode)
    # date_tuple format: (year, month, day, hour, minute, second)

Tags: python Excel xlrd data-analysis file-io

Posted on Fri, 23 Oct 2026 16:32:05 +0000 by artist19

Freaks City