Using openpyxl to insert a blank row before a specific row

Question

I've been working on a project, in which I search an .xlsx document for a cell containing a specific value "D", and then insert a blank row before the row of that cell

Here's the example code I have come up with:

import openpyxl
wb = openpyxl.load_workbook('TestFile4.xlsx')
sheet = wb['Sheet1']
for row in sheet.iter_rows():
    for cell in row:
        if cell.value == 'D':
            sheet.insert_rows(cell.row, amount=1)

When I run this script, instead of inserting one row before the row of the cell has value 'D', it inserted 5 rows like this: https://i.stack.imgur.com/saSWf.png

Can you help me? Thanks so much!

Maybe this can help https://stackoverflow.com/questions/17299364/insert-row-into-excel-spreadsheet-using-openpyxl-in-python — Piyush Chauhan, May 12 '19 at 15:17
Possible duplicate of [Insert row into Excel spreadsheet using openpyxl in Python](https://stackoverflow.com/questions/17299364/insert-row-into-excel-spreadsheet-using-openpyxl-in-python) — stovfl, May 12 '19 at 15:51

score 0 · Answer 1 · answered May 12 '19 at 15:25

This is a logical error, When insert new row automatically next row will change to the value you are searching hence when it loops the condition will again be true so it will again insert new row.

For Example: You are having 6 rows, so "for loop" will execute 6 times and your condition is true at 4th iteration and your are entering a new row. which means D will change it position to 5th row and if condition will again be True.

you can overcome this by adding Breaking the loop:

import openpyxl
flag=False
wb = openpyxl.load_workbook('Test.xlsx')
sheet = wb['Sheet1']
for row in sheet.iter_rows():
    for cell in row:
        if cell.value == 'D':
            sheet.insert_rows(cell.row, amount=1)
            flag=True
            break
    if flag:break
    else:
        continue
wb.save("test.xlsx")

Mohd Khan · Answer 2 · 2023-05-23T13:24:26.350

Enter empty string ("") value to a cell and it will appears as blank row to excel file.
I used count to make any number of empty row to excel file.
This is a class function that's why i used self in argument and count argument is there to make empty rows as count times.
Row is used as maximum row from my side but you can give any row of your choice and column is at which column you want to give the value.

Try it....

def add_empty_rows_v2(self,count:int):

        write_workbook = load_workbook(self.file_path)

        write_workbook_sheet = write_workbook['Sheet1']

        for i in range(count):
          max_row = write_workbook_sheet.max_row

       write_workbook_sheet.cell(row=max_row+1,column=1,value="")

        write_workbook.save(self.file_path)

Enter empty string ("") value to a cell and it will appear as a cell with a value and a non empty row. Set the cell value to None to mark the cell and empty/deleted. If all cells in the row are None then the row is blank. `worksheet.max_row` will consider a row where a cell has `""` and used. — moken, May 25 '23 at 05:53

Using openpyxl to insert a blank row before a specific row

2 Answers2