本文介绍使用python语言,借助openyxl库来实现操作excel(xlsx)文件,实现替换特定内容的需求。
目前实现了3个小功能:
- 全字匹配替换(mode1);(如:全字匹配 yocichen, 替换成为 yociXchen)
- 部分字符匹配替换(mode2);(如:thisisyociblog,替换成为 thisisyocichenblog)
- 全字匹配填充(mode3);(如:yoci,替换成为yoci: a foolish),用于在字符后面添加字符
源码:
import openpyxlimport reimport tracebackchangeCells = 0# replace the special content"""file: file path : strmode: type of the operatoration : inttext: the string need to be replaceed : int or strreplaceText: replacement Text : int or str"""def changeData(file, mode, text, replaceText):# load the file(*.xlsx)wb = openpyxl.load_workbook(file)# ! deal with one sheetws = wb.worksheets[0]global changeCells# get rows and columns of filerows = ws.max_rowcols = ws.max_columnchangeFlag = Falsetry:for row in range(1, rows+1):for col in range(1, cols+1):content = ws.cell(row=row, column=col).valueif(content != None):# mode1: fullmatch replacementif(mode == 1):if(content == text):ws.cell(row=row, column=col).value = replaceTextchangeFlag = TruechangeCells += 1# mode2: partial replacementelif(mode == 2):if(type(content) == str):ws.cell(row=row, column=col).value = content.replace(text, replaceText, 1)changeFlag = TruechangeCells += 1# mode3: partialmatch and fillingelif(mode == 3):if(type(content) == str):ws.cell(row=row, column=col).value = content.replace(text, text+replaceText, 1)changeFlag = TruechangeCells += 1else:return 0# status_1: modified successif(changeFlag):wb.save(file)return changeCells# status_2: no modifiedelse:return changeCells# status_3: exceptionexcept Exception as e:print(traceback.format_exc())# read the content of file"""file: file path : str"""def rdxl(file):# load the file(*.xlsx)wb = openpyxl.load_workbook(file)# ! deal with one sheetws = wb.worksheets[0]global changeCells# get rows and columns of filerows = ws.max_rowcols = ws.max_columnchangeFlag = Falsecells = 0for row in range(1, rows+1):for col in range(1, cols+1):content = ws.cell(row=row, column=col).valueprint(content)cells += 1print('cells', cells)if __name__ == "__main__":res = changeData('D:\\001.xlsx', 1, 7777, 'bug制造者')if(res != None):print('已修改 ', res, ' 处')# else:# print('操作失败:\n'+res)rdxl('D:\\001.xlsx')本文转载自:https://www.cnblogs.com/yocichen/p/11693243.html