【分享】python之读写excel以及查找共同的行

mswsg

1. # ! /usr/bin/env python3
   
2. # -*- coding: utf-8 -*-
   
3. '''
   
4. python3 find_common_line_in_two_excels.py file1.xls file2.xls output.xls
   
5. 
   
6. '''
   
7. from xlrd import *
   
8. import xlwt as ExcelWrite
   
9. import os
   
10. import sys
    
11. input1 = os.path.abspath(sys.argv[1])
    
12. input2 = os.path.abspath(sys.argv[2])
    
13. output = os.path.abspath(sys.argv[3])
    
14. 
    
15. def open_excel(excel):
    
16.     try:
    
17.         data = open_workbook(excel)
    
18.         return data
    
19.     except Exception as e:
    
20.         print (str(e))
    
21. def excel_table_byindex(excel,colnameindex=0,by_index=0):  #将文件按行放入列表 [[行]，[行]]
    
22.     data = open_excel(excel)
    
23.     table = data.sheets()[by_index]
    
24.     nrows = table.nrows
    
25.     ncols = table.ncols
    
26.     colnames =  table.row_values(colnameindex)
    
27.     row_list = []
    
28.     for rownum in range(nrows):
    
29.         row = table.row_values(rownum)
    
30.         row_list.append(row)
    
31.     return row_list
    
32. def main():
    
33.     tables1 = excel_table_byindex(input1)
    
34.     tables2 = excel_table_byindex(input2)
    
35.     for i in range(len(tables1)):
    
36.         if tables1[i] in tables2: #判断 文件一中的某一行是否在文件2中，是的话打印，否则打印 没有共有的行
    
37.             return tables1[i]
    
38. def writeXLS(file_name):
    
39.     if main():
    
40.         value = [main()]
    
41.         #print (value)
    
42.         xls = ExcelWrite.Workbook()
    
43.         sheet = xls.add_sheet("Sheet1")
    
44.         for i in range(len(value)):
    
45.             for j in range(0,len(value[0])):
    
46.                 sheet.write(i, j, value[i][j])
    
47.         xls.save(file_name)
    
48.     else:
    
49.         print ("no common")
    
50. 
    
51. if __name__=="__main__":
    
52.     writeXLS(output);
    

复制代码

yjh777

谢谢分享，学习了

Hadron74

曾写过一个程序，类似的功能，支持多sheet比较，注释和颜色标记。也分享出来。

1. __author__ = 'luhongc'
   
2. 
   
3. import xlrd
   
4. import xlwt
   
5. import xlutils.copy
   
6. 
   
7. 
   
8. def compare(oldFile,newFile,outputFile,comments=True,comment_pattern="DPL Comment",table_split="^Table\s*\d+$",max_columns=30):
   
9.     """compare based on the directions, outputD for out put"""
   
10. 
    
11.     oldwb = xlrd.open_workbook(oldFile)
    
12.     newwb = xlrd.open_workbook(newFile, formatting_info=True)
    
13. 
    
14. 
    
15. 
    
16.     yield "Pairing the files....\n"
    
17.     paired, OldExtra, NewExtra = pairFiles(oldwb,newwb)
    
18. 
    
19.     if (len(OldExtra)>0):
    
20.         sheets = "\n".join (OldExtra)
    
21.         yield "Warning: Sheet \n {}\nare only in Old File!\n".format(sheets)
    
22.     if (len(NewExtra)>0):
    
23.         sheets = "\n".join (NewExtra)
    
24.         yield "Warning: Sheet \n {}\nare only in New File!\n".format(sheets)
    
25. 
    
26.     yield "\nComparing the files....\n"
    
27. 
    
28.     outwb = xlutils.copy.copy(newwb)
    
29.     outwb.set_colour_RGB(0x2A,100,200,100)   # set light green color for output
    
30. 
    
31.     def get_sheet_by_name(book, name):
    
32.         """Get a sheet by name from xlwt.Workbook, a strangely missing method.
    
33.         Returns None if no sheet with the given name is present.
    
34.         """
    
35.     # Note, we have to use exceptions for flow control because the
    
36.     # xlwt API is broken and gives us no other choice.
    
37.         import itertools
    
38.         try:
    
39.             for idx in itertools.count():
    
40.                 sheet = book.get_sheet(idx)
    
41.                 if sheet.name == name:
    
42.                     return sheet
    
43.         except IndexError:
    
44.             return None
    
45. 
    
46.     for k,v in paired.iteritems():
    
47.         if v is not None:
    
48.             sheetOld = oldwb.sheet_by_name(v)
    
49.             sheetNew = newwb.sheet_by_name(k)
    
50.             sheetOut = get_sheet_by_name(outwb,k)
    
51.         else:
    
52.             sheetOld = None
    
53.             sheetNew = newwb.sheet_by_name(k)
    
54.             sheetOut = get_sheet_by_name(outwb,k)
    
55. 
    
56. 
    
57.         for txt in GDOcomMain(k,sheetOut,sheetNew,sheetOld,comments,comment_pattern,table_split,max_columns):
    
58.             yield txt
    
59.     outwb.save(outputFile)
    
60. 
    
61. def GDOcomMain(sheetName,sheetOut,sheetNew,sheetOld,comments,comment_pattern,table_split,max_columns):
    
62.     """ Main Comparison
    
63.     """
    
64.     yield "Comparing Sheet {}.".format(sheetName)
    
65.     if sheetOld is None:                               # For a New Only Sheet
    
66.         for txt in copyNew(sheetNew,sheetOut):
    
67.             yield txt
    
68.     else:
    
69.         has_comment,datapool = readOld(sheetOld,comment_pattern,table_split,max_columns)  # Read Data Pool from Old Sheet.
    
70.         nTable = len(datapool)
    
71.         if nTable > 1:
    
72.             nTable -= 1
    
73.         yield sheetName + " has " +str(nTable) +" table(s)."
    
74. 
    
75.         # Comparing file can read new sheet
    
76.         for txt in typeCompare(sheetName,sheetOut,sheetNew,datapool,has_comment,table_split,comment_pattern,comments,max_columns):
    
77.             yield txt
    
78. 
    
79. def hash_key(thisrow,comment_column):
    
80.     """ define hash key
    
81.     """
    
82.     return u"|".join(thisrow[:comment_column+1])
    
83. 
    
84. 
    
85. def readOld(sheet,comment_pattern,table_split,max_columns):
    
86.     """ readOld sheet
    
87.     """
    
88.     import re
    
89.     p = re.compile(table_split,re.UNICODE)        # split pattern
    
90. 
    
91.     datapool = []                # replied data pool
    
92.     bufs = []                    # buffers to store rows
    
93. 
    
94.     has_comment = False
    
95. 
    
96.     buf = []
    
97.     thispool = {}
    
98.     commentTag = None
    
99.     newpart = False
    
100.     for row in xrange(sheet.nrows):
     
101.         thisrow = [u'' for i in range(max_columns)]        # fill row with "" according to max_columns
     
102.         for col in xrange(sheet.ncols):
     
103.             cell = sheet.cell(row,col)
     
104.             v = unicode(cell.value).strip()
     
105.             if p.match(v):
     
106.                 newpart = True                            # split flag
     
107.             if v.upper() == comment_pattern.upper():      # detect comment_pattern
     
108.                 if commentTag is not None:
     
109.                     assert col == commentTag, "Comments should in the same column in old Sheet {}".format(sheet.name)
     
110.                 commentTag = col
     
111.                 has_comment = True
     
112.             thisrow[col] = v
     
113. 
     
114.         buf.append(thisrow)
     
115.         if newpart:
     
116.             datapool.append((thispool,commentTag))
     
117.             bufs.append(buf)
     
118.             thispool = {}
     
119.             commentTag = None
     
120.             buf = []
     
121.             newpart = False
     
122. 
     
123.     datapool.append((thispool,commentTag))         # The last part
     
124.     bufs.append(buf)
     
125. 
     
126.     for (pool,Tag),buf in zip(datapool,bufs):               # fill data pool
     
127.         for thisrow in buf:
     
128.             if Tag is None:                                 # if there is no comments
     
129.                 key = hash_key(thisrow,len(thisrow)-1)
     
130.                 pool[key] = ""
     
131.             else:
     
132.                 key = hash_key(thisrow,Tag-1)               # if there is comments
     
133.                 pool[key] = thisrow[Tag]
     
134.     return has_comment,datapool
     
135. 
     
136. 
     
137. def copyNew(sheetNew,sheetOut,color='yellow'):
     
138.     """Annotate a new sheet """
     
139.     for row in range(sheetNew.nrows):
     
140.         for col in range(sheetNew.ncols):
     
141.             v = sheetNew.cell(row,col).value
     
142.             style = xlwt.easyxf('pattern: pattern solid, fore_colour {}'.format(color))
     
143.             if v == u"":                                     # only change the null cells
     
144.                 sheetOut.write(row,col,v,style)
     
145.     yield "Sheet {} is new one and annotated in YELLOW.\n".format(sheetOut.name)
     
146. 
     
147. def typeCompare(sheetName,sheetOut,sheetNew,datapool,has_comment,table_split,comment_pattern,comments,max_columns,color='0x2A'):
     
148.     """ Comparing sheets and output
     
149.     """
     
150.     import re
     
151.     p = re.compile(table_split,re.UNICODE)       # table split pattern
     
152. 
     
153.     recordpool = []
     
154.     record = []
     
155.     newpart = False
     
156.     partid = 0
     
157.     commentTag = datapool[partid][1]
     
158. 
     
159.     # Check the tables in new sheet
     
160.     for row in xrange(sheetNew.nrows):
     
161.         for col in xrange(sheetNew.ncols):
     
162.             cell = sheetNew.cell(row,col)
     
163.             v = unicode(cell.value).strip()
     
164.             if p.match(v):
     
165.                 newpart = True
     
166.             if v.upper() == comment_pattern.upper():
     
167.                 assert col == commentTag, "Comments should in the same column in new Sheet {}".format(sheetNew.name)
     
168.         record.append(row)
     
169.         if newpart:
     
170.             recordpool.append((record,commentTag))
     
171.             record = []
     
172.             partid += 1
     
173.             try:
     
174.                 commentTag = datapool[partid][1]
     
175.             except:
     
176.                 raise ValueError("Sheet {} has different number of tables".format(sheetName))
     
177.             newpart = False
     
178. 
     
179.     recordpool.append((record,commentTag))
     
180. 
     
181.     assert len(recordpool) == len(datapool),"Sheet {} has different number of tables".format(sheetName)
     
182. 
     
183.     for partid,(record,Tag) in enumerate(recordpool):
     
184.         for row in record:
     
185.             thisrow = [u'' for i in range(max_columns)]       # fill the this row with null according to max_columns
     
186.             for col in xrange(sheetNew.ncols):
     
187.                 cell = sheetNew.cell(row,col)
     
188.                 v = unicode(cell.value).strip()
     
189.                 thisrow[col] = v
     
190.             if Tag is None:                                   # define key by commentTag
     
191.                 key = hash_key(thisrow,len(thisrow)-1)
     
192.             else:
     
193.                 key = hash_key(thisrow,Tag-1)
     
194.             if key in datapool[partid][0]:
     
195.                 # old records
     
196.                 oldTag = datapool[partid][1]
     
197.                 if oldTag is not None and comments:
     
198.                     sheetOut.write(row,oldTag,datapool[partid][0][key])
     
199.             else:
     
200.                 # new records
     
201.                 for col in xrange(sheetNew.ncols):
     
202.                     cell = sheetNew.cell(row,col)
     
203.                     v = cell.value
     
204.                     is_date = False
     
205.                     try:                                        # detect if the format is possible date field
     
206.                         date = float(v)
     
207.                         if date > 10000.0 and date < 99999.0:
     
208.                             is_date = True
     
209.                     except:
     
210.                         pass
     
211. 
     
212.                     if not is_date:
     
213.                         style = xlwt.easyxf('pattern: pattern solid, fore_colour {}; align: horiz center; border:top thin,bottom thin;'.format(color))
     
214.                         sheetOut.write(row,col,v,style)
     
215.     yield "Sheet {} is OK. New items are annotated in GREEN\n".format(sheetOut.name)
     
216. 
     
217. def pairFiles(oldwb,newwb):
     
218.     """paired the sheets based on format"""
     
219.     newSheets = { k.strip():k for k in newwb.sheet_names()}          # paired by stripped names
     
220.     oldSheets = { k.strip():k for k in oldwb.sheet_names()}
     
221. 
     
222.     oldExtra = set(oldSheets.keys()) - set(newSheets.keys())
     
223.     newExtra = set(newSheets.keys()) - set(oldSheets.keys())
     
224. 
     
225.     paired = {}
     
226.     for s,v in newSheets.iteritems():
     
227.         if s in oldSheets:
     
228.             paired[v] = oldSheets[s]
     
229.         else:
     
230.             paired[v] = None
     
231. 
     
232.     return paired, oldExtra, newExtra
     
233. 
     
234. if __name__ == "__main__":
     
235.     import sys
     
236.     oldD,newD,outputD = sys.argv[1:]
     
237.     for txt in compare(oldD,newD,outputD):
     
238.         print txt

复制代码