程序師世界是廣大編程愛好者互助、分享、學習的平台,程序師世界有你更精彩!
首頁
編程語言
C語言|JAVA編程
Python編程
網頁編程
ASP編程|PHP編程
JSP編程
數據庫知識
MYSQL數據庫|SqlServer數據庫
Oracle數據庫|DB2數據庫
您现在的位置: 程式師世界 >> 編程語言 >  >> 更多編程語言 >> Python

[python+selenium] read and write Excel files in one step

編輯:Python

Preface

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 writing

Several pieces .

Use excel For data management , Need to use xlrd、xlwt Open source package to read and write excel.

1、 install xlrd、xlwt

pip install xlrd
pip install xlwt

2、 Yes excel Data reading operation of table

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 , To make it easier to see

Chu and I are forced to enter keywords every time sleep The two seconds .

from selenium
python Exchange of learning Q Group :906715085###
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 .

Open file

data = xlrd.open_workbook("file_path") # Open file 

Get a workbook

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 

Get the number of rows and columns of a workbook

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 

3、 Yes Excel Write operation of file

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 

Last

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 !! Don't be careless in your study , This article ends here , Next

bye …


  1. 上一篇文章:
  2. 下一篇文章:
Copyright © 程式師世界 All Rights Reserved