cancel
Showing results for 
Search instead for 
Did you mean: 

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

Highlighted
Community Manager

[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:Star_Gold.pngStar_Gold.pngStar_gray.png

 

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.


Sonya Mihaljova
Community and Education Specialist

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
Tags (1)
4 REPLIES 4
Highlighted
Community Manager

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

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😊


Sonya Mihaljova
Community and Education Specialist

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
Highlighted
Community Manager

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

It looks like with minor changes, the following script posted as a solution here will be the answer for this task, too:

 

https://community.smartbear.com/t5/TestComplete-General-Discussions/TechCorner-Challenge-5-Comparing...


Sonya Mihaljova
Community and Education Specialist

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
Highlighted
Community Hero

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

*cracks knuckles* OK... I've been itching for a challenge.  Stay tuned...


Robert Martin
[Hall of Fame]
Please consider giving a Kudo if I write good stuff
----

Why automate?  I do automated testing because there's only so much a human being can do and remain healthy.  Sleep is a requirement.  So, while people sleep, automation that I create does what I've described above in order to make sure that nothing gets past the final defense of the testing group.
I love good food, good books, good friends, and good fun.

Mysterious Gremlin Master
Vegas Thrill Rider
Extensions available
Community Manager

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

@tristaanogre😁

That's the spirit 💪 !


Sonya Mihaljova
Community and Education Specialist

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
New Here?
Join us and watch the welcome video:
Announcements
Top Kudoed Authors