CODEWITHSUNDEEP
pythonexcelopenpyxl
GBSH
GBSH

Reputation: 21

How to delete the first rows in openpyxl, but don't know how many rows?

I am trying to delete the first rows, but i don't always know how many rows to delete.

I need to delete all the rows until the first cell on the first column is called "Account"

I made the following:

import openpyxl

wb=load_workbook('abcd.xlsx')
ws=wb.active

mr=ws.max_row
mc=ws.max_column

for row in ws.iter_rows():
    for i in range(1,mr+1):
        if ws.cell(row=i,column=1).value=='Account':
            first_row=ws.cell(row=i,column=1).value=='Account'

ws.delete_rows(1,first_row-1)


wb.save('example.xlsx')

It just deletes all the data from the excel

Upvotes: 0

Views: 644

Answers (3)

GBSH
GBSH

Reputation: 21

Ok I figured it out

import openpyxl

wb=load_workbook('abcd.xlsx')
ws=wb.active

mr=ws.max_row
mc=ws.max_column
for j in range(1,mc+1):
        for i in range(1,mr+1):
            if ws.cell(row=i,column=j).value=="Account":
                firstrow=int(cell.row)
                ws.delete_rows(1, firstrow)
wb.save('example.xlsx')

Thank you guys

Upvotes: 0

Charlie Clark
Charlie Clark

Reputation: 19527

You've set first_row to True. In Python this is the same as 1 which is why all the rows are deleted. Try this instead:

for row in ws.iter_rows(min_col=1. max_col=1):
     cell = row[0]
     if cell.value == 'Account':
         first_row = cell.row
         break

Upvotes: 2

WhoDoVooDoo
WhoDoVooDoo

Reputation: 46

Try this and see how it works for you. It's good to delete rows in a reverse order because if not you end up (for example) removing row 2, then move onto row 3, but the problem is now that what was row 3 has become row 2 and you're one step too far.

Edited to say: If you have a great number of rows to delete, you're generally better off collecting all the good rows and writing them to a new workbook. delete_rows isn't terribly quick.

import openpyxl

wb = openpyxl.load_workbook('abcd.xlsx')
ws = wb.active

mr = ws.max_row+1
mc = ws.max_column


def FindFirstAccount():

    rowsToDelete = []
    for cell in ws['A2':f'A{mr}']:
        for thisString in cell:
            if thisString.value != 'Account':
                rowsToDelete.append(thisString.row)
            else:
                return rowsToDelete


rowsToDelete = FindFirstAccount()


for row in reversed(rowsToDelete):
    ws.delete_rows(row)


wb.save('example.xlsx')

Upvotes: 0

Related Questions