def openFile(self):
s = QFileDialog.getOpenFileName(self,"Open file dialog","/","Excel files(*.xls)")
if not s == "":
if self.sender().text() == "Select Source":
self.sourcePathEdit.setText(str(s))
self.sourceSheetBox.clear()
sourceExcel = ExcelApp(s,"")
self.sourceSheetBox.addItems(sourceExcel.getSheetsName())
elif self.sender().text() == "Select Target":
self.targetPathEdit.setText(str(s))
self.targetSheetBox.clear()
targetExcel = ExcelApp(s,"")
self.targetSheetBox.addItems(targetExcel.getSheetsName())
def compareFiles(sourceFile, sourceSheet, targetFile, targetSheet, reportName, pkNO):
srcExcel = ExcelApp(sourceFile,"")
tgtExcel = ExcelApp(targetFile,"")
resultExcel = ExcelApp(reportName, ["Compare_Result","Records_in_Source_NOT_in_Target","Records_in_Target_NOT_in_Source"])
srcColCount = srcExcel.getColCount(sourceSheet, 0)
srcRowCount = srcExcel.getRowCount(sourceSheet, 0)
tgtRowCount = tgtExcel.getRowCount(targetSheet, 0)
srcbook = srcExcel.openExcel("r")
tgtbook = tgtExcel.openExcel("r")
rstbook = resultExcel.openExcel("w")
srcsheet = srcExcel.getSheetByName(srcbook, sourceSheet)
tgtsheet = tgtExcel.getSheetByName(tgtbook, targetSheet)
rstsheet = resultExcel.getSheetByIndex(rstbook, 0)
missingInSRCSheet = resultExcel.getSheetByIndex(rstbook, 1)
missingInTGTSheet = resultExcel.getSheetByIndex(rstbook, 2)
srcdic = {}
tgtdic = {}
k = 0
i = 0
j = 0
print time.strftime('%I-%M-%S_%Y-%m-%d',time.localtime(time.time()))
'''
Write missing records to result report
'''
while i < srcRowCount:
srcrowdata = srcExcel.getRowData(srcsheet, i)
srcdic.setdefault(srcExcel.getCellData(srcsheet, i, pkNO), []).append(srcrowdata)
i = i + 1
# print sorted(srcdic.iteritems())
while j < tgtRowCount:
tgtrowdata = tgtExcel.getRowData(tgtsheet, j)
tgtdic.setdefault(tgtExcel.getCellData(tgtsheet, j, pkNO), []).append(tgtrowdata)
j = j + 1
'''
1. check common records
2. write common records
3. delete common records and leave missing records
'''
i = 0
j = 0
r = 5
commonRecords = 0;
matchCount = [0]
differCount = [0]
for k in range(srcColCount):
matchCount.append(0)
differCount.append(0)
sortedSrcDic = sorted(srcdic.iteritems())
sortedTgtDic = sorted(tgtdic.iteritems())
while i < len(sortedSrcDic) and j < len(sortedTgtDic):
if sortedSrcDic[i][0] > sortedTgtDic[j][0]:
j = j + 1
elif sortedSrcDic[i][0] < sortedTgtDic[j][0]:
i = i + 1
else:
commonRecords = commonRecords + 1
# resultExcel.setCellData(rstsheet, r, 1, sortedSrcDic[i][0])
# resultExcel.setCellData(rstsheet, r + 1, 1, sortedTgtDic[j][0])
k = 0
while k < srcColCount:
if sortedSrcDic[i][1][0][k] == sortedTgtDic[j][1][0][k]:
resultExcel.setCellData(rstsheet, r, k + 1, sortedSrcDic[i][1][0][k])
resultExcel.setCellData(rstsheet, r + 1, k + 1, sortedTgtDic[j][1][0][k])
matchCount[k] = matchCount[k] + 1
else:
resultExcel.setCellData(rstsheet, r, k + 1, sortedSrcDic[i][1][0][k], True,"light-orange", "black")
resultExcel.setCellData(rstsheet, r + 1, k + 1, sortedTgtDic[j][1][0][k], True,"orange", "black")
differCount[k] = differCount[k] + 1
k = k + 1
r = r + 3
del sortedSrcDic[i]
del sortedTgtDic[j]
'''
Write missing records to result report
'''
i = 0
while i < len(sortedSrcDic):
j = 0
while j < srcColCount:
resultExcel.setCellData(missingInSRCSheet, i, j, sortedSrcDic[i][1][0][j])
j = j + 1
i = i + 1
i = 0
while i < len(sortedTgtDic):
j = 0
while j < srcColCount:
resultExcel.setCellData(missingInTGTSheet, i, j, sortedTgtDic[i][1][0][j])
j = j + 1
i = i + 1
'''
Summary
'''
resultExcel.setCellData(rstsheet, 0, 0, "Good Records on Field",True,"white","light-blue", "on")
resultExcel.setCellData(rstsheet, 1, 0, "Good Records Rate on Field",True,"white","light-blue", "on")
resultExcel.setCellData(rstsheet, 2, 0, "Bad Records on Field",True,"white","light-blue", "on")
resultExcel.setCellData(rstsheet, 3, 0, "Bad Records Rate on Field",True,"white","light-blue", "on")
resultExcel.setColWidth(rstsheet, 0, 6666)
for k in range(srcColCount):
if not differCount[k] == 0:
color = "red"
else:
color = "green"
resultExcel.setCellData(rstsheet, 0, k + 1, matchCount[k], True, "green","white", "on")
resultExcel.setCellData(rstsheet, 1, k + 1, "".join([str(matchCount[k]*100/commonRecords),"%"]),True,"green","white", "on")
resultExcel.setCellData(rstsheet, 2, k + 1, differCount[k], True, color,"white", "on")
resultExcel.setCellData(rstsheet, 3, k + 1, "".join([str(differCount[k]*100/commonRecords),"%"]), True,color,"white", "on")
'''
Save report
'''
resultExcel.saveExcel(rstbook)
print time.strftime('%I-%M-%S_%Y-%m-%d',time.localtime(time.time()))
