Ask a Question

How to Read and compare images in two excel files

SOLVED
Bharadwaj
Contributor

How to Read and compare images in two excel files

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;

Excel_File_Image.png

5 REPLIES 5
Bharadwaj
Contributor

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

 

Best regards,

Bharadwaj Pappu.

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();
}

 

-----
Joseph

Thank you Joseph.I will try this one out.

 

Best regards,

Bharadwaj Pappu.

TMUGHILENDRAN
Occasional Contributor

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();
}

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
Regards,
  /Alex [Community Hero]
____
[Community Heroes] are not employed by SmartBear Software but
are just volunteers who have some experience with the tools by SmartBear Software
and a desire to help others. Posts made by [Community Heroes]
may differ from the official policies of SmartBear Software and should be treated
as the own private opinion of their authors and under no circumstances as an
official answer from SmartBear Software.
The [Community Hero] signature is used with permission by SmartBear Software.
https://community.smartbear.com/t5/custom/page/page-id/hall-of-fame
================================
cancel
Showing results for 
Search instead for 
Did you mean: