python 解析excel

先把excel导出csv格式。

解析成json

# -*- coding: utf-8 -*-    import os  import sys  import os.path  import shutil  import Image  import jsonclass getoutofloop(Exception): passa ={'小图标':0,'名称':2,'价格':4,'部位':5,'类别':6,'星级':7,'备注':8,'切图编号':9,'总层数':11,'部件层':12,'是否润色层':13,'色值':14,'黑白':15,'定位':16}clasifyMap = {'a':'QingChun','b':'TianMei','c':'RiChang','d':'DaiMeng','e':'ZiRan','f':'ZhiYe','g':'QingShu','h':'JieRi','i':'GaoGuai','j':'LuoLi','k':'AnHei','l':'SheHua','m':'DiYu','n':'NanZhuang',}attStr = ''aItems = a.items()sorted(aItems, lambda x, y: cmp(x[1], y[1]))for item,value in aItems: # print '%s,=%s' %(item, value)attStr+= (item+':'+str(value)+4*' ')print attStrFaXingMap ={'FaXing':[]}FuZhuangMap ={'ShangZhuang':[],'XiaZhuang':[],'TaoZhuang':[],'MaoZi':[],"YaoShi":[]}XieWaMap ={'XieZi':[],'WaZi':[]}PeiShiMap ={'ErHuan':[],'XiongBu':[],'ShouTao':[],"ShouWan":[],"JiaoWan":[],"ZuoShou":[],"YouShou":[]}MeiRongMap ={'YanKuang':[],'TongKong':[],'MeiMao':[],'BiZi':[],'ZuiBa':[],'PiFu':[],'ZhuangShi':[]}HuanJingMap ={'QianJing':[],'BeiBu':[],'BeiJing':[],}jsonMap = {'FaXingMap':[['头发','FaXing']],'FuZhuangMap':[['上装','ShangZhuang'],['下装','XiaZhuang'],['连身','TaoZhuang'],['头饰','MaoZi'],['腰饰','YaoShi'],],'XieWaMap':[['鞋子','XieZi'],['袜子','WaZi'],],'PeiShiMap':[['耳环','ErHuan'],['胸部','XiongBu'],['手套','ShouTao'],['手腕',"ShouWan"],['脚腕',"JiaoWan"],['左手',"ZuoShou"],['右手',"YouShou"]],'MeiRongMap':[['眼眶','YanKuang'],['瞳孔','TongKong'],['眉毛','MeiMao'],['鼻子','BiZi'],['嘴巴','ZuiBa'],['皮肤','PiFu'],['装饰','ZhuangShi'],],'HuanJingMap':[['前景','QianJing'],['背部','BeiBu'],['背景','BeiJing'],]}def getPartMap(partName):for key,value in jsonMap.items():for tun in value:if partName == tun[0]:if key == 'FaXingMap':return FaXingMapif key == 'FuZhuangMap':return FuZhuangMapif key == 'XieWaMap':return XieWaMapif key == 'PeiShiMap':return PeiShiMapif key == 'MeiRongMap':return MeiRongMapif key == 'HuanJingMap':return HuanJingMapreturn {} # partMap = getPartMap('皮肤')# for item,value in partMap.items(): # print '-------  %s,=%s' %(item, value)# exit()fileName = 'data3.csv'curPath = os.getcwd()# 当前路径  aim_path = os.path.join(curPath, fileName) fileData = file(fileName,'r')fristAttributeStr = fileData.readline()lineCount = len(fileData.readlines())attribute = fristAttributeStr.split(",")cols = len(attribute)dataArray = [[0 for col in range(cols)] for row in range(lineCount)]# print cols# print dataArray# for attri in attribute:# print attri# attributeMap[attri] = attri# for item,value in attributeMap.items(): # print 'key=%s,value=%s' %(item, value)# attribute = [[0 for col in range(cols)] for row in range(300)]# print attributefileData = file(fileName,'r')preLineArray = []needReadNewLine = Trueline = ''valueArray = []for i in range(lineCount):if needReadNewLine:line = fileData.readline()line = line[:-1]valueArray = line.split(",")needReadNewLine = True# print '->'+line# print valueArray[8]if len(valueArray) < 8 : breakif len(line) < 17:continuehaveSameKey = False;#是否有相同的二级分类# print valueArray[8] + "  " + valueArray[7] + "  " + valueArray[9]try:for key,value in jsonMap.items():for tun in value:partName = tun[0]partKeyName = tun[1]if valueArray[8] == partName:haveSameKey = TruenewMap = {}newMap['rwName'] = valueArray[0]+'.png'newMap['buyNeedCoin'] = int(valueArray[4])newMap['saleGainCoin'] = int(valueArray[4])newMap['classify'] = clasifyMap[valueArray[6]]newMap['starLevel'] = int(valueArray[7])newMap['value'] = 100newMap['num'] = 1newMap['isNew'] = FalsenewMap['id'] = "1"newMap['heartNum'] = 1newMap['hexColor3B'] = 'ffffff'aimImgArr = []newImgArr = []newImgArr.append(valueArray[9]+'.png')#图片名字newImgArr.append('ffffff' if valueArray[14]=='无' else valueArray[14])#颜色blackWhiteAlphaStr = valueArray[15].decode('utf8');bwStr = blackWhiteAlphaStr[0:1].encode('utf8')# print bwStr + ('Ture' if bwStr == '黑' else 'False')bwNum = '0'if bwStr == '黑':bwNum = blackWhiteAlphaStr[1:]elif bwStr == '白':bwNum = '-'+blackWhiteAlphaStr[1:]# print blackWhiteAlphaStr + '->' + bwNumnewImgArr.append(bwNum)#黑白alphanewImgArr.append(valueArray[11])#global层级newImgArr.append(valueArray[12])#part层级newImgArr.append('true' if valueArray[13]=='是' else 'false')#是否是润色层newImgArr.append(valueArray[16])#定位aimImgArr.append(newImgArr)while True:line = fileData.readline()line = line[:-1]valueArray = line.split(",")if len(line) < 17:breakif  len(valueArray) >7 and len(valueArray[8]) ==0:newImgArr = []newImgArr.append(valueArray[9]+'.png')#图片名字newImgArr.append('ffffff' if valueArray[14]=='无' else valueArray[14])#颜色blackWhiteAlphaStr = valueArray[15].decode('utf8');bwStr = blackWhiteAlphaStr[0:1].encode('utf8')# print bwStr + ('Ture' if bwStr == '黑' else 'False')bwNum = '0'if bwStr == '黑':bwNum = blackWhiteAlphaStr[1:]elif bwStr == '白':bwNum = '-'+blackWhiteAlphaStr[1:]newImgArr.append(bwNum)#黑白alphanewImgArr.append(valueArray[11])#global层级newImgArr.append(valueArray[12])#part层级newImgArr.append('true' if valueArray[13]=='是' else 'false')#是否是润色层newImgArr.append(valueArray[16])#定位aimImgArr.append(newImgArr)else:needReadNewLine = FalsehaveSameKey = FalsebreaknewMap['aimImg'] = aimImgArr# FuZhuangMap['ShangZhuang'].append(newMap)getPartMap(partName)[partKeyName].append(newMap)raise getoutofloop()except getoutofloop:passif haveSameKey:needReadNewLine = True# print json.dumps(FaXingMap)# print json.dumps(FuZhuangMap)# print json.dumps(XieWaMap)# print json.dumps(PeiShiMap)print json.dumps(MeiRongMap)# print json.dumps(HuanJingMap)