Python While loop Takes too much time.

[Peter Otten]

Jaydeep Patil wrote:

> I have did excel automation using python.
> In my code I am creating python dictionaries for different three columns
> data at a time.There are are many rows above 4000. Lets have look in below
> function. Why it is taking too much time?
> 
> Code:
> 
> def transientTestDict(self,ws,startrow,startcol):
>         
>         self.hwaDict = OrderedDict()
>         self.yawRateDict = OrderedDict()
>         
>         rng = ws.Cells(startrow,startcol)
>         
>         while not rng.Value is None:
>             r = rng.Row
>             c = rng.Column
>             
>             time = rng.Value
>             
>             rng1 = rng.GetOffset(0,1)
>             hwa = rng1.Value
>             
>             rng2 = rng.GetOffset(0,2)
>             yawrate = rng2.Value
>             
>             self.hwaDict[time] = hwa,rng.Row,rng.Column
>             self.yawRateDict[time] = yawrate,rng.Row,rng.Column
>             
>             rng = ws.Cells(r+1,c)
> 
> 
> 
> Please have look in above code & suggest me to improve speed of my code.

Assuming that what slows down things is neither Python nor Excel, but the 
communication between these I'd try to do as much as possible in Python. For 
example (untested):

    def transientTestDict(self, ws, startrow, startcol):
        self.hwaDict = OrderedDict()
        self.yawRateDict = OrderedDict()
        
        time_col, hwa_col, yawrate_col = range(startcol, startcol+3)

        for row in xrange(startrow, sys.maxint):
            time = ws.Cells(row, time_col).Value
            if time is None:
                break
            hwa = ws.Cells(row, hwa_col).Value
            yawrate = ws.Cells(row, yawrate_col).Value

            self.hwaDict[time] = hwa, row, time_col
            self.yawRateDict[time] = yawrate, row, time_col

While this avoids cell arithmetic in Excel it still fetches every value 
separately, so I have no idea if there is a significant effect.

Does Excel provide a means to get multiple cell values at once? That would 
likely help.