Forum Discussion

maumrm's avatar
maumrm
Contributor
5 years ago

Verifying data in Excel file

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-Excel-spreadsheet/td-p/140671

 

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's avatar
    tristaanogre
    5 years ago

    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.

  • BenoitB's avatar
    BenoitB
    Community Hero
    5 years ago

    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's avatar
    tristaanogre
    Esteemed Contributor
    5 years ago

    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.

      • tristaanogre's avatar
        tristaanogre
        Esteemed Contributor
        5 years ago

        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.