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.

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)

{

{

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

}

}

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.

Hi Sumedha,

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

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.

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.