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...
  • 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

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.

 

  • synchi64's avatar
    synchi64
    Occasional Contributor
    5 years ago

    the exclcompare (https://community.smartbear.com/t5/TestComplete-General-Discussions/compare-Excel-files/m-p/162946#M30161) i just wasn't able to make it work, I used the exact code refered in the forum, and it didn't worked, i tried to find docs on that specific method and i didn't found anything that helped me make it work,

     

    as for the DDT (https://community.smartbear.com/t5/TestComplete-General-Discussions/Verifying-data-in-Excel-file/m-p/199322) i coded somthing with it but had a hard time switching sheets when not given the name of the sheet, since i don't know all the names of all the sheets i have to check, i couldn't use this method beacause of that,

     

    and yeah, it's always going to be long, i'm just trying to explore ideas that might help me make it a bit faster,

     

    have you used the methods you have linked to? perhaps you might help me with implementing those methods

    • BenoitB's avatar
      BenoitB
      Community Hero
      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

        thanks for the link,

         

        I really didn't found any good docs on that,

        i'll test it out right away,

         

        thanks again