Article

Working with Excel sheets in Python using openpyxl

Last updated 
Jan 19, 2019
 min read

In today's data-driven world, the ability to manipulate and work with Excel spreadsheets programmatically can be a game-changer. Python, a versatile and powerful programming language, offers us the tools we need to automate Excel-related tasks with ease. In this tutorial, we will see a demonstration on how to use Excel sheets in the python using openpyxl.

Setup

Execute below command to install necessary python package.

pip install openpyxl

Create Excel sheet

1# import Workbook
2from openpyxl import Workbook
3
4# create Workbook object
5wb=Workbook()
6
7# set file path
8filepath="/home/ubuntu/demo.xlsx"
9
10# save workbook 
11wb.save(filepath)

This will create a new excel file demo.xlsx.

Empty demo.xlsx
Empty demo.xlsx

Add data to the Excel sheet

Writing to a cell

1# import load_workbook
2from openpyxl import load_workbook
3
4# set file path
5filepath="/home/ubuntu/demo.xlsx"
6
7# load demo.xlsx 
8wb=load_workbook(filepath)
9
10# select demo.xlsx
11sheet=wb.active
12
13# set value for cell A1=1
14sheet['A1'] = 1
15
16# set value for cell B2=2
17sheet.cell(row=2, column=2).value = 2
18
19# save workbook 
20wb.save(filepath)
Output of above code
Output of above code

Appending group of values at the bottom of the current sheet

1# import Workbook
2from openpyxl import Workbook
3
4# create Workbook object
5wb=Workbook()
6
7# set file path
8filepath="/home/ubuntu/demo.xlsx"
9
10# select demo.xlsx
11sheet=wb.active
12
13data=[('Id','Name','Marks'),
14      (1,ABC,50),
15      (2,CDE,100)]
16
17# append all rows
18for row in data:
19    sheet.append(row)
20
21# save file
22wb.save(filepath)
Output of above code
Output of above program

Reading from an Excel sheet

Reading a cell

1# import load_workbook
2from openpyxl import load_workbook
3
4# set file path
5filepath="/home/ubuntu/demo.xlsx"
6
7# load demo.xlsx 
8wb=load_workbook(filepath)
9
10# select demo.xlsx
11sheet=wb.active
12
13# get b1 cell value
14b1=sheet['B1']
15
16# get b2 cell value
17b2=sheet['B2']
18
19# get b3 cell value
20b3=sheet.cell(row=3,column=2)
21# print b1, b2 and b3
22print(b1)
23
24print(b2)
25print(b3)

Output of above code:

Name
ABC
DEF

Iterating by rows

1# import load_workbook
2from openpyxl import load_workbook
3
4# set file path
5filepath="/home/ubuntu/demo.xlsx"
6# load demo.xlsx 
7wb=load_workbook(filepath)
8
9# select demo.xlsx
10sheet=wb.active
11
12# get max row count
13max_row=sheet.max_row
14
15# get max column count
16max_column=sheet.max_column
17
18# iterate over all cells 
19# iterate over all rows
20for i in range(1,max_row+1):
21     
22     # iterate over all columns
23     for j in range(1,max_column+1):
24
25          # get particular cell value    
26          cell_obj=ws.cell(row=i,column=j)
27
28          # print cell value     
29          print(cell_obj.value,end=' | ')
30
31     # print new line
32     print('n')

Output of above code:

Id | Name | Marks |

1 | ABC | 50 |

2 | CDE | 100 |

Add sheet to the existing xlsx

We can add Sheet 2 to demo.xlsx using below code.

1# import load_workbook
2from openpyxl import load_workbook
3
4# set file path
5filepath="/home/ubuntu/demo.xlsx"
6
7# load demo.xlsx 
8wb=load_workbook(filepath)
9
10# create new sheet
11wb.create_sheet('Sheet 2')
12
13# save workbook
14wb.save(filepath)
Output of above code
Output of above code

Also read: Tutorial on Django Jenkins Integration for Django Project

Copy data from one sheet to another sheet

1# import load_workbook
2from openpyxl import load_workbook
3
4# set file path
5filepath="/home/ubuntu/demo.xlsx"
6
7# load demo.xlsx 
8wb=load_workbook(filepath)
9
10# get Sheet
11source=wb.get_sheet_by_name('Sheet')
12
13# copy sheet
14target=wb.copy_worksheet(source)
15
16# save workbook
17wb.save(filepath)

From above code create a new sheet with same data as Sheet.

Output of above code
Output of above code

Remove sheet from existing xlsx

We can remove Sheet 2 from the demo.xlsx using below code.

1# import load_workbook
2from openpyxl import load_workbook
3
4# set file path
5filepath="/home/ubuntu/demo.xlsx"
6
7# load demo.xlsx 
8wb=load_workbook(filepath)
9
10# create new sheet
11wb.remove(wb.get_sheet_by_name('Sheet 2'))
12
13# save workbook
14wb.save(filepath)
Output of above code
Output of above code

Well, this is it! I hope you liked it.

Working with Excel sheets in Python using openpyxl opens up a world of possibilities for automating data-related tasks. We've explored essential operations such as creating and editing Excel sheets, reading data, and even managing multiple sheets within a single Excel file.If you're interested in leveraging Python for your software development needs, our python development services can provide you with tailored solutions to meet your specific requirements. Let's get started!

Reference links:
https://openpyxl.readthedocs.io/en/stable/tutorial.html
https://zetcode.com/python/openpyxl/

Also, take a look at this informative blog post on Enhancing Security with Two-Factor Authentication in Django Admin Panel.

Authors

Nensi Trambadiya

Software Engineer
I’m a Software Engineer with a passion for problem-solving and building efficient, high-performing systems. I specialize in creating reliable, user-centric software that meets both technical and business needs. I’m always exploring new tools and techniques to stay ahead in an ever-evolving tech landscape.

Tags

No items found.

Have a project in mind?

Read