python list

OpenpyXL || Reading data from Excel file.

In previous tutorial we learn about writing data into excel file using openpyxl now we will learn about Reading data from excel file.


Before jump into coding example please download this sample excel file. This is one of the dataset we’ll be using throughout this tutorial.

Reading data

import openpyxl
book= openpyxl.load_workbook(‘openpy102.xlsx’)
sheet = book.active

This will import openpyxl library and navigate to active sheet of openpy102.xlsx excel file .Now we can access all the cell of active sheet through sheet object.

Instead of selecting active sheet we can access sheets by its name also by mentioning sheet name.We can print all the sheet name by sheetnames properties.

print(book.sheetnames)

This will return all the sheets of the openpy102.xlsx .

Read by column name and column index reference

a1 = sheet[“A2”]
a2 = sheet[“A3”]
d3 = sheet.cell(row=4, column=3).value
print(a1.value)
print(a2.value)
print(d3)

Here a1 and a2 will fetch the data via column name reference and d3 via column index reference i.e. row number and column number (4th row and 3rd columns value)

Reading multiple cell using iter_rows

print(“\nLooping on rows”)
for row in sheet.iter_rows(min_row=1, min_col=1, max_row=15,max_col=5):
    for cell in row:
        print(cell.value, end=”\t\t”)

Reading multiple cell using iter_cols   

print(“\n Looping on cols”)
for col in sheet.iter_cols(min_row=1, min_col=1, max_row=3, max_col=6):    for cell in col:
       print(cell.value, end=”\t\t”)