Read Excel File in Python

I’ll show you how to open and read an Excel Workbook (.xlsx extension) and read from cells and worksheets in this tutorial.

We’ll open and read the excel file using xlrd python libs.

Install and import xlrd

xlrd is not in Pythons Standard Library, so it needs to be installed into your application. This excel libs help for excel sheet manipulation.

Sample Excel Sheet Data

Name         age     Salary
Roji          32      1234
Adam          34      2134

The below command helps to install the excel library:

pip install xlrd

And at the start of our Python program it can be imported by including the below line:

import xlrd

Python Open Excel File

To read the excel file, we need to open a Workbook, You can use the open_workbook command and assign it to an excel file path as a variable:

workbookData = xlrd.open_workbook("employee.xlsx")

Each Workbook can have many Worksheets, each of which has cells that can be referred by rows (marked by a number) and columns (indicated by a letter).

How to Read WorkSheet

We can read a worksheet in different ways using xlrd libs.

There is an sheet_names() object which is a list of all the worksheets.

print(workbookData.sheet_names())

Get worksheet by named like "test":

ws = workbookData.sheet_by_name('test')
print(ws)

You can also get worksheet objects by index , like I am accessing 0 index worksheet.

ws = workbookData.sheet_by_index(0)
print(ws.name)

How To Get Excel Row Data

We have a worksheet object, now we’ll get first row data using the below code:

first_row_data = ws.row(0)
print(first_row_data)

How To Get Excel Column Data

We’ll get first column data using the below code:

first_col_data = ws.col(0)
print(first_col_data)