cancel
Showing results for 
Search instead for 
Did you mean: 

Testing Excel Files

SOLVED
Highlighted
Occasional Contributor

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Leader

Re: Testing Excel Files

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								

 

 

Un sourire et ça repart

View solution in original post

6 REPLIES 6
Highlighted
Community Leader

Re: Testing Excel Files

If you dont tell us more about the way you alrerady do the comparison, we can't help you.

 

And if you look on the right side of your browser you'll find a Recommendations section and some seems to be for you ..

https://community.smartbear.com/t5/TestComplete-General-Discussions/compare-Excel-files/m-p/162945

https://community.smartbear.com/t5/TestComplete-General-Discussions/Verifying-data-in-Excel-file/m-p...

Un sourire et ça repart

Highlighted
Occasional Contributor

Re: Testing Excel Files

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

Highlighted
Community Leader

Re: Testing Excel Files

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.

 

Un sourire et ça repart

Highlighted
Occasional Contributor

Re: Testing Excel Files

the exclcompare (https://community.smartbear.com/t5/TestComplete-General-Discussions/compare-Excel-files/m-p/162946#M...) 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...) 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

Highlighted
Community Leader

Re: Testing Excel Files

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								

 

 

Un sourire et ça repart

View solution in original post

Highlighted
Occasional Contributor

Re: Testing Excel Files

thanks for the link,

 

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

i'll test it out right away,

 

thanks again

 

New Here?
Join us and watch the welcome video:
Top Kudoed Authors