How to Read Excel Files in Python with IronXL

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

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.py

workSheet = workBook.CreateWorkSheet("GDPByCountry")

Access Cell Values

: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"]

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

: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"]

: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}"