openpyxl Part 7 – Adding and Updating Spreadsheet Data

In the preceding parts of the openpyxl series we were working on a small workbook saved as wb2.xlsx. We retrieved the data from the workbook and converted it to other formats. But what if need to add new data or modify the data that is already in the workbook? In this part we’ll have a look at some of the most basic operations like adding and updating spreadsheet data. But before that let’s have a look at the contents of the workbook again:

As always, the first thing to do is import the workbook:

>>> from openpyxl import load_workbook
>>> workbook = load_workbook(filename="wb2.xlsx")
>>> sheet = workbook.active

Learn how to make beautiful GUI apps

in Python using the Kivy framework.

Comprehensive, for Kivy beginners, easy to follow.

Get the book here (PDF) or on Amazon:

ebook / paperback (black and white) / paperback (full color)

Adding Data

First let’s add some new data. Suppose there’s another company. Here’s how we can add the data to particular cells of row 11:

>>> sheet['A11'] = 'Bookworms Rule'
>>> sheet['B11'], sheet['C11'], sheet['D11'], sheet['E11'] = 'Rome', 'publisher', 124474, 40

To save the data permanently in the workbook, you must call the save method:

>>> workbook.save(filename="wb2.xlsx")

If you now open your spreadsheet, you will see the new data added to the last row:

Your Panda3D Magazine

Make Awesome Games and Other 3D Apps

with Panda3D and Blender using Python.

Cool stuff, easy to follow articles.

Get the magazine here (PDF).

Updating Spreadsheet Data

Updating data is equally simple. You just select the cells you want to modify and assign new data to them. So, here’s how you can change the name ‘Pet For You’ to ‘Pets for Everyone’:

>>> sheet['A8'] = 'Pets for Everyone'
>>> workbook.save(filename="wb2.xlsx")

Here you can see the updated data in the workbook:

Alternatively, you can select the cell that you want to update first and then change its value. Let’s change the income value of the second company from 854782 to 911500:

>>> income_cell = sheet['D3']
>>> income_cell.value = 911500
>>> workbook.save(filename="wb2.xlsx")

Here’s the spreadsheet after the modification:

Python Jumpstart Course

Learn the basics of Python, including OOP.

with lots of exercises, easy to follow

The course is available on Udemy.

One thing to note. You can’t save a workbook if it’s open. If you try, you will get a permission error:

>>> workbook.save(filename="wb2.xlsx")
Traceback (most recent call last):
  ...
PermissionError: [Errno 13] Permission denied: 'wb2.xlsx'

So, if something like that pops up, you probably didn’t close the file. Just close it and save again, now it should work.

These were just the basic operations on workbooks. In the next part we’ll see how to insert and delete rows and columns.

Blender Jumpstart Course

Learn the basics of 3D modeling in Blender.

step-by-step, easy to follow, visually rich

The course is available on Udemy and on Skillshare.