Search code examples
pythonexcelpython-2.7win32com

Erasing only a part of an excel with win32com and python

I have 3 parameters.

startLine, starColumn and width (here 2,8,3)

excel

How can I erase the selected area without writing blanks in each cells? (here there is only 30 line but there could potetialy be 10 000 lines)

Right now I'm succesfully counting the number of lines but I can't manage to find how to select and delete an area

self.startLine = 2
self.startColumn = 8
self.width = 8

self.xl = client.Dispatch("Excel.Application")
self.xl.Visible = 1
self.xl.ScreenUpdating = False
self.worksheet = self.xl.Workbooks.Open("c:\test.xls")
sheet = self.xl.Sheets("data")

#Count the number of line of the record
nb = 0
while sheet.Cells(start_line + nb, self.startColumn).Value is not None:
    nb += 1

#must select from StartLine,startColumn to startcolum+width,nb
#and then erase

self.worksheet.Save()

ps : the code works, I may have forgotten some part due do copy/pas error, in reality the handling of the excel file is managed by several classes inheriting from each other

thanks

Solution

  • What I usually do is that I record macro in Excel and than try to re-hack the VB in Python. For deleting content I got something like this, should not be hard to convert it to Python:

    Range("H5:J26").Select
Selection.ClearContents

    In Python it should be something like:

    self.xl.Range("H5:J26").Select()
self.xl.Selection.ClearContents()

    Working example:

    from win32com.client.gencache import EnsureDispatch

exc = EnsureDispatch("Excel.Application")
exc.Visible = 1
exc.Workbooks.Open(r"f:\Python\Examples\test.xls")
exc.Sheets("data").Select()
exc.Range("H5:J26").Select()
exc.Selection.ClearContents()