How to delete specific rows in excel by xlwings?

Question

Suppose there is a test.xlsx like :

No.1	Date
1	13-Feb-04
2	20-Feb-04
3	20-Mar-08

I want to delete some rows which date is early than 13-Feb-04.Because it has many rows, so the following code snippet finds the last date that happened prior to the 13-Feb-04.

import datetime

for day in ws.range('B2:L{}'.format(last_row)):
# checks if day is not prior to the key date
    if day.value <= datetime.datetime(2004, 2, 13, 0, 0):
        # since day is past the modification date,
        # the row we want is the previous one, hence minus 1
        to_delete = int(day.get_address(0, 0)[1:])-1
        # leave the for cycle
        break

the last_row is obtained by

import xlwings as xw
wb = xw.Book('test.xlsx')
ws = wb.sheets["Sheet1"]
last_row = ws.range(1,1).end('down').row

But the to_delete only gets the value 1 in it.I check the xlwings doc ,but I cannot find some tips for the problem.Can someboday give me some suggestions?thx..

ChitoAmaru · Accepted Answer · 2021-08-12 19:41:09Z

2

after several hours of trial and error, this seems to work:

import datetime

for day in ws.range('L2:L{}'.format(last_row)):   

    if day.value <= datetime.datetime(2016, 9, 24, 0, 0):
    
        delete_date_row = int(day.get_address(0, 0)[1:])-1  

 ws.range('2:{}'.format(delete_date_row)).api.Delete()

# note: remove the break statement

answered Aug 12, 2021 at 19:41

ChitoAmaru

515 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to delete specific rows in excel by xlwings?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related