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)