Excel file automation

[sarya]

Hi ,



I have to verify an excel file ,so can I verify individual cells in the spreadsheet .If cell comparison is possible, what is the kind of  code that needs to be there.Currently my code compared one output file to another sample fiel but if it does not find anything in the output file,it is not able to point out which value is missing. Can the verification be more elaborate?

function ExcelVerify(DataBeginsFromRow,path) {

ExcelVerify(DataBeginsFromRow,path) {

var sheets = ["100", "200", "300"];

for (var i = 0; i <= sheets.length - 1; i++) {

var driver = DDT.ExcelDriver(path, sheets);

var counter = 0;

while (! driver.EOF()) {

if (counter + 1 > DataBeginsFromRow)

{

for (var j = 0; j <= driver.ColumnCount - 1; j++)

{

Log.Message(driver.Value(j))

}

}

driver.Next();

counter++;

}

}

}

function ExcelCompare(file1,file2,sheet)

ExcelCompare(file1,file2,sheet)

{

var TestRecordSet = DDT.ExcelDriver(file1,sheet);

TestRecordSet = .ExcelDriver(file1,sheet);

var BaseRecordSet = DDT.ExcelDriver(file2,sheet);

BaseRecordSet = .ExcelDriver(file2,sheet);

while (! TestRecordSet.EOF() && ! BaseRecordSet.EOF())

(! TestRecordSet.EOF() && ! BaseRecordSet.EOF())

{

for (i = 0; i < DDT.CurrentDriver.ColumnCount; i++)

{

if (!(TestRecordSet.Value(i)== BaseRecordSet.Value(i)))

Log.Warning("The value in column" + TestRecordSet.ColumnName(i) + "does not match the expected value");

}

TestRecordSet.Next(); // Goes to the next record

BaseRecordSet.Next(); // Goes to the next record

}

return true;

;

}



Thanks,

Sumedha

[AlexeyKolosov]

Hi Sumedha,

If an exception is thrown when trying to address a non-existent column, you can try to catch the exception with a try..catch block and post the existing value to the test log. In all other cases, the code you use should be enough to compare an existing value with an empty value or null.

[sarya]

Hi  Allen,



I included the try,catch block in the statement to verify non-existing values .Can we compare each cell to cell value and post it in the log which values do not match because the current code that I have does not do that. It justs returns as false ?



Thanks,

Sumedha

[sarya]

Hey Allen,



I have one more doubt.The code below to compare two excel files compares only a single worksheet.If we need to verify two excel files each with 3-4 worksheets that both of them have same worksheets ,then how to loop around to verify each worksheet in one file against each in another file. Can you please suggest me how to write a loop for multiple worksheet verification in jscript.



Thanks,

Sumedha



function ExcelCompare(file1,file2,sheet)

ExcelCompare(file1,file2,sheet)

{

var TestRecordSet = DDT.ExcelDriver(file1,sheet);

TestRecordSet = .ExcelDriver(file1,sheet);

var BaseRecordSet = DDT.ExcelDriver(file2,sheet);

BaseRecordSet = .ExcelDriver(file2,sheet);

while (! TestRecordSet.EOF() && ! BaseRecordSet.EOF())

(! TestRecordSet.EOF() && ! BaseRecordSet.EOF())

{

for (i = 0; i < DDT.CurrentDriver.ColumnCount; i++)

{

if (!(TestRecordSet.Value(i)== BaseRecordSet.Value(i)))

Log.Warning("The value in column" + TestRecordSet.ColumnName(i) + "does not match the expected value");

}

TestRecordSet.Next(); // Goes to the next record

BaseRecordSet.Next(); // Goes to the next record

}

return true;

;

}

[AlexeyKolosov]

Hi Sumedha,

We will try to create a sample script for you and post it in the How To section of our support portal. When the script is ready, I will let you know.

[sarya]

Thanks Allen. Will be wating for the solution.



Thanks,

Sumedha

[sarya]

Hey Allen,



The script mentioned above is the one that compares the columns to columns and it publishes an error when there is some difference in value but it does not compare cell to cell and publishes the cell value where the difference occurs.Can you please tell me how to do that also in my script ?



Thanks,

Sumedha

[AlexeyKolosov]

Hi Sumedha,

We are working on a script which does this. When the script is ready, I will let you know.

[sarya]

Hey Allen,



Was wondering if we have the script for excel verification  in "How to" section.



Thanks,

Sumedha

[AlexeyKolosov]

Hi Sumedha,

No, we are still working on the script - creating free samples is a low-priority task for us. I will post an update in this thread when the script is ready as I already mentioned earlier.