import xlrd
import json
# import requests
# pip install xlrd
# pip install requests
def openWorkbook():
# 读取excel表的数据
workbook = xlrd.open_workbook(r'E:/case.xlsx')
# 选取需要读取数据的那一页
sheet = workbook.sheet_by_index(0)
# 获得行数和列数
rows = sheet.nrows
cols = sheet.ncols
#创建一个key、value合并单元格的值
unitCell = {}
for crange in sheet.merged_cells:
rs, re, cs, ce = crange
#print(str(rs)+","+str(re)+","+str(cs)+","+str(ce))
merged_cell_val = sheet.cell(rs, cs).value
#print(merged_cell_val)
sRow = rs
sCol = cs;
while sRow < re :
while sCol < ce :
unitCell[str(sRow) + "-" + str(sCol)] = merged_cell_val
sCol = sCol+1
sRow = sRow+1
sCol = cs;
# 创建一个数组用来存储excel中的数据
#print(unitCell)
p = []
for i in range(1, rows):
d = {}
for j in range(0, cols):
q = '%s' % sheet.cell(0, j).value
key = str(i) + "-" + str(j)
print(key)
if key not in unitCell:
d[q] = sheet.cell(i, j).value
if key in unitCell:
print(unitCell[key])
d[q] = unitCell[key]
ap = []
for k, v in d.items():
if isinstance(v, float): # excel中的值默认是float,需要进行判断处理，通过'"%s":%d'，'"%s":"%s"'格式化数组
ap.append('"%s":%d' % (k, v))
else:
ap.append('"%s":"%s"' % (k, v))
s = '{%s}' % (','.join(ap)) # 继续格式化
p.append(s)
t = '[%s]' % (','.join(p)) # 格式化
data=json.dumps(t,ensure_ascii=False)
return data.replace("\\","")
# with open('student4.json',"w",encoding='utf-8') as f:
# f.write(t)
#openWorkbook()
#url="http://111.111.111.111:8000/pushdata/"
#headers={"Content-Type":"application/json"}
data=openWorkbook()
print(data)
#re=requests.post(url=url,headers=headers,data=data)
#print(re.text)
