本文介绍使用python语言，借助openyxl库来实现操作excel（xlsx）文件，实现替换特定内容的需求。
目前实现了3个小功能：
1. 全字匹配替换（mode1）；（如：全字匹配 yocichen, 替换成为 yociXchen）
2. 部分字符匹配替换(mode2)；（如：thisisyociblog,替换成为 thisisyocichenblog）
3. 全字匹配填充(mode3)；（如：yoci,替换成为yoci: a foolish），用于在字符后面添加字符
源码：
1 import openpyxl 2 import re 3 import traceback 4 5 changeCells = 0 6 7 # replace the special content 8 """ 9 file: file path : str 10 mode: type of the operatoration : int 11 text: the string need to be replaceed : int or str 12 replaceText: replacement Text : int or str 13 """ 14 def changeData(file, mode, text, replaceText): 15 # load the file(*.xlsx) 16 wb = openpyxl.load_workbook(file) 17 # ! deal with one sheet 18 ws = wb.worksheets[0] 19 global changeCells 20 # get rows and columns of file 21 rows = ws.max_row 22 cols = ws.max_column 23 changeFlag = False 24 try: 25 for row in range(1, rows+1): 26 for col in range(1, cols+1): 27 content = ws.cell(row=row, column=col).value 28 if(content != None): 29 # mode1: fullmatch replacement 30 if(mode == 1): 31 if(content == text): 32 ws.cell(row=row, column=col).value = replaceText 33 changeFlag = True 34 changeCells += 1 35 # mode2: partial replacement 36 elif(mode == 2): 37 if(type(content) == str): 38 ws.cell(row=row, column=col).value = content.replace( 39 text, replaceText, 1) 40 changeFlag = True 41 changeCells += 1 42 # mode3: partialmatch and filling 43 elif(mode == 3): 44 if(type(content) == str): 45 ws.cell(row=row, column=col).value = content.replace( 46 text, text+replaceText, 1) 47 changeFlag = True 48 changeCells += 1 49 else: 50 return 0 51 # status_1: modified success 52 if(changeFlag): 53 wb.save(file) 54 return changeCells 55 # status_2: no modified 56 else: 57 return changeCells 58 # status_3: exception 59 except Exception as e: 60 print(traceback.format_exc()) 61 62 63 # read the content of file 64 """ 65 file: file path : str 66 """ 67 def rdxl(file): 68 # load the file(*.xlsx) 69 wb = openpyxl.load_workbook(file) 70 # ! deal with one sheet 71 ws = wb.worksheets[0] 72 global changeCells 73 # get rows and columns of file 74 rows = ws.max_row 75 cols = ws.max_column 76 changeFlag = False 77 cells = 0 78 for row in range(1, rows+1): 79 for col in range(1, cols+1): 80 content = ws.cell(row=row, column=col).value 81 print(content) 82 cells += 1 83 print('cells', cells) 84 85 86 if __name__ == "__main__": 87 res = changeData('D:\001.xlsx', 1, 7777, 'bug制造者') 88 if(res != None): 89 print('已修改 ', res, ' 处') 90 # else: 91 # print('操作失败: '+res) 92 rdxl('D:\001.xlsx')