Work with Multiple Excel Sheets in Python xlrd

Excel files often contain multiple sheets. Each sheet holds different data. Python's xlrd library helps read them all.

This guide shows you how to work with multiple Excel sheets. You will learn to access, read, and process data across worksheets.

Install xlrd Library

First, install xlrd using pip. Open your command line and run this command.

pip install xlrd

This installs the latest version of xlrd. Now you can import it in your Python scripts.

Open Excel File and Access Sheets

Use open_workbook to load your Excel file. This function returns a workbook object.

 
import xlrd

# Open the Excel file
workbook = xlrd.open_workbook('data.xlsx')

# Get all sheet names
sheet_names = workbook.sheet_names()
print("Sheet names:", sheet_names)

Sheet names: ['Sales', 'Inventory', 'Customers']

The sheet_names method lists all worksheets. You can see each sheet's name in order.

Access Individual Sheets

You can access sheets by name or index. Use sheet_by_name for specific sheets.

 
# Access sheet by name
sales_sheet = workbook.sheet_by_name('Sales')

# Access sheet by index (0-based)
first_sheet = workbook.sheet_by_index(0)

print("Sales sheet rows:", sales_sheet.nrows)
print("First sheet name:", first_sheet.name)

Sales sheet rows: 50
First sheet name: Sales

Sheet indices start at 0. The first sheet is index 0, second is index 1, and so on.

Read Data from Multiple Sheets

Loop through all sheets to read data. This is useful for processing entire workbooks.

 
# Process all sheets in the workbook
for sheet in workbook.sheets():
    print(f"Sheet: {sheet.name}")
    print(f"Rows: {sheet.nrows}, Columns: {sheet.ncols}")
    
    # Read first row from each sheet
    if sheet.nrows > 0:
        first_row = sheet.row_values(0)
        print("First row:", first_row)
    print("---")

Sheet: Sales
Rows: 50, Columns: 6
First row: ['Date', 'Product', 'Quantity', 'Price', 'Total', 'Region']
---
Sheet: Inventory
Rows: 25, Columns: 4
First row: ['ProductID', 'ProductName', 'Stock', 'ReorderLevel']
---
Sheet: Customers
Rows: 100, Columns: 5
First row: ['CustomerID', 'Name', 'Email', 'Phone', 'JoinDate']
---

This approach lets you process each sheet automatically. You can extract headers or analyze structure.

Read Specific Rows and Columns Across Sheets

Sometimes you need specific data from multiple sheets. Combine sheet access with cell reading.

 
# Read specific data from all sheets
for sheet_name in workbook.sheet_names():
    sheet = workbook.sheet_by_name(sheet_name)
    
    # Read first 3 rows
    print(f"First 3 rows from {sheet_name}:")
    for row_idx in range(min(3, sheet.nrows)):
        row_data = sheet.row_values(row_idx)
        print(f"Row {row_idx}: {row_data}")
    print()

First 3 rows from Sales:
Row 0: ['Date', 'Product', 'Quantity', 'Price', 'Total', 'Region']
Row 1: ['2023-01-01', 'Laptop', '5', '999.99', '4999.95', 'North']
Row 2: ['2023-01-02', 'Mouse', '20', '25.50', '510.00', 'South']

First 3 rows from Inventory:
Row 0: ['ProductID', 'ProductName', 'Stock', 'ReorderLevel']
Row 1: ['P001', 'Laptop', '45', '10']
Row 2: ['P002', 'Mouse', '120', '25']

For more targeted data extraction, learn about reading specific rows and columns with Python xlrd.

Compare Data Across Multiple Sheets

You can compare data between sheets. This is useful for validation and analysis.

 
# Compare product lists between Sales and Inventory sheets
sales_sheet = workbook.sheet_by_name('Sales')
inventory_sheet = workbook.sheet_by_name('Inventory')

# Get unique products from Sales (column index 1)
sales_products = set()
for row_idx in range(1, sales_sheet.nrows):  # Skip header
    product = sales_sheet.cell_value(row_idx, 1)
    sales_products.add(product)

# Get products from Inventory (column index 1)
inventory_products = set()
for row_idx in range(1, inventory_sheet.nrows):  # Skip header
    product = inventory_sheet.cell_value(row_idx, 1)
    inventory_products.add(product)

print("Products in Sales but not in Inventory:", sales_products - inventory_products)
print("Products in Inventory but not in Sales:", inventory_products - sales_products)

Products in Sales but not in Inventory: {'Monitor'}
Products in Inventory but not in Sales: {'Keyboard', 'Headphones'}

This comparison helps identify data inconsistencies. For advanced comparison techniques, see comparing Excel sheets in Python using xlrd.

Handle Different Data Types Across Sheets

Different sheets may contain different data types. xlrd handles this automatically.

 
# Check data types in different sheets
customers_sheet = workbook.sheet_by_name('Customers')

print("Data types in Customers sheet:")
for col_idx in range(min(5, customers_sheet.ncols)):
    cell = customers_sheet.cell(1, col_idx)  # First data row
    print(f"Column {col_idx}: {cell.ctype} - Value: {cell.value}")

# xlrd cell types: 0=empty, 1=text, 2=number, 3=date, 4=boolean, 5=error

Data types in Customers sheet:
Column 0: 1 - Value: C001
Column 1: 1 - Value: John Smith
Column 2: 1 - Value: [email protected]
Column 3: 1 - Value: 555-0123
Column 4: 3 - Value: 44927.0

For detailed date handling, explore handling Excel dates and times with Python xlrd.

Practical Example: Consolidate Data from Multiple Sheets

Here's a complete example that consolidates key information from all sheets.

 
def analyze_workbook(workbook_path):
    workbook = xlrd.open_workbook(workbook_path)
    analysis = {}
    
    for sheet in workbook.sheets():
        sheet_info = {
            'name': sheet.name,
            'rows': sheet.nrows,
            'columns': sheet.ncols,
            'headers': [],
            'data_rows': sheet.nrows - 1 if sheet.nrows > 1 else 0
        }
        
        # Get headers if available
        if sheet.nrows > 0:
            sheet_info['headers'] = sheet.row_values(0)
        
        analysis[sheet.name] = sheet_info
    
    return analysis

# Use the function
result = analyze_workbook('data.xlsx')
for sheet_name, info in result.items():
    print(f"Sheet: {info['name']}")
    print(f"Size: {info['rows']} rows x {info['columns']} columns")
    print(f"Headers: {info['headers']}")
    print()

Sheet: Sales
Size: 50 rows x 6 columns
Headers: ['Date', 'Product', 'Quantity', 'Price', 'Total', 'Region']

Sheet: Inventory
Size: 25 rows x 4 columns
Headers: ['ProductID', 'ProductName', 'Stock', 'ReorderLevel']

Sheet: Customers
Size: 100 rows x 5 columns
Headers: ['CustomerID', 'Name', 'Email', 'Phone', 'JoinDate']

Best Practices for Multiple Sheet Processing

Always check if sheets exist before accessing them. This prevents errors.

 
def safe_sheet_access(workbook, sheet_name):
    if sheet_name in workbook.sheet_names():
        return workbook.sheet_by_name(sheet_name)
    else:
        print(f"Sheet '{sheet_name}' not found")
        return None

# Safe access example
sheet = safe_sheet_access(workbook, 'NonExistentSheet')
if sheet:
    print("Sheet accessed successfully")

Sheet 'NonExistentSheet' not found

Handle large files efficiently. For big Excel files, learn about reading large Excel files efficiently with Python xlrd.

Conclusion

Working with multiple Excel sheets in Python xlrd is straightforward. You can access sheets by name or index.

Loop through all sheets to process data efficiently. Compare data across worksheets for validation.

Remember to handle different data types properly. Always check for sheet existence to avoid errors.

These techniques help you automate Excel data processing. You can analyze complex workbooks programmatically.