Ask a Question

Excel file automation

sarya
Frequent Contributor

Excel file automation

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


15 REPLIES 15

RE: Excel file automation

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.



Best regards,
Alexey

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
sarya
Frequent Contributor

RE: Excel file automation

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
Frequent Contributor

RE: Excel file automation

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;


;

}


RE: Excel file automation

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.



Best regards,
Alexey

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
sarya
Frequent Contributor

RE: Excel file automation

Thanks Allen. Will be wating for the solution.



Thanks,

Sumedha
sarya
Frequent Contributor

RE: Excel file automation

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

RE: Excel file automation

Hi Sumedha,


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



Best regards,
Alexey

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
sarya
Frequent Contributor

RE: Excel file automation

Hey Allen,



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



Thanks,

Sumedha

RE: Excel file automation

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.


Best regards,
Alexey

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
cancel
Showing results for 
Search instead for 
Did you mean: