How to Read Excel File in Python With Multiple Sheets

In Python, handling Microsoft Excel files is a common task, especially in data analysis, report generation, and automation workflows. Traditionally, libraries like openpyxl or pandas are used to manipulate Excel files. However, a lesser-known but powerful library called IronXL is gaining attention due to its robust capabilities and ease of use when working with Excel files in Python.

IronXL, a .NET-based library, enables reading and writing Excel files with various features such as handling multiple sheets, complex formatting, and supporting different Excel file formats. In this article, we’ll explore how to use IronXL Python to read multiple Excel sheets, including those with multiple sheets, and demonstrate some of its key features and functionalities.

Introduction to IronXL

IronXL for Python is a robust library designed to work with Microsoft Excel files. It allows developers to create, read, and manipulate Excel spreadsheets seamlessly. The main advantages of using IronXL include its ease of use, powerful functionalities, and the fact that it does not require Microsoft Excel to be installed on the server. Developers can read multiple files in an Excel file with ease.

How to Read Excel Files in Python with Multiple Sheets

Step 1: Import the IronXL Library.

Step 2: Add License Key.

Step 3: Load the Excel File.

Step 4: Accessing Sheets in the Workbook.

Why Use IronXL?

IronXL is ideal for various applications such as data analysis, reporting, and automation. Its intuitive API and comprehensive features make it a popular choice among developers. Some of the key features of IronXL include:

• No Dependency on Microsoft Excel: IronXL does not require Microsoft Excel to be installed, making it suitable for server environments. Doesnot use import pandas package.
• Support for Multiple Formats: It supports XLS files, XLSX files, and CSV file formats.
• Cross-Platform Compatibility: IronXL works on Windows, macOS, Linux, Docker, Azure, and AWS.
• Ease of Integration: With a natural and intuitive API, IronXL can be easily integrated into any Python project.

Step 1: Import the IronXL Library

Import IronXL using

 pip install IronXL

then, import the necessary libraries from IronXL:

from ironxl import License, WorkBook, WorkSheet

from ironxl import License, WorkBook, WorkSheet

Step 2: Add License Key

IronXL works with a license key, get your free license from here and place the license at the top of the code.

License.LicenseKey = "Your Key"

License.LicenseKey = "Your Key"

Step 3: Load the Excel File

You can load the Excel file using the WorkBook.Load() function. This method returns a Workbook object, which represents the entire Excel file.

workbook = WorkBook.Load('sample.xlsx')

workbook = WorkBook.Load('sample.xlsx')

'INSTANT VB TODO TASK: The following line uses invalid syntax:
'workbook = WorkBook.Load('sample.xlsx')

Step 4: Accessing Sheets in the Workbook

Once the workbook is loaded, you can access individual sheets. IronXL allows you to access sheets by name or index. Let’s assume that your Excel file has multiple sheets.

To access all sheets in the workbook, you can iterate through them:

# Get sheet names by reading excel files
sheet_names = workbook.WorkSheetsNames
print("Sheet Names:", sheet_names)
# Iterate through each sheet and read multiple excel files
for sheet in workbook.WorkSheets:
    print(f"Sheet Name: {sheet.Name}") # specific sheet
    for row in sheet.Rows:
        print([cell.Value for cell in row])

# Get sheet names by reading excel files
sheet_names = workbook.WorkSheetsNames
print("Sheet Names:", sheet_names)
# Iterate through each sheet and read multiple excel files
for sheet in workbook.WorkSheets:
    print(f"Sheet Name: {sheet.Name}") # specific sheet
    for row in sheet.Rows:
        print([cell.Value for cell in row])

Advanced Features of IronXL

In addition to reading and writing data, IronXL comes with many advanced features that allow you to manipulate Excel files more effectively:

• Cell Formatting: IronXL enables you to format cells with different styles, fonts, colors, and number formats.
• Handling Different Excel Formats: It supports .xlsx, .xls, and .csv formats.
• Performance: IronXL is optimized for high performance, making it suitable for working with large Excel files.
• Write Data Back to Excel: Besides reading data, you can also modify and save changes to Excel files using IronXL.

Input Excel file

The Excel file has two sheets:

Full Code Example

To read all the sheets: Here’s the complete code to read an Excel file with multiple sheets:

from IronXL import WorkBook, WorkSheet
# Load the Excel workbook
workbook = WorkBook.Load('sample.xlsx')
# Read Multiple Sheets
for sheet in workbook.WorkSheets:
    print(f"Sheet Name: {sheet.Name}")
    for row in sheet.Rows:
        print([cell.Value for cell in row])

from IronXL import WorkBook, WorkSheet
# Load the Excel workbook
workbook = WorkBook.Load('sample.xlsx')
# Read Multiple Sheets
for sheet in workbook.WorkSheets:
    print(f"Sheet Name: {sheet.Name}")
    for row in sheet.Rows:
        print([cell.Value for cell in row])

Code Explanation

• Importing Libraries: It imports WorkBook and WorkSheet classes from the IronXL library, which are used to work with Excel files and sheets, respectively.
• Loading the Excel Workbook: The Load() method is used to open the Excel file sample.xlsx and load it into a workbook object.
• Iterating through Multiple Sheets: The code loops through all sheets in the workbook using the WorkSheets property. For each sheet, it prints the sheet's name.
• Reading Rows in Each Sheet: For each sheet, it iterates through the rows and prints the values of each cell in that row as a list. The Value property of each cell is used to retrieve the content.

Output

IronXL License (Trial Available)

IronXL works on a valid license file attached to the code. Users can easily get a trial license from the license page.

To use the license, place the license somewhere in the code as below before using the IronXL library.

ironxl.License.LicenseKey = "Your License Key"

ironxl.License.LicenseKey = "Your License Key"

Conclusion

IronXL for Python is a powerful and efficient tool for handling Excel files with multiple sheets. Whether you're reading data, formatting cells, or handling larger files, IronXL simplifies the process, providing a clean and intuitive API. By following the steps above, you can quickly load and manipulate Excel files with multiple sheets in Python. IronXL stands out as an excellent choice for developers and data scientists who need to work with Excel in a seamless and effective way.

For more information and examples, you can refer to the IronXL Documentation.