介紹
xlrd(讀操作)鉴逞,xlwt(寫操作)
上述軟件下載后栓票,分別解壓衅鹿,之后在cmd命令下分別進(jìn)入對應(yīng)的目錄中運(yùn)行
python setup.py install
如果運(yùn)行過程中提示缺少setuptools,則先運(yùn)行python ez_setup.py之后在重復(fù)上面的步驟
PS:office的版本不要用太高的塞颁,建議最好用03版本的浦箱,且后綴為xls的
源碼bug修復(fù)
安裝好xlwt3后,找到formula.py文件殴边,將其中的
__slots__ = ["__init__", "__s", "__parser", "__sheet_refs", "__xcall_refs"]
修改為
__slots__ = [ "__s", "__parser", "__sheet_refs", "__xcall_refs"]
實(shí)戰(zhàn)
不廢話憎茂,碼起來~
import xlrd
import xlwt3
path = 'excel所在的路徑-小強(qiáng)測試品牌'
#打開excel
def open_excel(path):
try:
workbook = xlrd.open_workbook(path)
print("excel打開成功")
return workbook
except Exception as e:
print(str(e))
open_excel(path)
#讀取excel的信息
def show_excel(path):
workbook=xlrd.open_workbook(path)
#獲取sheet
sheets = workbook.sheet_names()
print("獲取excel中存在的sheet名稱", sheets)
sheet = workbook.sheets()[0] #通過索引順序獲取一個(gè)sheet
print("通過索引順序獲取一個(gè)sheet對象", sheet)
sheet = workbook.sheet_by_index(0) #通過索引順序獲取一個(gè)sheet
print("通過索引順序獲取一個(gè)sheet對象", sheet)
#sheet = workbook.sheet_by_name('Sheet1') #通過名稱獲取
#獲取行數(shù)珍语、列數(shù)锤岸、單元格
print("獲取總行數(shù)", sheet.nrows) #總行數(shù)
print("獲取總列數(shù)", sheet.ncols) #總列數(shù)
print("第1行的值", sheet.row_values(0)) #獲取整行的內(nèi)容
print("第2列的值", sheet.col_values(1)) #獲取整列的內(nèi)容
print("第2行2列的值", sheet.cell_value(1,1)) #獲取單元格的值
show_excel(path)
#寫入數(shù)據(jù)
def write_excel(path):
wb=xlwt3.Workbook()#創(chuàng)建工作薄
sheet=wb.add_sheet("xlwt3數(shù)據(jù)測試表",cell_overwrite_ok=True)#創(chuàng)建工作表
value = [["名稱", "小強(qiáng)python自動化測試實(shí)戰(zhàn)", "小強(qiáng)性能測試實(shí)戰(zhàn)"], ["價(jià)格", "52.3", "45"]]
for i in range(0,2):
for j in range(0,len(value[i])):
sheet.write(i,j,value[i][j])#三個(gè)參數(shù)分表代表行、列板乙、值
wb.save(path)
print("寫入數(shù)據(jù)成功")
write_excel(path)
#讀取數(shù)據(jù)
def read_excel(path,by_index=0):
workbook = open_excel(path)
table = workbook.sheets()[by_index]
nrows = table.nrows #行數(shù)
ncols = table.ncols #列數(shù)
print("第一種輸出形式")
for i in range(0,nrows):
row=table.row(i)
for j in range(0,ncols):
print(table.cell_value(i,j)," ",end="")#加上最后的參數(shù)end可以不換行
print()
print("第二種輸出形式")
for i in range(0,nrows):
ss = table.row_values(i)#獲取第i行的數(shù)據(jù)列表(整行)
print('ss=', ss)
for i in range(0, len(ss)):
print(ss[i])
print('------------------')
read_excel(path)