Forum Discussion

synchi64's avatar
synchi64
Occasional Contributor
5 years ago

Testing Excel Files

Greetings,

 

I need help to figure out a way to test excel files, essentially comparing excel files in two different folders,

I have a functioning code, however it takes way too long and I need to shorten the time it takes to do such a task,

 

can anyone point me to some docs or functioning scripts that would help me in my research?

 

thanks in advance

  • BenoitB's avatar
    BenoitB
    5 years ago

    ExcelCompare works well.and it's the fastest.

     

    How to make it works ?

     

    For excelCompare you must download the script extension package here (down of the page):

    https://support.smartbear.com/viewarticle/42199

     

    You copy file here (assuming you have standard installation):

    C:\Program Files (x86)\SmartBear\TestComplete 14\Bin\Extensions\ScriptExtensions

     

    In TestComplete go to File -> Install Script Extension -> click on Reload of the dialog and normally you should have the Excel compare available (if not checked, check it).

     

    For comparing specific sheet you have an article here:

    https://support.smartbear.com/viewarticle/9046/

     

    This code

    function TestCompare() {
  let file1 = "c:\\temp\\lsaexpert.xlsx";
  let file2 = "c:\\temp\\lsaexpertt2.xlsx";
  let resultFile = "c:\\temp\\result.xlsx";
  Log.Message(objectExcel.ExcelCompare(file1, file2, resultFile));
}

    Give a resultFile.xlsx containing that:

    This is a result file which highlights the differences between the files ...											
File 1 : c:\temp\lsaexpert.xlsx											
File 2 : c:\temp\lsaexpertt2.xlsx											
=================================================================================================================											
Sheet Name	Cell	Data in File 1	Data in File 2								
lsaexpert	B3	PULSAT	AAA								
lsaexpert	B7	PRO & CIE	AAAAAAAAA								
lsaexpert	D2	"74 rue Jean Jaures
59410 Anzin
SOCIÉTÉ EXPLOITANTE
REMY DISTRIBUTION ELECTROMENAGER
N° SIRET : 438 567 901 00030
Tél : 03.27.46.12.12
Site internet : www.anzin.extra.fr
"	aaaa								
lsaexpert	D7	"3 rue du Général de Gaulle
59225 Clary
SOCIÉTÉ EXPLOITANTE
SARL TAISNE PERE ET FILS
N° SIRET : 539 772 483 00019
Tél : 03.27.85.53.85
Fax : 03.27.85.17.53
Site internet : procie-clary.com
"	zzzzzz								
lsaexpert	E2	"Gérant : M. Gérald THURU
"	aaa

     

     

    • synchi64's avatar
      synchi64
      Occasional Contributor
      5 years ago

      currently, my code checks each cells of each sheets of each excel files,

       

      saves the data of the row, collumn, sheet and it's value

       

      and then compare the values,

       

      code:

      '''

      function ExcelDiff()
      {
      var ListFilesRef = [];
      var ListFilesToTest = [];
      var endwitherror = false;

      FilesRef = aqFileSystem.FindFiles([path], "*");
      if (FilesRef != null)
      while (FilesRef .HasNext())
      {
      aFile = FilesRef .Next();
      Log.Message(aFile.Name);
      ListFilesRef.push(aFile.Name);
      }

      FilesToTest = aqFileSystem.FindFiles([path], "*");
      if (FilesToTest != null)
      while (FilesToTest.HasNext())
      {
      aFile = FilesToTest.Next();
      Log.Message(aFile.Name);
      FilesToTest.push(aFile.Name);
      }

      for(var i = 0; i < ListFilesRef.length; i++)
      {
      var excelRef = ReadDataFromExcel([path]+ListFilesRef[i]);
      var excelToTest = ReadDataFromExcel([path]+ListFilesToTest[i]);

      for(var k = 0; k < excel19[0].length; k++)
      {
      if(excelRef[0][k] != excelToTest[0][k])
      {
      Log.Warning("differences detected", "differences detected between "+ListFilesRef[i]+" (Ref) and "+ListFilesToTest[i]+" (ToTest) on the sheet: "+excelRef[1][k]+" cell: "+excelRef[3][k]+excelRef[2][k]);
      endwitherror = true;
      }
      }
      }

      if(endwitherror == true)
      Log.Error("differences detected");
      }

      function ReadDataFromExcel(pathtoexcel)
      {
      var contentfile = [];
      var sheet = [];
      var row = [];
      var collumn = [];
      var total = [];

      var Excel = Sys.OleObject("Excel.Application");
      Excel.Workbooks.Open(pathtoexcel);
      var SheetCount = Excel.Sheets.Count;

      for(var k = 1; k<= SheetCount; k++)
      {
      Excel.Worksheets.Item(Excel.Sheets.Item(k).name).Activate();
      var RowCount = Excel.ActiveSheet.UsedRange.Rows.Count;
      var ColumnCount = Excel.ActiveSheet.UsedRange.Columns.Count;
      for (var i = 1; i <= RowCount; i++)
      {
      for (var j = 1; j <= ColumnCount; j++)
      {
      contentfile.push(VarToString(Excel.Cells.Item[i,j]));
      sheet.push(Excel.Sheets.Item(k).name);
      row.push(i);
      collumn.push(j);
      }
      }
      }

      total.push(contentfile);
      total.push(sheet);
      total.push(row);
      total.push(collumn);

      Excel.Quit();
      return total;
      }

      '''

       

      and i have come across those forums, however, I was unable to apply those solution,

      that's why i'm here

      • BenoitB's avatar
        BenoitB
        Community Hero
        5 years ago

        Why you cannot apply solution (especially object comparison) ?

         

        What i see is that every Excell operation is quite slow, no matter of the method used.

        If your excels are quite big, it's worse.

        On cell values comparison i've compared DDT and direct call of Excel, DDT was faster.