Read Data from Excel Files in Python — A Comprehensive Guide

4 min readJun 8, 2023

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.