Forum Discussion

kandy1984's avatar
kandy1984
Contributor
11 years ago

Trouble comparing .xlsx file

Hi,

I am using the code below to compare 2 .xlsx files.




Sub Main


    xlfile1 = "C:\Users\lac001\Desktop\aaa.xlsx"


    xlfile2 = "C:\Users\lac001\Desktop\bbb.xlsx"


    resfile = "C:\Users\lac001\Desktop\c.xlsx"


    Log.Message objectExcel.ExcelCompare(xlfile1, xlfile2, resfile)


    


End Sub





I found a problem when comparing 2 subsequence numbers eg, if file1 has number 4 and file2 has number 5 then this script does not gives any errors. if i change the number in file 1 to 3, then it returns as mismatch. i have attached the .xlsx files with number 6  and in another file number 7 and when i compare these 2 files, the script returns as they are the same.



I read this article (http://support.smartbear.com/viewarticle/42199/?_ga=1.74915985.2000112212.1395883530) and installed the script extension.



Thanks for helping.



Sudha



  • TanyaYatskovska's avatar
    TanyaYatskovska
    11 years ago
    Hi Sudha,

     


    I observe the same behavior on my computer. This sample was created by our TestComplete Customer Care Team. Could you please contact them to investigate what is going wrong?


     

  • kandy1984's avatar
    kandy1984
    Contributor
    11 years ago
    Hi,



    I have got a solution from the TC team. 



    It looks like there's a bug in the extension's code. Here's how you can fix it:

    1. Rename the ExcelCompare.tcx file to ExcelCompare.zip.

    2. Extract the archive to a local directory.

    3. You will find 2 files there - Description.xml and Script.vbs

    4. Open Script.vbs with any text editor.

    5. Navigate to line 69 (If Abs(cf1-cf2) > 1 Then)

    6. Modify the condition to make this line look like this:

    If Abs(cf1-cf2) >= 1 Then

    7. Pack the two files back into a zip archive, for example, ExcelCompare.zip.

    8. Rename its extension to tcx and add the modified extension to TC.



    Im not sure if those guys have fixed the code which is in the website now. But people who had already downloaded this extention can modify this to make it work.



    Thanks.



    Sudha
  • TanyaYatskovska's avatar
    TanyaYatskovska
    SmartBear Alumni (Retired)
    11 years ago
    Hi Sudha,

     


    I observe the same behavior on my computer. This sample was created by our TestComplete Customer Care Team. Could you please contact them to investigate what is going wrong?


     

  • kandy1984's avatar
    kandy1984
    Contributor
    11 years ago
    Thanks Tanya. I have notified them and will keep this post updated.
  • Bharadwaj's avatar
    Bharadwaj
    Contributor
    10 years ago

    Hi ,

    You can use this function 

    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;