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:

company	city	category	income	number_of_employees
Roses and Pansies	Toronto	gardening	456845.00	26
Reader’s Paradise	Sydney	publisher	854782.00	44
Dogs and Cats	Madrid	pets	458784.00	36
Enjoy Your Meal	New Orleans	restaurant	447884.00	18
Bloom Store	London	gardening	1254654.00	84
Tastes of the South	Miami	restaurant	587454.00	42
Pet For You	Athens	pets	987887.00	21
Eat ‘Em Up	Helsinki	restaurant	1254477.00	53
Mr. Bunny	Vienna	pets	254478.00	12

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

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:

company	city	category	income	number_of_employees
Roses and Pansies	Toronto	gardening	456845.00	26
Reader’s Paradise	Sydney	publisher	854782.00	44
Dogs and Cats	Madrid	pets	458784.00	36
Enjoy Your Meal	New Orleans	restaurant	447884.00	18
Bloom Store	London	gardening	1254654.00	84
Tastes of the South	Miami	restaurant	587454.00	42
Pet For You	Athens	pets	987887.00	21
Eat ‘Em Up	Helsinki	restaurant	1254477.00	53
Mr. Bunny	Vienna	pets	254478.00	12
Bookworms Rule	Rome	publisher	124474	40

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:

company	city	category	income	number_of_employees
Roses and Pansies	Toronto	gardening	456845.00	26
Reader’s Paradise	Sydney	publisher	854782.00	44
Dogs and Cats	Madrid	pets	458784.00	36
Enjoy Your Meal	New Orleans	restaurant	447884.00	18
Bloom Store	London	gardening	1254654.00	84
Tastes of the South	Miami	restaurant	587454.00	42
Pets for Everyone	Athens	pets	987887.00	21
Eat ‘Em Up	Helsinki	restaurant	1254477.00	53
Mr. Bunny	Vienna	pets	254478.00	12
Bookworms Rule	Rome	publisher	124474	40

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:

company	city	category	income	number_of_employees
Roses and Pansies	Toronto	gardening	456845.00	26
Reader’s Paradise	Sydney	publisher	911500.00	44
Dogs and Cats	Madrid	pets	458784.00	36
Enjoy Your Meal	New Orleans	restaurant	447884.00	18
Bloom Store	London	gardening	1254654.00	84
Tastes of the South	Miami	restaurant	587454.00	42
Pets for Everyone	Athens	pets	987887.00	21
Eat ‘Em Up	Helsinki	restaurant	1254477.00	53
Mr. Bunny	Vienna	pets	254478.00	12
Bookworms Rule	Rome	publisher	124474	40

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.