0

Basically below is my code,I try to verify every element from the excel sheet is present in the web page or not(Data is displayed in table format in web page). If it not there , the cell will be colored red else green. this code takes much time to execute because for every data in excel sheet tries reading every value in the table. there are 10 rows and columns and takes more than 2 hrs to exceute

    require 'rubyXL'
    require 'roo'
    workbook = Roo::Spreadsheet.open(file_name)
    workbook1 = RubyXL::Parser.parse(file_name)
    workbook.default_sheet='Sheet4'
    worksheet1 = workbook1['Sheet4']
    choose("lookupSearch", :option => "Contract")
    sleep 5
    for j in workbook.first_row..workbook.last_row
    d=j
      for k in workbook.first_column..workbook.last_column
           if (k==workbook.first_column && workbook.cell(j,k)!='nil')
           fill_in "searchInput", :with => workbook.cell(j,k)
           find(:xpath, '//*[@id="searchicon"]').click
           sleep 5
           elsif(workbook.cell(j,k)=='/')
           puts" "
           elsif(k==workbook.last_column)   
           puts""
           elsif()
           elsif(workbook.cell(j,k)=='nil')
             k=k+1
             findXpath=page.find('th,td', text: workbook.cell(j,k), :match => :prefer_exact).path
             print findXpath
             splitXpath=(findXpath.split("/"))
             count1=splitXpath.count
             value=splitXpath.at(count1-1)
                 if(value=="th[1]")
                   finalElementXpath= findXpath.sub("th[1]","th[2]")
                 elsif(value=="th[2]")           
                   finalElementXpath= findXpath.sub("th[2]","th[3]")
                 elsif(value=="th[3]")           
                   finalElementXpath= findXpath.sub("th[3]","th[4]")
                 elsif(value=="th[4]")           
                   finalElementXpath= findXpath.sub("th[4]","th[5]")
                 elsif(value=="th[5]")           
                   finalElementXpath= findXpath.sub("th[5]","th[6]")
                 elsif(value=="td[1]")
                   finalElementXpath= findXpath.sub("td[1]","td[2]")
                 elsif(value=="td[2]")           
                   finalElementXpath= findXpath.sub("td[2]","td[3]")
                 elsif(value=="td[3]")           
                   finalElementXpath= findXpath.sub("td[3]","td[4]")
                 elsif(value=="td[4]")           
                   finalElementXpath= findXpath.sub("td[4]","td[5]")
                 elsif(value=="td[5]")           
                   finalElementXpath= findXpath.sub("td[5]","td[6]")
                 end

          if (workbook.cell(j,k+1) == (find(:xpath, finalElementXpath).native.text)) 
             #worksheet1.sheet_data[j-1][k-1].change_fill ('008000')
             #worksheet1.sheet_data[j-1][k].change_fill ('008000')
             #worksheet1.sheet_data[j-1][k-1].change_fill ('008000')
             #worksheet1.sheet_data[j-1][k].change_fill ('008000')
             print "---#{workbook.cell(j,k+1)}--value  --- exist--------for  #{workbook.cell(j,k)}  ------------ \n"
          else
             print "-----object #{workbook.cell(j,k+1)} -- does not exist---for  #{workbook.cell(j,k)} --------- \n"
             #worksheet1.sheet_data[j-1][k].change_fill ('ff0000')
             #worksheet1.sheet_data[j-1][k-1].change_fill ('ff0000')
          end

else

        findXpath=page.find('th,td', text: workbook.cell(j,k), :match => :prefer_exact).path
        splitXpath=(findXpath.split("/"))
        count=splitXpath.count
        value=splitXpath.at(count-1)
        if(value=="th[1]")
                   finalElementXpath= findXpath.sub("th[1]","th[2]")
                 elsif(value=="th[2]")           
                   finalElementXpath= findXpath.sub("th[2]","th[3]")
                 elsif(value=="th[3]")           
                   finalElementXpath= findXpath.sub("th[3]","th[4]")
                 elsif(value=="th[4]")           
                   finalElementXpath= findXpath.sub("th[4]","th[5]")
                 elsif(value=="th[5]")           
                   finalElementXpath= findXpath.sub("th[5]","th[6]")
                 elsif(value=="td[1]")
                   finalElementXpath= findXpath.sub("td[1]","td[2]")
                 elsif(value=="td[2]")           
                   finalElementXpath= findXpath.sub("td[2]","td[3]")
                 elsif(value=="td[3]")           
                   finalElementXpath= findXpath.sub("td[3]","td[4]")
                 elsif(value=="td[4]")           
                   finalElementXpath= findXpath.sub("td[4]","td[5]")
                 elsif(value=="td[5]")           
                   finalElementXpath= findXpath.sub("td[5]","td[6]")
                 end


if (workbook.cell(j,k+1) == (find(:xpath, finalElementXpath).native.text))
     worksheet1.sheet_data[j-1][k-1].change_fill ('008000')
     worksheet1.sheet_data[j-1][k].change_fill ('008000')
     print "---#{workbook.cell(j,k+1)}--value  --- exist--------for  #{workbook.cell(j,k)}  ------------ \n"
else
     worksheet1.sheet_data[j-1][k-1].change_fill ('ff0000')
     worksheet1.sheet_data[j-1][k].change_fill ('ff0000')
     print "-----object #{workbook.cell(j,k+1)} -- does not exist---for  #{workbook.cell(j,k)} --------- \n"
end
end
end
end
 workbook1.write(file_name) 
Uzbekjon
  • 11,655
  • 3
  • 37
  • 54
RPD
  • 47
  • 8

0 Answers0