Python While loop Takes too much time.

[Jaydeep Patil]

On Monday, 30 June 2014 18:16:21 UTC+5:30, Peter Otten  wrote:
> 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.



Dear Peter,
I have tested code written by you. But still it is taking same time.



Regards
Jay