Read Excel File in Python Without Pandas (No Interop Needed)

Introduction

Reading Microsoft Excel files in Python is an essential skill for various data-related tasks and has become a standard practice in fields like data science, data analysis, business intelligence, and automation. Excel is one of the most commonly used tools for storing and organizing data due to its user-friendly interface and extensive features. When dealing with Microsoft Excel, pandas is the first library which comes to mind, but there are other robust libraries like IronXL which deliver performance and speed.

IronXL for Python is a robust library designed to work with Excel files. It allows developers to create, read, analyze data 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.

How to Read Excel File in Python Without Pandas Using IronXL.

Step 1: Import the IronXL Library.

Step 2: Add License Key.

Step 3: Load the Excel File.

Step 4: Read Excel files using IronXL.

Introducing IronXL

IronXL provides robust features for handling Excel files, including support for multiple sheets, cell formatting, reading and writing different file formats (like .xlsx and .xls), and working with large datasets, making it a versatile tool for data manipulation tasks.

Key Features of IronXL Python library

1. Easy Excel File Handling: IronXL simplifies the process of reading, writing, and editing Excel files in Python. Using IronXL you can manipulate Excel files and save them with ease. It also supports CSV files, XLS files.

2. Multiple Sheet Support: IronXL can easily read and write data across multiple sheets within a single Excel workbook. This is ideal when dealing with Excel files that contain different types of data or reports across various sheets.

   3. Read and Write Data:

      • Reading Data: IronXL allows you to access individual cells or entire rows and columns in an Excel file. It supports reading values from all types of cells including text, numbers, and dates.
   • Writing Data: The library supports to write Excel files, whether you need to update existing cells or add new data to the file. It also supports csv format without import csv, XLSX files
3. Cell Formatting: IronXL provides the ability to format Excel cells. You can set text color, font size, borders, and other visual styles directly within the Python script. This is useful for creating reports with specific formatting or when you need to highlight certain data points.
4. Compatibility with Various Excel Formats: IronXL can handle multiple Excel file formats, including .xlsx file format, .xls file format, and .csv file format. This makes it a versatile choice for interacting with Excel files that are shared or exported from various systems.
5. Support for Large Files: IronXL is optimized to handle large Excel files with many rows and columns, making it suitable for data analysis tasks that involve massive datasets.
6. Cross-Platform: While IronXL is built on .NET technologies, it is cross-platform and can be used on Windows, macOS, and Linux through Python.

Step 1: Import the IronXL Library

Import IronXL using python package manager for reading Excel spreadsheet data

pip install ironxl

pip install ironxl

Next step is to import the necessary libraries from IronXL:

from ironxl import License, WorkBook, WorkSheet

py

Step 2: Add License Key

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

License.LicenseKey = "Your Key"

py

Step 3: Load the Excel File

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')

py

Step 4: Read Excel files using IronXL

Now using the workbook object read the sheets and then read the data in the sheets

for sheet in workbook.WorkSheets:
    print(f"Sheet Name: {sheet.Name}")
    for row in sheet.Rows:
        print([cell.Value for cell in row]) # excel data

py

Complete Code

Input Excel file

from ironxl import License, WorkBook, WorkSheet
License.LicenseKey = "Your Key"
# 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])

py

Code Explanation

The provided code demonstrates how to use IronXL to read data from an Excel file with multiple sheets. Here's an explanation of the code step by step:

1. License Key Setup

The first line of the code sets up the IronXL license by specifying the license key using the License.LicenseKey property. This is required for activating the IronXL library and ensuring it can be used without restrictions.

2. Loading the Excel Workbook

The second line uses the WorkBook.Load() method to load an Excel file (sample.xlsx) into a WorkBook object. This object represents the entire workbook (i.e., the Excel file) and contains all the sheets and data within the file.

3. Iterating Through Sheets

The code then begins a loop through the sheets in the workbook. The WorkBook.WorkSheets property gives access to all the sheets in the loaded workbook. For each sheet, it prints the name of the sheet using sheet Name. This is useful for identifying which sheet is being processed, especially when working with multiple sheets.

4. Reading and Printing Data from Each Sheet

Within each sheet, the code loops through each row using sheet.Rows. Each row is a collection of cells in that specific row. The code then extracts the value of each cell in the row using cell.Value, and prints it as a list for each row.

Output

IronXL License (Trial Available)

IronXL works on a valid license key in 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"

py

Conclusion

IronXL is the best alternative to pandas module when it comes to handling Excel files. It provides a powerful and straightforward way to handle Excel files in Python without the need to import pandas. It supports various Excel formats, which makes it an excellent choice for developers looking to automate data processing and reporting tasks. Developers looking for a pandas alternative can easily start using IronXL which has full support for reading writing and manipulating Excel sheets.