OpenpyXL || Insertion and deletion of rows and columns.

In this tutorial we will learn how to insert and delete rows and columns from excel sheet. We are going to use the same data set from previous tutorial. If you haven’t downloaded that dataset please download it.



Fig:Snapshot of sample dataset

Insert a single row.

We can add new rows by simply passing a row index (at which row we want to add new row) as parameter to the insert_rows() function. 

Example:

import openpyxl
book= openpyxl.load_workbook(‘openpy102.xlsx’)
sheet = book[‘daily sales’]
#inserting new row at 3rd
row.sheet.insert_rows(3)
book.save(‘openpy102.xlsx’)

Output:

Here in the snapshot of the output we can see new row has been inserted at 3rd index.

Insert multiple rows at once.

Similarly as a single row we can add multiple rows by the insert_rows() function, but this time we will pass 2 parameters. First parameter defines the starting index and the second defines the number of rows to be inserted.

Example:

import openpyxl
book= openpyxl.load_workbook(‘openpy102.xlsx’)
sheet = book[‘daily sales’]
#insert new rows
sheet.insert_rows(3)
#insert multiple rows at once
sheet.insert_rows(6,3)
book.save(‘openpy102.xlsx’)

Output:

Here in the snapshot of the output we can see 4 new rows have been inserted. sheet.insert_rows(3) will insert  1 row at 3rd index  and sheet.insert_rows(6,3) will insert  3 rows at 6th,7th and 8th row index . here in the sheet.insert_rows(6,3) function 6 indicates starting row and 3 indicates number of rows to be inserted.

Delete a single row .

To delete a single row from excel sheet we will use delete_rows() function with single parameter to delete_rows function.

Example :

import openpyxl
book= openpyxl.load_workbook(‘openpy102.xlsx’)
sheet = book[‘daily sales’]
#delete row
sheet.delete_rows(3)
book.save(‘openpy102.xlsx’)

Output:

Here in the snapshot of the output we can see the 3rd row has been deleted and the rows below the deleted rows are shifted up by one step.

Delete multiple rows at once.

To delete multiple rows at once we will pass 2 parameters to the delete_rows() function. First parameter defines the starting row index and the 2nd defines the number of rows to be deleted.

Example:

import openpyxl
book= openpyxl.load_workbook(‘openpy102.xlsx’)
sheet = book[‘daily sales’]
#delete multiple row at once
sheet.delete_rows(6,3)
book.save(‘openpy102.xlsx’)

Output:

Here in the snapshot of the output we can see the 3 rows from 6th to 9th index have been deleted.

Insert a single column.

To insert a column we will pass the column index as parameter to insert_cols() function.

Example:

import openpyxl
book= openpyxl.load_workbook(‘openpy102.xlsx’)
sheet = book[‘daily sales’]
#insert new column
sheet.insert_cols(2)
book.save(‘openpy102.xlsx’)

Output:

Here in the snapshot of the output screen we can see the new column has been inserted at 2nd index (column B).

Insert multiple columns at once.

To insert multiple columns at once we will pass 2 parameters to the insert_cols() function. First one defines the starting column index and second defines numbers of columns to be inserted.

Example:

import openpyxl
book= openpyxl.load_workbook(‘openpy102.xlsx’)
sheet = book[‘daily sales’]
#insert new column
sheet.insert_cols(4,2)
book.save(‘openpy102.xlsx’)

Output:

Here in the snapshot of the output sheet we can see the new 2 rows have been inserted at column index 4 and 5 (D and E)

Delete a single column.

To delete a column from existing sheet we will pass that column’s  index to the delete_cols() function .When the single row has been deleted remaining columns will be shifted to its  left column for example, if we delete 5th column(column E) the content of 6th column(column F) will  be shifted to 5th  column (column E) .

Example:

import openpyxl
book= openpyxl.load_workbook(‘openpy102.xlsx’)
sheet = book[‘daily sales’]
#delete column from existing sheet
sheet.delete_cols(5)
book.save(‘openpy102.xlsx’)

Output:

Here in the snapshot of the output screen we can see  the 5th  column has been deleted and the remaining columns are shifted to the left column. content of 6th (column F) shifted to 5th (column E), content  of 7ths column (column G) shifted to 6th (column F) and so on. 

Delete multiple column at once.

To delete multiple column at once we will pass 2 parameters to the delete_cols() function. First one defines the starting index and the second one defines the number of columns to be deleted. Hence remaining columns will be shifted back to the starting index(i.e. 1st parameter of the delete_cols() function). 

Example:

import openpyxl
book= openpyxl.load_workbook(‘openpy102.xlsx’)
sheet = book[‘daily sales’]
#delete column from existing sheet
sheet.delete_cols(2,4)
book.save(‘openpy102.xlsx’)

Output:

Here in the snapshot of the output we can see the 4 columns have been deleted (column B to E) and the remaining column have been shifted back  i.e column F shifted to B, column G shifted to C and so on.