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;
