Thursday, 27 February 2014

Selenium Data Driven | Python | xlrd | .XLSX


XLRD

xlrd is a library that is used to extract data from an excel file using Python Binding. It helps you to do data scrapping from an Excel spreadsheet.


Setup xlrd with Python 

1| Open cmd prompt
2| Locate the folder C:\Python27\Scripts in cmd and enter
pip install xlrd



Excel

Create an Excel sheet that resembles this:




from selenium import webdriver
import unittest, time
import xlrd

class moduleName(unittest.TestCase):
    def setUp(self):
        self.driver = webdriver.Firefox()
        self.driver.implicitly_wait(30)
        self.base_url = "https://www.google.co.in"
        self.verificationErrors = []
        self.accept_next_alert = True
        
    def test_Login(self):
        driver=self.driver
        driver.get(self.base_url +"/")
        driver.maximize_window()
        wb=xlrd.open_workbook('C://sams.xlsx')
        sheetname = wb.sheet_names() 
        #Read for XLSX Sheet names
        sh1 = wb.sheet_by_index(0) 
                        
        i=0
        while (i<2):
            rownum=(i)
            rows = sh1.row_values(rownum)
            element = driver.find_element_by_id("gbqfq")
            driver.find_element_by_id("gbqfq").send_keys(rows[0])        
            element.submit()
            time.sleep(3)
            print "The keyword [" + rows[0] + "] is searched"
            driver.back()
            time.sleep(3)
            i=i+1
    
    def tearDown(self):
        self.driver.quit()

if __name__ == "__main__":
    unittest.main()



Test Console looks the same as below after Execution



NOTE| Use Python 2.7 instead of Python 3; xlrd doesn't support Python 3

37 comments:

  1. Your information about selenium is really interesting. Also I want to know the latest new techniques which are implemented in Selenium. Please update it in your website.
    Best Selenium training Chennai

    ReplyDelete
  2. import unittest
    import xlrd
    from selenium import webdriver


    class fblogin(unittest.TestCase):
    def setUp(self):
    self.driver=webdriver.Firefox()
    self.driver.get("URL")
    self.driver.maximize_window()
    def test_fblogin(self):
    driver=self.driver
    wb=xlrd.open_workbook("C://Python34//Scripts//pythondata.xlsx")
    sheetname = wb.sheet_names()
    sh1 = wb.sheet_by_index(0)
    i=0
    while (i<2):
    rownum=(i)
    rows = sh1.row_values(rownum)
    UserName = driver.find_element_by_id('UserName')
    driver.find_element_by_id('UserName').send_keys(rows[0, 1])
    driver.implicitly_wait(10)
    print("The Gbouser name [" + rows[0, 1] + "] is entered")
    Password = driver.find_element_by_id('Password')
    driver.find_element_by_id('Password').send_keys(rows[1, 1])
    print("The Password [" + rows[1, 1] + "] is entered")
    driver.implicitly_wait(10)
    driver.back()

    i=i+1
    Login = driver.find_element_by_name('Login').click()
    driver.implicitly_wait(10)

    def tearDown(self):
    self.driver.quit()


    if __name__ == "__main__":
    unittest.main()

    ------------------------------------After Execution i got following error message----------------------------
    Traceback (most recent call last):
    File "loop.py", line 13, in test_fblogin
    wb=xlrd.open_workbook("C://Python34//Scripts//pythondata.xlsx")
    File "C:\Python34\lib\site-packages\xlrd\__init__.py", line 394, in open_workbook
    f = open(filename, "rb")
    FileNotFoundError: [Errno 2] No such file or directory: 'C://Python34//Scripts//pythondata.xlsx'


    Please let me know whats wrong with above script thansk in Advance_Amit

    ReplyDelete
  3. Java is one of the popular technologies with improved job opportunity for hopeful professionals. Java Training Chennai helps you to study this technology in details.If you are looking for best Java Training Institutes in Chennai reach Fita academy.

    ReplyDelete
  4. The information you posted here is useful to make my career better. Thanks for sharing such a informative post. keep updates...

    Regards..
    Salesforce Training in Chennai

    ReplyDelete
  5. Nice post. Oracle is a relational database management system produced by oracle corporation. Nowadays most of the multinational companies used this oracle database for storing and managing their data's and programs. So learning Oracle Training in Chennai is one of the best idea to make a bright career.

    ReplyDelete
  6. Thanks for sharing this information. Salesforce is a cloud based CRM. Nowadays most of the multinational companies used this CRM for managing their customers. To know more details call 9841746595.

    Regards..
    Salesforce Training Institutes in Chennai

    ReplyDelete
  7. Really nice post. SEO is one of the digital marketing techniques used for improve the website ranking in search engine result page. To know more details please call 9003623340.
    Regards..
    SEO Course in Chennai

    ReplyDelete
  8. Really awesome blog. Software testing is a method of executing the application or program with the intent of searching the software errors. Software Testing Course in Chennai offering this course at reasonable cost.

    ReplyDelete
  9. Excellent information. HTML5 is a markup language used for designing responsive website and it is also used for structuring and presenting the website content.

    Regards.
    HTML5 Training Institutes in Chennai | PHP Training Institute in Chennai

    ReplyDelete
  10. I get a lot of great information from this blog. Thank you for your sharing this informative blog. Just now I have completed hadoop certification course at a leading academy. If you are interested to learn Hadoop Training Chennai visit FITA IT training and placement academy.

    ReplyDelete
  11. Hi, Your blog is really very informative and useful for me. Thanks for sharing this valuable blog.
    Regards..
    Unix Training Institutes in Chennai

    ReplyDelete
  12. Cloud computing is storing and accessing the large data sets over the internet instead of your PC computer. So that you can manage the data and program anywhere through the internet.
    Regards..
    Cloud Computing Training in Chennai

    ReplyDelete
  13. Thanks for sharing informative article on mobile testing. It helped me to understand the future of mobile application development and need of mobile application testing training. Think of joining mobile application testing training in Chennai from reputed IT placement or training academy.

    ReplyDelete
  14. There are lots of information about latest technology and how to get trained in them, like Hadoop Training Chennai have spread around the web, but this is a unique one according to me. The strategy you have updated here will make me to get trained in future technologies(Hadoop Training in Chennai). By the way you are running a great blog. Thanks for sharing this.

    ReplyDelete
  15. Thank you for the useful post. It helps a lot in my training. I share your blog with my students. Keep posting more.
    Selenium Training Institute in Chennai | Selenium Training Institute in Chennai

    ReplyDelete
  16. Best Java Training Institute In ChennaiThis information is impressive; I am inspired with your post writing style & how continuously you describe this topic. After reading your post, thanks for taking the time to discuss this, I feel happy about it and I love learning more about this topic..

    ReplyDelete
  17. Its very useful post! this is informative article ! useful to job searching and to best training and managing safety hazards! http://goo.gl/ynyuD1
    |http://goo.gl/bzqJvN|http://goo.gl/GQqnSD|http://goo.gl/RuAGa4 keep posting thanks for sharing.


    ReplyDelete
  18. you did great job, thanks for sharing this post with some useful snap shots.
    Regards,
    cloud computing training in chennai

    ReplyDelete
  19. Thanks for the great information in your blog Selenium Training in Chennai

    ReplyDelete
  20. It was very nice blog to learn about SAP HR.Thanks for sharing new things.SAP HR

    ReplyDelete
  21. This comment has been removed by the author.

    ReplyDelete
  22. • thanks for ur valuable information about DOT NET ...
    dot net training in chennai

    ReplyDelete
  23. I agree with your thoughts!!! For more information check here.

    ReplyDelete
  24. Amazing, thanks a lot my friend, I was also siting like a your banner image when I was thrown into Selenium.
    When I started learning then I understood it has got really cool stuff.


    Best Selenium Training Institute in Chennai

    Selenium Training in Velachery


    Free Selenium Tutorials

    ReplyDelete
  25. The blog gave me idea to setup xlrd with python The code shared was very much useful and helpful Thanks for sharing it
    Selenium Training in Chennai || Python Training in Chennai

    ReplyDelete
  26. We share it this blog was really useful to me.Keep it touch!!!I am reading ur post from the beginning, it was so interesting to read & I feel thanks to you for posting such a good blog, keep updates regularly
    Digital Marketing Course in Chennai | SEO Training in Chennai

    ReplyDelete
  27. Really awesome blog. Your blog is really useful for me. Thanks for sharing this informative blog. Keep update your blog. Selenium Training in Chennai

    ReplyDelete
  28. really you have posted an informative blog. it will be really helpful to many peoples. thank you for sharing this kind of an interesting blog. keep on sharing such kind of an articles.
    java training in chennai

    ReplyDelete
  29. This is excellent information. It is amazing and wonderful to visit your site.Thanks for sharing this information&its very useful to me...
    Android training in chennai
    Ios training in chennai

    ReplyDelete

  30. This is excellent information. It is amazing and wonderful to visit your site.Thanks for sharing this information,this is useful to me...
    Android Training in Chennai
    Ios Training in Chennai

    ReplyDelete
  31. if all runs well (i hope so), you will get this message "Successfully installed xlrd". If you get a "Permission denied" error, it means that you are not logged as root.
    To make sure has been installed correctly, type following command, which will show you the version of the xlrd module:
    python -c "import xlrd; print xlrd.__VERSION__"
    Hadoop Training in Chennai
    Hadoop Training Institute in Chennai

    ReplyDelete