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
- Download the Python Library to read Excel files
- Load and read an Excel file (workbook)
- Create an Excel workbook in CSV or XLSX
- Edit cell values in a range of cells
- Validate spreadsheet data
- 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.
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")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")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)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.
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.pyworkSheet = workBook.CreateWorkSheet("GDPByCountry")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"]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)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"]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}"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
How do I read Excel files in Python?
You can read Excel files in Python using IronXL by loading the workbook with the WorkBook.Load method. Simply provide the path to your Excel file, like workbook = ironxl.WorkBook.load('path/to/workbook.xlsx').
Can I edit Excel files in Python without Excel installed?
Yes, with IronXL, you can edit Excel files in Python without needing Microsoft Excel installed. You can modify cell values, add worksheets, and apply formulas directly through the library.
How can I install the IronXL library for Python?
To install IronXL for Python, use the pip package manager by running the command pip install ironxl in your terminal.
What are the advantages of using IronXL for Excel file manipulation in Python?
IronXL allows you to handle Excel files without requiring Microsoft Excel. It offers features such as reading, editing, creating new workbooks, and applying formulas, making it a robust solution for spreadsheet manipulation.
How do I access and modify a specific cell in an Excel sheet using IronXL?
To access a specific cell in an Excel sheet using IronXL, use the cell index like cell = worksheet['A1']. You can read the value using cell.value and modify it by assigning a new value with cell.value = 'New Value'.
Is it possible to work with ranges of cells in IronXL?
Yes, IronXL allows you to work with cell ranges using the Range class. You can access a range of cells with a string indexer like range_of_cells = worksheet['B2:E5'] and perform operations over them.
How do I add formulas to Excel cells using IronXL in Python?
To add formulas to Excel cells using IronXL, set the formula using the formula property on a cell. For instance, cell.formula = '=A1+B1' adds the values in cells A1 and B1.
Does IronXL require Microsoft Office Interop?
No, IronXL does not require Microsoft Office Interop. It operates independently of Microsoft Excel, allowing you to process Excel files without additional dependencies.
