Find And Replace Text In Xlsx File With Python Openpyxl In Whichever Cell It Appear In Within Sheet

I currently need to replace some text: 'hello' with 'hi' wherever it may appear in an xlsx file(s) [think search and replace]. Following the logic from Find and replace in cells fr

Solution 1:

Use in keyword and replace method

import openpyxl

wb = openpyxl.load_workbook("sourcefile.xlsx")
ws = wb["sheet1"]

i = 0
for r in range(1,ws.max_row+1):
    for c in range(1,ws.max_column+1):
        s = ws.cell(r,c).value
        if s != None and "hello" in s: 
            ws.cell(r,c).value = s.replace("hello","hi") 

            print("row {} col {} : {}".format(r,c,s))
            i += 1

wb.save('targetfile.xlsx')
print("{} cells updated".format(i))

If you want case insensitive search/replace or more complicated matching you can use a regular expression. Add import #re and use

if s != None and re.search('hello',s,flags=re.I): 
    ws.cell(r,c).value = re.sub('hello',"Hi",s,flags=re.I)

Solution 2:

Thanks for this solution. Also, I ran into issue when trying to implement similar solution when one of the values on the cell was integer instead of string. The fix to it was the using s = str(ws.cell(r,c).value)

eg:

import openpyxl

wb = openpyxl.load_workbook("sourcefile.xlsx")
ws = wb["sheet1"]

i = 0
for r in range(1,ws.max_row+1):
    for c in range(1,ws.max_column+1):
        s = str(ws.cell(r,c).value)
        if s != None and "hello" in s: 
            ws.cell(r,c).value = s.replace("hello","hi") 

            print("row {} col {} : {}".format(r,c,s))
            i += 1

wb.save('targetfile.xlsx')
print("{} cells updated".format(i))