CODEWITHSUNDEEP
pythonexcelxlwings
Zhilai Liu
Zhilai Liu

Reputation: 13

How to delete specific rows in excel by xlwings?

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..

Upvotes: 0

Views: 841

Answers (1)

ChitoAmaru
ChitoAmaru

Reputation: 51

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

Upvotes: 2

Related Questions