Openpyxl Reading Data in python

openpyxl is a Python library to read and write Excel 2010 xlsx/xlsm/xltx/xltm files.

In this article we look at reading data in an excel worksheet using python

Installation

pip install openpyxl

Examples

This is the sample data in the spreadsheet

Reading an individual cell

import openpyxl  
  
workbook = openpyxl.load_workbook('test3.xlsx')  
  
sheet = workbook.active  
  
x1 = sheet['A1']  
x2 = sheet['A2']  
#using cell() function  
x3 = sheet.cell(row=3, column=1)  
  
print("The first cell value:",x1.value)  
print("The second cell value:",x2.value)  
print("The third cell value:",x3.value)  

This displayed the following

>>> %Run openpyxlreaddata.py
The first cell value: Germany
The second cell value: France
The third cell value: Spain

total number of rows and columns

# import openpyxl module
import openpyxl
 
# to open the workbook
# workbook object is created
workbook = openpyxl.load_workbook('test3.xlsx')  
sheet = workbook.active
 
# print the total number of rows
print(sheet.max_row)

# print total number of column
print(sheet.max_column)

This displayed the following

>>> %Run openpyxlrowcolumn.py
6
3

Display column name

# importing openpyxl module
import openpyxl
 
# to open the workbook
# workbook object is created
workbook = openpyxl.load_workbook('test3.xlsx')  
 
sheet = workbook.active
max_col = sheet.max_column
 
# Loop will print all columns name
for i in range(1, max_col + 1):
    cell = sheet.cell(row = 1, column = i)
    print(cell.value)

This displayed the following

>>> %Run openpyxlcolumn.py
Country
Capital
Population

Display column 1

# importing openpyxl module
import openpyxl
 
# workbook object is created
workbook = openpyxl.load_workbook('test3.xlsx')  
 
sheet = workbook.active
maxrows = sheet.max_row
 
# print all values of first column
for i in range(1, maxrows + 1):
    cell = sheet.cell(row = i, column = 1)
    print(cell.value)

This displayed the following

>>> %Run openpyxlcolumn1.py
Country
Germany
France
Spain
Italy
Portugal

Display a row

# importing openpyxl module
import openpyxl
 
# workbook object is created
workbook = openpyxl.load_workbook('test3.xlsx')  
 
sheet = workbook.active
 
maxcols = sheet.max_column
 
# Will print a particular row value
for i in range(1, maxcols + 1):
    cell = sheet.cell(row = 2, column = i)
    print(cell.value, end = " ")

This displayed the following

>>> %Run openpyxlrows.py
Germany Berlin 83190556