How to Search In Excel Worksheet with Python
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.
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:
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:
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.