python进行两个表格对比的方法

时间:2022-10-11 08:47:17

如下所示:

?
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
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
# -*- coding:utf-8 -*-
import xlrd
import sys
import re
import json
 
dict1={}
dict2={}
mylist=[u'系统运维管理',u'安全管理机构',u'安全管理制度',u'人员安全管理',u'网络安全',u'物理安全',u'网络安全',u'主机安全',u'应用安全',u"网络安全",u"主机安全",u"主机安全",u'系统建设管理']
def check():
  data=xlrd.open_workbook('test.xls')
  for i in range(0,13):
    print u"第%s个表格"%(i+1)
    print mylist[i]
    print "-"*60
    try:
      table=data.sheets()[i]
    except IndexError,e:
      break
    nrows=table.nrows
    count=1
    if i<5:
      m=10
    else:
      m=16
    for j in range(m,nrows):
      cell_1=table.cell(j,2).value
      #print cell_1,
      cell_2=table.cell(j,3).value
      nu=re.split(r'\)',cell_2)
      num=re.split(r'\)',nu[0])
      if num is not None:
        #print num[0],
        pass
      else:
        print u"没有标记序号"
      try:
        if j<nrows:
          if table.cell(j,2).value==table.cell(j+1,2).value:
            count+=1
          else:
            print u"检查项【%s】共有【%s】项"%(table.cell(j,2).value,count)
            key=mylist[i]+table.cell(j,2).value
            dict1[key]=count
            count=1
        else:
          count+=1
          print u"检查项【%s】共有【%s】项"%(table.cell(j,2).value,count)
          key=mylist[i]+table.cell(j,2).value
          dict1[key]=count
        
      except IndexError,e:
        #count+=1
        print u"检查项【%s】共有【%s】项"%(table.cell(j,2).value,count)
        key=mylist[i]+table.cell(j,2).value
        dict1[key]=count
    print "-"*60
  print json.dumps(dict1, encoding='UTF-8', ensure_ascii=False)
  print u"完成excel表格读取-Thanks"
 
def ASGcheck(filename):
  data=xlrd.open_workbook(filename)
  table=data.sheets()[0]
  print "-"*60
  nrows=table.nrows
  count=1
  for j in range(1,nrows):
    cell_1=table.cell(j,2).value
    #print cell_1,
    cell_2=table.cell(j,3).value
    nu=re.split(r'\)',cell_2)
    num=re.split(r'\)',nu[0])
    if num is not None:
      #print num[0],
      pass
    else:
      print u"没有标记序号"
    try:
      if j<nrows:
        if table.cell(j,3).value==table.cell(j+1,3).value:
          count+=1
        else:
          print u"安全层面【%s】检查项【%s】共有【%s】项"%(table.cell(j,2).value,table.cell(j,3).value,count)
          key=table.cell(j,2).value+table.cell(j,3).value
          dict2[key]=count
          count=1
      else:
        count+=1
        print u"安全层面【%s】检查项【%s】共有【%s】项"%(table.cell(j,2).value,table.cell(j,3).value,count)
        key=table.cell(j,2).value+table.cell(j,3).value
        dict2[key]=count
      
    except IndexError,e:
      #count+=1
      print u"安全层面【%s】检查项【%s】共有【%s】项"%(table.cell(j,2).value,table.cell(j,3).value,count)
      key=table.cell(j,2).value+table.cell(j,3).value
      dict2[key]=count
  #print json.dumps(dict2, encoding='UTF-8', ensure_ascii=False)
  print "-"*60
def standard():
  choiceA=raw_input(u"请输入A的等级:A2,A3,A4")
  choiceS=raw_input(u"请输入S的等级:S2,S3,S4")
  choiceG=raw_input(u"请输入G的等级:G2,G3,G4")
  Aname=str(choiceA)+".xlsx"
  Sname=str(choiceS)+".xlsx"
  Gname=str(choiceG)+".xlsx"
  check()
 
  def compare():
    print "*"*60
    for key in dict2:
      try:
        if dict2[key]!=dict1[key]:
          print u"存在异常项"
          print u"从程序中导出的检查项【%s】共有%s项"%(key,json.dumps(dict1[key], encoding='UTF-8', ensure_ascii=False))
          print u"从分支查询的检查项【%s】共有%s项"%(key,json.dumps(dict2[key], encoding='UTF-8', ensure_ascii=False))
        else:
          pass
      
      except KeyError,e:
        print u"分支中的检查项【%s】共有%s项"%(key,json.dumps(dict2[key], encoding='UTF-8', ensure_ascii=False)),
        print u"程序中没有查询到该项"
 
 
  #比较分支结果和程序导出的结果 
  ASGcheck(Aname)
  compare()
  ASGcheck(Sname)
  compare()
  ASGcheck(Gname)
  compare()
standard()
#ASGcheck('S3.xlsx')

以上这篇python进行两个表格对比的方法就是小编分享给大家的全部内容了,希望能给大家一个参考,也希望大家多多支持服务器之家。

原文链接:https://blog.csdn.net/qq1124794084/article/details/52863231