Read Excel Files with Python

Excel files (spreadsheets) are used by people worldwide for organizing, analyzing, and storing tabular data. Due to their popularity, developers frequently encounter situations where they need to extract data from Excel or create reports in Excel format. Being able to read Excel files with Python opens up a wide range of possibilities for data processing and automation. In this article, you will learn how to read data (text or number values) from a cell, a cell range, or an entire worksheet by using the Spire.XLS for Python library.

• Read Data of a Particular Cell in Python
• Read Data from a Cell Range in Python
• Read Data from an Excel Worksheet in Python
• Read Value Rather than Formula in a Cell in Python

Python Library for Reading Excel

Spire.XLS for Python is a reliable enterprise-level Python library for creating, writing, reading and editing Excel documents (XLS, XLSX, XLSB, XLSM, ODS) in a Python application. It provides a comprehensive set of interfaces, classes and properties that allow programmers to read and write Excel files with ease. Specifically, a cell in a worksheet can be accessed using the Worksheet.Range property and the value of the cell can be obtained using the CellRange.Value property.

The library is easy to install by running the following pip command. If you’d like to manually import the necessary dependencies, refer to How to Install Spire.XLS for Python in VS Code

pip install Spire.XLS

Classes and Properties in Spire.XLS for Python API

• Workbook class: Represents an Excel workbook model, which you can use to create a workbook from scratch or load an existing Excel document and do modification on it.
• Worksheet class: Represents a worksheet in a workbook.
• CellRange class: Represents a specific cell or a cell range in a workbook.
• Worksheet.Range property: Gets a cell or a range and returns an object of CellRange class.
• Worksheet.AllocatedRange property: Gets the cell range containing data and returns an object of CellRange class.
• CellRange.Value property: Gets the number value or text value of a cell. But if a cell has a formula, this property returns the formula instead of the result of the formula.

Read Data of a Particular Cell in Python

With Spire.XLS for Python, you can easily get the value of a certain cell by using the CellRange.Value property. The steps to read data of a particular Excel cell in Python are as follows.

• Instantiate Workbook class
• Load an Excel document using LoadFromFile method.
• Get a specific worksheet using Workbook.Worksheets[index] property.
• Get a specific cell using Worksheet.Range property.
• Get the value of the cell using CellRange.Value property

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
wb = Workbook()

# Load an Excel file
wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Data.xlsx")

# Get a specific worksheet
sheet = wb.Worksheets[0]

# Get a specific cell
certainCell = sheet.Range["D9"]

# Get the value of the cell
print("D9 has the value: " + certainCell.Value)

Read Data from a Cell Range in Python

We already know how to get the value of a cell, to get the values of a range of cells, such as certain rows or columns, we just need to use loop statements to iterate through the cells, and then extract them one by one. The steps to read data from an Excel cell range in Python are as follows.

• Instantiate Workbook class
• Load an Excel document using LoadFromFile method.
• Get a specific worksheet using Workbook.Worksheets[index] property.
• Get a specific cell range using Worksheet.Range property.
• Use for loop statements to retrieve each cell in the range, and get the value of a specific cell using CellRange.Value property

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
wb = Workbook()

# Load an existing Excel file
wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Data.xlsx")

# Get a specific worksheet
sheet = wb.Worksheets[0]

# Get a cell range
cellRange = sheet.Range["A2:H5"]

# Iterate through the rows
for i in range(len(cellRange.Rows)):

    # Iterate through the columns
    for j in range(len(cellRange.Rows[i].Columns)):

        # Get data of a specific cell
        print(cellRange[i + 2, j + 1].Value + "  ", end='')

    print("")

Read Data from an Excel Worksheet in Python

Spire.XLS for Python offers the Worksheet.AllocatedRange property to automatically obtain the cell range that contains data from a worksheet. Then, we traverse the cells within the cell range rather than the entire worksheet, and retrieve cell values one by one. The following are the steps to read data from an Excel worksheet in Python.

• Instantiate Workbook class
• Load an Excel document using LoadFromFile method.
• Get a specific worksheet using Workbook.Worksheets[index] property.
• Get the cell range containing data from the worksheet using Worksheet.AllocatedRange property.
• Use for loop statements to retrieve each cell in the range, and get the value of a specific cell using CellRange.Value property

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
wb = Workbook()

# Load an existing Excel file
wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Data.xlsx")

# Get the first worksheet
sheet = wb.Worksheets[0]

# Get the cell range containing data
locatedRange = sheet.AllocatedRange

# Iterate through the rows
for i in range(len(sheet.Rows)):

    # Iterate through the columns
    for j in range(len(locatedRange.Rows[i].Columns)):

        # Get data of a specific cell
        print(locatedRange[i + 1, j + 1].Value + "  ", end='')

    print("")

Read Value Rather than Formula in a Cell in Python

As mentioned earlier, when a cell contains a formula, the CellRange.Value property returns the formula itself, not the value of the formula. If you want to get the value, you need to use the str(CellRange.FormulaValue) method. The following are the steps to read value rather than formula in an Excel cell in Python.

• Instantiate Workbook class
• Load an Excel document using LoadFromFile method.
• Get a specific worksheet using Workbook.Worksheets[index] property.
• Get a specific cell using Worksheet.Range property.
• Determine if the cell has a formula using CellRange.HasFormula property.
• Get the formula value of the cell using str(CellRange.FormulaValue) method

from spire.xls import *
from spire.xls.common import *

# Create a Workbook object
wb = Workbook()

# Load an Excel file
wb.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Formula.xlsx")

# Get a specific worksheet
sheet = wb.Worksheets[0]

# Get a specific cell
certainCell = sheet.Range["D4"]

# Determine if the cell has formula
if(certainCell.HasFormula):

    # Get the formula value of the cell
    print(str(certainCell.FormulaValue))

Conclusion

In this blog post, we learned how to read data from cells, cell regions, and worksheets in Python with the help of Spire.XLS for Python API. We also discussed how to determine if a cell has a formula and how to get the value of the formula. This library supports extraction of many other elements in Excel such as images, hyperlinks and OEL objects. Check out our online documentation for more tutorials. If you have any questions, please contact us by email or on the forum.

• Python Create or Update Excel
• Python Read Formulas In Excel
• Python Read Hyperlinks in Excel
• Python Extract OLE Objects from Excel