Forum Discussion

sonya_m's avatar
sonya_m
SmartBear Alumni (Retired)
5 years ago

[TechCorner Challenge #6] - Compare HTML table with Excel file and correct data in Excel file

Hi TestComplete Community!

 

Welcome to our weekly challenge where anyone can put their tool knowledge to the test! By completing the task, you will practice your TestComplete skills, learn new things about working with the product, and get into the TechCorner Leaderboard!

 

Check out this week’s task:

 

Since working with HTML tables and Excel files is quite common when working with TestComplete, here’s one more task connected to this topic.

 

Task: Create a script to compare an HTML table with an Excel file and correct the data in the Excel file.

Difficulty:

 

So, when the script encounters an HTML table cell, the value of which is different from the value of the corresponding Excel table cell, the Excel cell value should be changed to what’s actually shown in the table.

Resources:

- The HMTL table can be found here - Web Orders table.

- The Excel file is attached below.

 

Steps to follow to fulfill this task:

1. Parse the Orders table.
2. Save the table to the variable.
3. Working with Microsoft Excel Files - here you can choose the right way to extract data from the Excel file (TestComplete 14.50 users, check out the new Excel support - it does not require Excel to be installed on your machine).
4. Using a loop in the script, compare the table variable and Excel content cell-by-cell.
5. If the value in the Excel cell is different from the HTML table cell, it should be replaced with what’s shown in the table.

 

Good luck!

 

P.S. You can find the TechCorner Leaderboard and the rules of how to participate in the TechCorner Challenge by this link.

Scripting
TechCornerChallenge
  • sonya_m's avatar
    sonya_m
    SmartBear Alumni (Retired)
    5 years ago

    Here's a tip for all willing to try completing this challenge.

    This is the script to start with that you can use an example to parse the Orders table and save it to a variable:

     

     

    function Test()
{
  var table = Sys.Browser("chrome").Page("https://www.w3schools.com/html/html_tables.asp").Panel("belowtopnav").Panel(0).Panel("main").Panel(1).Panel(0).Table("customers");  
if(!Project.Variables.VariableExists("MyTable"))
    Project.Variables.AddVariable("MyTable", "Table");  ParseTreeTable(Project.Variables.VariableByName("MyTable"), table);  PostTableToLog(Project.Variables.VariableByName("MyTable"));
}function ParseTreeTable(TableVariable, ATable)
{
  TableVariable.RowCount = ATable.RowCount;
  for (var k = 0; k < ATable.ColumnCount(0); k++)
      TableVariable.AddColumn();  for (var i = 0; i < ATable.RowCount; i++)
  {
    // Goes through cells   
    for (var j = 0; j < ATable.ColumnCount(i); j++)
    {    
      // Obtains a cell
      var cell = ATable.Cell(i, j);      TableVariable.$set("Item",j,i, cell.innerText);
    }
  }
}function PostTableToLog(TableVariable)
{
  Log.AppendFolder("Table");
  for (var i = 0; i < TableVariable.RowCount; i++)  {
    Log.AppendFolder("Row " + i);    // Goes through cells
    for (var j = 0; j < TableVariable.ColumnCount; j++)
    {
      // Posts the cell's text to the log
      Log.Message("Cell " + j + ": " + TableVariable.Item(j,i));
    }
    Log.PopLogFolder();
  }  Log.PopLogFolder();
}

     

     

    Now, you will need to extract data from the Excel table, compare values, and replace values in the Excel file with the correct ones!

    Good luck😊