cancel
Showing results for 
Search instead for 
Did you mean: 

[TechCorner Challenge #5] Comparing content of HTML table with Excel file data

SOLVED
Community Manager

[TechCorner Challenge #5] Comparing content of HTML table with Excel file data

Hi TestComplete Community!

 

Ready for a challenge? Here it is! Learn new things about working with the product, and get into the TechCorner Leaderboard!

 

Task: Create a script that will compare the values of an HTML table and an Excel file cell by cell and log the difference between them.

Difficulty: Star_Gold.pngStar_gray.pngStar_gray.png

 

For the task implementation, you can use the Web Orders table and the attached Excel file. Here is an example of TestComplete’s Test Log demonstrating the difference between the sources:

 

test-log.png

 

Solve the task and post your scripts here. We are looking forward to seeing your scripts!

 

Helpful resources:

You can loop through the table as described in this article: Parsing HTML Tables.

As for working with Excel, TestComplete offers several ways to extract the data - choose the one you want to use in Working with Microsoft Excel Files. If you are using the latest TestComplete version (14.50), you can utilize the new Excel support - it does not even require Excel to be installed on the machine!

 

Note: the HTML table size and the Excel table size might be different. So, it’s a good idea to throw an error if the dimensions are not the same.

 

Good luck!

 

P.S. You can find the TechCorner Leaderboard and rules of how to participate in the 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)
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Manager

Comparing content of HTML table with Excel file data

Task: Create a script that will compare the values of an HTML table and an Excel file cell by cell and log the difference between them.

 

This is a solution created for [TechCorner Challenge #5]

 

 

Hi All,

 

my 2 cents to this challenge 🙂

 

I've attached the Excel file, I'm working in the code. The code is written in JavaScript and it uses the new Excel object (which is really great!). Here is what you can see in the test log after executing the script:

TanyaGorbunova_0-1597837406097.png

 

//JavaScript
function CompareTables()
{
  //obtain the path to the web table
  var table = Sys.Browser("chrome").Page("http://secure.smartbearsoftware.com/samples/testcomplete12/weborders/").Form("aspnetForm").Table(0).Cell(0, 1).Panel(1).Panel(2).Table("orderGrid");
  
  //let's save a web table to the TestComplete table  
  if(!Project.Variables.VariableExists("MyTable"))
    Project.Variables.AddVariable("MyTable", "Table");  
  SaveTreeTableToVariable(Project.Variables.VariableByName("MyTable"), table);  

  //obtain the Excel file
  var excelFile = Excel.Open("C:\\Book1.xlsx "); //specify the path to your excel file here
  var excelSheet = excelFile.SheetByTitle("Sheet1");
  
  //Compare the web table with the Excel table
  var compResults = "";
  compResults = CompareTableWithExcel(Project.Variables.VariableByName("MyTable"),excelSheet)
  
  //Post comparison results to the test log
  if (compResults !="")
     Log.Warning("The tables are different", compResults);
  else
    Log.Message("The tables are the same");
  
}

function SaveTreeTableToVariable(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 and save it to a variable
      var cell = ATable.Cell(i, j);      
      TableVariable.$set("Item",j,i, cell.innerText);
    }
  }
}

function CompareTableWithExcel(TableVariable, excelSheet)
{
  var compResults = "";
  //iterate through all cells
  for (var i = 0; i < TableVariable.RowCount; i++)  {
    for (var j = 0; j < TableVariable.ColumnCount; j++)
    {
      //compare values
      if (excelSheet.Cell(j+1, i+1).Value !=  TableVariable.Item(j,i))
        compResults += "Row " + i + ", Column " + j + " -- Expected: " + TableVariable.Item(j,i) + ", Found: " + excelSheet.Cell(j+1, i+1).Value + "\r\n";
    }
  }  
  return compResults;
}

 

 

---------
Tanya Gorbunova
SmartBear Community Manager

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

View solution in original post

Tags (1)
3 REPLIES 3
Highlighted
Community Manager

Re: [TechCorner Challenge #5] Comparing content of HTML table with Excel file data

Who wants to try solving this one?🙂

 

@m_essaid @tristaanogre @anupamchampati @BenoitB 


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 Leader

Re: [TechCorner Challenge #5] Comparing content of HTML table with Excel file data

Sorry I don't have Web Module license.

Highlighted
Community Manager

Comparing content of HTML table with Excel file data

Task: Create a script that will compare the values of an HTML table and an Excel file cell by cell and log the difference between them.

 

This is a solution created for [TechCorner Challenge #5]

 

 

Hi All,

 

my 2 cents to this challenge 🙂

 

I've attached the Excel file, I'm working in the code. The code is written in JavaScript and it uses the new Excel object (which is really great!). Here is what you can see in the test log after executing the script:

TanyaGorbunova_0-1597837406097.png

 

//JavaScript
function CompareTables()
{
  //obtain the path to the web table
  var table = Sys.Browser("chrome").Page("http://secure.smartbearsoftware.com/samples/testcomplete12/weborders/").Form("aspnetForm").Table(0).Cell(0, 1).Panel(1).Panel(2).Table("orderGrid");
  
  //let's save a web table to the TestComplete table  
  if(!Project.Variables.VariableExists("MyTable"))
    Project.Variables.AddVariable("MyTable", "Table");  
  SaveTreeTableToVariable(Project.Variables.VariableByName("MyTable"), table);  

  //obtain the Excel file
  var excelFile = Excel.Open("C:\\Book1.xlsx "); //specify the path to your excel file here
  var excelSheet = excelFile.SheetByTitle("Sheet1");
  
  //Compare the web table with the Excel table
  var compResults = "";
  compResults = CompareTableWithExcel(Project.Variables.VariableByName("MyTable"),excelSheet)
  
  //Post comparison results to the test log
  if (compResults !="")
     Log.Warning("The tables are different", compResults);
  else
    Log.Message("The tables are the same");
  
}

function SaveTreeTableToVariable(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 and save it to a variable
      var cell = ATable.Cell(i, j);      
      TableVariable.$set("Item",j,i, cell.innerText);
    }
  }
}

function CompareTableWithExcel(TableVariable, excelSheet)
{
  var compResults = "";
  //iterate through all cells
  for (var i = 0; i < TableVariable.RowCount; i++)  {
    for (var j = 0; j < TableVariable.ColumnCount; j++)
    {
      //compare values
      if (excelSheet.Cell(j+1, i+1).Value !=  TableVariable.Item(j,i))
        compResults += "Row " + i + ", Column " + j + " -- Expected: " + TableVariable.Item(j,i) + ", Found: " + excelSheet.Cell(j+1, i+1).Value + "\r\n";
    }
  }  
  return compResults;
}

 

 

---------
Tanya Gorbunova
SmartBear Community Manager

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

View solution in original post

Tags (1)
New Here?
Join us and watch the welcome video:
Announcements
Top Kudoed Authors