5 Ways to Parse Multi-Sheet Excel in Python

Parsing Excel files is an integral part of data processing, especially when you deal with spreadsheets that have multiple sheets. Each sheet can contain different datasets that require different methods of processing. This article will explore five efficient ways to parse multi-sheet Excel files in Python, ensuring that you can easily manage, analyze, and manipulate complex Excel data structures.

Using openpyxl

Openpyxl is a popular Python library for reading and writing Excel 2010 xlsx/xlsm/xltx/xltm files. Here’s how you can use it to parse multiple sheets:

• Install the library if you haven't already:

 pip install openpyxl 

• Load the workbook and access sheets:

from openpyxl import load_workbook

wb = load_workbook(filename = 'example.xlsx')
sheet_names = wb.sheetnames  # List all sheet names

# Accessing specific sheet by name
sheet = wb['Sheet1']

# Reading data
for row in sheet.iter_rows(min_row=1, max_row=5, values_only=True):
    for cell in row:
        print(cell)

📝 Note: Openpyxl is excellent for small to medium-sized Excel files, but for very large files, consider using libraries like xlrd or pandas for better performance.

Pandas

Pandas provides a DataFrame object, which can be very useful for data manipulation, including parsing Excel files:

• Ensure pandas is installed:

 pip install pandas 

• Parse multiple sheets:

import pandas as pd

excel_file = 'example.xlsx'
sheets = pd.read_excel(excel_file, sheet_name=None)

for sheet_name, data in sheets.items():
    print(f"Data from sheet: {sheet_name}")
    print(data.head())  # Print first few rows of the sheet

• Specifying multiple sheets by name or index:

# Multiple sheets by name
data = pd.read_excel(excel_file, sheet_name=['Sheet1', 'Sheet2'])

# By index
data = pd.read_excel(excel_file, sheet_name=[0, 1])

xlrd

xlrd is one of the oldest libraries for reading data and formatting information from Excel files. Although it doesn’t support newer formats like .xlsx directly, it’s useful for .xls files:

• Install:

 pip install xlrd 

• Reading multiple sheets:

import xlrd

wb = xlrd.open_workbook('example.xls')

for sheet in wb.sheets():
    print(f"Sheet: {sheet.name}")
    for rowx in range(sheet.nrows):
        print(sheet.row_values(rowx))

💡 Note: xlrd has issues with newer Excel file formats; openpyxl or pandas are better alternatives for .xlsx files.

pyexcel

PyExcel is another library designed to work with many Excel file formats, simplifying the reading and writing process:

• Install with:

 pip install pyexcel-xlsx 

• Parse an Excel file:

from pyexcel_xlsx import get_data

data = get_data('example.xlsx')

for sheet_name in data.keys():
    print(f"Sheet: {sheet_name}")
    for row in data[sheet_name]:
        print(row)

Automating Excel with Python

While not a single library approach, this method involves using the Excel Application Object via pywin32 or pyexcelerate for Windows users:

• Install pywin32:

 pip install pywin32 

• Script to automate Excel:

import win32com.client as win32
excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Open('example.xlsx')

for sheet in wb.Sheets:
    print(f"Sheet: {sheet.Name}")
    for row in range(1, sheet.Rows.Count + 1):
        print([sheet.Cells(row, col).Value for col in range(1, sheet.Columns.Count + 1)])

wb.Close(False)
excel.Quit()

📈 Note: This method is very resource intensive and should be used when you need Excel's specific functions or to automate operations beyond simple data extraction.

From openpyxl's straightforward approach to pandas' data manipulation capabilities, each library or method offers unique benefits when parsing multi-sheet Excel files. Here are some final considerations:

• Data Integrity: Always check for consistent formatting across sheets to ensure the integrity of your data.
• Performance: Choose the library that best fits the size of your Excel files. Pandas is generally faster for larger files, while openpyxl might be more suitable for smaller, simpler tasks.
• Functionality: For complex tasks involving Excel-specific functions or automation, using Excel directly through pywin32 can be advantageous despite the performance hit.
• Maintainability: Consider the readability and maintainability of your code. Libraries like pandas provide a cleaner, more Pythonic approach, which is beneficial for sharing and collaborating on scripts.

The choice of tool depends on your specific requirements, the nature of the Excel files, and your comfort level with each library or method. By selecting the right tool, you can streamline your data processing tasks, making your workflow more efficient and less error-prone.

What is the best method to parse large Excel files?

+

For large Excel files, using Pandas with pd.read_excel() is generally the most efficient due to its optimized performance for handling big datasets. Consider also xlrd or openpyxl with streaming methods for extremely large files.

Can I use these libraries to modify Excel files as well?

+

Yes, libraries like openpyxl and Pandas can also write data back to Excel files, though for complex operations or UI interaction, pywin32 might be more suitable.

What should I do if my Excel file has formatting I need to preserve?

+

Use openpyxl or pywin32 which can handle and preserve formatting when reading and writing Excel files. Pandas focuses more on data rather than formatting.