python 读写excel

python 读取excel 文件

from xlrd import open_workbook
workbook=open_workbook('your_file')
#获取sheet
s = workbook.sheet_by_index(0)
# s = workbook.sheet_by_name(self.sheet)
#如果存在title,字典格式输出
title = s.row_values(0)
for row in range(1, s.nrows):
    print(dict(zip(title,s.row_values(row))))
#如果不存在title，列表格式输出
for row in range(1, s.nrows):
    print(s.row_values(row))    

python写入已存在的excel文件

from xlrd import open_workbook
from xlutils.copy import copy

oldworkbook=open_workbook('your_file')
#这里获取oldsheet 主要是为了之后用到的行数
oldsheet=oldworkbook.sheet_by_index(0)
title = a.row_values(0)

#复制
newworkbook=copy(oldworkbook,)
newsheet= newworkbook.get_sheet(0)

data={"执行时间":"30","结果":"passed"}

for row in range(1, oldsheet.nrows):
    for key in data:
        if key in title:
           s.write(row,title.index(key),data[key])
newworkbook.save('your_file')

遇到的问题

1. 运行后，文件打不开，要保存为xls格式
2. 编码问题，Pycharm 中指定工程编码：Settings–>Editor–>File Encodings
3. 获取列表索引值：listname.index('listvalue')