Recently, I want to try to make an interface automation script , First, you need to read data from the file , I updated an article a long time ago , But it has great limitations , Only one can be read at a time Excel One of them sheet surface , And the read format cannot meet the requirements , Found a lot of ways , It's finally worked out .
Also encountered a lot of pits , Because of self-study python, No systematic study , Basic knowledge is not solid , And I don't use it in my daily work , After a while, you need to start from scratch
=_=, Therefore, the pit encountered is the reason why the basic knowledge is not mastered . Record it , Convenient for later review or knowledge review .
Now it's a pit :
def getTables(): for excel in os.listdir(data_path): print(excel) if
excel.startswith('~'): break excelPath = os.path.join(data_path, excel) excels
= xlrd.open_workbook(excelPath) sheetNames = excels.sheet_names()
print('sheetNames:',sheetNames) return sheetNames
# Here I'll just take what I got sheet name return Yes , This is a list type def getData(sheets): data = [] for i in
range(2, sheets.nrows):# When you write here ,sheets Unable to bring it out automatically nrows, Forced writing will also report errors
data.append(dict(zip(sheets.row_values(1), sheets.row_values(i))))
print(' Read out data:',data) getData(getTables())
Keep prompting this :
since list There is no such method ,len() You can also get all the rows , So I changed it to len(), such :
def getData(sheets): data = [] for i in range(2, len(sheets)):
data.append(dict(zip(sheets.row_values(1), sheets.row_values(i))))
print(' Read out data:',data)
But still not :
So I looked back at what I had written before test, see light suddenly :
Attach the correct code
import os,xlrd data_path =
os.path.join('F:\pythonProjrect\itf_simple','data')#os.path.join() function : Connect two or more pathnames
print(data_path) # Get all under the directory Excel in sheet def getTables(): for excel in
os.listdir(data_path):#os.listdir() Method returns a list of files or folder names contained in the specified folder print(excel) if
excel.startswith('~'): break excelPath = os.path.join(data_path, excel)# Splicing path
excels = xlrd.open_workbook(excelPath) sheetNames =
excels.sheet_names()# Get all under the directory sheet Name of ,sheetNames by list type
print('sheetNames:',sheetNames) # Cycle from sheetNames Read in sheet surface , Returned is sheet Memory address of for
sheet in sheetNames: table = excels.sheet_by_name(sheet) print('table:',table)
return table
# from getTables() Returned in table Extract data from ,sheet The data in forms a list ,Excel The header and data in are composed respectively key:value Format of def
getData(sheets): data = [] for i in range(2, sheets.nrows):
data.append(dict(zip(sheets.row_values(1), sheets.row_values(i))))
print(' Read out data:',data) getData(getTables())
So it works ~
Technology