Working with Excel sheets in Python using openpyxl

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.
Execute below command to install necessary python package.
pip install openpyxl
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.
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)
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)
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
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 |
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)
Also read: Tutorial on Django Jenkins Integration for Django Project
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.
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)
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.