python比较两个excel表格的差异
一个同事有两个excel表格要比较差异, 找了一下有相关软件,如: beyond compare, excel compare但这两个似乎都是直接排序再比较的.
然后没办法,就用python折腾了一个脚本. 这个脚本先读入要比较的文件中的表. 读的时候 ,如果没有空行就把它和它前面的加一起,直到有空行. 这样比较的话, 不能得到具体那一行有差异, 只有一个大概的位置. 如果表格中间空行越少,越精确.
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
#!/usr/bin/python
#-*- coding:utf-8 -*-
import xlrd, os, sys
if len(sys.argv) != 3:
print "\n ./script diff_1.xlsx diff_2.xlsx \n"
sys.exit()
diff = {}
xls1 = sys.argv
xls2 = sys.argv
if os.path.isfile(xls1) and os.path.isfile(xls2):
pass
else:
print "\n ./script diff_1.xlsx diff_2.xlsx \n"
sys.exit()
def getcontent(table):#获取xlsx表内容
tmp_data = {}
tmp_table = ''
all_data = {}
for j in xrange(table.nrows):
tmp = table.row_values(j)
if list(set(tmp)) == ['']:
tmp_data = ''
if tmp_table != '':
all_data = tmp_table
tmp_table = ''
else:
tmp2 = ""
for i in tmp:
try:
tmp2 = tmp2 + i + ","
except:
tmp2 = tmp2 + str(i)+ ","
tmp_table = tmp_table + tmp2 + "\n"#把多行的内容放一起
return (tmp_data, all_data)
def write_file(excel_diff, filename):
f = open(filename, 'w')
f.write(excel_diff)
f.close()
def diff_content(table1,table2): #检查两个表差异
diff_tmp = []
for i in table1:
if i in table2:
pass
else:
diff_tmp.append(i)
return list(set(diff_tmp))
def get_rows(diff, all_data): #获取差异位置
tmp = []
for i in diff:
for j in all_data:
if all_data.strip() == i.strip():
tmp.append(j)
break
return tmp
for i in range(0,2):# 比较几个表
data1 = xlrd.open_workbook(xls1)
table1 = data1.sheets()
data2 = xlrd.open_workbook(xls2)
table2 = data2.sheets()
tmp1, all1 = getcontent(table1)
tmp2, all2 = getcontent(table2)
result = diff_content(tmp1, tmp2) #1 中有 2 没有的具体内容
#result2= diff_content(tmp2, tmp1)#2 中有 1 没有的具体内容
diff = get_rows(result,all1)
print sorted(diff)
#write_file(sorted(diff), "/tmp/diff.txt")
#diff = get_rows(result2,all2)
#print sorted(diff)
页:
[1]