1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
| import tkinter as tk
import tkinter.messagebox as msb
import tkinter.filedialog as fd
import tkinter.ttk as ttk
import re
import xlwings as xw
import sys
import os.path
import pywintypes
class Window(object):
def __init__(self):
self.top = tk.Tk()
self.file=''
self.newfile=''
self.top.title("删除excel手机号码")
sw = self.top.winfo_screenwidth()
sh = self.top.winfo_screenheight()
ww = 460
wh = 220
x = (sw-ww) // 2
y = (sh-wh) // 2
self.top.geometry("{}x{}+{}+{}".format(ww,wh,x,y))
self.top.maxsize(800,600)
self.top.minsize(200,150)
self.createWdiget()
self.top.wm_attributes('-topmost',1) #主窗口置顶
self.top.mainloop()
def createWdiget(self):
self.fileText = tk.Text(self.top,height=2,width=45,takefocus=0)
self.fileText.grid(row=0,column=0,pady=10,padx=20)
openBut = ttk.Button(self.top,text="打开文件",command=self.getFileName)
openBut.grid(row=0,column=1)
processBut = ttk.Button(self.top,text="开始处理",command=self.delPhoneNum)
processBut.grid(row=1,column=1,pady=10)
self.processBar = ttk.Progressbar(self.top, length=240, mode="determinate", orient=tk.HORIZONTAL)
self.processBar.grid(row=2,column=0,columnspan=2,pady=10,)#,sticky=tk.W)
def getFileName(self):
file = fd.askopenfilename(initialdir='./')
self.fileText.delete(1.0,tk.END) #1行0列到最后，先清掉text再插入
self.fileText.insert(tk.INSERT,os.path.split(file)[1]) #在光标位置插入
self.file = file
def isValidFile(self):
if not self.file:
return False
fdir = os.path.dirname(self.file) #原路径
fname = os.path.split(self.file)[1].split(".")[0] #原文件名
fext = os.path.split(self.file)[1].split(".")[1] #原扩展名
self.newfile = os.path.join(fdir,fname+"_NoPhone")
if fext not in ("xls","xlsx"):
return False
return True
def delPhoneNum(self):
if not self.isValidFile():
msb.showinfo("","未打开文件或打开的是非excel文件！")
return
key = "投诉内容"
pattern = r"1\d{10}[,，.。/、\\]?" #手机号，及后面可能出现的字符
r = re.compile(pattern)
try:
wb = xw.Book(self.file) #YY PC 报OSError "_ctypes/callproc.c" line 918 in GetResul
except OSError as e:
msb.showinfo("",e)
return
sht = wb.sheets[0] #要求放在第一张表
for i in sht.range("A1:AZ3"): #在前3行，前52列(A~AZ)里 找目标列
if i.value == key:
add= i.get_address(False,False)
break
col = add[:-1]
row = str(int(add[-1])+1) #从目标列下一行开始处理
cellRange = "{}{}:{}2000".format(col,row,col) #最多处理2000行
lastRow = sht.range(cellRange).end("down").row #最后一行
cellRange = "{}{}:{}{}".format(col,row,col,lastRow)
hgt = sht.range(add).height #获取行高
#开始替换删除
self.processBar["maximum"] = lastRow-1 #进度条最大值为总行数
#self.processBar["value"] = 0
for cell in sht.range(cellRange): #处理里不能点excel 否则报错
if cell.value:
cell.value = r.sub("",cell.value).strip(" \n\r") #先去号码 再去尾空
self.processBar.step() #进度加1
self.top.update() #实时刷新
sht.range(cellRange).row_height = hgt #设置行高
try:
wb.save(self.newfile)
except pywintypes.com_error as e:
msb.showinfo("",e)
else:
msb.showinfo("","处理完成！")
if __name__ == '__main__':
s = Window()
|