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”)
