Working with Excel sheets in Python using openpyxl

In this story, we will see a demonstration of how to use Excel sheets in python using openpyxl.

Setup

Execute the below command to install the necessary python package.

pip install openpyxl

Create an Excel sheet

# import Workbook
from openpyxl import Workbook

# create Workbook object
wb = Workbook()

# set file path
filepath = "/home/nensi/demo.xlsx"

# save workbook
wb.save(filepath)

This will create a new excel file with the name demo.xlsx in the file path.

The outcome of the above code

Write in the Excel sheet

In the below code, We will write to the cell using the cell name, row&column number.

# import load_workbook
from openpyxl import load_workbook

# set file path
filepath = "/home/nensi/demo.xlsx"

# load demo.xlsx
wb = load_workbook(filepath)

# get active sheet
sheet = wb.active

# set value for cell A1=1
sheet['A1'] = 1

# set value for cell B2=2
sheet.cell(row=2, column=2).value = 2

# save workbook
wb.save(filepath)

The outcome of the above code

We can write multiple rows in the sheet as well. Below is the code for that.

# import Workbook
from openpyxl import Workbook

# create Workbook object
wb = Workbook()

# set file path
filepath = "/home/nensi/demo.xlsx"

# get active sheet
sheet = wb.active

# data
data = [('Id', 'Name', 'Marks'), (1, "A", 50), (2, "B", 100)]

# append all rows
for row in data:
    sheet.append(row)

# save file
wb.save(filepath)

The outcome of the above code

Append to the Excel sheet

In the above code, we wrote multiple rows to the sheet. Now, we can append more rows as well to the sheet using the below code.

# import load_workbook
from openpyxl import load_workbook

# set file path
filepath = "/home/nensi/demo.xlsx"

# load workbook
wb = load_workbook(filepath)

# get active sheet
sheet = wb.active

# data
data = [(3, "C", 50), (4, "D", 100), (5, "E", 45)]

# append all rows
for row in data:
    sheet.append(row)

# save file
wb.save(filepath)

The outcome of the above code

Reading from Excel sheet

We can read specific cell values using the below code.

# import load_workbook
from openpyxl import load_workbook

# set file path
filepath = "/home/nensi/demo.xlsx"

# load demo.xlsx
wb = load_workbook(filepath)

# get active sheet
sheet = wb.active

# get b1 cell value
b1 = sheet['B1']

# get b2 cell value
b2 = sheet['B2']

# get b3 cell value
b3 = sheet.cell(row=3, column=2)

# print b1, b2 and b3
print(b1.value)
print(b2.value)
print(b3.value)

The output of the above code:

Name
A
B

Now, we will read multiple rows instead of a specific cell.

# import load_workbook
from openpyxl import load_workbook

# set file path
filepath = "/home/nensi/demo.xlsx"

# load demo.xlsx
wb = load_workbook(filepath)

# get active sheet
sheet = wb.active

# get max row count
max_row = sheet.max_row

# get max column count
max_column = sheet.max_column

# iterate over all cells
# iterate over all rows
for i in range(1, max_row + 1):
    # iterate over all columns
    for j in range(1, max_column + 1):
        # get particular cell value
        cell_obj = sheet.cell(row=i, column=j)
        # print cell value
        print(cell_obj.value, end=' | ')
    # print new line
    print('\n')

The output of the above code:

Id | Name | Marks |
1 | A | 50 |
2 | B | 100 |
3 | C | 50 |
4 | D | 100 |
5 | E | 45 |

Add new sheet to the existing Excel file

We will add Sheet 2 to demo.xlsx using the below code.

# import load_workbook
from openpyxl import load_workbook

# set file path
filepath = "/home/nensi/demo.xlsx"

# load demo.xlsx
wb = load_workbook(filepath)

# create new sheet
wb.create_sheet('Sheet 2')

# save workbook
wb.save(filepath)

The outcome of the above code

Copy data from one sheet to another sheet

# import load_workbook
from openpyxl import load_workbook

# set file path
filepath = "/home/nensi/demo.xlsx"

# load demo.xlsx
wb = load_workbook(filepath)

# get Sheet
source = wb["Sheet"]

# copy sheet
target = wb.copy_worksheet(source)

# save workbook
wb.save(filepath)

From the above code, we have created a new sheet called Sheet Copy with the same data as the Sheet.

The outcome of the above code

Remove sheet from existing Excel file

Now, Most of the file operations except deleting a sheet are covered in the above section. We will remove Sheet Copy from the demo.xlsx that is added in the above code.

# import load_workbook
from openpyxl import load_workbook

# set file path
filepath = "/home/nensi/demo.xlsx"

# load demo.xlsx
wb = load_workbook(filepath)

# remove sheet
wb.remove(wb['Sheet Copy'])

# save workbook
wb.save(filepath)

The outcome of the above code

Well, this is it! We have covered all basic sheet operations with the openpyxl library. I hope you liked this story.

If you find this story useful, press👏 button as many times as you can and share this story with others. You can leave your feedback/suggestions in the comments 💬 below. For future stories, you can follow me on medium to receive post updates🔔.

Would you like to check out my other articles?

Using Python Pandas With Excel

Commonly used SQL queries using Django ORM

Adding formatting to the xlsx file with xlsxwriter Python

Partial string search in Apache Solr

django-jenkins integration for Django project