xlrd / xlwt

Language: Python

Data

xlrd and xlwt were created to handle Excel 97–2003 files in Python. They predate modern libraries like openpyxl, and are still used in legacy systems for reading and writing .xls files efficiently.

xlrd and xlwt are Python libraries used for reading (xlrd) and writing (xlwt) Excel files in the old .xls format. They provide tools to extract, manipulate, and create Excel spreadsheets programmatically.

Installation

pip: pip install xlrd xlwt

conda: conda install -c anaconda xlrd xlwt

Usage

xlrd is used to read data from .xls files, while xlwt is used to write data to .xls files. Both libraries provide access to sheets, rows, and cells, allowing for reading, writing, formatting, and basic Excel manipulation.

Reading an Excel file with xlrd

import xlrd
workbook = xlrd.open_workbook('example.xls')
sheet = workbook.sheet_by_index(0)
for row_idx in range(sheet.nrows):
    print(sheet.row_values(row_idx))

Opens an existing Excel file, accesses the first sheet, and prints all rows.

Writing an Excel file with xlwt

import xlwt
workbook = xlwt.Workbook()
sheet = workbook.add_sheet('Sheet1')
sheet.write(0, 0, 'Name')
sheet.write(0, 1, 'Age')
sheet.write(1, 0, 'Alice')
sheet.write(1, 1, 30)
workbook.save('output.xls')

Creates a new Excel file, adds headers and data, and saves it as `output.xls`.

Formatting cells with xlwt

import xlwt
style = xlwt.easyxf('font: bold on, color red; align: horiz center')
sheet.write(0, 0, 'Header', style)
workbook.save('styled.xls')

Applies bold, red, and center-aligned style to a cell while writing to Excel.

Reading specific columns and rows with xlrd

for row_idx in range(1, sheet.nrows):
    name = sheet.cell_value(row_idx, 0)
    age = sheet.cell_value(row_idx, 1)
    print(f'{name} - {age}')

Reads only specific columns from each row, skipping headers.

Writing formulas with xlwt

sheet.write(2, 1, xlwt.Formula('B2+B3'))
workbook.save('formulas.xls')

Writes a formula to a cell to calculate the sum of other cells.

Error Handling

XLRDError: Unsupported format, or corrupt file: Ensure you are opening an .xls file. xlrd no longer supports .xlsx files in recent versions.

TypeError: write() argument 3 must be a string or number: Convert data to a string or numeric type before writing to a cell.

FileNotFoundError: Check that the path to the Excel file exists before reading.

Best Practices

Use xlrd strictly for reading old .xls files; for .xlsx files, use openpyxl or pandas.

Use xlwt for writing small to medium .xls files; xlwt does not support .xlsx.

Organize sheets and cell writes carefully to avoid overwriting data.

Use cell formatting sparingly for readability; complex formatting may be limited.

Always close or save the workbook to persist changes.

Official Docs Github