python openpyxl library – Read excel details or multiple rows as tuple

openpyxl is one of the python specific library to handle excel operations like read & write content.

In this post, we will see the openpyxl available features to

• Read and write excel sheet cell wise
• Fetch the excel content as tuple
• Write the tuple or list of tuples into excel sheet

Install openpyxl –

pip install openpyxl

Before we will be going over the library, let’s 1st see what are the steps to read content from excel in manual way

• Open workbook from an existing location where excel file is present
• Open the required sheet
• Get the required content based on row & cell

The same steps we will see using openpyxl

Read excel single cell data

#1

sh.cell(RowNo, ColumnNo).value

# xlReader.py
# author - qavalidation.com | youtube.com/qavbox

import openpyxl

wb = openpyxl.load_workbook("./test_data.xlsx")
sh = wb['Sheet1']

print(sh.cell(1, 1).value)  # 1st row & 1st column value

python xlReader.py

OutPut –

username

To use the openpyxl library in code, we need to import openpyxl

load_We have test_data.xlsx file under our project directory and path of the file will be passed as parameter for load_workbook method

Now create a sheet variable sh to refer to specified sheet i.e – Sheet1

sh.cell() will accept 2 parameters rowNumber & columnNumber to fetch the cell content, use value property to get the exact content.

Note –

print(sh.cell(1, 1).value) will print the cell data from 1st row & 1st column

The above way is to fetch one cell value by specifying row & column index or number.

#2

We can even directly fetch the cell value by using excel sheet’s default row and column header names

sh['[RowName][ColumnName]'].value

sh = wb['Sheet1']

print(print(sh['A1'].value))  # 1st row & 1st column value

Output –

username

A is the 1st column header name

1 is the 1st row name

Read multiple rows value

There are multiple ways to loop over the rows and fetch the cell data, let’s see them one by one

To get the rows count –

sh.max_row

To get the columns count –

sh.max_column

Now let’s see the ways to fetch multiple rows data –

#1

We can specify the excel sheet default column number / header

# xlReader.py
# author - qavalidation.com | youtube.com/qavbox

import openpyxl

wb = openpyxl.load_workbook("./test_data.xlsx")
sh = wb['Sheet1']

for row in sh['A']:
    print(row.value)

sh['A'] will return all the rows of 1st column [column name A]

#2

# xlReader.py
# author - qavalidation.com | youtube.com/qavbox

import openpyxl

wb = openpyxl.load_workbook("./test_data.xlsx")
sh = wb['Sheet1']

for i in range(1, sh.max_row+1):
    for cell in sh[i]:
        print(cell.value)
        

From the above nested for loop,

1st we are looking across rows starting from 1st row to maximum rows that has data

2nd loop basically goes over each row i.e - sh[i]

sh[i] returns list of cell in ith row & cell.value provides the actual data.

#3

We can use sh.iter_rows() method to get each row, and then fetch the cell from each row [with out using index]

for row in sh.iter_rows(1, sh.max_row):
    for cell in row:
        print(cell.value)

#4 – Read multiple rows data as list of tuples

# return data as tuples
sheet_cells = []
for rows in sh.iter_rows():
    row_cells = []
    for cell in rows:
        row_cells.append(cell.value)
    sheet_cells.append(tuple(row_cells))

print(sheet_cells)

for i in range(1, len(sheet_cells)):
    print(sheet_cells[i][0] + " " + str(sheet_cells[i][1]))

print(sheet_cells)

output –

[('username', 'password', 'type'), 
('abc', 123, 'valid'), 
('def', 456, 'invalid')]

in the 2nd inner for loop, we are appending the cell values to the row_cells[] as list with append() method

then for each row, we are converting the row_cells to tuple and adding to sheet_cells[] as list of tuples

As per the need, we can loop over the list of tuples variable to get the data and use!

for i in range(1, len(sheet_cells)):
print(sheet_cells[i][0] + " " + str(sheet_cells[i][1]))

In the above code, we started the range from 1 means excluding the column headers

and we used so we used sheet_cells[i][0] – i is the iterating over the list of tuples, and 2nd index specifies which tuple index to print

output –

abc 123
def 456

Get all the sheet names from the excel

# xlReader.py
# author - qavalidation.com | youtube.com/qavbox

import openpyxl

wb = openpyxl.load_workbook("./test_data.xlsx")
print(wb.sheetnames)

To get the current active sheet name

print(wb.active.title)

Hope this helps!