Home > Net >  Search and replace all cells with a certain value with openpyxl
Search and replace all cells with a certain value with openpyxl

Time:11-17

For my job I have large amount of excel files in which I have to replace certain values. I just started with openpyxl and tried the following code:

import openpyxl
from openpyxl import load_workbook
wb1 = load_workbook(filename = 'testfile.xlsx')
ws1 = wb1.active


i = 0
for r in range(1,ws1.max_row 1):
    for c in range(1,ws1.max_column 1):
        s = ws1.cell(r,c).value
        if s != None or 'NM181841' in s: 
            ws1.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))

On which I get following error "TypeError: argument of type 'NoneType' is not iterable" this happends in line five: if s != None or 'NM181841' in s:

Does anyone have an idea what I did wrong?

Thanks!

CodePudding user response:

You are trying to iterate through a type which is not iterable in the following:

or 'NM181841' in s:

What this line practically says is: "find 'NM181841' in 's'" thus it would required to loop through 's' which is not possible since

TypeError: argument of type 'NoneType' is not iterable

CodePudding user response:

I found my own mistake, instead of: s = ws1.cell(r,c).value

I had to use

s = str(ws1.cell(r,c).value)

With the help of the @MwBakker

  • Related