最近媳妇工作上遇到一个重复性劳动,excel表格查重,重复的标记起来,问我能不能写个程序让它自动查重标记
必须安排
第一次正儿八经写python,边上网查资料,边写
终于成功了
在此记录一下
首先安装xlwings库
1
|
pip install xlwings
|
写代码
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
107
108
|
import xlwings as xw
# 输入表名
title = input ()
# 指定不显示地打开Excel,读取Excel文件
app = xw.App(visible = False , add_book = False )
wb = app.books. open (title) # 打开Excel文件
sheet = wb.sheets[ 0 ] # 选择第0个表单
# 获取表行数
sheetInfo = sheet.used_range
maxRow = sheetInfo.last_cell.row
# maxColumn = sheetInfo.last_cell.column
# print('表行数:',maxRow)
# 单据编号
num = []
# 报销类型
baoxiaoType = []
# 部门
department = []
# 收款方
name = []
# 报销金额
money = []
# 将需要的数据读取保存
for row in range ( 2 , maxRow):
value = sheet. range ( "A" + str (row)).value
num.append(value)
value = sheet. range ( "C" + str (row)).value
baoxiaoType.append(value)
value = sheet. range ( "H" + str (row)).value
department.append(value)
value = sheet. range ( "N" + str (row)).value
name.append(value)
value = sheet. range ( "K" + str (row)).value
money.append(value)
# print(num)
# print(baoxiaoType)
# print(department)
# print(name)
# print(money)
# 保存标记为重复的行号
flag = []
# 判断是否已经标记为重复
# 重复返回Ture
# 否则返回False
def isRepeat(index):
for num in flag:
if num = = index:
return True
else :
continue
return False
# 遍历每一行,进行查重
for row in range ( 0 , len (money)):
# 判断是否已经标记为重复
# 如果重复不做判断,结束本次循环
# 否则断续向下执行
if True = = isRepeat(row + 2 ):
continue
elif False = = isRepeat(row + 2 ):
# 获取当前行数据
current = money[row]
# 遍历后面行是否和当前行数据重复
for subRow in range ( 1 , len (money)):
# 获取下一行数据
subCur = money[subRow]
# 判断当前行内容和对比行内容是否相等
if current = = subCur:
# 再判断编号行内容是否相等
if num[row] = = num[subRow]:
continue
else :
# 对比其它内容是否相等
if (
(department[row] = = department[subRow])
and (baoxiaoType[row] = = baoxiaoType[subRow])
and (name[row] = = name[subRow])
):
# 将重复行行号保存,表格的表头,且表头行号从1 开始,所以行号等于当前索引+2
flag.append(subRow + 2 )
# 设置两个重复行的首列单元格颜色
cell = sheet. range ( "A" + str (row + 2 ))
cell.color = 0 , 255 , 255
subcell = sheet. range ( "A" + str (subRow + 2 ))
subcell.color = 0 , 255 , 255
# 打印提示
print ( "重复起始行:" , row + 2 , "重复行" , subRow + 2 )
# 保存当前工作簿
wb.save()
# 关闭当前工作簿
wb.close()
# 退出excel程序
app.quit()
# 阻塞不退出
input ( "Press Any Key" )
|
鉴于媳妇办公电脑不方便安装python环境,所以打包成exe可执行程序,使用pyinstaller工具
安装
1
|
pip install pyinstaller
|
打包
1
2
3
|
# -F 打包为单文件
# -i 指定图标
pyinstaller -F *.py -i *.ico
|
以上就是用python对excel查重的详细内容,更多关于python excel查重的资料请关注服务器之家其它相关文章!
原文链接:https://www.cnblogs.com/Doyoung/p/14077054.html