How to Read Excel Files in Python with IronXL

This guide provides Python developers with step-by-step instructions on utilizing the IronXL library to read and edit Microsoft Excel documents.

IronXL is a comprehensive Excel file processing library that supports multiple programming languages, including .NET and Python. This tutorial focuses specifically on using IronXL in Python scripts to read and edit Microsoft Excel documents.

For a separate tutorial on how to read and edit Microsoft Excel documents in .NET applications, please refer to the following here.

Reading and creating Excel files in Python is easy using the IronXL for Python software library.


Overview

How to Read Excel File in Python

  1. Download the Python Library to read Excel files
  2. Load and read an Excel file (workbook)
  3. Create an Excel workbook in CSV or XLSX
  4. Edit cell values in a range of cells
  5. Validate spreadsheet data
  6. Export data using Entity Framework

Tutorial

Step 1: Add IronXL as a Dependency in Your Python Project

To integrate the IronXL library into your Python project, you must install it as a dependency using the widely used Python package manager, pip. Open the terminal and execute the following command:

 pip install IronXL

This will install the specified version of IronXL in your project, making it accessible for import.

Please note
IronXL for Python relies on the IronXL for .NET library. Therefore, it is necessary to have the .NET 6.0 SDK installed on your machine in order to use IronXL for Python.


Step 2: Load an Excel Workbook

The WorkBook class represents an Excel workbook. To open an Excel file, we use the WorkBook.Load method, specifying the path of the Excel file.

:path=/static-assets/excel-python/content-code-examples/tutorials/how-to-read-excel-file-csharp-1.py
# Load existing spreadsheet
workbook = WorkBook.Load("Spreadsheets\\GDP.xlsx")
PYTHON

Each WorkBook can have multiple WorkSheet objects. Each one represents a single Excel worksheet in the Excel document. Use the WorkBook.get_worksheet method to retrieve a reference to a specific Excel worksheet.

:path=/static-assets/excel-python/content-code-examples/tutorials/how-to-read-excel-file-csharp-2.py
# Assuming workBook is an existing instance of WorkBook
workSheet = workBook.GetWorkSheet("GDPByCountry")
PYTHON

Creating new Excel Documents

To create a new Excel document, construct a new WorkBook object with a valid file type.

:path=/static-assets/excel-python/content-code-examples/tutorials/how-to-read-excel-file-csharp-3.py
# Create a new WorkBook with the specified Excel file format
workBook = WorkBook(ExcelFileFormat.XLSX)
PYTHON

Note: Use ExcelFileFormat.XLS to support legacy versions of Microsoft Excel (95 and earlier).

Add a Worksheet to an Excel Document

As explained previously, an IronXL for Python WorkBook contains a collection of one or more WorkSheets.

This is how one workbook with two worksheets looks in Excel.

This is how one workbook with two worksheets looks in Excel.

To create a new worksheet, call workbook.create_worksheet with the name of the worksheet.

:path=/static-assets/excel-python/content-code-examples/tutorials/how-to-read-excel-file-csharp-4.py
workSheet = workBook.CreateWorkSheet("GDPByCountry")
PYTHON

Access Cell Values

Read and Edit a Single Cell

Access to the values of individual spreadsheet cells is carried out by retrieving the desired cell from its WorkSheet as shown below:

:path=/static-assets/excel-python/content-code-examples/tutorials/how-to-read-excel-file-csharp-5.py
# Load existing spreadsheet
workbook = WorkBook.Load("test.xlsx")
worksheet = workbook.DefaultWorkSheet

# Access cell B1 in the worksheet
cell = worksheet["B1"]
PYTHON

IronXL for Python's Cell class represents an individual cell in an Excel spreadsheet. It contains properties and methods that enable users to access and modify the cell's value directly.

With a reference to a Cell object, we can read and write data to and from a spreadsheet cell.

Read and Write a Range of Cell Values

The Range class represents a two-dimensional collection of Cell objects. This collection refers to a literal range of Excel cells. Obtain ranges by using the string indexer on a WorkSheet object.

:path=/static-assets/excel-python/content-code-examples/tutorials/how-to-read-excel-file-csharp-6.py
# Access cell B1 in the worksheet
cell = workSheet["B1"]

# Read the value of the cell as a string
value = cell.StringValue
print(value)

# Write a new value to the cell
cell.Value = "10.3289"
print(cell.StringValue)
PYTHON

Add Formula to a Spreadsheet

Set the formula of Cells with the formula property.

:path=/static-assets/excel-python/content-code-examples/tutorials/how-to-read-excel-file-csharp-7.py
# Access range D2:D101 in the worksheet
range_ = workSheet["D2:D101"]
PYTHON

The code below iterates through each cell and sets a percentage total in column C.

:path=/static-assets/excel-python/content-code-examples/tutorials/how-to-read-excel-file-csharp-8.py
# Iterate through all rows with a value
for y in range(2, i):
    # Get the C cell
    cell = workSheet[f"C{y}"]
    # Set the formula for the Percentage of Total column
    cell.Formula = f"=B{y}/B{i}"
PYTHON

Summary

IronXL.Excel is a standalone Python library for reading a wide variety of spreadsheet formats. It does not require Microsoft Excel to be installed and is not dependent on Interop.

Frequently Asked Questions

What is this Python library for handling Excel files?

IronXL for Python is a comprehensive library for reading and editing Excel files in Python without needing Microsoft Excel installed. It supports various spreadsheet formats and is based on the IronXL .NET library.

How do I install this Python library for Excel file manipulation?

You can install IronXL for Python using pip, the Python package manager, by executing `pip install ironxl` in your terminal.

What are the prerequisites for using this Python tool for Excel?

IronXL for Python requires the .NET 6.0 SDK to be installed on your machine as it relies on the IronXL .NET library.

How do I load an Excel workbook using this library?

To load an Excel workbook, use the `WorkBook.Load` method and provide the path to the Excel file. For example: `workbook = ironxl.WorkBook.load('path/to/workbook.xlsx')`.

Can I create a new Excel document with this Python library?

Yes, you can create a new Excel document by constructing a new `WorkBook` object and adding worksheets using `workbook.create_worksheet('SheetName')`.

How do I read and edit a cell's value in this Excel handling library?

Access a cell by specifying its index, such as `cell = worksheet['A1']`. You can read its value with `cell.value` and edit it by assigning a new value, like `cell.value = 'New Value'`.

Is it possible to work with a range of cells in this Excel library?

Yes, the `Range` class allows you to work with a two-dimensional collection of cells. Access a range using the string indexer like `range_of_cells = worksheet['B2:E5']` and iterate over them to perform operations.

How can I add formulas to cells using this Python Excel library?

Set a formula for a cell using the `formula` property. For example, `cell.formula = '=A1+B1'` to add values in cells A1 and B1.

Does this Python library require Microsoft Excel to be installed?

No, IronXL does not require Microsoft Excel to be installed, nor does it depend on Microsoft Office Interop.

Chaknith Bin
Software Engineer
Chaknith works on IronXL and IronBarcode. He has deep expertise in C# and .NET, helping improve the software and support customers. His insights from user interactions contribute to better products, documentation, and overall experience.
Talk to an Expert Five Star Trust Score Rating

Ready to Get Started?