Table of Contents
Install with Pip
pip install Spire.XLS
Related Links
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
- Python
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
- Python
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
- Python
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
- Python
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.