CODEWITHSUNDEEP
excelparsing
Dupond
Dupond

Reputation: 338

Find cell in excel with Python without iterating

I need to find values in cells in several hundreds excel. Until now i am parsing each excel file by iterating each line but it is too long ( excell files got over 3000 lines). But when using ctrl+f in excel the result is instataneous.

My question : how to find quickly (more quick than iterating) a value in a excel file that has many lines.

Thanks for your help.

Upvotes: 1

Views: 429

Answers (2)

Dupond
Dupond

Reputation: 338

Following Nurzhan comment, here is the answer (to copy the excel file in a matrix and then find the value in the matrix with 3 conditions) : 

matrix = numpy.zeros(shape=(sheet2.max_row+,23),dtype=object)   
j=1
for row in sheet2:
    i=1
    for cell in row:                
        matrix[j][i] = cell.value  
        i=i+1
    j=j+1



for i in range(sheet2.max_row-50,1,-1):
    if Model==matrix[i][9] and matrix[i][12] != None and "A" in matrix[i][1] : #
            Var1 = matrix[i][1]
            Var2  = matrix[i][9]
            Var3 = matrix[i][12]
            break

By iterating the 3000 lines in excel it was about 25seconds and now by coping data in a matrix the result is in less than a second.

Upvotes: 0

Brian Champlin
Brian Champlin

Reputation: 21

The way to do this is to use com automation.

Install pythonnet to get access to the common language runtime (CLR). There are pre-built binaries at http://www.lfd.uci.edu/~gohlke/pythonlibs/#pythonnet.

https://github.com/pythonnet/pythonnet

I don't have excel so I cannot provide you with a functional example, but your code will end up something like the examples here: https://discourse.mcneel.com/t/close-an-excel-session-with-python/613.

Upvotes: 1

Related Questions