python  Excel 转为 json格式.zip

#coding:utf-8 ######将指定文件夹内的xlsx文件转换为json格式 import os import sys import json import subprocess import getpass import xlrd reload(sys) sys.setdefaultencoding('utf-8') import traceback os.chdir('D:/workcode/pythonTool/configfile') class SearchFile: def __init__(self): self.fileList = [] self.recursiveDir(os.getcwd()) def recursiveDir(self, srcPath): ''' 递归指定目录下的所有文件''' dirList = [] #所有文件夹 files = os.listdir(srcPath) #返回指定目录下的所有文件，及目录（不含子目录） for f in files: #目录的处理 if (os.path.isdir(srcPath + '/' + f)): if (f[0] == '.'): #排除隐藏文件夹 pass else: #添加非需要的文件夹 dirList.append(f) #文件的处理 elif (os.path.isfile(srcPath + '/' + f)): self.fileList.append(srcPath + '/' + f) #添加文件 #遍历所有子目录,并递归 for dire in dirList: #递归目录下的文件 self.recursiveDir(srcPath + '/' + dire) def getAllFile(self): ''' get all file path''' return tuple(self.fileList) class ExcelHandle: def __init__(self): self.content = [] self.filename = '' self.sheetname = '' def decode(self,filename,sheetname): try: filename = filename.decode('utf-8') sheetname = sheetname.decode('utf-8') except Exception: print traceback.print_exc() return filename, sheetname def read_excel(self,filename,sheetname): self.filename, self.sheetname = self.decode(filename,sheetname) self.content = [] rbook = xlrd.open_workbook(self.filename) sheet = rbook.sheet_by_name(self.sheetname) rows = sheet.nrows cols = sheet.ncols keys = sheet.row_values(0) for i in range(rows-1): row_content = {} for j in range(cols): tempValue = sheet.cell_value(i+1, j) if (sheet.cell_type(i+1, j) == 2) and (tempValue%1.0 == 0.0): row_content[keys[j]] = int(tempValue) else: row_content[keys[j]] = tempValue self.content.append(row_content) print '[' + ','.join("'" + str(element) + "'" for element in row_content) + ']' def write_json(self): json_str = json.dumps(self.content, ensure_ascii=False, sort_keys=True, indent=4, separators=(',',':')) output_file = self.filename.replace(".xlsx", ".json") file = open(output_file, "w") file.write(json_str) file.close() def excel2json(filename,sheetname): execlhdl = ExcelHandle() execlhdl.read_excel(filename,sheetname) execlhdl.write_json() def startConvert(): searchfile = SearchFile() fileList = list(searchfile.getAllFile()) for file in fileList: (filepath,filename) = os.path.split(file) (shotname,extension) = os.path.splitext(filename) print filepath,filename if (extension == '.xlsx'): excel2json(file,shotname) if __name__ == "__main__": startConvert()