Read Data from Excel Files in Python — A Comprehensive Guide
Reading data from Excel files is a crucial task in various fields, including data analysis, reporting, and data visualization. By using Python, we can read data from Excel spreadsheets programmatically and convert them into data frames or other data structures that can be easily manipulated. In this article, we will explore how to read data from Excel files with Python.
We will discuss the following topics:
- Read Data from an Excel Worksheet in Python
- Read Data from a Specific Row in Excel in Python
- Read Data from a Specific Column in Excel in Python
- Read Data from a Specific Cell Range in Excel in Python
- Read the Cell or Data Types in Excel in Python
Python Library to Read Data from Excel Files
To read data from Excel files in Python, this article uses a Python Excel library: Spire.XLS for Python.
Spire.XLS for Python is a multifunctional and easy-to-use library for creating, reading, editing, and converting Excel files within Python applications. With this library, you can easily work with a lot of spreadsheet formats, such as XLS, XLSX, XLSB, XLSM, and ODS. Moreover, you are also able to render Excel files to other types of file formats, such as PDF, HTML, CSV, Text, Image, XML, SVG, ODS, PostScript, and XPS.
You can easily install Spire.XLS for Python from pypi by running the following commands in your terminal:
pip install Spire.Xls
For more detailed information about the installation, you can check this official documentation: How to Install Spire.XLS for Python in VS Code.
Read Data from an Excel Worksheet in Python
The following code example explains how to read data from a specific worksheet in an Excel file in Python:
from spire.xls import *
from spire.xls.common import *
#Create a workbook
workbook = Workbook()
#Load an Excel file
workbook.LoadFromFile("Sample.xlsx")
#Get the first worksheet of the file
worksheet = workbook.Worksheets[0]
#Get the cells collection of the worksheet
cellRangeCollection = worksheet.Cells
str =""
#Iterate through the cells in the collection
for cellRange in cellRangeCollection:
#Get the values of the cells
result = "Cell: " + cellRange.RangeAddress + " Value: " + cellRange.Value
str = str + result +"\n"
#Write the values into a .txt file
with open("WorksheetData.txt", "w") as text_file:
text_file.write(str)
workbook.Dispose()
Read Data from a Specific Row in Excel in Python
The following code example explains how to read data from a specific row in an Excel worksheet in Python:
from spire.xls import *
from spire.xls.common import *
#Create a workbook
workbook = Workbook()
#Load an Excel file
workbook.LoadFromFile("Sample.xlsx")
#Get the first worksheet of the file
worksheet = workbook.Worksheets[0]
#Get the cells collection of the first row
cellRangeCollection = worksheet.Rows[0].Cells
str =""
#Iterate through the cells in the collection
for cellRange in cellRangeCollection:
#Get the values of the cells
result = "Cell: " + cellRange.RangeAddress + " Value: " + cellRange.Value
str = str + result +"\n"
#Write the values into a .txt file
with open("RowData.txt", "w") as text_file:
text_file.write(str)
workbook.Dispose()
Read Data from a Specific Column in Excel in Python
The following code example explains how to read data from a specific column in an Excel worksheet in Python:
from spire.xls import *
from spire.xls.common import *
#Create a workbook
workbook = Workbook()
#Load an Excel file
workbook.LoadFromFile("Sample.xlsx")
#Get the first worksheet of the file
worksheet = workbook.Worksheets[0]
#Get the cells collection of the first column
cellRangeCollection = worksheet.Columns[0].Cells
str =""
#Iterate through the cells in the collection
for cellRange in cellRangeCollection:
#Get the values of the cells
result = "Cell: " + cellRange.RangeAddress + " Value: " + cellRange.Value
str = str + result +"\n"
#Write the values into a .txt file
with open("ColumnData.txt", "w") as text_file:
text_file.write(str)
workbook.Dispose()
Read Data from a Specific Cell Range in Excel in Python
The following code example explains how to read data from a specific cell range in an Excel worksheet in Python:
from spire.xls import *
from spire.xls.common import *
#Create a workbook
workbook = Workbook()
#Load an Excel file
workbook.LoadFromFile("Sample.xlsx")
#Get the first worksheet of the file
worksheet = workbook.Worksheets[0]
#Get the cells collection of a specific cell range
cellRangeCollection = worksheet.Range["A1:C5"].Cells
str =""
#Iterate through the cells in the collection
for cellRange in cellRangeCollection:
#Get the values of the cells
result = "Cell: " + cellRange.RangeAddress + " Value: " + cellRange.Value
str = str + result +"\n"
#Write the values into a .txt file
with open("CellRangeData.txt", "w") as text_file:
text_file.write(str)
workbook.Dispose()
Read the Cell or Data Types in Excel in Python
Reading the cell types in Excel is useful for identifying the type of data that is stored within a particular cell or range of cells. Excel spreadsheets can contain a variety of data types, including strings, numbers, formulas, and other types of data.
The following code example explains how to read the data types of a range of cells in an Excel worksheet in Python:
from spire.xls import *
from spire.xls.common import *
#Create a workbook
workbook = Workbook()
#Load an Excel file
workbook.LoadFromFile("Sample.xlsx")
#Get the first worksheet of the file
worksheet = workbook.Worksheets[0]
#Get the cells collection of a specific cell range
cellRangeCollection = worksheet.Range["A2:A7"].Cells
#Iterate through the cells in the collection
for range in cellRangeCollection:
#Get the data types of the cells
cellType = worksheet.GetCellType(range.Row, range.Column, False)
#Add the results to specific cells of the worksheet
worksheet[range.Row,range.Column + 1].Text = str(cellType)
worksheet[range.Row,range.Column + 1].Style.Font.Color = Color.get_Red()
worksheet[range.Row,range.Column + 1].Style.Font.IsBold = True
#Save the result file
workbook.SaveToFile("GetCellTypes.xlsx", ExcelVersion.Version2016)
workbook.Dispose()
Conclusion
In this article, we have discussed various possible situations of reading data from Excel files in Python. We hope it is helpful to you.
Related Topics
Easily Create or Edit Excel Files with Python
Python — How to Convert Excel XLS or XLSX to PDF
Python — How to Export Data from Database to Excel (Step by Step Guide)