Verifying data in Excel file

[maumrm]

My tested application outputs the reports as excel files. These files have user and date information which does not lend them well to a file comparison. I'd like to be able to verify data contained in a range of rows and columns contained in selected sheets of the excel file.

This topic has been broached previously, and I believe Robert was on the right track in this post, however, it does not have enough detail for me. https://community.smartbear.com/t5/TestComplete-General-Discussions/How-to-test-the-content-of-an-Ex...

I am able to use the following script routine to get the data from select sheets of my outputted excel file. Can I now use this information that is contained in the Test Log to compare to baseline values? How is that done? How can I specify the rows and columns? 

var RecNo;
  
// Posts data to the log (helper routine)
function ProcessData()
{
  var Fldr, i;
  
  Fldr = Log.CreateFolder("Record: " + aqConvert.VarToStr(RecNo));
  Log.PushLogFolder(Fldr);
  
  for(i = 0; i < DDT.CurrentDriver.ColumnCount; i++)
    Log.Message(DDT.CurrentDriver.ColumnName(i) + ": " + aqConvert.VarToStr(DDT.CurrentDriver.Value(i)));
  
  Log.PopLogFolder();
  RecNo = RecNo + 1;
}
  
// Creates the driver (main routine)
function TestDriver()
{
  var Driver;
  
  // Creates the driver
  // If you connect to an Excel 2007 sheet, use the following method call:
  // Driver = DDT.ExcelDriver("C:\\MyFile.xlsx", "Sheet1", true);
  Driver = DDT.ExcelDriver("C:\\MyFile.xls", "Sheet1");
  
  // Iterates through records
  RecNo = 0;
  while (! Driver.EOF() )
  {
    ProcessData(); // Processes data
    Driver.Next(); // Goes to the next record
  }
  
  // Closes the driver
  DDT.CloseDriver(Driver.Name);
}

It seems that this is where the data driven loop comes in, but I don't know how to make use of that to confirm values. 

Apologies if this is a duplicate post in some way. I tried to post this question previously but was not able to find record of it, so I am posting again.

Thanks,

Mike

[tristaanogre]

Rather than outputting to the log, you could run while loops on both the DDT drivers.  Something like

while (!driver1.EOF) and (!driver2.EOF) {
if driver1.column1.value <> driver2.column1.value
    Log.Message('The values don't match")
else Log.Message("the values match")
driver1.Next
driver2.Next
}

This is just pseudo code, mind, as it seems you have a good handle on how to set up the drivers, etc.  But this is how I would do the row by row, column by column comparison of the two EXCEL files.

[maumrm]

Thank you Robert. I don't really have a handle on the code side. I stick to KeyWord Tests. However needing to verify data contained in only sections of an excel file is pushing me to needing more capability. The section of script I literally took from the following link and used it:

https://support.smartbear.com/testcomplete/docs/testing-with/data-driven/drivers.html

[BenoitB]

Row and Column start from 0

Each time you use the Driver.Next the row increment by one

On each row you acces the column i with DDT.CurrentDriver.Value(i)  (remember index start to 0).

The DDT method work without licence of Excel, just need the MSSoft access driver.

If you have Excel licence you can use another approach and access directly to the desired cell.

I made an example for you:

function readExcel(Ligne, Colonne) {
  let excelObject, excelWorkbookObj, excelMainSheet, sheetName;
  let result = "";
  try {
    sheetName                 = "your sheet name";
    excelObject               = Sys.OleObject("Excel.Application");
    if (excelObject == null)
      throw Error("Excel n'est pas installé !");
    excelObject.Visible       = false;
    excelObject.DisplayAlerts = false;
    excelWorkbookObj          = excelObject.Workbooks.Open("your path and name of excel file");
    excelMainSheet            = excelWorkbookObj.Sheets.item(sheetName);
    // Don't use .Text because it's displayed value nor .Value because it can be affected by cell formatn .Value2 is the underlying value, so the only choice.
    result = excelMainSheet.Cells.Item(Ligne, Colonne).Value2; 
  }
  catch(e) {
    Log.Error("Erreur durant la lecture Excel", e.message);
  }
  finally {
    if (excelObject != null) {
      excelObject.Application.ActiveWorkbook.Close;
      excelObject.Quit;
      excelObject      = null;
      excelWorkbookObj = null;
      excelMainSheet   = null;
      // Use your own method to kill process to avoid remaining EXCEL process in memory
      system.killProcess("EXCEL");
    }
  }
return result;
}

[tristaanogre · Accepted solution]

Gotcha... OK, then... rewriting your code a bit.  Note that I'm removing the ProcessData function as all that is doing is going through the driver an writnig to the log.  

function TestDriver()
{
  var Driver;
  
  // Creates the driver
  // If you connect to an Excel 2007 sheet, use the following method call:
  // Driver = DDT.ExcelDriver("C:\\MyFile.xlsx", "Sheet1", true);
  TestDriver = DDT.ExcelDriver("C:\\MyFile.xls", "Sheet1");
  BaseDriver = DDT.ExcelDriver("C:\\BaselineFile.xls", "Sheet1"):

  
  // Iterates through records
  RecNo = 0;
  while (!TestDriver.EOF() && !BaseDriver.EOF())
  {
    for (var i = 0, i < TestDriver.ColumnCount, i++) {
       if (TestDriver.Value(i) <> BaseDriver.Value(i)) {
       Log.Warning("Test file column " + TestDriver.ColumnName(i) + " does not match baseline")
       }
   }
    TestDriver.Next(); // Goes to the next record
    BaseDriver.Next();
  }
  
  // Closes the driver
  DDT.CloseDriver(TestDriver.Name);
  DDT.CloseDriver(BaseDriver.Name);
}

In side the for loop is where you do all your comparisons, etc.  I only included a very basic log warning but you could detail it out more with the values of test versus the values of baseline, etc.  Generally, this is what you're looking for... looping through BOTH files in parallel, one row at a time, comparing column values.

[maumrm]

Thanks Robert, I am amazed that this is so few lines. I do get "Unexpected token <" on line 17 "for (var i = 0, i < TestDriver.ColumnCount, i++) {"

Why is that? If I change that to an "=" sign then I get "Unexpected token ++" on the same line.

[BenoitB]

Equality is done by ==,  single = is assignation.

[tristaanogre]

Ah... replace the "," with ";" in the for statement.  My bad... rapid typing without thinking.

[BenoitB]

... and my bad .. bad reading  ...

 

[tristaanogre]

Corrected for loop:

   for (var i = 0; i < TestDriver.ColumnCount; i++) {
       if (TestDriver.Value(i) <> BaseDriver.Value(i)) {
       Log.Warning("Test file column " + TestDriver.ColumnName(i) + " does not match baseline")
       }