5 Ways to Iterate Excel Sheets in Python

In the realm of data analysis, Python stands out as a powerful tool due to its versatility and rich library ecosystem. Among these libraries, openpyxl and pandas are widely recognized for handling Excel files, offering multiple ways to read, process, and manipulate data within these spreadsheets. Let's explore five effective methods to iterate through Excel sheets using Python.

1. Using Openpyxl

Openpyxl is an excellent library for working with Excel files, specifically those in the .xlsx format. Here's how you can iterate through sheets:

• Install openpyxl using pip install openpyxl.

from openpyxl import load_workbook

# Load the workbook
wb = load_workbook('sample.xlsx')

# Iterate through worksheets
for sheet in wb.worksheets:
    print(sheet.title)
    for row in sheet.iter_rows(values_only=True):
        print(row)

⚠️ Note: Remember that openpyxl doesn't load data from sheets by default; you need to enable data_only=True when loading the workbook if you want to access cell values without formulas.

2. Using Pandas

Pandas simplifies data manipulation by providing high-level data structures. Here's how you can iterate through sheets with pandas:

• Install pandas with pip install pandas.

import pandas as pd

# Read the Excel file into a pandas ExcelFile
excel_file = pd.ExcelFile('sample.xlsx')

# Iterate through sheets
for sheet in excel_file.sheet_names:
    df = excel_file.parse(sheet_name=sheet)
    print(f"Sheet: {sheet}")
    print(df)

3. Using Xlrd

Xlrd is another library for reading Excel files but it's particularly useful for older .xls files:

• Install xlrd with pip install xlrd.

import xlrd

# Open the workbook
book = xlrd.open_workbook('sample.xls')

# Iterate through sheets
for sheet in book.sheets():
    print(f"Sheet: {sheet.name}")
    for row in range(sheet.nrows):
        print(sheet.row_values(row))

4. Using Openpyxl to Access Cells Directly

If you need to navigate through cells explicitly rather than rows, openpyxl allows for this:

from openpyxl import load_workbook

wb = load_workbook('sample.xlsx')

# Iterate through sheets and access cell values
for sheet in wb.worksheets:
    for row in range(1, sheet.max_row + 1):
        for column in range(1, sheet.max_column + 1):
            cell_value = sheet.cell(row=row, column=column).value
            print(cell_value)

⚠️ Note: Remember to adjust the loop range to skip the header row if it's not needed.

5. Using Pandas' to_excel for Writing Iterative Data

Pandas not only reads data but can also write iterative data into Excel files:

import pandas as pd

# Example DataFrame
data = {'A': [1, 2, 3], 'B': [4, 5, 6]}
df = pd.DataFrame(data)

# Create an ExcelWriter object
with pd.ExcelWriter('output.xlsx') as writer:
    for i in range(1, 4):
        # Modify DataFrame and write to a new sheet
        df['Sheet'] = f'Sheet {i}'
        df.to_excel(writer, sheet_name=f'Sheet {i}', index=False)

Throughout these methods, we've focused on efficient techniques to iterate through Excel sheets in Python. Each method has its unique advantages:

• openpyxl excels at deep access to Excel files, allowing for complex manipulations.
• pandas offers simplicity and high-level operations for data manipulation, making it ideal for quick data analysis tasks.
• xlrd is beneficial for those who need to work with legacy Excel files.

The key points we've covered are:

• How to read and iterate through sheets using openpyxl, pandas, and xlrd.
• The flexibility of pandas to both read and write data iteratively.
• Some practical notes on handling cell values and headers.

This exploration illustrates Python's powerful integration with Excel, enabling users to handle data in myriad ways. These tools ensure that data analysis, reporting, and even the automation of repetitive Excel tasks are not only possible but also quite efficient.

How do I choose between openpyxl and pandas for Excel operations?

+

If you need complex Excel manipulations or access to cell styles, openpyxl is a better choice. For quick data analysis and high-level operations, pandas is more suitable due to its simplicity and performance.

Can I use these methods on both .xls and .xlsx files?

+

Yes, with some considerations. openpyxl is designed for .xlsx files, pandas can handle both formats, and xlrd is best for .xls files although it can also read .xlsx with an extra library.

What if my Excel file has a large number of sheets?

+

All methods can handle multiple sheets efficiently. However, for very large datasets or numerous sheets, you might need to consider performance optimization techniques like lazy loading or reading chunks of data with pandas.