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.

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.

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)

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)

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)

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.

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)

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.