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!

1 Answers

0
Mohamed Abubakkar On

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