utilize selenium When doing automated testing , Data is often used for batch testing , Common methods are reading txt file ,xml file ,csv Files and excel There are several kinds of documents .
Use excel For data management , Need to use xlrd、xlwt Open source package to read and write excel.
pip install xlrd
pip install xlwt
stay C:\Users\Any\Desktop Next , We have a name called test1 Of excel file , Inside Sheet1 The data table contains the following data ：
We expect to get the search terms from the above table , And search keywords in Baidu input box . Import first xlrd and xlwt, Because the running speed is too fast , In order to make it easy to see clearly, I will force sleep The two seconds .
from selenium
python Exchange of learning Q Group ：660193417 ###
import webdriverfrom selenium.webdriver.support.
wait import WebDriverWaitfrom selenium.
webdriver.support import expected_conditionsfrom selenium.webdriver.common.
by import Byimport timeimport xlrd,xlwtif __name__=="__main__":
driver=webdriver.Chrome()
driver.get("http://www.baidu.com")
file_test=xlrd.open_workbook(r"C:\Users\Any\Desktop\test1.xlsx")# Read test1.xlsx file
count=len(file_test.sheets())# Get the number of workbooks in this file print(" The total number of workbooks is ：",count) table1=file_test.sheet_by_name("Sheet1")# Get the data of the workbook according to the workbook name
nrows=table1.nrows # Get the number of workbook rows ncols=table1.ncols # Get the number of workbook Columns
print("Sheet1 The number of lines is ：",nrows," The number of columns is ：",ncols) # Start with the second line , Traverse Sheet1 Data in （ The first line is the header ）
for i in range(1,nrows):
rowvalues=table1.row_values(i) # Read data by line
key=rowvalues[1] # The first column is the serial number , Let's take the search term in the second column
print(" Search terms ：",key)
driver.find_element_by_id("kw").clear()# Clear the search box
driver.find_element_by_id("kw").send_keys(key) # Fill in the Baidu search box according to the search words WebDriverWait(driver,3).until(expected_conditions.visibility_of_element_located((By.ID,"su")))# Explicit wait driver.find_element_by_id("su").click()# Click Baidu button
time.sleep(2)
driver.quit()
After running, you will see print The data are as follows , We can confirm that our data is correct .print It is also convenient for debugging . From the browser, you can also intuitively feel
Affected by the whole search process .
There are many more operations for reading workbooks , Here are some commonly used .
data = xlrd.open_workbook("file_path") # Open file
There are several ways , The above code is obtained by the name of the workbook .
table=data.sheets()[0] # By index order table=data.sheet_by_index(0) # By index order table=data.sheet_by_name(u"name") # Get by name
table.nrows # Get the number of lines
table.nrows # Get the number of columns
Get the row and column values of a workbook （ Array ）
table.row_values(i) # Get a row value
table.col_values(i) # Get a column value
Get the value of a cell
cell_A1 = table.cell(0,0).value # obtain A1 The value of the cell
cell_A1 = table.row(0)[0].value # Index cells by rows A1 Value
cell_A1 = table.col(0)[0].value # Index cells by columns A1 Value
import xlrd,xlwtif __name__=="__main__":
wookbook = xlwt.Workbook() # Create Workbook
sheet1 = wookbook.add_sheet('Sheet_one',cell_overwrite_ok=True) # establish sheet, The name is Sheet_one
headlist = [' Serial number ',' keyword ',' remarks '] # Header data row = 0 col = 0 # Write header data for head in headlist: sheet1.write(row,col,head)
col=col+1 # write in 4 Row data len = 5 for i in range(1,len):
sheet1.write(i,0,i)
sheet1.write(i,1," Search for keywords {}".format(i))
sheet1.write(i,2," ")
print(" Write in the second {} Row data ".format(i))
wookbook.save(r"C:\Users\Any\Desktop\xlwt_text.xlsx") # Save the file , Name it xlwt_text.xlsx
After running , You can see that a xlwt_text.xlsx The file of , Open and see the data inside ,sheet The name and its contents are the data we just entered
about Excel Data write operation of , There are several commonly used .
establish excel file
wookbook = xlwt.Workbook() # Create Workbook
establish Sheet
sheet = wookbook.add_sheet('Sheet_name',cell_overwrite_ok=True) # establish sheet
Write cell data
sheet.write(row,col,data) # Write the cell data
Save the file
wookbook.save(file_path) # preservation
Have you learned ？ It's so simple. Don't tell me you haven't learned , If you have any questions, please remember to like the comments ！！
