Solved
Forum Discussion
Bharadwaj
9 years agoContributor
Hi The code which I am providing here reads the excel files in 2 folders and compares one with the other. The only catch here is the file name of both the files in the 2 different folders should be the same ,but with a different prefix. For example
C:\AcceessExcelFiles\Access_File1.xlsx
C:\SQLExcelFiles\Sql_File1.xlsx
function CompareExcelFilesInDirectories(ExpectedDirPath:string,ActualDirPath:string,ExpFilePrefix:string,ActFilePrefix:string); var MissingExpFiles,MissingActFiles,filesFailed,filesPassed,ExpectedFileName,ActualFileName:string; CompareFilesDict,ky:OleVariant; filesPassedMsg,filesFailedMsg:string; ExpSheet,ActSheet:string; i,j:integer; begin try try CompareFilesDict := Sys.OleObject['Scripting.Dictionary']; MapDirectoryFiles(MissingExpFiles,MissingActFiles,CompareFilesDict,ExpectedDirPath+'\',ActualDirPath+'\',ExpFilePrefix,ActFilePrefix); if CompareFilesDict.Count > 0 then Log.Message('Total Files in the actual and expected folders to compare: ' + VarToStr(CompareFilesDict.Count)); ky := CompareFilesDict.Keys; for i := 0 to CompareFilesDict.Count -1 do begin try ActualFileName := ActualDirPath + '\' + ActFilePrefix+CompareFilesDict.Item(ky[i]); ExpectedFileName := ExpectedDirPath + '\' + ExpFilePrefix+CompareFilesDict.Item(ky[i]); Log.AppendFolder(' The the files : ' + ExpectedFileName + ' and '+ ActualFileName + ' are being compared'); if not CompareExcel(ExpectedFileName, ActualFileName) then begin filesFailed := CompareFilesDict.Item(ky[i]) + ',' + filesFailed; end else begin filesPassed := CompareFilesDict.Item(ky[i]) + ',' + filesPassed; end; finally Log.PopLogFolder; end; end; if TidyString(filesFailed) <> '' then begin filesFailed := RemoveLastSeparator( filesFailed,','); filesFailedMsg := ' The files whaich have failed comparision are as follows : ' + #13#10; aqString.ListSeparator := ','; for i:= 0 to aqString.GetListLength(filesFailed) -1 do begin filesFailedMsg := filesFailedMsg + ' Expected File : ' + ExpectedDirPath + '\' + ExpFilePrefix+aqString.GetListItem(filesFailed,i) +#13#10+ ' Actual File : ' + ActualDirPath + '\' + ActFilePrefix+aqString.GetListItem(filesFailed,i) + #13#10; end; end; if TidyString(filesPassed) <> '' then begin filesPassed := RemoveLastSeparator( filesPassed,','); filesPassedMsg := ' The files whaich have passed comparision are as follows : ' + #13#10; aqString.ListSeparator := ','; for i:= 0 to aqString.GetListLength(filesPassed) -1 do begin filesPassedMsg := filesPassedMsg + ' Expected File : ' + ExpectedDirPath + '\' + ExpFilePrefix+aqString.GetListItem(filesPassed,i) +#13#10+ ' Actual File : ' + ActualDirPath + '\' + ActFilePrefix+aqString.GetListItem(filesPassed,i) + #13#10; end; end; except Log.Error('Exception in CompareExcelFilesInDirectories',exceptionmessage); end; finally Log.Message('Click on addtional information for Comparision Summary : ','Comparision Summary ' +#13#10 + filesPassedMsg + #13#10 +#13#10 + filesFailedMsg+ #13#10 +#13#10 + 'Files which have been skipped comparision : ' +#13#10 + MissingExpFiles +',' + MissingActFiles ); end; end; { Summary: Compare excel files by comparing cell by cell and not using DDT } function CompareExcel(ExpectedFileName, ActualFileName, RegExpr: OleVariant = nil, CompareProperty: string = 'Text'): boolean; var excel,excelProcess, expectedFile, actualFile, expectedSheet, actualSheet, regEx: OleVariant; s, expectedStr, actualStr: string; col, row, i, j,picCount: integer; actClipBoardPic,expClipBoardPic:OleVariant; begin try result := true; //check expected file exists if not aqFile.Exists(ExpectedFileName) then begin result := false; Log.Warning('Expected file not exists.', ExpectedFileName); exit; end; //check actual file exists if not aqFile.Exists(ActualFileName) then begin result := false; Log.Error('Actual file not exists.', ActualFileName); exit; end; excelProcess := Sys.WaitProcess('Excel',5000); if excelProcess.Exists then excelProcess.Terminate; //open files excel := Sys.OleObject['Excel.Application']; expectedFile := excel.WorkBooks.Open(ExpectedFileName); actualFile := excel.WorkBooks.Open(ActualFileName); //get sheets expectedSheet := expectedFile.WorkSheets[1].UsedRange; actualSheet := actualFile.WorkSheets[1].UsedRange; //compare no of columns col := expectedSheet.Columns.Count; //will hold the highest col if expectedSheet.Columns.Count <> actualSheet.Columns.Count then begin s := 'Expected: ' + VarToStr(expectedSheet.Columns.Count) + #13#10 + 'Actual: ' + VarToStr(actualSheet.Columns.Count); Log.Error('Excel no of columns are not identical.', s); result := false; //exit; if expectedSheet.Columns.Count < actualSheet.Columns.Count then col := actualSheet.Columns.Count; end; //compare no of rows row := expectedSheet.Rows.Count; //will hold the highest row if expectedSheet.Rows.Count <> actualSheet.Rows.Count then begin s := 'Expected: ' + VarToStr(expectedSheet.Rows.Count) + #13#10 + 'Actual: ' + VarToStr(actualSheet.Rows.Count); Log.Error('Excel no of rows are not identical.', s); result := false; //exit; if expectedSheet.Rows.Count < actualSheet.Rows.Count then row := actualSheet.Rows.Count; end; if VarType(regExpr) > 3 then begin //Creates the regular expression object regEx := HISUtils.RegExpr; regEx.Expression := regExpr; end; picCount := GetMax(VarToStr(actualSheet.WorkSheet.Shapes.Count)+','+VarToStr(expectedSheet.WorkSheet.Shapes.Count),','); {Compare Pictures in the cells} if VarToInt(actualSheet.WorkSheet.Shapes.Count) <> VarToInt(expectedSheet.WorkSheet.Shapes.Count) then begin Log.Error('There might be pictures that are either missing in actual or expected sheets,click on addtional information tab for details....',' Expected Sheet picture count : '+VarToStr(expectedSheet.WorkSheet.Shapes.Count)+#13#10 + ' Actual Sheet picture count : '+VarToStr(actualSheet.WorkSheet.Shapes.Count)+#13#10 ); end; {end} //compare the images if picCount > 0 then begin for i:= 1 to picCount do begin try //Sys.Clipboard := actualSheet.WorkSheet.Shapes.Item(1).DrawingObject.CopyPicture(2,-4147); //Sys.Clipboard := actualSheet.WorkSheet.DrawingObjects[i].CopyPicture(2,-4147); Sys.Clipboard := actualSheet.Worksheet.Rows[14].Columns[1].CopyPicture(1,-4147); Log.Picture(Sys.Clipboard,'Catual Picture','hello'); except end; end; end; for i := 1 to row do //for each row begin for j := 1 to col do //for each column begin expectedStr := aqString.Trim(VarToStr(Evaluate('expectedSheet.Rows[i].Columns[j].' + CompareProperty))); actualStr := aqString.Trim(VarToStr(Evaluate('actualSheet.Rows[i].Columns[j].' + CompareProperty))); if VarType(regExpr) > 3 then //apply reg expr if it is defined, vartype(nil) = 3 begin //Replaces the text matching the expression with <ignore> expectedStr := regEx.Replace(expectedStr, '<ignore>'); actualStr := regEx.Replace(actualStr, '<ignore>'); end; // If expected string is 'SUN' (the super-user for Sun4 systems) AND this is a Sun5 test, change it to 'TTT' if (expectedstr = 'SUN') then expectedstr := GetUserIDs('0'); if not Utilities.SameText(expectedStr, actualStr) then begin Log.Error('Cell [' + VarToStr(i) + ',' + VarToStr(j) + '] : Expected - ' + VarToStr(expectedStr) + ' | Actual - ' + VarToStr(actualStr)); result := false; //exit; end; end; end; if result then Log.Message('Files are identical,click on Addtional Information tab for details... ',' Expected File : ' + #13#10 + ExpectedFileName + #13#10 + 'Actual File : '+#13#10+ ActualFileName) else Log.Error('Files are not identical,click on Addtional Information tab for details... ',' Expected File : ' + #13#10 + ExpectedFileName + #13#10 + 'Actual File : '+#13#10+ ActualFileName); finally //close files expectedFile.Close; actualFile.Close; end; end; function MapDirectoryFiles(var MissingExpFiles:string;var MissingActFiles:string;var CompareFilesDict;ExpectedDirPath:string,ActualDirPath:string,ExpFilePrefix:string,ActFilePrefix:string); var i,j,cnt,unmatchAt,recCnt:integer; dir,actFile,expFile,actUnmatchAt,expUnmatchAt,filesAdded:string; commonExp,commonAct:string; actFolder,expFolder, expFileCol,actFileCol:oleVariant; begin try actFolder := aqFileSystem.GetFolderInfo(ActualDirPath); actFileCol := actFolder.Files; expFolder := aqFileSystem.GetFolderInfo(ExpectedDirPath); expFileCol := expFolder.Files; if actFileCol.Count <> expFileCol.Count then Log.Warning('The total number of expected file count is not equal to actual file count,click on addtional information tab for details... ','Expected File Directory Path '+#13#10+ ExpectedDirPath + #13#10 + 'Expected Dir File Count ' + VarToStr(expFileCol.Count) + #13#10 + 'Actual File Directory Path '+#13#10+ ActualDirPath + #13#10 + 'Actual Dir File Count ' + VarToStr(actFileCol.Count) + #13#10 ); if expFileCol.Count > actFileCol.Count then cnt := expFileCol.Count else cnt := actFileCol.Count; {Get all the files that are present in exp dir but not in actual dir} recCnt := 0; for i := 0 to expFileCol.Count-1 do begin expFile := expFileCol.Item[i]; if not aqFileSystem.Exists(ActualDirPath + ActFilePrefix+StripFilePrefix(expFile.Name,ExpFilePrefix)) then begin MissingActFiles := expFile.Name+ ','+MissingActFiles; //Log.Message('MissingActFiles ' + MissingActFiles); end else begin commonAct := StripFilePrefix(expFile.Name,ExpFilePrefix) + ',' + commonAct; end; end; MissingActFiles := RemoveLastSeparator( MissingActFiles,','); //Log.Message(' Files which are present in Expected Directory but not in Actual Directory : ' + MissingActFiles); commonAct := RemoveLastSeparator( commonAct,','); //Log.Message('Common Files in Actual folder : ' + commonAct); {Get all the files that are present in act dir but not in exp dir} for i := 0 to actFileCol.Count-1 do begin actFile := actFileCol.Item[i]; if not aqFileSystem.Exists(ExpectedDirPath + ExpFilePrefix+StripFilePrefix(actFile.Name,ActFilePrefix)) then begin MissingExpFiles := actFile.Name+ ','+MissingExpFiles; //Log.Message('MissingExpFiles ' + MissingExpFiles); end else begin commonExp := StripFilePrefix(actFile.Name,ActFilePrefix) + ',' + commonExp; end; end; MissingExpFiles := RemoveLastSeparator( MissingExpFiles,','); //Log.Message(' Files which are present in Actual Directory but not in Expected Directory : ' + MissingExpFiles); commonExp := RemoveLastSeparator( commonExp,','); //Log.Message('Common Files in Expected folder : ' + commonExp); if aqstring.Trim(commonAct) = aqstring.Trim(commonExp) then begin aqString.ListSeparator := ','; for j := 0 to aqString.GetListLength(commonExp) -1 do begin CompareFilesDict.Add('Key'+VarToStr(j),aqString.GetListItem(commonExp,j)); end; end; except Log.Error('Error',exceptionmessage); end; end;
Related Content
Recent Discussions
Check PopupMenu exists
Solved2 hours agomfoster711- 24 hours agosimonaferrara
- 2 days agomacheshmeh