需求如下:
读取Excel表格中的sheet也中的测试数据,并转换成json格式数据
#coding:utf-8
import json
import xlrd
import sys
reload(sys)
sys.setdefaultencoding('utf-8')
def tableToJson():
source = xlrd.open_workbook(r"C:\Users\Administrator\Desktop\0603\0603Interface.xlsx")
# 获取workbook中所有的sheet
sheets = source.sheets()
# 循环遍历所有sheet
print len(sheets) #15
list = [] #字典列表
totalList = [] #json列表
for i in range(len(sheets)):
table = source.sheet_by_index(i)
print "loop",i #第几个sheet
keys = table.col_values(1) #获取第i个表第2列的所有值
for j in xrange(3,table.ncols):
test_value = table.col_values(j) #获取第i个表第4列起的值作为用例的参数值
for k in range(len(test_value)):
#test_value[k] = test_value[k].decode('gbk').encode('utf-8')
s = test_value[k]
#print s
#test_name = table.cell(0,j).value #获取第i个表第4列起的每列第一行内容作为用例名
data = dict(zip(keys,test_value)) #等长两列表转为字典
list.append(data)
data1 = {}
data1[sheets[i].name] = list
data1_to_json = json.dumps(data1,ensure_ascii=False,encoding="gb2312") #将列表中的unicode转中文
print u"用例数据:",data1_to_json
totalList.append(data1_to_json)
list = []
return totalList
if __name__ == '__main__':
jsondata = tableToJson()
#print u'test_name_debug:',jsonData
#写入文件
f = open(r"F:\TestData.json",'w+')
for i in jsondata:
f.write(i)
f.write("\n")
#f.write(jsondata)
f.close()