Pandas Read Excel Alternatives (Without Using Interop) | IronXL Python

Excel files are ubiquitous in data analysis and manipulation tasks, offering a convenient way to store and organize tabular data. In Python, there are multiple libraries available for reading Excel files, each with its own set of features and capabilities. Two prominent options are Pandas and IronXL, both offering efficient methods for reading Excel files in Python.

In this article, we'll compare the functionality and performance of Pandas and IronXL to Read Excel files in Python.

Pandas - Open Source Library

Pandas is a powerful open-source data analysis and manipulation library for Python. It introduces the DataFrame data structure, which is a two-dimensional labeled data structure with columns of potentially different types. Pandas offers a wide range of functionalities for data manipulation, including reading and writing data from various sources, such as CSV files, SQL databases, and Excel files.

Some key features of Pandas include:

DataFrame

Pandas introduces the DataFrame data structure, which is essentially a two-dimensional labeled data structure with columns of potentially different types. It's similar to a spreadsheet or SQL table, making it easy to perform operations like filtering, grouping, and aggregation on tabular data.

Data manipulation

Pandas offers a wide range of functions for data manipulation, including merging, reshaping, slicing, indexing, and pivoting data. These operations allow users to clean, transform, and prepare data for analysis or visualization efficiently.

Time series functionality

Pandas provides robust support for working with time series data, including tools for date/time indexing and resampling, and convenient methods for handling missing data and time zone conversion.

Integration with libraries

Pandas can seamlessly collaborate with various Python libraries frequently employed in data analysis and scientific computations, including NumPy, Matplotlib, and Scikit-learn. This interoperability allows users to leverage the strengths of different libraries within a single analysis workflow.

Overall, Pandas is a powerful tool for data manipulation and analysis in Python, and it's widely used in various domains, including finance, economics, biology, and social sciences.

IronXL- The Python Excel Library

IronXL is a Python library designed specifically for working with Excel files. It provides an intuitive API for reading, writing, and manipulating Excel documents in Python. IronXL aims to simplify Excel file operations by offering a straightforward interface and eliminating the need for external dependencies, such as Microsoft Excel or Excel Interop.

Some key features of IronXL are listed below:

Intuitive Python 3+ Excel Document API

IronXL offers a Python 3+ Excel document API that's intuitive and easy to use, allowing developers to seamlessly read, edit, and create Excel spreadsheet files.

Cross-Platform Support

Designed for Python 3+ and compatible with Windows, Mac, Linux, and cloud platforms, IronXL ensures flexibility in deployment environments.

No Need for Microsoft Office or Excel Interop

Developers can work with Excel files in Python without installing Microsoft Office or dealing with Excel Interop, simplifying the integration process and minimizing dependencies.

Compatibility

Supports Python 3.7+ on various operating systems including Microsoft Windows, macOS, Linux, Docker, Azure, and AWS. Compatible with popular IDEs like JetBrains PyCharm and other Python IDEs.

Versatile Workbooks Handling

Create, load, save, and export spreadsheets in various formats including XLS, XLSX, XSLT, XLSM, CSV, TSV, JSON, HTML, Binary, and Byte Array.

Powerful Worksheet Editing

Edit metadata, set permissions and passwords, create and remove worksheets, manipulate sheet layout, handle images, and more.

Advanced Cell Range Operations

Perform various operations on cell ranges such as sorting, trimming, clearing, copying, finding and replacing values, setting hyperlinks, and merging and unmerging cells.

Flexible Cell Styling

Customize cell styles including font, size, border, alignment, and background pattern, and apply conditional formatting.

Math Functions and Data Formats

Utilize math functions like average, sum, min, and max, and set cell data formats including text, number, formula, date, currency, scientific, time, boolean, and custom formats.

Creating a Python Project using PyCharm

First of all, Python needs to be installed on your machine. Install the latest version of Python 3.x from the official Python website. When installing Python, ensure you choose the option to add Python to the system PATH, allowing access from the command line.

To demonstrate the functionality of both Pandas and IronXL in reading Excel files, let's create a Python project using PyCharm, a popular integrated development environment (IDE) for Python.

1. Open PyCharm and create a new Python project.

   

2. Configure the Project as follows:

   • Give the project a name. In this case "pythonReadExcel"

   • Choose the desired location of the project

   • Choose the Interpreter type: Project venv

   • Select Python version

   

3. Click "Create" to create the project.

Install Pandas and IronXL using pip

Installing Pandas

To install Pandas in your Project, you can follow these steps:

1. Open Command Prompt or Terminal: In PyCharm, from View->Tool Windows->Terminal.

   

2. Install Pandas via pip: Pandas can be installed using the pip package manager. Run the following command in the terminal:

   pip install pandas

   This command installs the Pandas library and its dependencies from the Python Package Index (PyPI).

   

3. Install OpenPyXL via pip: OpenPyXL is the library that helps read and write Excel files. It is one of the dependencies used by Pandas. When you install Pandas, OpenPyXL is automatically installed if not present already. If somehow it isn't installed, then you can install it using the following command in the terminal:

pip install openpyxl

Installing IronXL

To install IronXL in a Python project, follow these steps:

1. Ensure Prerequisites: Before installing IronXL, make sure you have the necessary prerequisites installed on your system:

   .NET 6.0 SDK: IronXL relies on the IronXL .NET library, specifically .NET 6.0, as its underlying technology. Ensure that you have the .NET 6.0 SDK installed on your machine. You can download it from the official.NET website.

2. Open Command Prompt or Terminal: Do the same as before.
3. Install IronXL via pip: IronXL can be installed using the pip package manager. Run the following command:

 pip install IronXL

This command will collect, download, and install the IronXL library and its dependencies from the Python Package Index (PyPI).

Reading Excel Files using Pandas and IronXL

As we have set up everything, we'll move on to reading Excel files using both libraries. The demo Excel file that we are going to read has the following values with header row as Name, Marks, and Res:

Using Pandas

Step 1

Import the Pandas library and use the read_excel() function to read column data from the Excel file.

import pandas as pd
# Read the Excel file
df = pd.read_excel("file.xlsx")

import pandas as pd
# Read the Excel file
df = pd.read_excel("file.xlsx")

When using Pandas' read_excel() function, you can specify several options for displaying as required:

header: Specifies which row in the Excel file to use as column names. You can set it to None to indicate that there is no header row, or you can provide an integer indicating the row number. If skipped, the headers are set to bool default true, and the first row positions get displayed as header row labels.

index_col: Specifies which column or columns to use as the index of the DataFrame. You can pass a single column name or column index. Or you can pass a list of column names or column indices to create a MultiIndex.

sheet_name: Specifies the sheet(s) to read from the Excel file. You can provide the sheet name as a string, or an integer indicating the zero-indexed sheet positions.

usecols: Specifies which columns to read from the Excel file. You can pass either a single column name or a column index. Or you could pass a list of column names or column indices to read specific columns.

dtype: Specifies the data types for columns. You can pass a dictionary where keys are column names or column indices and values are the desired data types.

converters: Specifies functions to apply to columns for custom parsing. You can pass a dictionary where keys are column names or column indices and values are functions.

na_values: Specifies additional strings to recognize as NaN (Not a Number) values. You can pass a list of strings to be treated as NaN.

parse_dates: Specifies which columns to parse as dates. You can pass either a single column name or a column index. Or you could pass a list of column names or indices to parse as dates.

date_parser: Specifies a function to use for parsing dates. You can pass a function that accepts a string and returns a datetime object.

skiprows: Specifies the number of rows to skip at the beginning of the Excel file.

These options provide flexibility when reading Excel files with Pandas, allowing you to customize the reading process according to your specific requirements.

Step 2

Display the contents of the DataFrame.

print(df)

print(df)

Here is the output of the above code:

Using IronXL

Step 1: Import the IronXL library and use the WorkBook.Load() method to load the Excel file. In the Load method parameter, you can pass the valid file URLs, local file path object, or filename if it is in the same directory as the script.

from ironxl import WorkBook
# Load the Excel file like object
workbook = WorkBook.Load("file.xlsx")

from ironxl import WorkBook
# Load the Excel file like object
workbook = WorkBook.Load("file.xlsx")

Step 2: With IronXL, you can request multiple sheets and also print column labels. Access the worksheets and cells to read the columns stored data. The cells can be of any data type like numeric columns, or string columns. The cell values can be converted to int by parsing string columns to numeric values using the IntValue property and vice versa.

# Access the first worksheet
# Loads the first sheet from list of int default worksheets
worksheet = workbook.DefaultWorkSheet
# Select a cell and return the converted value
cell_value = worksheet["A2"].IntValue
print(cell_value)
# Read from the entire worksheet elegantly.
for cell in worksheet:
    print("Cell {} has value '{}'".format(cell.AddressString, cell.Text))

# Access the first worksheet
# Loads the first sheet from list of int default worksheets
worksheet = workbook.DefaultWorkSheet
# Select a cell and return the converted value
cell_value = worksheet["A2"].IntValue
print(cell_value)
# Read from the entire worksheet elegantly.
for cell in worksheet:
    print("Cell {} has value '{}'".format(cell.AddressString, cell.Text))

Here is the output of the above code with a proper display format showcasing the versatility of IronXL:

For more information on working with Excel files, please visit this code examples page.

Conclusion

In conclusion, both Pandas and IronXL offer efficient methods for reading Excel files in Python. However, IronXL provides several advantages over Pandas, particularly in terms of ease of use, performance, and specialized Excel handling capabilities. IronXL's intuitive API and comprehensive features make it a superior choice for projects requiring extensive Excel manipulation tasks.

Additionally, IronXL eliminates the need for external dependencies like Microsoft Excel or Excel Interop, simplifying the development process and enhancing portability across different platforms. Therefore, for Python developers seeking a robust and efficient solution for Excel file operations, IronXL emerges as the preferred choice, offering better facilities and enhanced functionalities compared to Pandas. For more detailed information on IronXL, please visit this documentation page.

IronXL provides a free trial to test out its functionality and feasibility for your Python projects. This trial allows developers to explore the full range of features and capabilities offered by IronXL without any financial commitment upfront. Whether you're considering IronXL for data import/export tasks, report generation, or data analysis, the free trial offers an opportunity to evaluate its performance and suitability for your specific requirements.

For more information on licensing options and to download the free trial, visit the IronXL website's licensing page. Here, you'll find detailed information about licensing terms, including options for commercial usage and support. To get started with IronXL and experience its benefits firsthand, download the library from here.