import os
import json
import xlrd2
fileTypeArray = [".xlsx",".xls"]
def readAllExecl():
currentPath = os.getcwd()+"/files"
for dir in [x for x in os.listdir(currentPath)]:
localPath = os.path.join(currentPath, dir)
if os.path.isfile(localPath):
filesp = os.path.splitext(localPath)
for k in fileTypeArray:
if filesp[1] == k:
filename = os.path.basename(localPath)
readExecl(localPath,filename.split('.')[0])
def readExecl(path,name):
workbook = xlrd2.open_workbook(path)
sheet2_name = workbook.sheet_names()[0]
sheet=workbook.sheet_by_name(sheet2_name) # sheet索引从0开始
# sheet的名称,行数,列数
adict = {}
for i in range(1,sheet.nrows):
data = {}
for j in range(0,sheet.ncols):
value = TransformationType(sheet.cell_value(i,j))
if isinstance(value , str):
if isJsonString(value):
data[TransformationType(sheet.cell_value(0,j))] = eval(value)
else:
data[TransformationType(sheet.cell_value(0,j))] = value
else:
data[TransformationType(sheet.cell_value(0,j))] = value
adict[TransformationType(sheet.cell_value(i,0))]= data
data = json.dumps(adict,indent=1,ensure_ascii=False)
_json_save_path = os.getcwd() + "/" + name + '.json'
f=open(_json_save_path,'w')
f.write(data)
f.close()
print("already create json:" + path)
return data,_json_save_path
def isJsonString(str):
try:
eval(str)
except Exception as e :
return False
return True
def TransformationType(var):
if isinstance(var ,float) : #type(var) == 'float':
str1 = int(var)
elif isinstance(var, str): #type(var) == 'unicode':
str1 = var
else:
raise Exception("type is not deal")
str1 = var
return str1
(三)Python字典格式转json格式
将读取的excel数据存入字典。
adict = {}
for i in range(1,sheet.nrows):
data = {}
for j in range(0,sheet.ncols):
value = TransformationType(sheet.cell_value(i,j))
if isinstance(value , str):
if isJsonString(value):
data[TransformationType(sheet.cell_value(0,j))] = eval(value)
else:
data[TransformationType(sheet.cell_value(0,j))] = value
else:
data[TransformationType(sheet.cell_value(0,j))] = value
adict[TransformationType(sheet.cell_value(i,0))]= data
data = json.dumps(adict,indent=1,ensure_ascii=False)
(四)保存json格式文件并把结果显示在界面上
把转换后的json文本显示到程序界面,文本可拷贝可编辑。
# 点击,转换文件
def start_transform_file():
global _label_filepath_text,_text,_label_state_text
_file_path = _label_filepath_text.get()
_file_type_list = [".xlsx", ".xls"]
if _file_path is not None:
if os.path.isfile(_file_path):
filesp = os.path.splitext(_file_path)
for k in _file_type_list:
if filesp[1] == k:
filename = os.path.basename(_file_path)
_json_data = excel2json.readExecl(_file_path, filename.split('.')[0])
_text.insert("end",_json_data[0])
_label_state_text.set("转换完毕,json文件存放地址为:"+_json_data[1])