Python xlrd Guide: Load Workbooks and Iterate Sheets

Python xlrd is a powerful library. It reads Excel files. This guide shows workbook loading. It also covers sheet iteration.

You will learn practical techniques. These handle Excel data efficiently. The examples are beginner-friendly.

What is xlrd?

xlrd is a Python library. It extracts data from Excel files. It supports .xls format primarily.

The library is lightweight. It doesn't need Excel installed. It works across platforms.

xlrd handles various Excel features. These include formulas and formatting. It also manages multiple sheets.

Installing xlrd

First, install the library. Use pip for installation. The command is straightforward.

pip install xlrd

For detailed installation steps, see our guide on install xlrd and xlwt in Python.

Loading Excel Workbooks

Start by importing xlrd. Use the open_workbook function. It loads your Excel file.

import xlrd

# Load the workbook
workbook = xlrd.open_workbook('sample_data.xls')

print(f"Workbook loaded successfully!")
print(f"Number of sheets: {workbook.nsheets}")

Workbook loaded successfully!
Number of sheets: 3

The open_workbook function is crucial. It returns a workbook object. This object contains all data.

Handle file paths correctly. Use absolute or relative paths. Ensure file existence.

Accessing Workbook Information

Workbook objects have useful attributes. Check sheet counts. Get sheet names.

import xlrd

workbook = xlrd.open_workbook('sample_data.xls')

# Get workbook information
print(f"Sheet names: {workbook.sheet_names()}")
print(f"Number of sheets: {workbook.nsheets}")

# Check if a sheet exists
sheet_exists = 'Sheet1' in workbook.sheet_names()
print(f"Sheet1 exists: {sheet_exists}")

Sheet names: ['Sales', 'Inventory', 'Employees']
Number of sheets: 3
Sheet1 exists: True

Sheet names help navigation. Use them for specific sheet access. This is better than indices.

Iterating Through Sheets

Iteration processes all sheets. Use loops for this. Several methods work.

Method 1: Using sheet_names()

Get all sheet names first. Loop through them. Access each sheet individually.

import xlrd

workbook = xlrd.open_workbook('sample_data.xls')

# Iterate using sheet names
for sheet_name in workbook.sheet_names():
    sheet = workbook.sheet_by_name(sheet_name)
    print(f"Processing sheet: {sheet_name}")
    print(f"Rows: {sheet.nrows}, Columns: {sheet.ncols}")
    print("---")

Processing sheet: Sales
Rows: 50, Columns: 6
---
Processing sheet: Inventory
Rows: 25, Columns: 4
---
Processing sheet: Employees
Rows: 10, Columns: 5
---

Method 2: Using sheets() method

The sheets method returns all sheets. It's more direct. No need for name lookup.

import xlrd

workbook = xlrd.open_workbook('sample_data.xls')

# Iterate using sheets() method
for sheet in workbook.sheets():
    print(f"Sheet: {sheet.name}")
    print(f"Dimensions: {sheet.nrows}x{sheet.ncols}")
    
    # Display first few rows
    for row_idx in range(min(3, sheet.nrows)):
        row_data = sheet.row_values(row_idx)
        print(f"Row {row_idx}: {row_data}")
    print("---")

Sheet: Sales
Dimensions: 50x6
Row 0: ['Date', 'Product', 'Quantity', 'Price', 'Total', 'Region']
Row 1: ['2023-01-01', 'Widget A', 10.0, 25.5, 255.0, 'North']
Row 2: ['2023-01-02', 'Widget B', 5.0, 30.0, 150.0, 'South']
---
Sheet: Inventory
Dimensions: 25x4
Row 0: ['Product', 'Stock', 'Reorder Level', 'Supplier']
Row 1: ['Widget A', 100.0, 20.0, 'Supplier X']
Row 2: ['Widget B', 50.0, 15.0, 'Supplier Y']
---

Method 3: Using indices

Access sheets by index. Useful for positional access. Be careful with index bounds.

import xlrd

workbook = xlrd.open_workbook('sample_data.xls')

# Iterate using indices
for i in range(workbook.nsheets):
    sheet = workbook.sheet_by_index(i)
    print(f"Sheet {i}: {sheet.name}")
    print(f"First cell: {sheet.cell_value(0, 0)}")
    print("---")

Sheet 0: Sales
First cell: Date
---
Sheet 1: Inventory
First cell: Product
---
Sheet 2: Employees
First cell: Name
---

Reading Data from Sheets

Once you have a sheet, read data. Several methods are available. Choose based on needs.

import xlrd

workbook = xlrd.open_workbook('sample_data.xls')
sheet = workbook.sheet_by_name('Sales')

# Read specific cell
cell_value = sheet.cell_value(1, 1)
print(f"Cell B2: {cell_value}")

# Read entire row
row_data = sheet.row_values(0)
print(f"Header row: {row_data}")

# Read entire column
column_data = sheet.col_values(1)
print(f"Product column sample: {column_data[:3]}")

# Read cell with different data types
cell_type = sheet.cell_type(1, 2)
print(f"Cell type at row 1, col 2: {cell_type}")

Cell B2: Widget A
Header row: ['Date', 'Product', 'Quantity', 'Price', 'Total', 'Region']
Product column sample: ['Product', 'Widget A', 'Widget B']
Cell type at row 1, col 2: 2

Data types matter in xlrd. Numbers, text, and dates differ. Handle them appropriately.

Practical Example: Data Processing

Combine loading and iteration. Create a data processor. This example sums sales.

import xlrd

def process_sales_data(file_path):
    workbook = xlrd.open_workbook(file_path)
    
    for sheet in workbook.sheets():
        print(f"\nProcessing: {sheet.name}")
        
        if sheet.nrows > 1:  # Check if sheet has data
            total_sales = 0
            sales_count = 0
            
            # Skip header row, start from row 1
            for row_idx in range(1, sheet.nrows):
                try:
                    # Assuming sales amount is in column 4 (index 3)
                    sale_amount = sheet.cell_value(row_idx, 3)
                    if isinstance(sale_amount, (int, float)):
                        total_sales += sale_amount
                        sales_count += 1
                except:
                    continue
            
            print(f"Total sales: ${total_sales:.2f}")
            print(f"Number of sales records: {sales_count}")
            if sales_count > 0:
                print(f"Average sale: ${total_sales/sales_count:.2f}")

# Usage
process_sales_data('sample_data.xls')

Processing: Sales
Total sales: $405.00
Number of sales records: 2
Average sale: $202.50

Processing: Inventory
Total sales: $0.00
Number of sales records: 0

Processing: Employees
Total sales: $0.00
Number of sales records: 0

Error Handling

Always handle potential errors. Files might not exist. Sheets could be missing.

import xlrd
import os

def safe_workbook_processing(file_path):
    try:
        # Check if file exists
        if not os.path.exists(file_path):
            print(f"Error: File {file_path} not found")
            return
        
        # Load workbook
        workbook = xlrd.open_workbook(file_path)
        
        # Check if workbook has sheets
        if workbook.nsheets == 0:
            print("Error: Workbook has no sheets")
            return
        
        # Process sheets
        for sheet in workbook.sheets():
            print(f"Found sheet: {sheet.name}")
            
    except xlrd.XLRDError as e:
        print(f"XLDR Error: {e}")
    except Exception as e:
        print(f"Unexpected error: {e}")

# Test with various scenarios
safe_workbook_processing('sample_data.xls')
safe_workbook_processing('nonexistent.xls')

Found sheet: Sales
Found sheet: Inventory
Found sheet: Employees
Error: File nonexistent.xls not found

Best Practices

Follow these guidelines. They improve code quality. They prevent common issues.

Always close resources. xlrd handles this automatically. But be mindful of memory.

Use context managers when possible. They ensure proper cleanup. This prevents resource leaks.

Validate data types. Excel cells vary. Check before processing.

Handle large files carefully. Process row by row. Avoid loading everything at once.

Common Issues and Solutions

Users often face specific problems. Here are common ones with solutions.

File format issues occur. xlrd primarily supports .xls. For .xlsx, use other libraries.

Date handling can be tricky. xlrd has special date functions. Use them for Excel dates.

Memory problems with large files. Process in chunks. Don't load all data at once.

If you need to remove xlrd, see our guide on how to uninstall xlrd in Python.

Advanced Techniques

Beyond basic iteration, xlrd offers more. These techniques handle complex scenarios.

Work with merged cells. xlrd provides merged cell info. Handle them appropriately.

Access cell formatting. While limited, some format data is available. Use it carefully.

Handle different Excel versions. xlrd works with various Excel formats. Test your specific files.

For comprehensive xlrd learning, check our Python xlrd tutorial for reading Excel files.

Conclusion

Python xlrd is excellent for Excel processing. Loading workbooks is straightforward. Sheet iteration is flexible.

You learned multiple iteration methods. Each has its use cases. Choose based on your needs.

Remember error handling. It makes your code robust. It handles real-world scenarios.

Practice with different Excel files. Experiment with the examples. Build your confidence with xlrd.

The skills you gained are valuable. They apply to data analysis tasks. They work in automation scripts.