Ask a Question

Excel file automation

RE: Excel file automation

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.


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,



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

RE: Excel file automation

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

RE: Excel file automation

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.


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,



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

RE: Excel file automation

Hi Sumedha,


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


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: