CODEWITHSUNDEEP
pythonexcelopenpyxl
Daan Van de Voorde
Daan Van de Voorde

Reputation: 31

openpyxl skip row if it contains data

My program is overwriting the first row everytime but i want it to skip every row that has data in it and go to the next row and so on until it finds a completely empty row and then add data there. The data gets added to the correct column but not the correct row. I know the my loops and checks are way too nested but I'm going to revisit that later.

import openpyxl as xl
from classes import Inschrijving


wb = xl.load_workbook("mosselen.xlsx")
ws = wb['ZATERDAG']


questions = [
    ('Voornaam: '),
    ('Achternaam: '),
    ('Aantal mosselen groot: '),
    ('Aantal mosselen groot voor helpers: '),
    ('Aantal mosselen klein: '),
    ('Aantal mosselen klein voor helpers: '),
    ('Aantal paardenworsten groot: '),
    ('Aantal paardenworsten groot voor helpers: '),
    ('Aantal paardenworsten klein: '),
    ('Aantal paardenworsten klein voor helpers: '),
    ('Brood: '),
    ('Betaald: ')]


def new():
    voornaam = input(questions[0])
    achternaam = input(questions[1])
    mg = input(questions[2])
    mgh = input(questions[3])
    mk = input(questions[4])
    mkh = input(questions[5])
    pg = input(questions[6])
    pgh = input(questions[7])
    pk = input(questions[8])
    pkh = input(questions[9])
    brood = input(questions[10])
    betaald = input(questions[11])
    insch1 = Inschrijving(voornaam=voornaam, achternaam=achternaam, mg=mg, mgh=mgh, mk=mk, mkh=mkh, pg=pg, pgh=pgh, pk=pk, pkh=pkh, brood=brood, betaald=betaald)
    print(f'{insch1.voornaam, insch1.achternaam}\nmosellen groot {insch1.mg}\nmosellen groot helper {insch1.mgh}\nmosellen klein {insch1.mk}\nmosellen klein helper {insch1.mkh}\npaardenworsten groot {insch1.pg}\npaardenworsten groot helper {insch1.pgh}\npaardenworsten klein {insch1.pk}\npaardenworsten klein helper {insch1.pkh}\nbrood {insch1.brood}\nbetaald {insch1.betaald}')

    if mg == "0":
        mg = None
    if mgh == "0":
        mgh = None
    if mk == "0":
        mk = None
    if mkh == "0":
        mkh = None
    if pg == "0":
        pg = None
    if pgh == "0":
        pgh = None
    if pk == "0":
        pk = None
    if pkh == "0":
        pkh = None
    if brood == "n":
        brood = None

    row_index = 6
    column_index_to_start = 4
    for row in ws.iter_rows(min_row=5, min_col=4, max_col=4, max_row=ws.max_row - 9):
        for cell in row:
            print(cell.value)
            if cell.value is None:
                for row2 in ws.iter_rows(min_row=5, min_col=5, max_col=5, max_row=ws.max_row- 9):
                    for cell2 in row2:
                        if cell2.value is None:
                            for col_index, value in enumerate((voornaam, achternaam, mg, mgh, mk, mkh, pg, pgh, pk, pkh, brood, betaald),
                                        column_index_to_start):
                                ws.cell(row=row_index, column=col_index).value = value
                        else:
                            pass
            else:
                pass



def start():
    a = input('(1) Nieuwe inschrijving of (2) bijwerken: \n')
    if a == "1":
        new()
    elif a == "2":
        edit()
    else:
        print("Ongeldig")
        start()


def edit():
    pass


start()
wb.save(filename='mosselen_test1.xlsx')
wb.close()

Upvotes: 1

Views: 476

Answers (2)

Daan Van de Voorde
Daan Van de Voorde

Reputation: 31

This is the correct code that works for me it searches for empty spots then writes there

    for cell in ws.iter_rows(min_row=5, min_col=4, max_col=5, max_row=ws.max_row - 7):
        # cell == (Column 4 - name, Column 5 - surname)
        if all((c.value is None for c in cell)):
            print(f'{cell} is empty')

            # cell[0].row is the current Row
            row_index = cell[0].row
            column_index_to_start = 4

            for col_index, value in enumerate(
                    (voornaam, achternaam, mg, mgh, mk, mkh, pg, pgh, pk, pkh, brood, betaald), column_index_to_start):
                ws.cell(row=row_index, column=col_index).value = value
            break

Upvotes: 0

furkanayd
furkanayd

Reputation: 882

Just editting the line contains data writer with below lines will work for you.

while ws.cell(row=row_index, column=col_index).value != "":
    row_index += 1
ws.cell(row=row_index, column=col_index).value = value

Upvotes: 1

Related Questions