How to Read and compare images in two excel files

[Bharadwaj]

Hi All,

I  have two excel files in whch I have tabular data as well as pictures in it. I can easily read the tabulat data and compare them but I am not aware of how to read the pictures in them and comparing them .Does testcomplete support such a thing or is there any other way of doing it like reading the picture from the excel file placing it on a cliupboard and performing an image comparision,something like that. Please give me some suggestions on it. I am attaching the sample Excel file image(its saved in the .xlsx which is a Excel workbook format.) Also I am uisng the following code for reading the data from the excel and comparing the data. Any ideas or suggestions please welcome.. 

Best regards,

Bharadwaj Pappu

function CompareExcel(ExpectedFileName, ActualFileName, RegExpr: OleVariant = nil, CompareProperty: string = 'Text'): boolean;
var 
excel,
expectedFile, actualFile,
expectedSheet, actualSheet,
regEx: OleVariant;
s, expectedStr, actualStr: string;
col, row, i, j: integer;
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;
//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;

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;

[Bharadwaj]

Guys can some one pelase help me in this issue. Thanks you.

Best regards,

Bharadwaj Pappu.

[joseph_michaud · Accepted solution]

Use the Excel OLE Object to provide the pictures:

function LogExcelPicture(num)
{
  var Excel = Sys.OleObject("Excel.Application");
  
  Excel.Workbooks.Open("C:\\Book1.xls");
  Excel.WorkSheets(1).Shapes(num).Copy();
  Log.Picture(Sys.Clipboard, "clipboard picture");
  Excel.Quit();
}

[Bharadwaj]

Thank you Joseph.I will try this one out.

Best regards,

Bharadwaj Pappu.

[TMUGHILENDRAN]

Can you give me the same code in VBScript so i can do image compare.

I was trying to do but got stuck with i am converting the code syntax.

function LogExcelPicture(num)
{
  var Excel = Sys.OleObject("Excel.Application");
  
  Excel.Workbooks.Open("C:\\Book1.xls");
  Excel.WorkSheets(1).Shapes(num).Copy();
  Log.Picture(Sys.Clipboard, "clipboard picture");
  Excel.Quit();
}

[AlexKaras]

Hi,

I believe it will be something like that (not tested, just converted code):

Function LogExcelPicture(ByVal num)
  Dim Excel

  Set Excel = Sys.OleObject("Excel.Application")
  
  Call Excel.Workbooks.Open("C:\Book1.xls")
  Call Excel.WorkSheets(1).Shapes(num).Copy()
  Call Log.Picture(Sys.Clipboard, "clipboard picture")
  Call Excel.Quit()
End Function