Home » Openpyxl Reading Data in python

Openpyxl Reading Data in python

Oracle Java Certification
Java SE 11 Programmer I [1Z0-815] Practice Tests
Java SE 11 Programmer II [1Z0-816] Practice Tests
1 Year Subscription
Spring Framework Basics Video Course
Java SE 11 Developer (Upgrade) [1Z0-817]

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

Table of Contents

Installation

pip install openpyxl

Examples

This is the sample data in the spreadsheet

Country Capital Population
Germany Berlin 83190556
France Paris 67413000
Spain Madrid 47450795
Italy Rome 60317116
Portugal Lisbon 10344802

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 

You may also like

Leave a Comment

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More