openpyxl Part 8 – Inserting and Deleting Rows and Columns

In the previous part of the openpyxl series we were talking about adding and updating spreadsheet data. This part is sort of continuation of the previous one – today we’ll be inserting and deleting rows and columns. But first let’s have another look at our wb2.xlsx workbook. After the modifications in the previous part it now contains the following data:

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

So, let’s import the workbook:

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

There are methods to insert and delete rows and columns that you get out of the box. They have pretty self-explanatory names: insert_rows, insert_cols, delete_rows and delete_cols. Let’s try them out.

These methods take two arguments. The first argument, idx,  is the index of the row or column before which you want to insert a row or column respectively. In the two methods used to delete a row or column the index determines the row or column that is supposed to be deleted, or, if more than one row or column is to be deleted, the row or column where the deletion should start. These indices are 1-based and we use numeric indices for both rows and columns, so the index of column B is 2, the index of column E is five, and so on.

The second argument, amount, is optional and defaults to 1. It determines how many rows or columns are to be inserted before the given index or deleted starting from the given index.

And now let’s how the methods work.

Inserting Rows

First let’s insert a single row between row 4 and 5, so before row 5:

>>> sheet.insert_rows(idx=5)

Now let’s see all the data again:

>>> for row in sheet.iter_rows(values_only=True):
...     print(row)
... 
('company', 'city', 'category', 'income', 'number_of_employees')
('Roses and Pansies', 'Toronto', 'gardening', 456845, 26)
("Reader's Paradise", 'Sydney', 'publisher', 911500, 44)
('Dogs and Cats', 'Madrid', 'pets', 458784, 36)
(None, None, None, None, None)
('Enjoy Your Meal', 'New Orleans', 'restaurant', 447884, 18)
('Bloom Store', 'London', 'gardening', 1254654, 84)
('Tastes of the South', 'Miami', 'restaurant', 587454, 42)
('Pets for Everyone', 'Athens', 'pets', 987887, 21)
("Eat 'Em Up", 'Helsinki', 'restaurant', 1254477, 53)
('Mr. Bunny', 'Vienna', 'pets', 254478, 12)
('Bookworms Rule', 'Rome', 'publisher', 124474, 40)

As you can see, a new row has been inserted. And now let’s insert 4 rows before row 9, so before the row with the ‘Pets for Everyone’ data:

>>> sheet.insert_rows(idx=9, amount=4)
>>> for row in sheet.iter_rows(values_only=True):
...     print(row)
... 
('company', 'city', 'category', 'income', 'number_of_employees')
('Roses and Pansies', 'Toronto', 'gardening', 456845, 26)
("Reader's Paradise", 'Sydney', 'publisher', 911500, 44)
('Dogs and Cats', 'Madrid', 'pets', 458784, 36)
(None, None, None, None, None)
('Enjoy Your Meal', 'New Orleans', 'restaurant', 447884, 18)
('Bloom Store', 'London', 'gardening', 1254654, 84)
('Tastes of the South', 'Miami', 'restaurant', 587454, 42)
(None, None, None, None, None)
(None, None, None, None, None)
(None, None, None, None, None)
(None, None, None, None, None)
('Pets for Everyone', 'Athens', 'pets', 987887, 21)
("Eat 'Em Up", 'Helsinki', 'restaurant', 1254477, 53)
('Mr. Bunny', 'Vienna', 'pets', 254478, 12)
('Bookworms Rule', 'Rome', 'publisher', 124474, 40)

As you can see, it works too. Remember that these changes are not permanent until you save the workbook, so if you opened the workbook now, you wouldn’t see the new rows. However, I’m not going to save the workbook now because I’m going to delete all the new rows and columns in a moment.

Inserting Columns

And now let’s add a new column at the very beginning. This is supposed to be column A, so it should be inserted before the first column:

>>> sheet.insert_cols(idx=1)
>>> for row in sheet.iter_rows(values_only=True):
...     print(row)
... 
(None, 'company', 'city', 'category', 'income', 'number_of_employees')
(None, 'Roses and Pansies', 'Toronto', 'gardening', 456845, 26)
(None, "Reader's Paradise", 'Sydney', 'publisher', 911500, 44)
(None, 'Dogs and Cats', 'Madrid', 'pets', 458784, 36)
(None, None, None, None, None, None)
(None, 'Enjoy Your Meal', 'New Orleans', 'restaurant', 447884, 18)
(None, 'Bloom Store', 'London', 'gardening', 1254654, 84)
(None, 'Tastes of the South', 'Miami', 'restaurant', 587454, 42)
(None, None, None, None, None, None)
(None, None, None, None, None, None)
(None, None, None, None, None, None)
(None, None, None, None, None, None)
(None, 'Pets for Everyone', 'Athens', 'pets', 987887, 21)
(None, "Eat 'Em Up", 'Helsinki', 'restaurant', 1254477, 53)
(None, 'Mr. Bunny', 'Vienna', 'pets', 254478, 12)
(None, 'Bookworms Rule', 'Rome', 'publisher', 124474, 40)

As you can see, the column has been inserted. And now let’s insert 3 columns before column D, so the one with index 4:

>>> sheet.insert_cols(idx=4, amount=3)
>>> for row in sheet.iter_rows(values_only=True):
...     print(row)
... 
(None, 'company', 'city', None, None, None, 'category', 'income', 'number_of_employees')
(None, 'Roses and Pansies', 'Toronto', None, None, None, 'gardening', 456845, 26)
(None, "Reader's Paradise", 'Sydney', None, None, None, 'publisher', 911500, 44)
(None, 'Dogs and Cats', 'Madrid', None, None, None, 'pets', 458784, 36)
(None, None, None, None, None, None, None, None, None)
(None, 'Enjoy Your Meal', 'New Orleans', None, None, None, 'restaurant', 447884, 18)
(None, 'Bloom Store', 'London', None, None, None, 'gardening', 1254654, 84)
(None, 'Tastes of the South', 'Miami', None, None, None, 'restaurant', 587454, 42)
(None, None, None, None, None, None, None, None, None)
(None, None, None, None, None, None, None, None, None)
(None, None, None, None, None, None, None, None, None)
(None, None, None, None, None, None, None, None, None)
(None, 'Pets for Everyone', 'Athens', None, None, None, 'pets', 987887, 21)
(None, "Eat 'Em Up", 'Helsinki', None, None, None, 'restaurant', 1254477, 53)
(None, 'Mr. Bunny', 'Vienna', None, None, None, 'pets', 254478, 12)
(None, 'Bookworms Rule', 'Rome', None, None, None, 'publisher', 124474, 40)

Again, the new columns are there. Now we have quite a few cells with no data in them. We could now put some data in them, but what we are going to do is just delete them.

Deleting Rows

Let’s start with deleting row 5. It’s the one we inserted before, just after the ‘Dogs and Cats’ row:

>>> sheet.delete_rows(idx=5)
>>> for row in sheet.iter_rows(values_only=True):
...     print(row)
... 
(None, 'company', 'city', None, None, None, 'category', 'income', 'number_of_employees')
(None, 'Roses and Pansies', 'Toronto', None, None, None, 'gardening', 456845, 26)
(None, "Reader's Paradise", 'Sydney', None, None, None, 'publisher', 911500, 44)
(None, 'Dogs and Cats', 'Madrid', None, None, None, 'pets', 458784, 36)
(None, 'Enjoy Your Meal', 'New Orleans', None, None, None, 'restaurant', 447884, 18)
(None, 'Bloom Store', 'London', None, None, None, 'gardening', 1254654, 84)
(None, 'Tastes of the South', 'Miami', None, None, None, 'restaurant', 587454, 42)
(None, None, None, None, None, None, None, None, None)
(None, None, None, None, None, None, None, None, None)
(None, None, None, None, None, None, None, None, None)
(None, None, None, None, None, None, None, None, None)
(None, 'Pets for Everyone', 'Athens', None, None, None, 'pets', 987887, 21)
(None, "Eat 'Em Up", 'Helsinki', None, None, None, 'restaurant', 1254477, 53)
(None, 'Mr. Bunny', 'Vienna', None, None, None, 'pets', 254478, 12)
(None, 'Bookworms Rule', 'Rome', None, None, None, 'publisher', 124474, 40)

So, the row is gone. And now let’s delete the rows 8-11 that we also inserted before:

>>> sheet.delete_rows(idx=8, amount=4)
>>> for row in sheet.iter_rows(values_only=True):
...     print(row)
... 
(None, 'company', 'city', None, None, None, 'category', 'income', 'number_of_employees')
(None, 'Roses and Pansies', 'Toronto', None, None, None, 'gardening', 456845, 26)
(None, "Reader's Paradise", 'Sydney', None, None, None, 'publisher', 911500, 44)
(None, 'Dogs and Cats', 'Madrid', None, None, None, 'pets', 458784, 36)
(None, 'Enjoy Your Meal', 'New Orleans', None, None, None, 'restaurant', 447884, 18)
(None, 'Bloom Store', 'London', None, None, None, 'gardening', 1254654, 84)
(None, 'Tastes of the South', 'Miami', None, None, None, 'restaurant', 587454, 42)
(None, 'Pets for Everyone', 'Athens', None, None, None, 'pets', 987887, 21)
(None, "Eat 'Em Up", 'Helsinki', None, None, None, 'restaurant', 1254477, 53)
(None, 'Mr. Bunny', 'Vienna', None, None, None, 'pets', 254478, 12)
(None, 'Bookworms Rule', 'Rome', None, None, None, 'publisher', 124474, 40)

Now we got rid of all the rows with all empty cells. Next, let’s take care of the columns that we don’t need.

Deleting Columns

Let’s start by deleting the first column:

>>> sheet.delete_cols(idx=1)
>>> for row in sheet.iter_rows(values_only=True):
...     print(row)
... 
('company', 'city', None, None, None, 'category', 'income', 'number_of_employees')
('Roses and Pansies', 'Toronto', None, None, None, 'gardening', 456845, 26)
("Reader's Paradise", 'Sydney', None, None, None, 'publisher', 911500, 44)
('Dogs and Cats', 'Madrid', None, None, None, 'pets', 458784, 36)
('Enjoy Your Meal', 'New Orleans', None, None, None, 'restaurant', 447884, 18)
('Bloom Store', 'London', None, None, None, 'gardening', 1254654, 84)
('Tastes of the South', 'Miami', None, None, None, 'restaurant', 587454, 42)
('Pets for Everyone', 'Athens', None, None, None, 'pets', 987887, 21)
("Eat 'Em Up", 'Helsinki', None, None, None, 'restaurant', 1254477, 53)
('Mr. Bunny', 'Vienna', None, None, None, 'pets', 254478, 12)
('Bookworms Rule', 'Rome', None, None, None, 'publisher', 124474, 40)

And finally, let’s remove the three columns between 3 and 5 (so columns C, D and E):

>>> sheet.delete_cols(idx=3, amount=3)
>>> for row in sheet.iter_rows(values_only=True):
...     print(row)
... 
('company', 'city', 'category', 'income', 'number_of_employees')
('Roses and Pansies', 'Toronto', 'gardening', 456845, 26)
("Reader's Paradise", 'Sydney', 'publisher', 911500, 44)
('Dogs and Cats', 'Madrid', 'pets', 458784, 36)
('Enjoy Your Meal', 'New Orleans', 'restaurant', 447884, 18)
('Bloom Store', 'London', 'gardening', 1254654, 84)
('Tastes of the South', 'Miami', 'restaurant', 587454, 42)
('Pets for Everyone', 'Athens', 'pets', 987887, 21)
("Eat 'Em Up", 'Helsinki', 'restaurant', 1254477, 53)
('Mr. Bunny', 'Vienna', 'pets', 254478, 12)
('Bookworms Rule', 'Rome', 'publisher', 124474, 40)

Now the workbook looks exactly the same as at the beginning of this article.

Up to this moment we’ve been working on just one sheet. In the next part we’ll see how to create and remove sheets.