# -*- coding: utf-8 -*- # 这段代码主要的功能是把excel表格转换成python可用的列表和字典数据 import sys import xlrd #http://pypi.python.org/pypi/xlrd import json import time from datetime import datetime from xlrd import xldate_as_tuple class NameFlag: master='~' main='*' Type='#' error='!' class SplitFlag: flag1='|' flag2=':' class SheetType: # 普通表 # 输出JSON ARRAY NORMAL = 0 # 有主外键关系的主表 # 输出JSON MAP MASTER = 1 # 有主外键关系的附表 # 输出JSON MAP SLAVE = 2 # 支持的数据类型 class DataType: Int= 'int' Float='float' STRING= 'str' BOOL= 'bool' DATE= 'date' Obj='obj' ARRAY= '[]' DIC= '{}' UNKNOWN= 'unknown' class HeadSetting: name='' Type=DataType.UNKNOWN index=0 param=None def __init__(self,name,Type,index): self.name=name self.Type=Type self.index=index class SheetInfo: name='' sheetName='' Type = SheetType.NORMAL dataType=DataType.ARRAY idHead=HeadSetting('',DataType.UNKNOWN,0) masterHead=HeadSetting('',DataType.UNKNOWN,0) slaves= [] head= [] sheet={} table=[] masterCols=[] def __init__(self,name): self.name=name self.sheetName=name self.Type=SheetType.NORMAL self.dataType=DataType.ARRAY self.idHead=None self.masterHead=None self.slaves=[] self.head=[] self.sheet={} self.table=[] self.masterCols=[] class ExcelInfo: sheetInfos={'':SheetInfo('')} headRow=0 Round=True ignoreEmpty=True def __init__(self,excelName,hr,r,i): self.sheetInfos={} self.headRow=hr self.Round=r self.ignoreEmpty=i self.setupSheetInfos(xlrd.open_workbook(excelName)) self.parseSheetInfos() pass #获取最终所有可用表单 def FinalTable(self): table={} for key in self.sheetInfos: sheetInfo=self.sheetInfos[key] if sheetInfo.Type==SheetType.SLAVE: continue if sheetInfo.idHead.Type==DataType.DIC: table[sheetInfo.name]=sheetInfo.sheet else: table[sheetInfo.name]=sheetInfo.table return table #处理表单父子关系 def parseSheetInfos(self): if self.sheetInfos==None: pass for sheetInfo in self.sheetInfos.values(): if sheetInfo.masterHead==None or sheetInfo.masterHead.name not in self.sheetInfos: if sheetInfo.slaves!=None and len(sheetInfo.slaves)>0: sheetInfo.Type=SheetType.MASTER continue if sheetInfo.dataType==DataType.DIC: for r in range(0,len(sheetInfo.table)): masterRow=self.sheetInfos[sheetInfo.masterHead.name].sheet[sheetInfo.masterCols[r]] if sheetInfo.name not in masterRow: masterRow[sheetInfo.name]={} idHead=sheetInfo.table[r][sheetInfo.idHead.name] masterRow[sheetInfo.name][idHead]=sheetInfo.table[r] elif sheetInfo.dataType==DataType.Obj: for r in range(0,len(sheetInfo.table)): idHead=sheetInfo.table[r][sheetInfo.idHead.name] self.sheetInfos[sheetInfo.masterHead.name].sheet[sheetInfo.masterCols[r]][idHead]=sheetInfo.table[r] else: for r in range(0,len(sheetInfo.table)): masterRow=self.sheetInfos[sheetInfo.masterHead.name].sheet[sheetInfo.masterCols[r]] if sheetInfo.name not in masterRow: masterRow[sheetInfo.name]=[] masterRow[sheetInfo.name].append(sheetInfo.table[r]) # 预处理表单数据 def setupSheetInfos(self, workbook): self.sheetInfos = {} sheetNames=workbook.sheet_names() for index in range(len(sheetNames)): sheet_name=sheetNames[index] if sheet_name[0]==NameFlag.error: continue sheetInfo=SheetInfo(sheet_name) sheetInfo.sheetName=sheet_name if sheet_name.count(NameFlag.master) > 0: pair=sheet_name.split(NameFlag.master) sheetInfo.name=pair[0].strip() sheetInfo.Type=SheetType.SLAVE sheetInfo.masterHead=HeadSetting(pair[1].strip(),DataType.UNKNOWN,0) if sheetInfo.name.count('#') > 0: pair=sheetInfo.name.split('#') sheetInfo.dataType=pair[1] sheetInfo.name=pair[0] self.sheetInfos[sheetInfo.name]=sheetInfo row=self.headRow - 1 #初始化表单数据 for sheetInfo in self.sheetInfos.values(): if sheetInfo.Type==SheetType.SLAVE: self.sheetInfos[sheetInfo.masterHead.name].slaves.append(sheetInfo.name) sheet=workbook.sheet_by_name(sheetInfo.sheetName) cols = sheet.ncols for col in range(0,cols): cell=sheet.cell_value(row,col) ctype=sheet.cell(row,col).ctype if ctype==0 or cell[0]==NameFlag.error: continue head_setting=HeadSetting(cell,DataType.UNKNOWN,col) if cell[0]==NameFlag.master: if sheetInfo.masterHead!=None: sheetInfo.masterHead.Type=cell[1:len(cell)] sheetInfo.masterHead.index=col continue elif cell[0]==NameFlag.main: sheetInfo.idHead=head_setting cell=cell[1:len(cell)] head_setting.name=cell if cell.count(NameFlag.Type) != 0: pair=cell.split(NameFlag.Type) head_setting.name=pair[0].strip() head_setting.Type=pair[1].strip() if len(pair)>2: head_setting.param=pair[2].strip() sheetInfo.head.append(head_setting) if sheetInfo.idHead==None and sheetInfo.head!=None and len(sheetInfo.head)>0: sheetInfo.idHead=sheetInfo.head[0] sheetInfo.table = [] sheetInfo.sheet = {} for i_row in range(self.headRow,sheet.nrows): self.parseRow(sheet, i_row, sheetInfo) for sheetInfo in self.sheetInfos.values(): if sheetInfo.Type!=SheetType.SLAVE and len(sheetInfo.slaves)>0: sheetInfo.Type=SheetType.MASTER def Int(self,num): if self.Round: num=float(num) if num>0: return int(num+0.5) else: return int(num-0.5) return int(float(num)) def unknownValue(self,num,ctype): if ctype == 2 and num % 1 == 0: # 如果是整形 return self.Int(num) elif ctype == 3: # 转成datetime对象 date = datetime(*xldate_as_tuple(num, 0)) return date.strftime('%Y/%d/%m %H:%M:%S') elif ctype == 4: return False if num == 0 else True else: return num # 解析一行 def parseRow(self,sheet,rowIndex, sheetInfo) : if sheetInfo.head==None or len(sheetInfo.head)==0: return result = {} if sheetInfo.masterHead!=None: cell =sheet.cell_value(rowIndex,sheetInfo.masterHead.index) #cell = row[sheetInfo.masterHead.index] sheetInfo.masterCols.append(cell) headIndex=0 if sheetInfo.idHead!=None: headIndex=sheetInfo.idHead.index for i in range(0,len(sheetInfo.head)): name = sheetInfo.head[i].name if name[0]=='!': continue Type = sheetInfo.head[i].Type param=sheetInfo.head[i].param index= sheetInfo.head[i].index cell =sheet.cell_value(rowIndex,index) #cell = row[index] if cell == None: if self.ignoreEmpty==False: result[name] = None continue #ctype： 0 empty,1 string, 2 number, 3 date, 4 boolean, 5 error ctype=sheet.cell(rowIndex,index).ctype if Type==DataType.UNKNOWN: # number string boolean cell=self.unknownValue(cell,ctype) if cell=='' and self.ignoreEmpty: continue elif Type==DataType.DATE: # 转成datetime对象 date = datetime(*xldate_as_tuple(cell, 0)) cell = date.strftime('%Y/%d/%m %H:%M:%S') elif Type==DataType.Int: if ctype == 2 : cell = self.Int(cell) else: Warning("type error at [%s,%s] ,%s is not a Int"%(rowIndex,index,cell)) elif Type==DataType.Float: if ctype == 2: # 如果是数值 if param!=None: r=self.Int(param) r=pow(10,r) cell=cell*r cell=self.Int(cell) cell=cell/r else: Warning("type error at [%s,%s] ,%s is not a Float"%(rowIndex,index,cell)) elif Type==DataType.STRING: cell=str(cell) if cell=='': continue elif Type==DataType.BOOL: cell = False if cell == 0 else True elif Type==DataType.Obj: if index==headIndex: result[name]=str(cell) continue if ctype==1: temp=cell.split(SplitFlag.flag1) if len(temp)>0: for value in temp: tp=value.split(SplitFlag.flag2) ##if len(tp)==0: ## result["数据格式不对"]=str(cell) if len(tp)==1: result[tp[0]]="" elif len(tp)>1: result[tp[0]]=cell[len(tp[0])+1:len(value)] continue elif Type==DataType.ARRAY: if ctype==1: if cell=='': continue temp=cell.split(SplitFlag.flag1) if len(temp)>0: cell=temp else: temp.append(cell) cell=temp else: temp=self.unknownValue(cell,ctype) if temp=='': continue cell=[temp] elif Type==DataType.DIC: if index==headIndex: result[name]=str(cell) continue cl={} if ctype==1: temp=cell.split(SplitFlag.flag1) if len(temp)>0: for value in temp: tp=value.split(SplitFlag.flag2) if len(tp)==1: cl[tp[0]]="" elif len(tp)>1: cl[tp[0]]=tp[1] if len(cl)>0: cell=cl ##else: ## cell={"数据格式不对":cell} else : print("无法识别的类型:[%s,%s],%s,%s"%(rowIndex,index,cell,type(cell))) result[name] = cell sheetInfo.table.append(result) if sheetInfo.idHead!=None: if sheetInfo.idHead.name in result: sheetInfo.sheet[result[sheetInfo.idHead.name]]=result elif len(result)>0: sheetInfo.sheet[result[list(result)[0]]]=result