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:

So, let’s 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)

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.

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).

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.

Python Jumpstart Course

Learn the basics of Python, including OOP.

with lots of exercises, easy to follow

The course is available on Udemy.

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.

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.

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.