"Ask questions" Selenium + Python's Excel data parameterization

Background

The recent period of time the public has been a lot of users of the message, the editorial department of the small series are ecstatic, in this thank you little friends love. In order to interact with you, small make a serious view of each of the small friends of the message, there are questions asked Fiddler grab bag tools, there are questions about automated selenium, of course, and asked where the small scissors, O (∩_∩) o~. Small series decided this issue of the article to pick a automation of people are more concerned about the question to answer:

Q: How does Python get a column value in Excel to Parameterize in a script recorded with selenium, such as how to parameterize a login user name and password?

A: You can use XLRD to read the contents of Excel for parameterization. Of course, in order to facilitate the detailed understanding of the small partners, the small series to introduce specific methods.

Selenium Environment Preparation:

1. Installing Java
2. Installing Python2.7
3. Install Pip
4. Install Selenium via PIP
5. Download the selenium server and run it

Sogou test the public before sharing similar content, while the online also has a more detailed tutorial, so skip. If the small partners do not understand the content here, the recommended reference (http://www.cnblogs.com/fnng/archive/2013/05/29/3106515.html)

To write a login use case:

1. STEP1: Visit http://www.effevo.com (play an advertising Effevo is Sogou self-developed project management system, completely free, very useful)
2. STEP2: Click Sign in at the top right corner of the page
3. STEP3: Login After entering user name and password
4. STEP4: Check if the avatar is present in the upper right corner

Implementation code:

# Encoding:utf-8"' Created on May 4, 2016 @author:dongming ' fromSeleniumImportWebdriver fromSelenium.common.exceptionsImportNosuchelementexception fromSelenium.webdriver.common.keysImportKeys fromSelenium.webdriver.support.uiImportWebdriverwaitImportTimebrowser = Webdriver. Firefox () Browser.get ("Http://www.effevo.com")assert "Effevo" inchBrowser.title#点击登录按钮Browser.find_element_by_xpath (".//*[@id = ' home ']/div/div[2]/header/nav/div[3]/ul/li[2]/a"). Click () Time.sleep (1) browser.find_element_by_id (' Passname '). Send_keys (' [email protected] ') browser.find_element_by_id (' Password '). Send_keys (' test1234 ') Browser.find_element_by_xpath (".//*[@id = ' content ']/div/div[6]/input"). Click () Time.sleep (2)Try: Elem = Browser.find_element_by_xpath (".//*[@id = ' Ee-header ']/div/div/div/ul[2]/li/a/img")exceptNosuchelementexception:#if Elem = = None:        assert 0,U "Login failed, unable to find top right corner Avatar"Browser.close ()

To create an Excel file:

Next, we will parameterize the username and password entered above so that the data is read from the Excel file. We name the Excel file Data.xlsx, which has two columns of data, the first column is username, and the second is password.

Installing XLRD third-party libraries

Python读取Excel文件需要使用第三方的库文件xlrd，我们到python官网下载http://pypi.python.org/pypi/xlrd模块安装。

1. Download xlrd-0.9.4.tar.gz
2. Unzip the file. Because the file is compressed with the tar command, it is recommended that Windows users extract 7zip, after extracting content as follows:

3. Command line run setup.py file: setup.py Install

4. After the installation is complete, we can import xlrd in the Python script.

XLRD Read Excel file:

Several important functions of the XLRD library file operation Excel are

data = xlrd.open_workbook(‘excelFile.xls‘) #打开Excel文件读取数据tabledata.sheets()[0]  #通过索引顺序获取获取一个工作表table.row_values(i)     # 获取整行的值（数组）nrows = table.nrows     #获取行数

For ease of use, we encapsulate the functions used above as Excel_table_byindex () functions.
Implementation code:

ImportXlrd def open_excel(file= ' File.xls '):        Try: data = Xlrd.open_workbook (file)returnDataexceptException,e:PrintSTR (e)#根据索引获取Excel表格中的数据 parameter: File:excel file path Colnameindex: Table header column name is the row, so by_index: Index of Table def excel_table_byindex(file= ' File.xls ', colnameindex=0, by_index= 0):data = open_excel (file) Table = Data.sheets () [By_index] nrows = table.nrows#行数Colnames = Table.row_values (Colnameindex)#某一行数据List =[] forRowNuminchRange1, nrows): row = Table.row_values (rownum)ifRow:app = {} forIinchRange (len (colnames)): app[colnames[i]] = Row[i] List.append (APP)returnList

Parameterization of Excel data:

# Encoding:utf-8"' Created on May 4, 2016 @author:dongming ' fromSeleniumImportWebdriver fromSelenium.common.exceptionsImportNosuchelementexception fromSelenium.webdriver.common.keysImportKeys fromSelenium.webdriver.support.uiImportWebdriverwaitImportTimeImportXlrd#import xdrlib, sys def open_excel(file= ' File.xls '):        Try: data = Xlrd.open_workbook (file)returnDataexceptException,e:PrintSTR (e)#根据索引获取Excel表格中的数据 parameter: File:excel file path Colnameindex: Table header column name is the row, so by_index: Index of Table def excel_table_byindex(file= ' File.xls ', colnameindex=0, by_index= 0):data = open_excel (file) Table = Data.sheets () [By_index] nrows = table.nrows#行数Colnames = Table.row_values (Colnameindex)#某一行数据List =[] forRowNuminchRange1, nrows): row = Table.row_values (rownum)ifRow:app = {} forIinchRange (len (colnames)): app[colnames[i]] = Row[i] List.append (APP)returnList def Login():Listdata = Excel_table_byindex ("E:\\data.xlsx",0)if(Len (listdata) <=0):assert 0,U "Excel data Exception"         forIinchRange0, Len (listdata)): Browser = Webdriver. Firefox () Browser.get ("Http://www.effevo.com")assert "Effevo" inchBrowser.title#点击登录按钮Browser.find_element_by_xpath (".//*[@id = ' home ']/div/div[2]/header/nav/div[3]/ul/li[2]/a"). Click () Time.sleep (1) browser.find_element_by_id (' Passname '). Send_keys (listdata[i][' username ']) browser.find_element_by_id (' Password '). Send_keys (listdata[i][' Password ']) Browser.find_element_by_xpath (".//*[@id = ' content ']/div/div[6]/input"). Click () Time.sleep (2)Try: Elem = Browser.find_element_by_xpath (".//*[@id = ' Ee-header ']/div/div/div/ul[2]/li/a/img")exceptNosuchelementexception:assert 0,U "Login failed, unable to find top right corner Avatar"Browser.close ()if__name__ = =' __main__ ': Login ()

"Ask questions" Selenium + Python's Excel data parameterization