descript: 拖拉两个xls文件到此小程序中, 自动进行比对并输出比对结果(xls)
# -*- coding: utf-8 -*-
# Import wx.Python
import wx
import xlrd,xlwt
import os.path
# Declare GUI Constants
DRAG_SOURCE = wx.NewId()
#=====================================
def encoding(s,coding=None):
cl = ['utf8', 'gb2312','gb18030']
if coding:
cl.append(coding)
for a in cl:
try:
s.decode(a)
return a
except UnicodeEncodeError:
pass
return 'unknown'
def toUnicode(s,coding=None):
if isinstance(s,unicode):
return s
return s.decode(encoding(s,coding))
#=================================================
# Define File Drop Target class
class FileDropTarget(wx.FileDropTarget):
""" This object implements Drop Target functionality for Files """
def __init__(self, obj):
""" Initialize the Drop Target, passing in the Object Reference to
indicate what should receive the dropped files """
# Initialize the wxFileDropTarget Object
wx.FileDropTarget.__init__(self)
# Store the Object Reference for dropped files
self.obj = obj
self._initContent()
def _initContent(self):
self.content = {'same':[],'diff':[]}
def _log(self,string):
self.obj.WriteText(toUnicode(string))
def _analyze(self,xls={}):
def combine_rows(sheet):
rows = []
for rownum in range(sheet.nrows):
rows.append("-".join([toUnicode(str(row) if isinstance(row,float) else row) \
for row in sheet.row_values(rownum)]))
return rows
#FIXME optime
name = xls.keys()
self._log(u"正在比对 "+name[0]+u" , "+name[1]+u" 中...... \n")
rows = []
for sheet in xls.values():
rows.append(combine_rows(sheet))
same_rows = list(set(rows[0]) & set(rows[1]))
file1_rows = list(set(rows[0]) - set(rows[1]))
file2_rows = list(set(rows[1]) - set(rows[0]))
self.content['same'].extend(same_rows)
self.content['diff'].extend(\
[{'name':name[0],'rows':file1_rows},
{'name':name[1],'rows':file2_rows}])
self._log("比对完成 \n")
return 1
def _dumpToXls(self,path):
self._log("生成比对结果中...... \n")
xls = xlwt.Workbook()
sheet_same = xls.add_sheet(u'相同数据',cell_overwrite_ok=True)
same_rows = self.content['same']
def write_rows(sheet,rows):
for r in range(len(rows)):
cells = rows[r].split('-')
for c in range(len(cells)):
sheet.write(r,c,cells[c])
write_rows(sheet_same,same_rows)
sheet_diff = {}
for diff in self.content['diff']:
name = diff.get('name','default')
rows = diff.get('rows',[])
sheet_diff[name] = xls.add_sheet(name+u"独有",cell_overwrite_ok=True)
write_rows(sheet_diff[name],rows)
result = os.path.join(path,u" 和 ".join(sheet_diff.keys())+u'的比对结果.xls')
xls.save(result)
self._log(u"生成比对结果如下:\n%s\n" % (result,))
self._initContent()
def _compare(self,filenames):
filenames = filenames if isinstance(filenames,list) else list(filenames)
#for mutiple files
xls={}
try:
for file in filenames:
name = os.path.basename(file).split('.')[0]
xls[name] = xlrd.open_workbook(file).sheets()[0]
except xlrd.biffh.XLRDError,e:
self._log("请载入xls格式文件")
return False
#filenames.sort(self._analyze)
self._analyze(xls)
self._dumpToXls(os.path.dirname(filenames[0]))
def OnDropFiles(self, x, y, filenames):
""" Implement File Drop """
# For Demo purposes, this function appends a list of the files dropped at the end of the widget's text
# Move Insertion Point to the end of the widget's text
self.obj.SetInsertionPointEnd()
# append a list of the file names dropped
self._log("\n加载了 %d 个文件:\n" % (len(filenames)))
if len(filenames) != 2:
self._log("只能比对两个文件\n")
return False
self._compare(filenames)
class MainWindow(wx.Frame):
""" This window displays the GUI Widgets. """
def __init__(self,parent,id,title):
#wx.Frame.__init__(self,parent,-4, title, size = (430,270), style=wx.DEFAULT_FRAME_STYLE|wx.NO_FULL_REPAINT_ON_RESIZE)
wx.Frame.__init__(self,parent,-4, title, size = (430,270), \
style=wx.MINIMIZE_BOX | wx.MAXIMIZE_BOX | wx.SYSTEM_MENU | wx.CAPTION | wx.CLOSE_BOX | wx.CLIP_CHILDREN )
self.SetBackgroundColour(wx.WHITE)
wx.StaticText(self, -1, u"请将要比对的EXCEL文件拖拉进下面区域进行比对",(3,0))
self.text = wx.TextCtrl(self, -1, "", pos=(2,15),size=(418,220), style = wx.TE_MULTILINE|wx.HSCROLL|wx.TE_READONLY)
dt = FileDropTarget(self.text)
self.text.SetDropTarget(dt)
# Display the Window
self.Show(True)
def CloseWindow(self, event):
""" Close the Window """
self.Close()
def OnDragInit(self, event):
""" Begin a Drag Operation """
# Create a Text Data Object, which holds the text that is to be dragged
tdo = wx.PyTextDataObject(self.text.GetStringSelection())
# Create a Drop Source Object, which enables the Drag operation
tds = wx.DropSource(self.text)
# Associate the Data to be dragged with the Drop Source Object
tds.SetData(tdo)
# Intiate the Drag Operation
tds.DoDragDrop(True)
class MyApp(wx.App):
""" Define the Drag and Drop Example Application """
def OnInit(self):
""" Initialize the Application """
# Declare the Main Application Window
frame = MainWindow(None, -1, u"Excel文件比对小程序")
# Show the Application as the top window
self.SetTopWindow(frame)
return True
# Declare the Application and start the Main Loop
app = MyApp(0)
app.MainLoop()
)：