python 读excel每行替换_python 操作excel实现替换特定内容

1 importopenpyxl2 importre3 importtraceback4

5 changeCells =06

7 #replace the special content

8 """

9 file: file path : str10 mode: type of the operatoration : int11 text: the string need to be replaceed : int or str12 replaceText: replacement Text : int or str13 """

14 defchangeData(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 globalchangeCells20 #get rows and columns of file

21 rows =ws.max_row22 cols =ws.max_column23 changeFlag =False24 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).value28 if(content !=None):29 #mode1: fullmatch replacement

30 if(mode == 1):31 if(content ==text):32 ws.cell(row=row, column=col).value =replaceText33 changeFlag =True34 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 =True41 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 =True48 changeCells += 1

49 else:50 return051 #status_1: modified success

52 if(changeFlag):53 wb.save(file)54 returnchangeCells55 #status_2: no modified

56 else:57 returnchangeCells58 #status_3: exception

59 exceptException as e:60 print(traceback.format_exc())61

62

63 #read the content of file

64 """

65 file: file path : str66 """

67 defrdxl(file):68 #load the file(*.xlsx)

69 wb =openpyxl.load_workbook(file)70 #! deal with one sheet

71 ws =wb.worksheets[0]72 globalchangeCells73 #get rows and columns of file

74 rows =ws.max_row75 cols =ws.max_column76 changeFlag =False77 cells =078 for row in range(1, rows+1):79 for col in range(1, cols+1):80 content = ws.cell(row=row, column=col).value81 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('操作失败:\n'+res)

92 rdxl('D:\\001.xlsx')