How to Read an Excel file in Python

Processing data stored in Excel files is a frequent task in data analysis and manipulation workflows, be it for extracting data, conducting analysis, or generating reports. Python, with its versatile libraries, offers efficient tools to read, process, and analyze Excel files effortlessly. One such powerful library is IronXL, which provides robust functionality for working with Excel files. In this tutorial, we’ll explore how to read Excel files using the IronXL library. IronXL is a comprehensive Excel file-processing library that supports multiple programming languages, including .NET and Python.

How to Read an Excel file in Python

1. Create or Open Existing Project
2. Install IronXL Library by pip-install IronXL command
3. Add Import
4. Load Excel workbook
5. Select Excel Sheet
6. Read Single Cell Value
7. Read Range of Cell Value

What is IronXL:

IronXL is a comprehensive Excel file-processing library that simplifies working with Excel files in Python. IronXL simplifies tasks like reading, editing, exporting data, and manipulating Excel files. It provides features like loading existing Excel workbooks, creating new documents, reading and writing cell values, and exporting data. If you're dealing with spreadsheets, IronXL is a valuable resource for Python Developers!

Create or Open a New Project:

Start by Creating a new Project or opening an existing One. You can use any Python IDE, I am using Microsoft Visual Studio 2022.

Install IronXL:

To install a Python library using the Visual Studio Package Manager Console, follow the following steps,

1. Go to View > Other Windows > Package Manager Console.
2. In the Package Manager Console, you'll see a command line interface.
3. Write the following command in the Package Manager Console. Pip Install ironXL
4. Press Enter to execute the command.
5. You'll see the installation process running in the Package Manager Console, and once it's finished, the library will be installed and ready to use in your Python projects.

Read Excel files in Python:

Now, Let's begin writing code for reading Excel files in Python.

Import IronXL:

Write the following line of code to import classes from ironXL.

from IronXL import WorkBook;

Add License Key:

You need to add a License Key for using IronXL in your Project. You can get a free trial key from here. Just Provide your email, and get your license key within a minute for free. No Credit Card Required.

License.LicenseKey = "IRONSUITE.myEmail.GMAIL.COM.0000-BA96C17620-A5432QKH-DEPLOYMENT.TRIAL-EFTQGH.TRIAL.EXPIRES.16.APR.2025";

Loading an Existing Excel Workbook:

Let’s start by loading an existing Excel workbook. The WorkBook class represents an Excel sheet. To open an Excel file, use the WorkBook.Load method, specifying the path to the Excel file:

from IronXL import WorkBook

# Load an existing spreadsheet
workbook = WorkBook.Load("student.xlsx")

# Get a specific worksheet
worksheet = workbook.GetWorkSheet("Sheet1")

The above code initializes a connection to an existing Excel file (“student.xlsx”), loads its contents into a WorkBook object (a file like object), and then retrieves a specific worksheet (“Sheet1”) for further manipulation

Code Explanation:

Import Statement:

from IronXL import WorkBook: This line imports the WorkBook class from the IronXL library. The WorkBook class represents an Excel workbook (spreadsheet) that we can manipulate.

Loading an Existing Spreadsheet:

We load an existing Excel spreadsheet named “student.xlsx”. The Load method creates a WorkBook object by reading the contents of the specified Excel file. This allows us to work with the data within the workbook.

Accessing a Specific Worksheet:

After loading the workbook, we retrieve a specific worksheet from it. In this case, we’re getting the worksheet named “Sheet1”. The GetWorkSheet method returns a WorkSheet object representing that particular sheet. We can then perform various operations on this worksheet, such as reading cell values, writing data, or formatting. This method helps us to choose a particular sheet for work from multiple sheets available.

The following Excel file will be loaded in memory.

Reading Cell Values of Excel File

Now that, we have loaded our Excel file in memory, Let's read its cell values. IronXL Provides two ways to read Cell values from an Excel sheet. The two ways are:

• Reading Single Cell Values
• Reading Range of Cell Values

Reading Single Cell Values:

To access the value of an individual spreadsheet cell using IronXL for Python, you can retrieve the desired cell from its corresponding worksheet. Here's how you can do it:

print("Students Names are:")
for x in range(2,12):
    cell = worksheet[f"C{x}"];
    print(cell)

This will read Cell Value one by one, and print all user Name on Output. The for loop iterates over a range of numbers from 2 (inclusive) up to, but not including, 12 (exclusive). Inside the loop, we retrieve the value of a specific cell from the worksheet. The cell is located in column “C” and the row number is determined by the current value of x. For example, when x is 2, we retrieve the value from cell “C2”. Finally, we print the value of the retrieved cell to the console. This will display the content of the cell (e.g., a student’s name) for each iteration of the loop.
The output is as:

Final Complete Code is:

from ironxl import *;

License.LicenseKey = "IRONSUITE.DIGITAL.STORIESLAP.GMAIL.COM.21393-BD96A13220-A26WQKH-NUH2Q7CJTNST-HQ6Z4LJ63B2S-W6426L4SY3XU-CAALJQJNSSDJ-AYDAKEAKB5HN-Q6GZDNEIRU2I-3O65MH-TQJ7652IHP2MEA-DEPLOYMENT.TRIAL-EFTQGH.TRIAL.EXPIRES.16.APR.2024";
workbook = WorkBook.Load("student.xlsx")

# Get a specific worksheet
worksheet = workbook.GetWorkSheet("Sheet1")
print("Students Names are:")
for x in range(2,12):
    cell = worksheet[f"C{x}"];
    print(cell)

Reading Range of Cell Values:

To read a range of cell values from a worksheet using IronXL for Python, you can obtain a Range object representing the desired collection of cells. Here's how you can do it:

# Access range C1:C12 in the worksheet
student_names = worksheet["C2:C12"]

for stu_name in student_names:
    print(stu_name)

The above code retrieves the student names from cells in column “C” (rows 2 to 12) of the worksheet by giving cell range.

Code Explanation:

student_names = worksheet["C2:C12"]: This line creates a range of cells in column “C” (from row 2 to row 12) within the specified worksheet. The variable student_names now holds this range of cells. The loop iterates through each cell in the student_names range. For each iteration, the value of the current cell (student name) is assigned to the variable stu_name. Inside the loop, we print the value of the current student name to the console. This will display each student’s name from the specified range.

The output is as

In this way, IronXL Provides an efficient way of reading Excel files. It supports .xlsx file and CSV file formats. It allows us to work with multiple Excel sheets. You can import and manipulate Excel files into Python using IronXL, allowing you to read and write Excel files effortlessly. Data can be organized into columns stored within the spreadsheet, enabling easy manipulation and analysis before exporting back to Excel.

Conclusion:

In conclusion, IronXL offers a seamless solution for handling Excel files in Python, providing efficient tools for reading, editing, and exporting data. With its comprehensive functionality, IronXL simplifies tasks such as loading existing Excel workbooks, accessing specific worksheets, and retrieving cell values effortlessly. Users can explore its capabilities through a free trial, allowing them to assess its suitability for their needs before making a purchase. Whether it's data analysis, reporting, or other tasks, IronXL empowers Python developers to enhance productivity and efficiency in Excel file processing workflows, ensuring a smooth transition from trial to licensed use for continued benefit.