Excel数据操作
1. 模块说明
import xlrd
详细说明地址:https://www.cnblogs.com/insane-Mr-Li/p/9092619.html
from xlutils.copy import copy
详细说明地址:https://www.cnblogs.com/machangwei-8/p/10739115.html
2.代码执行
# 打开文件,获取到表单的数据
def get_TableData(self):
data = xlrd.open_workbook(self.file_name)
table = data.sheet_by_name(self.sheet_name)
return table
# 获取xls文件的行数
def get_Rows(self):
table = self.data
return table.nrows
# 获取xls文件的列数
def get_Clon(self):
table = self.data
return table.ncols
# 根据索引获取xls文件单个单元格的数据
def get_cell_value(self, row, col):
table = self.data
return table.cell_value(row, col)
说明:table = self.data --- 执行get_TableData的结果即为self.data
# 将表单的数据信息以字典的形式保存:[{key:value},{key1:value1}]
def get_DictData(self):
data = xlrd.open_workbook(self.file_name)
table = data.sheet_by_name(self.sheet_name)
nows = table.nrows
nols = table.ncols
DictData= []
# 将excel参数保存为字典
for i in range(1, nows):
sheet_data = {}
for j in range(nols):
# 第一行的value作为字典的key
keys = table.cell_value(0, j)
values = table.cell_value(i, j)
sheet_data[keys] = values
DictData.append(sheet_data)
return DictData
# 根据caseid获取到数据行信息
def get_LineData(self, caseid):
if type(self.get_data_lines()) is dict:
# print("Testcase is only 1")
if self.get_data_lines()[0]["ID"] == caseid:
line_data = self.get_data_lines()
return line_data
else:
for j in range(0, len(self.get_data_lines())):
if self.get_data_lines()[j]["ID"] == caseid:
line_data = self.get_data_lines()[j]
return line_data
# 写入数据
def write_Excel(self, row, col, value):
# 根据row,col,value写入数据
read_data = xlrd.open_workbook(self.file_name)
write_data = copy(read_data)
sheet_data = write_data.get_sheet(0)
sheet_data.write(row, col, value)
write_data.save(self.file_name)
持续更新.....