在工作表中出现的任何单元格中，使用python openpyxl查找并替换xlsx文件中的文本 [英] Find and Replace text in xlsx file with python openpyxl in whichever cell it appear in within sheet

问题描述

我目前需要在xlsx文件中的任何地方替换一些文本:"hello"为"hello" [请考虑搜索并替换].

I currently need to replace some text: "hello" with "hi" wherever it may appear in an xlsx file(s) [think search and replace].

遵循中的逻辑在python 中，我看到我能够成功打开源xlsx文件，然后最终保存到新的xlsx文件，但是我的文本从未被替换.

Following the logic from Find and replace in cells from excel in python, I see that I am able to successfully open the source xlsx file, and then finally save to a new xlsx file, however my text never gets replaced.

注意:我的文本可能出现在字符串的开头，中间或结尾，并且出现在其中的单元格可能在一个xlsx文件之间变化.

Note: my text may appear in the beginning, middle, or end of a string and the cell in which it appears may vary from one xlsx file to the next.

这是我当前的代码:

wb = openpyxl.load_workbook(sourcefile.xlsx')
            wb.sheetnames
            sheet = wb["sheet1"]
            amountOfRows = sheet.max_row
            amountOfColumns = sheet.max_column

            for i in range(amountOfColumns):
                for k in range(amountOfRows):
                    cell = str(sheet[get_column_letter(i+1)+str(k+1)].value)
                    if( str(cell[0]) == "hello"):
                        newCell = "hi"+cell[1:]
                        sheet[get_column_letter(i+1)+str(k+1)]=newCell

            wb.save('targetFile.xlsx')

有什么主意我要搞砸吗?任何指导将不胜感激！

Any idea where I am messing this up? Any guidance would be greatly appreciated!

推荐答案

使用 in 关键字和替换方法

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))

如果您希望不区分大小写的搜索/替换或更复杂的匹配，可以使用正则表达式.添加 import #re 并使用

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)

这篇关于在工作表中出现的任何单元格中，使用python openpyxl查找并替换xlsx文件中的文本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！