1

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.

enderland
  • 13,825
  • 17
  • 98
  • 152
Dupond
  • 328
  • 1
  • 2
  • 9
  • Look at this http://stackoverflow.com/questions/34054272/openpyxl-optimizing-cells-search-speed. – Nurjan Oct 19 '16 at 02:41

2 Answers2

1

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.

Brian Champlin
  • 21
  • 1
  • COM objects are not directly accessible from pythonnet currently. But I made a simple wrapper for my immediate use case recently: https://github.com/pythonnet/pythonnet/issues/260 – denfromufa Nov 04 '16 at 11:22
0

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.

Dupond
  • 328
  • 1
  • 2
  • 9