The xlrd Module - How To Handle Excel Files In Python?

Have you ever wondered how can one handle the MS-Excel files using a programming language? In this tutorial, we will be learning how to handle Excel files with the help of the xlrd module in the Python Programming Language.

So let’s get started!

Introduction to MS-Excel

Microsoft Excel is one of the most significant computer applications because of the critical function it plays in many industries. It is the most widely used spreadsheet application in various corporate tasks, classroom assignments, and even personal data management.

Excel was initially introduced in 1985. Since then, it has played an important part in formula-based arithmetic and computations, as well as other activities that may necessitate mathematical calculations.

Excel has been used by many organizations, personal and institutional enterprises because of its usability and capacity to serve as a visual basis for many applications.

Introduction to xlrd module

The xlrd module may be used to obtain data from a spreadsheet. Python, for example, may be used to read, write, or alter data.

In addition, the user may be required to travel through several sheets and extract data based on some criteria, as well as edit some rows and columns and perform a great deal of labor. To extract data from a spreadsheet, use the xlrd module.

Before implementing the xlrd module, one needs to make sure that the module is imported into the program using the command line mentioned below in the CMD on your system.

pip install xlrd

Using the xlrd module in Python

We would be working on a few things with the help of the xlrd module one after another. Before doing anything let’s look at the dataset we would be using.

You can use any dataset you find suitable or create your own custom dataset. I downloaded a random finance dataset which can be downloaded here.

1. Loading the Excel File Into the Program

Initially, we would be importing the xlrd module and then use the open_workbook function under the xlrd module in order to load the workbook whose path is mentioned inside the function.

# Importing Module
import xlrd
# Loading Excel file
wb = xlrd.open_workbook('Financial Sample.xlsx')

2. Reading the Excel File

Now there can be multiple workbooks inside a single excel file but we want to work on the first workbook and for the same, we will make use of the sheet_by_index function and pass the index of the sheet we want to work on.

# Storing the first sheet into a variable
sheet = wb.sheet_by_index(0)

Printing value of a cell

After loading a certain workbook, we would like to print the value of a certain cell using the cell_value function which would require the row and column number of the cell we want to read.

# Printing various cell values
print("Value of 0-0 cell: ",sheet.cell_value(0, 0))
print("Value of 20-4 cell: ",sheet.cell_value(20, 4))

The output after execution is:

Value of 0-0 cell:  Segment
Value of 20-4 cell:  1006.0

Printing the Number of rows and columns

In order to print the number of rows and columns in the excel file, we would make use of the ‘nrows’ and ‘ncols’ function.

# Get max no of rows and columns
print("Number of Rows: ", sheet.nrows)
print("Number of Columns: ",sheet.ncols)

The output after execution is:

Number of Rows:  701
Number of Columns:  16

Printing the Names of All the Columns

This can be achieved using the code mentioned below. We just need to print the value of each column cell in the first row.

# Get all column names
print("ALL COLUMN NAMES ARE: ")
for i in range(sheet.ncols):
    print(sheet.cell_value(0,i))

The output after execution is shown below:

ALL COLUMN NAMES ARE: 
Segment
Country
Product
Discount Band
Units Sold
Manufacturing Price
Sale Price
Gross Sales
Discounts
 Sales
COGS
Profit
Date
Month Number
Month Name
Year

Printing the first 10 rows of 5 columns

This can be achieved using the code mentioned below. You can print multiple rows and columns if you wish to.

# Get first 10 rows for 5 columns
for i in range(11):
    for j in range(5):
        print(sheet.cell_value(i,j), end="\t\t\t")
    print()

The output is mentioned below:

Segment			Country			Product			Discount Band			Units Sold			
Government			Canada			Carretera			None			1618.5			
Government			Germany			Carretera			None			1321.0			
Midmarket			France			Carretera			None			2178.0			
Midmarket			Germany			Carretera			None			888.0			
Midmarket			Mexico			Carretera			None			2470.0			
Government			Germany			Carretera			None			1513.0			
Midmarket			Germany			Montana			None			921.0			
Channel Partners			Canada			Montana			None			2518.0			
Government			France			Montana			None			1899.0			
Channel Partners			Germany			Montana			None			1545.0

Conclusion

Congratulations! You just learned how to load and read excel files using the xlrd module. Hope you enjoyed it! 😇

Liked the tutorial? In any case, I would recommend you to have a look at the tutorials mentioned below:

Thank you for taking your time out! Hope you learned something new!! 😄