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,

Here is the comparison script:

function Main()

{

  var oExcel = Sys.OleObject("Excel.Application");

  var aSheets = Sys.OleObject("Scripting.Dictionary");

  //var vSheet = "Sheet1";

  //aSheets.Add(vSheet, vSheet); 

  

  var fileName = "C:\\Temp\\book1.xls";

  var aInfo1 = createInfo(oExcel, fileName, aSheets);

  var fileName = "C:\\Temp\\book1.xls";

  var aInfo2 = createInfo(oExcel, fileName, aSheets);

  var oResult = compare(aInfo1, aInfo2);

  if (oResult.length > 0)

  {

    Log.Warning(oResult);

  }

  else

  {

    Log.Message("The books are the same."); 

  }

  oExcel.Quit();

}

function createInfo(oExcel, fileName, aSheets)

{

  oExcel.Workbooks.Close();

  oExcel.Workbooks.Open(fileName);

  var oSheets = oExcel.Worksheets;

  sheetCount = oSheets.Count;

  if (aSheets.Count == 0)

  {

    for (var sheetId = 1; sheetId < sheetCount + 1; sheetId++)

    {

      var oSheet = oSheets.Item(sheetId);

      aSheets.Add(oSheet.Name, oSheet.Name);

    }

  }

  var vSheets = Sys.OleObject("Scripting.Dictionary");

  for (var sheetId = 1; sheetId < sheetCount + 1; sheetId++)

  {

    var oSheet = oSheets.Item(sheetId);

    if (aSheets.Item(oSheet.Name) == null)

    {

      continue;

    }

    var vSheet = Sys.OleObject("Scripting.Dictionary");

    var rowCount = oSheet.UsedRange.Rows.Count;

    for (var rowId = 1; rowId < rowCount + 1; rowId++)

    {

      var rowObj = oSheet.Rows.Item(rowId);

      var vRow = Sys.OleObject("Scripting.Dictionary");

      var cellCount = rowObj.Cells.Count;

      for (var cellId = 1; cellId < cellCount + 1; cellId++)

      {

        var cellObj = rowObj.Cells.Item(cellId);

        vRow.Add(cellObj.Column, cellObj.Text);

      }

      vSheet.Add(rowId, vRow);

    }

    vSheets.Add(oSheet.Name, vSheet);

  }

  return vSheets;

}

function compare(aInfo1, aInfo2)

{

  var strCompare = "";

  if (aInfo1.Count != aInfo1.Count)

  {

    strCompare = strCompare + "Wrong sheet count!"

    return strCompare;

  }

  var vKeys = aInfo1.Keys().toArray();

  var sheetCount = aInfo1.Count;

  for (var sheetId = 0; sheetId < sheetCount; sheetId++)

  {

    if (!aInfo2.Exists(vKeys[sheetId]))

    {

      strCompare = strCompare + "Sheet names are different!";

      return strCompare;

    }

    var oSheet1 = aInfo1.Item(vKeys[sheetId]);

    var oSheet2 = aInfo2.Item(vKeys[sheetId]);

    var rowCount1 = oSheet1.Count;

    var rowCount2 = oSheet2.Count;

    if (rowCount1 != rowCount2)

    {

      strCompare = strCompare + "Wrong used row count in the '" + vKeys[sheetId] + "' sheet."

      return strCompare;

    }

    for (var rowId = 0; rowId < rowCount1; rowId++)

    {

      var rowObj1Key = oSheet1.Keys().toArray()[rowId];

      if (!oSheet2.Exists(rowObj1Key))

      {

        strCompare = strCompare + "The '" + rowObj1Key + "' row key was not found.";

        return strCompare;

      }

      var oRow1 = oSheet1.Item(rowObj1Key);

      var oRow2 = oSheet2.Item(rowObj1Key);

      var cellCount = oRow1.Count;

      for (var cellId = 0; cellId < cellCount; cellId++)

      {

        var celObj1Key = oRow1.Keys().toArray()[cellId]

        if (!oRow2.Exists(celObj1Key))

        {

          strCompare = strCompare + "The '" + celObj1Key + "' cell was not found." + "\r\n";

          return strCompare;

        }

        var oCell1 = oRow1.Item(celObj1Key);

        var oCell2 = oRow2.Item(celObj1Key);

        if (!SameText(oCell1, oCell2))

        {

          strCompare = strCompare + "The "  + vKeys[sheetId] + ":\r\n The [" + rowId + ", " + cellId + "] cell was changed." + "\r\n" + "  The '" + oCell1 + "' value." + "\r\n" + "  The '" + oCell2 + "' value." + "\r\n";

        }

      }

    }

  }

  return strCompare;

}

The script will be added to the How To section later.

[sarya]

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]

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]

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]

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

[AlexeyKolosov]

Hi Sumedha,

Please post the two files here or send them to us via our Contact Support form (http://www.automatedqa.com/support/message).