Working with Python and Excel together is really fun for a VBA Developer! It somehow shows you what all these years of coding with VBA have taught you! Today, I was thinking how to implement the .Find() method of searching in Excel values.
At first, I have tought about going old school and simply loop through all the cells in a given Excel range, check their values and thus implement a “manual” search. For this, I have generated an Excel file with the xlswriter library and I have read it with the xlrd library. Both are quite easy to be used, if you are aware how the Excel object model is built.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
import xlsxwriter from xlsxwriter.utility import xl_rowcol_to_cell import xlrd #First part of the code, used only to create some Excel file with data wbk = xlsxwriter.Workbook('hello.xlsx') wks = wbk.add_worksheet() i = -1 for x in range(1, 1000, 11): i+=1 cella = xl_rowcol_to_cell(i, 0) #0,0 is A1! cellb = xl_rowcol_to_cell(i, 1) cellc = xl_rowcol_to_cell(i, 2) #print (cella) wks.write(cella,x) wks.write(cellb,x*3) wks.write(cellc,x*4.5) myPath= r'C:\Desktop\hello.xlsx' wbk.close() #SecondPart of the code for sh in xlrd.open_workbook(myPath).sheets(): for row in range(sh.nrows): for col in range(sh.ncols): myCell = sh.cell(row, col) print(myCell) if myCell.value == 300.0: print('-----------') print('Found!') print(xl_rowcol_to_cell(row,col)) quit() |
This is the generated excel file from the first part of the code. (Does it remind something from here?):
This is what Python prints, which is actually quite nice:
Secondly, I remembered that I have actually learnt programming in Python some years ago and this is a programming blog, thus I should probably not code as a first year student (or so). Thus, I have decided to introduce at least one function. So, the function was named findCell and I have rewritten my code to this:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
import xlsxwriter import os import xlrd import time from xlsxwriter.utility import xl_rowcol_to_cell def findCell(sh, searchedValue): for row in range(sh.nrows): for col in range(sh.ncols): myCell = sh.cell(row, col) if myCell.value == searchedValue: return xl_rowcol_to_cell(row, col) return -1 myName = 'hello.xlsx' wbk = xlsxwriter.Workbook(myName) wks = wbk.add_worksheet() i = -1 for x in range(1, 1000, 11): i+=1 cella = xl_rowcol_to_cell(i, 0) #0,0 is A1! cellb = xl_rowcol_to_cell(i, 1) cellc = xl_rowcol_to_cell(i, 2) wks.write(cella,x) wks.write(cellb,x*3) wks.write(cellc,x*4.5) myPath= os.getcwd()+"\\"+myName searchedValue = 300 for sh in xlrd.open_workbook(myPath).sheets(): print(findCell(sh, searchedValue)) input('Press ENTER to exit') |
Now, it is a bit fancier, as the code could be executed with a click. On the previous one, I have written quit() , thus one should execute it from the console. Still, B10 is found:
Thirdly, I have read a comment from @ashleedawg, that one should be able to use the Excel API and thus use the Find() method from it. The whole programming becomes quite easy this way, using the xlwings library:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
import xlwings as xw bookName = r'C:\somePath\hello.xlsx' sheetName = 'Sheet1' wb = xw.Book(bookName) sht = wb.sheets[sheetName] myCell = wb.sheets[sheetName].api.UsedRange.Find('test') print('---------------') print (myCell.address) input() |
Thus, from an imput like this:
I was still able to get the A10:
That’s all folks! All the code is in GitHub here – Python Find.