时间戳

首页 > 解决方案 > 如何使用python替换excel中单元格中的多个值？

python - 如何使用python替换excel中单元格中的多个值？

问题描述

我有一个excel表：

ABC 01th,02th,03th,04th BCD 26th,02th CVF 01th

预期输出 ABC 01st,02nd,03rd,04th BCD 26th,02nd CVF 01st

我已经尝试过此代码，但如果单元格中仅存在该单个值，它将被替换。我需要用新值替换单元格中的所有错误值。有人能帮忙吗。

   from xlrd import open_workbook
   import xlsxwriter
   rb = open_workbook("Dates.xlsx")
   wb = xlsxwriter.Workbook('Updated_Workbook1.xlsx')
   ws = wb.add_worksheet()
   s_orig = rb.sheet_by_index(0)
   NEW_Values= {'01th': '01st', 
        '02th': '02nd', 
        '03th': '03rd',
        '21th': '21st',
        '22th':'22nd',
        '23th':'23rd',
        '31th':'31st'}
    for row in range(s_orig.nrows):
        for col in range(s_orig.ncols):
            if s_orig.cell(row,col).value in NEW_Values:
        # s.write(row, col, NEW_Values[item])
                ws.write(row, col, NEW_Values[s_orig.cell(row,col).value])
            else:
                ws.write(row, col, s_orig.cell(row,col).value)
    wb.close()

标签： pythonexcel

解决方案

这里有一个建议：这个

import re

NEW_Values= {'01th': '01st', '02th': '02nd', '03th': '03rd', '21th': '21st',
             '22th': '22nd', '23th': '23rd', '31th': '31st'}
re_new_values = re.compile(r'|'.join(NEW_Values))
def repl(match):
    return NEW_Values[match.group()]

value = "ABC 01th,02th,03th,04th BCD 26th,02th CVF 01th"
print('Before:', value)
value = re_new_values.sub(repl, value)
print('After :', value)

将导致

Before: ABC 01th,02th,03th,04th BCD 26th,02th CVF 01th
After : ABC 01st,02nd,03rd,04th BCD 26th,02nd CVF 01st

这看起来像您的预期输出。r'|'.join(NEW_Values)产生01th|02th|03th|21th|22th|23th|31th在正则表达式搜索中起作用的模式，如'01th' or '02th' or ... or '31th'. 该sub方法是使用该函数通过映射repl替换匹配项。valueNEW_Values

如果我没有犯任何错误，那么这可能是一种适合您的程序的方法：

import re
...

NEW_Values= {'01th': '01st', '02th': '02nd', '03th': '03rd', '21th': '21st',
             '22th': '22nd', '23th': '23rd', '31th': '31st'}
re_new_values = re.compile(r'|'.join(NEW_Values))
def repl(match):
    return NEW_Values[match.group()]

for row in range(s_orig.nrows):
    for col in range(s_orig.ncols):
        ws.write(row, col, re_new_values.sub(repl, s_orig.cell(row,col).value))

wb.close()

PS：您也可以使用lambda一次性功能：

        ws.write(row, col, re_new_values.sub(lambda m: NEW_Values[m.group()],
                                             s_orig.cell(row,col).value))

推荐阅读