Forum Discussion

sarya's avatar
sarya
Frequent Contributor
15 years ago

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

Sort By
Show More
  • sarya's avatar
    sarya
    Frequent Contributor
    15 years ago
    Hi Allen,



    Thanks so much for the scripts. I am not able to get the whole procedure of verification so if you can add some comments with the functions as in what all they are doing .



    createInfo(oExcel, fileName, aSheets)

    CompareInfo(aInfo1,aInfo2)



    Thanks,

    Sumedha
  • sarya's avatar
    sarya
    Frequent Contributor
    15 years ago

    Hi Allen,



    I compared two different files with different names .So the result of comparison was the message "sheet names are different" although it did not post any information about the content of the two files that is entirely different.



    Thanks,

    Sumedha

  • AlexeyKolosov's avatar
    AlexeyKolosov
    Staff
    15 years ago

    Hi Sumedha,


    The createInfo routine creates a dictionary which contains sheets, rows and cells from a file using the specified sheets. The compare routine compares dictionaries generated from XLS files.




    "sheet names are different"



    The message is posted if a sheet with the specified name cannot be found in the second file. For example, if you compare "Sheet 1" from file A and there is no sheet named "Sheet 1" in file B.

  • sarya's avatar
    sarya
    Frequent Contributor
    15 years ago

    Hey Allen,



    I compared two excel files with same sheet name but different values in one single column in both the sheets but it returns with the message as "The books are the same" although the worksheets have different values in the column.



    Thanks,

    Sumedha