Read and Write to an excel file using Python openpyxl module

Python provides openpyxl module for operating with Excel files.

How to create Excel files, how to write, read etc. can be implemented by this module.

For installing openpyxl module, we can write this command in command prompt

pip install openpyxl

If we want to give a sheet title name

Example code

import openpyxl
my_wb = openpyxl.Workbook()
my_sheet = my_wb.active
my_sheet_title = my_sheet.title
print("My sheet title: " + my_sheet_title)

Output

My sheet title:Sheet

To change Title Name

Example code

import openpyxl
my_wb = openpyxl.Workbook()
my_sheet = my_wb.active
my_sheet.title = "My New Sheet"
print("sheet name is : " + sheet.title)

Output

sheet name is : My New Sheet

Insert data or to write to an Excel sheet

Example code

import openpyxl
my_wb = openpyxl.Workbook()
my_sheet = my_wb.active
c1 = my_sheet.cell(row = 1, column = 1)
c1.value = "Aadrika"
c2 = my_sheet.cell(row= 1 , column = 2)
c2.value = "Adwaita"
c3 = my_sheet['A2']
c3.value = "Satyajit"
# B2 = column = 2 & row = 2.
c4 = my_sheet['B2']
c4.value = "Bivas"
my_wb.save("C:\Users\TP\Desktop\Book1.xlsx")

Output

To add Sheets in the Workbook

Example code

import openpyxl
my_wb = openpyxl.Workbook()
my_sheet = my_wb.active
my_wb.create_sheet(index = 1 , title = "new sheet")
my_wb.save("C:\Users\TP\Desktop\Book1.xlsx")

Output

Display Total number of rows.

Example code

import openpyxl
my_path = "C:\Users\TP\Desktop\Book1.xlsx"
my_wb_obj = openpyxl.load_workbook(my_path)
my_sheet_obj = my_wb_obj.active
print(my_sheet_obj.max_row)

Output

2

Display a particular cell value

Example code

import openpyxl
# Give the location of the file
My_path = "C:\Users\TP\Desktop\Book1.xlsx"
wb_obj = openpyxl.load_workbook(my_path)
my_sheet_obj = my_wb_obj.active
my_cell_obj = my_sheet_obj.cell(row = 1, column = 1)
print(my_cell_obj.value)

Output

Aadrika

Display total number of columns

Example code

import openpyxl
# Give the location of the file
My_path = "C:\Users\TP\Desktop\Book1.xlsx"
My_wb_obj = openpyxl.load_workbook(path)
my_sheet_obj = my_wb_obj.active
print(sheet_obj.max_column)

Output

2

Display all columns name

Example code

import openpyxl
# Give the location of the file
my_path = "C:\Users\TP\Desktop\Book1.xlsx"
# workbook object is created
my_wb_obj = openpyxl.load_workbook(my_path)
my_sheet_obj = my_wb_obj.active
my_max_col = my_sheet_obj.max_column
for i in range(1, my_max_col + 1):
   my_cell_obj = my_sheet_obj.cell(row = 1, column = i)
   print(my_cell_obj.value) 

Output

Aadrika Adwaita

Display first column value

Example code

import openpyxl
# Give the location of the file
my_path = "C:\Users\TP\Desktop\Book1.xlsx"
my_wb_obj = openpyxl.load_workbook(my_path)
my_sheet_obj = my_wb_obj.active
my_row = my_sheet_obj.max_row
for i in range(1, my_row + 1):
   cell_obj = my_sheet_obj.cell(row = i, column = 1)
   print(cell_obj.value)

Output

Aadrika
Satyajit

Print a particular row value

Example code

import openpyxl
# Give the location of the file
my_path = "C:\Users\TP\Desktop\Book1.xlsx"
my_wb_obj = openpyxl.load_workbook(my_path)
my_sheet_obj = my_wb_obj.active
my_max_col = my_sheet_obj.max_column
for i in range(1, my_max_col + 1):
   cell_obj = my_sheet_obj.cell(row = 2, column = i)
   print(cell_obj.value, end = " ")

Output

Satyajit Bivas