class py2excel(object):
"""
"""
def __init__(self, pyfile, sourcefile, dstfile):
"""
pyfile:py, sourcefile:source excel, excel:dest excel
"""
self.pyfile = os.path.abspath(pyfile)
if sourcefile == '':
self.sourcefile = sourcefile
else:
self.sourcefile = os.path.abspath(sourcefile)
self.dstfile = os.path.abspath(dstfile)
self.xlsx = None
self.xbook = None
self.sheetCNames = {}
self.sheetENames = {}
self.mapSheet = {}
####################导入py文件#######################
def importPyModule(self):
"""
import self.pyfile as python module
"""
self.pyModule = None
try:
sys.path.append(PY_MODULE_PATH)
except NameError:
pass
#try:
pyPath, filename= os.path.split(self.pyfile)
pypos = filename.strip().rfind(".py")
if pypos < 0:
print( "pypypypypypypypy")
else:
filename = filename[:pypos]
sys.path.append(pyPath)
#try:
self.pyModule = __import__(filename)
#except:
#print( 'import %s' %(self.pyfile))
#sys.exit(1)
sys.path.pop(-1)
sys.path.pop(-1)
def getSheetNameFromModule(self):
if hasattr(self.pyModule, 'allDatas'):
return self.pyModule.allDatas
else:
return None
############################从策划表读取信息#######################################
def openXlsx(self):
if xlsxtool.checkExtName(self.sourcefile, '.xlsx') or xlsxtool.checkExtName(self.sourcefile, ".xls"):
self.xbook = ExcelTool(self.sourcefile)
if not self.xbook.getWorkbook():
print( "打开文件失败" )
return
self.xlsx = self.xbook.getXLSX()
def getSheetCNames(self):
allDatas = self.getSheetNameFromModule()
sheetCNames = {}
for index in range(1, self.xbook.getSheetCount() + 1):
sheetName = self.xbook.getSheetNameByIndex(index)
if sheetName.startswith(EXPORT_PREFIX_CHAR):
if allDatas is None:
sheetCNames[index] = sheetName
elif sheetName[1:].encode("utf-8") in allDatas: #py文件编码认为是utf-8
sheetCNames[index] = sheetName
if len(sheetCNames) == 0:
print( 'no sheet' )
self.xbook.close()
sys.exit(1)
if allDatas is None and len(sheetCNames) > 1: #这个主要处理，没有allDatas的时候
for k,v in sheetCNames.iteritems():
print( "%d:%s"%(k,v) )
while True:
ii = raw_input('input your choice:')
try:
ii = int(ii)
except:
continue
if ii > 0 and ii < len(sheetCNames):
print( sheetCNames[ii] )
self.sheetCNames[ii] = sheetCNames[ii]
break
else:
self.sheetCNames = sheetCNames
def readXlsxHeader(self):
"""
读取中英文对照
"""
if self.xlsx is None:
print( "no file opened" )
self.names = {} #sn:表的中文名字,engName，chnName:字典key的英文(中文)名字，
for si, sn in self.sheetCNames.iteritems(): #chinese name of sheetname, sheetindex
sheet = Sheet(self.xbook, si)
self.names[sn] = {}
tmpEInt = 1
tmpCInt = 1
for (engStruct, chnName) in zip(sheet.getRowValues(EXPORT_DEFINE_ROW -1), sheet.getRowValues(EXPORT_DEFINE_ROW)):
if engStruct.find('['):
engName = engStruct[:engStruct.find('[')]
else:
engName = 'undefineE_%d'%(tmpEInt,)
tmpEInt += 1
if chnName is None:
chnName = 'undefineC_%d'%(tmpCInt,)
tmpCInt += 1
self.names[sn][engName] = chnName
self.sheet = None
self.xbook.close() #覆盖的时候这是必须的
self.xbook = None
return self.names
def writeNewXlsx(self):
"""
py的字典写入到xlsx
"""
def getWorkbook():
dirs, filename = os.path.split(self.dstfile)
if not os.path.isdir(dirs):
os.makedirs(dirs)
return ExcelTool(self.dstfile)
if self.xbook is not None:
self.xbook.close()
self.xbook = None
self.xbook = getWorkbook()
if os.path.isfile(self.dstfile):
self.xbook.getWorkbook(forcedClose = True)
if self.xbook.getXApp().Workbooks.Count == 0:
newWB = self.xbook.getXApp().Workbooks.Add()
newWB.SaveAs(self.dstfile)
newWB.Close()
if self.xbook.getXLSX() is None:
if not self.xbook.getWorkbook(forcedClose =True):
print( "unknow error" )
return
if self.sourcefile != '':
self.writeXlsxWithC()
else:
self.writeXlsxWithoutC() #没有中文
def writeXlsxWithoutC(self): #没有中文
self.parseWriteSheet('datas')
data = None
if hasattr(self.pyModule, 'datas'):
data = self.pyModule.datas
if data is None:
return
headerKeys = self.getHeaderKeys(data)
self.newSheet = self.getWriteSheet('datas')
self.writeXlsxHeader(headerKeys)
self.writeData2Cells(data, headerKeys)
self.xbook.close(saveChanges = True)
def writeXlsxWithC(self): #有中文的文件
cnames = self.names.keys()
self.parseWriteSheet(cnames)
for cname, e2cDict in self.names.iteritems():
self.newSheet = self.getWriteSheet(cname)
self.newSheet.UsedRange = None #清空表的内容
data = None
if self.getSheetNameFromModule() is not None:
if cname[1:].encode("utf-8") not in self.getSheetNameFromModule():
continue
else:
data = self.getSheetNameFromModule()[cname[1:].encode("utf-8")]
elif hasattr(self.pyModule, 'datas'):
data = self.pyModule.datas
if data is None or not isinstance(data, dict):
continue
headerKeys = self.getHeaderKeys(data)
headerCNames = []
for p, he in enumerate(headerKeys):
cname = e2cDict.get(he, "py_%s"%(str(he),))
headerCNames.append(cname)
self.writeXlsxHeader(headerCNames)
self.writeData2Cells(data, headerKeys)
self.xbook.close(saveChanges = True)
def writeXlsxHeader(self, headerCNames):
"""
写到导出xlsx的第一行
"""
for pos, cn in enumerate(headerCNames): #ANSI编码
self.newSheet.Cells(1, pos+1).Value = cn
def writeData2Cells(self, data, headerKeys):
"""
字典的数据写入到excel中
"""
if self.newSheet is None:
return
for vp, v in enumerate(data.itervalues()): #value include key
for p, he in enumerate(headerKeys):
text = self.convertType(v.get(he, ''))
self.newSheet.Cells(vp+2, p+1).Value = text
return
def getHeaderKeys(self, data):
headerKeys = []
for v in data.itervalues(): #{1111:{'key':values,,}}
for vk in v.keys():
if vk not in headerKeys:
headerKeys.append(vk)
return headerKeys
def getWriteSheet(self, cname):
"""
从workbook选取所要写入数据的sheet
"""
if cname in self.repeatUse:
newSheet = self.xbook.getSheetByIndex(self.repeatUse.pop(cname))
elif len(self.useless) > 0:
newSheet = self.xbook.getSheetByIndex(self.useless.pop(-1))
newSheet.Name = cname
else:
newSheet = self.xbook.getXLSX().Sheets.Add()
newSheet.Name = cname
return newSheet
def parseWriteSheet(self, cnames):
"""
对即将要写的表做一些分析，保证一些表依旧存在
"""
self.repeatUse = {} #表需要覆盖
self.useless = [] #这些表被看做无用，需要新表的时候从这里取
for index in range(1, self.xbook.getSheetCount()+1):
name = self.xbook.getSheetNameByIndex(index)
if name in cnames:
self.repeatUse[name] = index
else:
self.useless.append(index)
return
def convertType(self, val):
"""
类型转换
"""
if isinstance(val, str):
return val.decode("utf-8")
elif isinstance(val, (dict, list, tuple)):
return xlsxtool.value_to_text(val)
return val
def run(self):
self.importPyModule()
if self.sourcefile != '':
self.openXlsx()
self.getSheetCNames()
self.readXlsxHeader()
self.writeNewXlsx()
def Orderbackup(self):
ex = ExcelTool()
path = 'D://code/Orders.xls'
sheet_name = 'Orders'
value_title = [["Start time","start region","Used","Price","End time","End eegion"]]
ex.WriteExcle(path,sheet_name,value_title)
ex.WriteExcAp(path,self.D_backup)
def openXlsx(self):
if xlsxtool.checkExtName(self.sourcefile, '.xlsx') or xlsxtool.checkExtName(self.sourcefile, ".xls"):
self.xbook = ExcelTool(self.sourcefile)
if not self.xbook.getWorkbookEx():
print( "打开文件失败" )
return
def openXlsx(self): if xlsxtool.checkExtName(self.sourcefile, '.xlsx') or xlsxtool.checkExtName(self.sourcefile, ".xls"): self.xbook = ExcelTool(self.sourcefile) if not self.xbook.getWorkbook(): print( "打开文件失败" ) return self.xlsx = self.xbook.getXLSX()
def __initXlsx(self): self.xbook = ExcelTool(self.infile) while not self.xbook.getWorkbook(forcedClose = True): xlsxtool.exportMenu(EXPORT_INFO_RTEXCEL, OCallback = self.resetXlsx)
class xlsx2py(object):
"""
将excel数据导出为py文件 使用过程需要进行编码转换
"""
def __init__(self, infile, outfile):
sys.excepthook = xlsxError.except_hook #traceback处理,希望输出中文
self.infile = os.path.abspath(infile) #暂存excel文件名
self.outfile = os.path.abspath(outfile) #data文件名
return
def __initXlsx(self):
self.xbook = ExcelTool(self.infile)
while not self.xbook.getWorkbook(forcedClose = True):
xlsxtool.exportMenu(EXPORT_INFO_RTEXCEL, OCallback = self.resetXlsx)
def resetXlsx(self):
"""
输入O(other)的回调
关闭已打开的excel，然后重新打开
"""
self.xbook.getWorkbook(forcedClose = True)
def __initInfo(self):
if hasattr(self, "mapIndex"):
del self.mapIndex
self.__exportSheetIndex = [] #存储可导表的索引
self.headerDict = {} #导出表第一行转为字典
self.mapDict = {} #代对表生成的字典(第一行是代对表说明忽略)
self.g_dctDatas = {}
self.g_fdatas = {}
EXPORT_GLOBAL_HEAD_DATA['data'] = {}
#####################执行主题##########################
def run(self):
"""
带有$的列数据需要代对表,首先生成代对字典
"""
self.__initXlsx() #初始excel相关
self.__initInfo() #初始导表相关
if self.sth4Nth(): #进入下一个阶段
self.openFile()
self.constructMapDict() #生成代对字典
self.__onRun()
def __onRun(self):
self.writeLines = 0 #记录已写入的excel的行数
self.parseDefineLine() #分析文件
###############寻找代对表和标记导入的表##################
def sth4Nth(self):
"""
something for nothing, 代对表和导入表需要有
"""
for index in range(1, self.xbook.getSheetCount() + 1):
sheetName = self.xbook.getSheetNameByIndex(index)
if sheetName == EXPORT_MAP_SHEET:
self.__onFindMapSheet(index)
if sheetName.startswith(EXPORT_PREFIX_CHAR_ALL): #默认导出
self.__onFindExportSheet(index)
elif sheetName.startswith(EXPORT_PREFIX_CHAR_CLIENT) and EXPORT_PLARFORM == EXPORT_PLARFORM_CLIENT : #导出客户端专属表
self.__onFindExportSheet(index)
elif sheetName.startswith(EXPORT_PREFIX_CHAR_SERVER) and EXPORT_PLARFORM == EXPORT_PLARFORM_SERVER : #导出服务器专属表
self.__onFindExportSheet(index)
return self.onSth4Nth()
def onSth4Nth(self):
"""
"""
if not hasattr(self, 'mapIndex'):
self.xlsxClear(EXPORT_ERROR_NOMAP)
if len(self.__exportSheetIndex) == 0:
self.xlsxClose()
return False
#xlsxError.error_input(EXPORT_ERROR_NOSHEET)
return True
def __onFindMapSheet(self, mapIndex):
self.mapIndex = mapIndex
return
def __onFindExportSheet(self, Eindex):
"""
完毕
"""
self.__exportSheetIndex.append(Eindex)
def constructMapDict(self):
"""
生成代对字典， 代对表只有一个
"""
mapDict = {}
sheet = self.xbook.getSheetByIndex(self.mapIndex)
if not sheet:
return
for col in range(0, self.xbook.getRowCount(self.mapIndex)):
colValues = self.xbook.getColValues(sheet, col)
if colValues:
for v in [e for e in colValues[MAP_DATA_ROW-1:] if e[0] and isinstance(e[0], str) and e[0].strip()]:
print (v)
mapStr = v[0].replace('：', ":") #中文"："和":"
try:
k, v = mapStr.split(":")
k = str.strip(k)
v = str.strip(v)
mapDict[k] = v
except Exception as errstr:
print( "waring：需要检查代对表 第%d列, err=%s"%(col , errstr))
self.__onConstruct(mapDict)
return
def __onConstruct(self, mapDict):
"""
代对字典生成完毕
"""
self.mapDict = mapDict
return
#####################文件头检测#######################
def parseDefineLine(self):
self.__checkDefine() #检查定义是否正确
self.__checkData() #检查数据是否符合规则
def __reCheck(self, head):
pattern = "(\w+)(\[.*])(\[\w+\])"
reGroups =re.compile(pattern).match(head)
if not reGroups:
return ()
return reGroups.groups()
def __convertKeyName(self, name):
#避免语言差异，变量名始终使用字符串
# try:
# tname = eval(name)
# except:
# pass
# else:
# if type(tname) == int or type(tname) == float:
# return tname
return name
def __checkDefine(self):
"""
第一行的个元素是否符合定义格式"name[signs][func]"以及key是否符合规定
"""
print( "检测文件头(第一行)是否正确" )
for index in self.__exportSheetIndex:
self.sheetKeys = []
headList = self.xbook.getRowValues(self.xbook.getSheetByIndex(index), EXPORT_DEFINE_ROW -1 )
enName = [] #检查命名重复临时变量
reTuples = []
self.headerDict[index] = {}
for c, head in enumerate(headList):
if head is None or head.strip() == '': #导出表的第一行None, 则这一列将被忽略
self.__onCheckSheetHeader(self.headerDict[index], c, None)
continue
reTuple = self.__reCheck(head)
if len(reTuple) == 3: #定义被分拆为三部分:name, signs, func,signs可以是空
name, signs, funcName = reTuple[0], reTuple[1][1:-1], reTuple[2][1:-1]
funcName = funcName +"Func" #修正函数名称
name = self.__convertKeyName(name)
for s in signs: #符号定义是否在规则之内
if s not in EXPORT_ALL_SIGNS:
self.xlsxClear(EXPORT_ERROR_NOSIGN, (EXPORT_DEFINE_ROW, c+1))
if EXPORT_SIGN_GTH in signs: #是否为key
self.sheetKeys.append(c)
if len(self.sheetKeys) > EXPORT_KEY_NUMS: #key是否超过规定的个数
self.xlsxClear(EXPORT_ERROR_NUMKEY, (EXPORT_DEFINE_ROW, c+1))
if name not in enName: #name不能重复
enName.append(name)
else:
self.xlsxClear(EXPORT_ERROR_REPEAT, \
(self.xbook.getSheetNameByIndex(index).encode(FILE_CODE), EXPORT_DEFINE_ROW, c+1))
if not hasFunc(funcName): #funcName是否存在
self.xlsxClear(EXPORT_ERROR_NOFUNC, (xlsxtool.toGBK(funcName), c+1))
else:
self.xlsxClear(EXPORT_ERROR_HEADER, (self.xbook.getSheetNameByIndex(index).encode(FILE_CODE), EXPORT_DEFINE_ROW, c+1))
bExportClient = EXPORT_SIGN_CLIENT in signs
bExportServer = EXPORT_SIGN_SERVER in signs
if EXPORT_PLARFORM == EXPORT_PLARFORM_CLIENT and bExportClient : #定义了'c'
self.__onCheckSheetHeader(self.headerDict[index], c, (name, signs, funcName))
elif EXPORT_PLARFORM == EXPORT_PLARFORM_SERVER and bExportServer : #定义了's'
self.__onCheckSheetHeader(self.headerDict[index], c, (name, signs, funcName))
elif not bExportClient and not bExportServer : #未定义'cs'
self.__onCheckSheetHeader(self.headerDict[index], c, (name, signs, funcName))
else : #忽略此列
self.__onCheckSheetHeader(self.headerDict[index], c, None)
self.__onCheckDefine()
return
def __onCheckSheetHeader(self, DataDict, col, headerInfo):
DataDict[col] = headerInfo
def __onCheckDefine(self):
if len(self.sheetKeys) != EXPORT_KEY_NUMS: #key也不能少
self.xlsxClear(EXPORT_ERROR_NOKEY, ("需要%d而只有%d"%(EXPORT_KEY_NUMS,len(self.sheetKeys))))
print( "文件头检测正确", time.ctime(time.time()) )
def sheetIndex2Data(self):
self.sheet2Data = {}
for index in self.__exportSheetIndex:
dataName = self.getSheetDataName(index)
if dataName in self.sheet2Data:
self.sheet2Data[dataName].append(index)
else:
self.sheet2Data[dataName] = [index]
def __checkData(self):
"""
列数据是否符合命名规范, 生成所需字典
"""
self.sheetIndex2Data()
self.dctDatas = self.g_dctDatas
self.hasExportedSheet = []
for dataName, indexList in self.sheet2Data.items():
self.curIndexMax = len(indexList)
self.curProIndex = []
for index in indexList:
sheet = self.xbook.getSheetByIndex(index)
self.curProIndex.append(index)
cols = self.xbook.getRowCount(index)
rows = self.xbook.getColCount(index)
if dataName not in self.dctDatas:
self.dctDatas[dataName] = {}
self.dctData = self.dctDatas[dataName]
for row in range(EXPORT_DATA_ROW, rows + 1):
rowval = self.xbook.getRowValues(sheet, row - 1)
childDict = {}
for col in range(1, cols + 1):
val = rowval[col - 1]
if val != None:
val = (str(rowval[col - 1]),)
else:
val = ("",)
#val = (self.xbook.getText(sheet, row, col),)
if self.headerDict[index][col-1] is None:
continue
name, sign, funcName = self.headerDict[index][col-1]
if EXPORT_SIGN_DOLLAR in sign and len(val[0]) > 0:
self.needReplace({'v':val[0], "pos":(row, col)})
v = self.mapDict[xlsxtool.GTOUC(xlsxtool.val2Str(val[0]))] #mapDict:key是unicode.key都要转成unicode
else:
v = val[0]
if EXPORT_SIGN_DOT in sign and (v is None or len(v) == 0) :
self.xlsxClear(EXPORT_ERROR_NOTNULL, (col, row))
try:
sv = v#xlsxtool.toGBK(v)
except:
sv = v
func = getFunc(funcName)
try:
v = func(self.mapDict, self.dctData, childDict, sv)
except Exception as errstr:
self.xlsxClear(EXPORT_ERROR_FUNC, (errstr, funcName, sv, 'row:'+str(row), 'col:'+str(col) ))
for ss in sign.replace('$',''):
if EXPORT_SIGN[ss] != None :
EXPORT_SIGN[ss](self,{"v":v,"pos":(row, col)})
#if isinstance(v, (isinstance, unicode)):
# try:
# v = v.decode("gb2312").encode("utf-8")
# except:
# pass
childDict[name] = v
print( "当前:%i/%i" % (row, rows) )
self.dctData[self.tempKeys[-1]] = copy.deepcopy(childDict)
self.checkGlobalDef()
#如果有最终检查处理函数，则调用检查
overFunc = self.mapDict.get('overFunc')
if overFunc is not None and hasFunc(overFunc):
func = getFunc(overFunc)
self.dctData = func(self.mapDict, self.dctDatas, self.dctData, dataName)
self.dctDatas[dataName] = self.dctData
self.g_dctDatas.update(self.dctDatas)
self.__onCheckSheet()
self.__onCheckData()
self.writeFoot()
def __onCheckSheet(self):
if hasattr(self, "tempKeys"):
del self.tempKeys
return
def __onCheckData(self):
self.exportSheet()
##############符号字典的相关设置EXPORT_SIGN###################
def isNotEmpty(self, cellData):
if cellData['v'] is None:
self.xlsxClear(EXPORT_ERROR_NOTNULL, (cellData['pos'], ))
def needReplace(self, cellData):
"""宏替代"""
v = cellData["v"].strip()
if isinstance(v, float): #防止数字报错(1:string) mapDict 是unicode字符串
v = str(int(v))
if v not in self.mapDict: #检测而不替换
self.xlsxClear(EXPORT_ERROR_NOTMAP, (cellData['pos'], v))
def isKey(self, cellData):
if not hasattr(self, "tempKeys"):
self.tempKeys = []
if cellData['v'] not in self.tempKeys:
self.tempKeys.append(cellData['v'])
else:
self.xlsxClear(EXPORT_ERROR_REPKEY, (cellData['pos'], \
(self.tempKeys.index(cellData['v'])+3, cellData['pos'][1] ), cellData['v']) )
###############export to py部分######################
def exportSheet(self):
"""
导出
"""
self.__onExportSheet()
return
def __onExportSheet(self):
"""
数据转成py文件
"""
self.writeXLSX2PY()
return
def openFile(self):
"""
文件目录创建
"""
dirPath = os.path.split(self.outfile)[0]
if not os.path.isdir(dirPath):
try:
xlsxtool.createDir(dirPath)
except:
self.xlsxClear(EXPORT_ERROR_CPATH, (dirPath, ))
try:
fileHandler = codecs.open(self.outfile, "w+",'utf-8')
#fileHandler = open(self.outfile, "w+")
except:
self.xlsxClear(EXPORT_ERROR_FILEOPEN, (self.outfile, ))
self.__onOpenFile(fileHandler) #目录创建成功,文件打开
return
def __onOpenFile(self, fileHandler):
"""
py文件打开了,可以写文件了
"""
self.fileName = self.outfile
self.fileHandler = fileHandler
del self.outfile
def xlsxWrite(self, stream):
"""
写入data文件
"""
if not hasattr(self, "fileHandler"):
self.xlsxClear(EXPORT_ERROR_FILEOPEN, ())
try:
self.fileHandler.write(stream)
except Exception as errstr:
self.xlsxClear(EXPORT_ERROR_IOOP, (errstr))
def writeXLSX2PY(self):
"""
文件 前几行文字
"""
self.writeBody()
return
def getSheetDataName(self,sheetIndex):
try:
SheetName = self.xbook.getSheetNameByIndex(sheetIndex)
except:
self.xlsxClear(EXPORT_ERROR_SHEET_NAME)
sheetName = SheetName[1:]
if sheetName in self.mapDict:
return self.mapDict[sheetName]
else:
self.xlsxClear(EXPORT_ERROR_NOMAP, (sheetName.encode(FILE_CODE),))
return ""
def checkGlobalDef(self):
# SheetName = self.getSheetDataName(self.curProIndex[-1])
# dataFileInfo = (self.infile + '.' + SheetName).encode("UTF-8")
self.hasExportedSheet.append(self.curProIndex[-1])
if len(self.hasExportedSheet) <= 1:
globalDefs = self.mapDict.get('globalDefs', '') #获取全局定义数据
if len(globalDefs) > 0:
func = getFunc(globalDefs)
globalDefs = func(self.dctData)
if len(globalDefs) > 0:
globalDefs += "\n"
if "globalDefs" in self.g_fdatas:
self.g_fdatas["globalDefs"] += globalDefs
else:
self.g_fdatas["globalDefs"] = globalDefs
def writeBody(self):
#for index in self.curProIndex:
# xlsxError.info_input(EXPORT_INFO_ING, (self.xbook.getSheetNameByIndex(index).encode(FILE_CODE), ))
print( "开始写入文件:", time.ctime(time.time()) )
if EXPORT_LANG == EXPORT_LANG_PYTHON:
self.xlsxWrite(EXPORT_DATA_HEAD)
for key,val in EXPORT_GLOBAL_HEAD_DATA['data'].items():
self.xlsxWrite(val+'\n')
if len(EXPORT_GLOBAL_HEAD_DATA['data'])>0:
self.xlsxWrite('\n')
if "globalDefs" in self.g_fdatas:
self.xlsxWrite(self.g_fdatas["globalDefs"])
for dataName, datas in self.g_dctDatas.items():
if EXPORT_LANG == EXPORT_LANG_PYTHON:
stream = dataName + "="
stream += xlsxtool.dict_to_text_py(datas) + "\n"
#stream += "%s\n" % (datas)
self.xlsxWrite(stream)
elif EXPORT_LANG == EXPORT_LANG_LUA:
stream = dataName + "="
stream += xlsxtool.dict_to_text_lua(datas) + "\n"
self.xlsxWrite(stream)
else:
jsonhandle = codecs.open(EXPORT_FILENAME_NO_EXT + "." + dataName + ".json", "w+",'utf-8')
s = json.dumps(datas)
jsonhandle.write("{%s}" % (s[1:-1]))
jsonhandle.close()
def getNoteFlag(self,isEnd):
if EXPORT_LANG == EXPORT_LANG_PYTHON :
return '"""'
elif EXPORT_LANG == EXPORT_LANG_LUA :
if isEnd:
return '--]]'
else:
return '--[['
else :
return ''
def writeFoot(self):
"""
文件尾
"""
if len(self.hasExportedSheet) < len(self.__exportSheetIndex):
return
# allDataDefs = self.mapDict.get('allDataDefs', '')
# if len(allDataDefs) > 0:
# func = getFunc(allDataDefs)
# allDataDefs = func(self.dctData)
# if "allDataDefs" in self.g_fdatas:
# self.g_fdatas["allDataDefs"] += allDataDefs
# else:
# self.g_fdatas["allDataDefs"] = allDataDefs
stream = "\n"+self.getNoteFlag(False)
stream += "\nallDatas = {\n"
for dataName, indexList in self.sheet2Data.items():
for index in indexList:
SheetName = self.xbook.getSheetNameByIndex(index)
sheetName = SheetName[1:]
stream += "\t'" + sheetName
stream += "':"
stream += dataName
stream += ",\n"
# if len(allDataDefs) > 0:
# stream += "\t" + self.g_fdatas["allDataDefs"] + ",\n"
stream +="}"
stream += "\n"+self.getNoteFlag(True)
self.xlsxWrite(stream)
self.xlsxbyebye()
print( "写完了time:", time.ctime(time.time()) )
##############其他##################
def xlsxClose(self):
"""
关闭文档
"""
if hasattr(self, "fileHandler"):
self.fileHandler.close()
self.xbook.close()
return
def xlsxClear(self, errno = 0, msg = ''):
"""
程序异常退出清理打开的Excel
"""
self.xlsxClose()
if errno > 0:
raise xlsxError.xe(errno, msg)
else:
sys.exit(1)
def xlsxbyebye(self):
"""
正常退出
"""
self.xlsxClose()
return
def getSheetsCounts(self):
return reduce(lambda x,y:x+y, \
[self.xbook.getColCount(index) for index in self.__exportSheetIndex])
def __initXlsx(self): self.xbook = ExcelTool(self.infile) while not self.xbook.getWorkbook(forcedClose = True): xlsxtool.exportMenu(EXPORT_INFO_RTEXCEL, OCallback = self.resetXlsx)
class xlsx2py(object):
"""
将excel数据导出为py文件 使用过程需要进行编码转换
"""
def __init__(self, infile, outfile):
sys.excepthook = xlsxError.except_hook #traceback处理,希望输出中文
self.infile = os.path.abspath(infile) #暂存excel文件名
self.outfile = os.path.abspath(outfile) #data文件名
return
def __initXlsx(self):
self.xbook = ExcelTool(self.infile)
while not self.xbook.getWorkbook(forcedClose = True):
xlsxtool.exportMenu(EXPORT_INFO_RTEXCEL, OCallback = self.resetXlsx)
def resetXlsx(self):
"""
输入O(other)的回调
关闭已打开的excel，然后重新打开
"""
self.xbook.getWorkbook(forcedClose = True)
def __initInfo(self):
self.__exportSheetIndex = [] #存储可导表的索引
self.headerDict = {} #导出表第一行转为字典
self.mapDict = {} #代对表生成的字典(第一行是代对表说明忽略)
#####################执行主题##########################
def run(self):
"""
带有$的列数据需要代对表,首先生成代对字典
"""
self.__initXlsx() #初始excel相关
self.__initInfo() #初始导表相关
self.openFile()
self.sth4Nth() #进入下一个阶段
self.constructMapDict() #生成代对字典
self.__onRun()
def __onRun(self):
self.writeLines = 0 #记录已写入的excel的行数
self.parseDefineLine() #分析文件
###############寻找代对表和标记导入的表##################
def sth4Nth(self):
"""
something for nothing, 代对表和导入表需要有
"""
for index in range(1, self.xbook.getSheetCount() + 1):
sheetName = self.xbook.getSheetNameByIndex(index)
if sheetName == EXPORT_MAP_SHEET:
self.__onFindMapSheet(index)
if sheetName.startswith(EXPORT_PREFIX_CHAR):
self.__onFindExportSheet(index)
self.onSth4Nth()
def onSth4Nth(self):
"""
"""
if not hasattr(self, 'mapIndex'):
self.xlsxClear(EXPORT_ERROR_NOMAP)
if len(self.__exportSheetIndex) == 0:
xlsxError.error_input(EXPORT_ERROR_NOSHEET)
return
def __onFindMapSheet(self, mapIndex):
self.mapIndex = mapIndex
return
def __onFindExportSheet(self, Eindex):
"""
完毕
"""
self.__exportSheetIndex.append(Eindex)
def constructMapDict(self):
"""
生成代对字典， 代对表只有一个
"""
mapDict = {}
sheet = self.xbook.getSheetByIndex(self.mapIndex)
if not sheet:
return
for col in range(0, self.xbook.getRowCount(self.mapIndex)):
colValues = self.xbook.getColValues(sheet, col)
if colValues:
for v in [e for e in colValues[1:] if e[0] and isinstance(e[0], str) and e[0].strip()]:
print (v)
mapStr = v[0].replace('：', ":") #中文"："和":"
try:
k, v = mapStr.split(":")
k = str.strip(k)
v = str.strip(v)
mapDict[k] = v
except Exception as errstr:
print( "waring：需要检查代对表 第%d列, err=%s"%(col , errstr))
self.__onConstruct(mapDict)
return
def __onConstruct(self, mapDict):
"""
代对字典生成完毕
"""
self.mapDict = mapDict
return
#####################文件头检测#######################
def parseDefineLine(self):
self.__checkDefine() #检查定义是否正确
self.__checkData() #检查数据是否符合规则
def __reCheck(self, head):
pattern = "(\w+)(\[.*])(\[\w+\])"
reGroups =re.compile(pattern).match(head)
if not reGroups:
return ()
return reGroups.groups()
def __convertKeyName(self, name):
try:
tname = eval(name)
except:
pass
else:
if type(tname) == int or type(tname) == float:
return tname
return name
def __checkDefine(self):
"""
第一行的个元素是否符合定义格式"name[signs][func]"以及key是否符合规定
"""
print( "检测文件头(第一行)是否正确" )
for index in self.__exportSheetIndex:
self.sheetKeys = []
headList = self.xbook.getRowValues(self.xbook.getSheetByIndex(index), EXPORT_DEFINE_ROW -1 )
enName = [] #检查命名重复临时变量
reTuples = []
self.headerDict[index] = {}
for c, head in enumerate(headList):
if head is None or head.strip() == '': #导出表的第一行None, 则这一列将被忽略
self.__onCheckSheetHeader(self.headerDict[index], c, None)
continue
reTuple = self.__reCheck(head)
if len(reTuple) == 3: #定义被分拆为三部分:name, signs, func,signs可以是空
name, signs, funcName = reTuple[0], reTuple[1][1:-1], reTuple[2][1:-1]
name = self.__convertKeyName(name)
for s in signs: #符号定义是否在规则之内
if s not in EXPORT_ALL_SIGNS:
self.xlsxClear(EXPORT_ERROR_NOSIGN, (EXPORT_DEFINE_ROW, c+1))
if EXPORT_SIGN_GTH in signs: #是否为key
self.sheetKeys.append(c)
if len(self.sheetKeys) > EXPORT_KEY_NUMS: #key是否超过规定的个数
self.xlsxClear(EXPORT_ERROR_NUMKEY, (EXPORT_DEFINE_ROW, c+1))
if name not in enName: #name不能重复
enName.append(name)
else:
self.xlsxClear(EXPORT_ERROR_REPEAT, \
(self.xbook.getSheetNameByIndex(index).encode(FILE_CODE), EXPORT_DEFINE_ROW, c+1))
if not hasFunc(funcName): #funcName是否存在
self.xlsxClear(EXPORT_ERROR_NOFUNC, (xlsxtool.toGBK(funcName), c+1))
else:
self.xlsxClear(EXPORT_ERROR_HEADER, (self.xbook.getSheetNameByIndex(index).encode(FILE_CODE), EXPORT_DEFINE_ROW, c+1))
self.__onCheckSheetHeader(self.headerDict[index], c, (name, signs, funcName)) #定义一行经常使用存起来了
self.__onCheckDefine()
return
def __onCheckSheetHeader(self, DataDict, col, headerInfo):
DataDict[col] = headerInfo
def __onCheckDefine(self):
if len(self.sheetKeys) != EXPORT_KEY_NUMS: #key也不能少
self.xlsxClear(EXPORT_ERROR_NOKEY, ("需要%d而只有%d"%(EXPORT_KEY_NUMS,len(self.sheetKeys))))
print( "文件头检测正确", time.ctime(time.time()) )
def sheetIndex2Data(self):
self.sheet2Data = {}
for index in self.__exportSheetIndex:
SheetName = self.xbook.getSheetNameByIndex(index)
sheetName = SheetName[SheetName.find(EXPORT_PREFIX_CHAR)+1:]
if sheetName in self.mapDict:
dataName = self.mapDict[sheetName]
if dataName in self.sheet2Data:
self.sheet2Data[dataName].append(index)
else:
self.sheet2Data[dataName] = [index]
def __checkData(self):
"""
列数据是否符合命名规范, 生成所需字典
"""
self.sheetIndex2Data()
self.dctDatas = g_dctDatas
self.hasExportedSheet = []
for dataName, indexList in self.sheet2Data.items():
self.curIndexMax = len(indexList)
self.curProIndex = []
for index in indexList:
sheet = self.xbook.getSheetByIndex(index)
self.curProIndex.append(index)
cols = self.xbook.getRowCount(index)
rows = self.xbook.getColCount(index)
if dataName not in self.dctDatas:
self.dctDatas[dataName] = {}
self.dctData = self.dctDatas[dataName]
for row in range(3, rows + 1):
childDict = {}
for col in range(1, cols + 1):
val = (self.xbook.getText(sheet, row, col),)
if self.headerDict[index][col-1] is None:
continue
name, sign, funcName = self.headerDict[index][col-1]
if '$' in sign and len(val[0]) > 0:
self.needReplace({'v':val[0], "pos":(row, col)})
v = self.mapDict[xlsxtool.GTOUC(xlsxtool.val2Str(val[0]))] #mapDict:key是unicode.key都要转成unicode
else:
v = val[0]
if EXPORT_SIGN_DOT in sign and v is None:
self.xlsxClear(EXPORT_ERROR_NOTNULL, (col, row))
try:
sv = v#xlsxtool.toGBK(v)
except:
sv = v
func = getFunc(funcName)
try:
v = func(self.mapDict, self.dctData, childDict, sv)
except Exception as errstr:
self.xlsxClear(EXPORT_ERROR_FUNC, (errstr, funcName, sv, row, col))
for ss in sign.replace('$',''):
EXPORT_SIGN[ss](self,{"v":v,"pos":(row, col)})
#if isinstance(v, (isinstance, unicode)):
# try:
# v = v.decode("gb2312").encode("utf-8")
# except:
# pass
childDict[name] = v
print( "当前:%i/%i" % (row, rows) )
self.dctData[self.tempKeys[-1]] = copy.deepcopy(childDict)
self.writeHead()
overFunc = self.mapDict.get('overFunc')
if overFunc is not None:
func = getFunc(overFunc)
self.dctData = func(self.mapDict, self.dctDatas, self.dctData, dataName)
self.dctDatas[dataName] = self.dctData
g_dctDatas.update(self.dctDatas)
self.__onCheckSheet()
self.__onCheckData()
self.writeFoot()
def __onCheckSheet(self):
if hasattr(self, "tempKeys"):
del self.tempKeys
return
def __onCheckData(self):
self.exportSheet()
##############符号字典的相关设置EXPORT_SIGN###################
def isNotEmpty(self, cellData):
if cellData['v'] is None:
self.xlsxClear(EXPORT_ERROR_NOTNULL, (cellData['pos'], ))
def needReplace(self, cellData):
"""宏替代"""
v = cellData["v"].strip()
if isinstance(v, float): #防止数字报错(1:string) mapDict 是unicode字符串
v = str(int(v))
if v not in self.mapDict: #检测而不替换
self.xlsxClear(EXPORT_ERROR_NOTMAP, (cellData['pos'], v))
def isKey(self, cellData):
if not hasattr(self, "tempKeys"):
self.tempKeys = []
if cellData['v'] not in self.tempKeys:
self.tempKeys.append(cellData['v'])
else:
self.xlsxClear(EXPORT_ERROR_REPKEY, (cellData['pos'], \
(self.tempKeys.index(cellData['v'])+3, cellData['pos'][1] ), cellData['v']) )
###############export to py部分######################
def exportSheet(self):
"""
导出
"""
self.__onExportSheet()
return
def __onExportSheet(self):
"""
数据转成py文件
"""
self.writeXLSX2PY()
return
def openFile(self):
"""
文件目录创建
"""
dirPath = os.path.split(self.outfile)[0]
if not os.path.isdir(dirPath):
try:
xlsxtool.createDir(dirPath)
except:
self.xlsxClear(EXPORT_ERROR_CPATH, (dirPath, ))
try:
fileHandler = codecs.open(self.outfile, "w+",'utf-8')
#fileHandler = open(self.outfile, "w+")
except:
self.xlsxClear(EXPORT_ERROR_FILEOPEN, (self.outfile, ))
self.__onOpenFile(fileHandler) #目录创建成功,文件打开
return
def __onOpenFile(self, fileHandler):
"""
py文件打开了,可以写文件了
"""
self.fileName = self.outfile
self.fileHandler = fileHandler
del self.outfile
def xlsxWrite(self, stream):
"""
写入data文件
"""
if not hasattr(self, "fileHandler"):
self.xlsxClear(EXPORT_ERROR_FILEOPEN, ())
try:
self.fileHandler.write(stream)
except Exception as errstr:
self.xlsxClear(EXPORT_ERROR_IOOP, (errstr))
def writeXLSX2PY(self):
"""
文件 前几行文字
"""
self.writeBody()
return
def writeHead(self):
print( "开始写入文件:", time.ctime(time.time()) )
try:
SheetName = self.xbook.getSheetNameByIndex(self.curProIndex[-1])
except:
print( "获取表的名字出错" )
sheetName = SheetName[SheetName.find(EXPORT_PREFIX_CHAR)+1:]
if sheetName in self.mapDict:
dataName = self.mapDict[sheetName]
self.hasExportedSheet.append(self.curProIndex[-1])
else:
self.xlsxClear(2, (sheetName.encode(FILE_CODE),))
stream = ""
dataFileInfo = (self.infile + '.' + SheetName).encode("UTF-8")
if len(self.hasExportedSheet) <= 1:
stream = EXPORT_DATA_HEAD
globalDefs = self.mapDict.get('globalDefs', '')
if len(globalDefs) > 0:
func = getFunc(globalDefs)
globalDefs = func(self.dctData)
if len(globalDefs) > 0:
globalDefs += "\n"
if "globalDefs" in g_fdatas:
g_fdatas["globalDefs"] += globalDefs
else:
g_fdatas["globalDefs"] = globalDefs
def writeBody(self):
#for index in self.curProIndex:
# xlsxError.info_input(EXPORT_INFO_ING, (self.xbook.getSheetNameByIndex(index).encode(FILE_CODE), ))
self.xlsxWrite(EXPORT_DATA_HEAD)
if "globalDefs" in g_fdatas:
self.xlsxWrite(g_fdatas["globalDefs"])
for dataName, datas in g_dctDatas.items():
stream = dataName + "="
#stream += xlsxtool.dict_to_text(datas) + "\n"
stream += "%s\n" % (datas)
self.xlsxWrite(stream)
jsonhandle = codecs.open(self.fileHandler.stream.name + "." + dataName + ".json", "w+",'utf-8')
s = json.dumps(datas)
jsonhandle.write("{%s}" % (s[1:-1]))
jsonhandle.close()
def writeFoot(self):
"""
文件尾
"""
if len(self.hasExportedSheet) < len(self.__exportSheetIndex):
return
allDataDefs = self.mapDict.get('allDataDefs', '')
if len(allDataDefs) > 0:
func = getFunc(allDataDefs)
allDataDefs = func(self.dctData)
if "allDataDefs" in g_fdatas:
g_fdatas["allDataDefs"] += allDataDefs
else:
g_fdatas["allDataDefs"] = allDataDefs
stream = "\nallDatas = {\n"
for dataName, indexList in self.sheet2Data.items():
for index in indexList:
SheetName = self.xbook.getSheetNameByIndex(index)
sheetName = SheetName[SheetName.find(EXPORT_PREFIX_CHAR)+1:]
stream += "\t'" + sheetName
stream += "':"
stream += dataName
stream += ",\n"
if len(allDataDefs) > 0:
stream += "\t" + g_fdatas["allDataDefs"] + ",\n"
stream +="}"
self.xlsxWrite(stream)
self.xlsxbyebye()
print( "写完了time:", time.ctime(time.time()) )
##############其他##################
def xlsxClose(self):
"""
关闭文档
"""
if hasattr(self, "fileHandler"):
self.fileHandler.close()
self.xbook.close()
return
def xlsxClear(self, errno = 0, msg = ''):
"""
程序异常退出清理打开的Excel
"""
self.xlsxClose()
if errno > 0:
raise xlsxError.xe(errno, msg)
else:
sys.exit(1)
def xlsxbyebye(self):
"""
正常退出
"""
self.xlsxClose()
return
def getSheetsCounts(self):
return reduce(lambda x,y:x+y, \
[self.xbook.getColCount(index) for index in self.__exportSheetIndex])
def getWorkbook(): dirs, filename = os.path.split(self.dstfile) if not os.path.isdir(dirs): os.makedirs(dirs) return ExcelTool(self.dstfile)
def update_vfix (conf):
if not os.path.exists(conf['vfixmpx']):
print ("*APPLYING FIX")
t = ExcelTool (conf['vindexconf'], conf['lvlupmpx'], '..')
t.apply_fix (conf['vfixmpx'])
def getData(self):
ex = ExcelTool()
self.D = ex.readExcToInt(self.path)
return self.D
data/EM-SM/20200116/2-MPX/levelup.mpx #specific export
data/EM-SM/vindex.xslx #for multiple exports of the same object set
data/vindex-conf.json #for multiple object sets
The voc-index is supposed to be persistent over multiple exports. In the case
above there is one from January 11 and another from January 16. So a good place
for the vocabulary index file is in project root (as shown above).
XAMPLE CONF
{
"tasks":[
{"index":"./mpx:sammlungsobjekt/mpx:sachbegriff"},
{"index_with_attribute": ["./mpx:sammlungsobjekt/mpx:geogrBezug","bezeichnung"]},
{"attribute_index":["./mpx:sammlungsobjekt/mpx:geogrBezug/@bezeichnung" "verantwortlich"]}
]
}
"""
import sys
import os
sys.path.append (os.path.join(__file__,'../../lib'))
from ExcelTool import ExcelTool
if __name__ == "__main__":
import argparse
parser = argparse.ArgumentParser()
parser.add_argument('-c', '--conf', required=True)
parser.add_argument('-i', '--input', required=True)
parser.add_argument('-x', '--xlsdir', required=False)
args = parser.parse_args()
t=ExcelTool.from_conf (args.conf,args.input, args.xlsdir)
def __initXlsx(self):
self.xbook = ExcelTool(self.infile)
self.xbook.getWorkbook()
class xlsx2py(object):
"""
将excel数据导出为py文件 使用过程需要进行编码转换
targets = 'py|json|lua'
"""
def __init__(self, infile, outfile, targets):
sys.excepthook = xlsxError.except_hook # traceback处理,希望输出中文
self.infile = os.path.abspath(infile) # 暂存excel文件名
self.outfile = os.path.abspath(outfile) # data文件名
self.targets = targets
self.tempKeys = []
def __initXlsx(self):
self.xbook = ExcelTool(self.infile)
self.xbook.getWorkbook()
def __initInfo(self):
self.__exportSheetName = [] # 存储可导表的索引
self.headerDict = {} # 导出表第一行转为字典
self.mapDict = {} # 代对表生成的字典(第一行是代对表说明忽略)
def run(self):
"""
带有$的列数据需要代对表,首先生成代对字典
"""
self.__initXlsx() # 初始excel相关
self.__initInfo() # 初始导表相关
self.sth4Nth() # 进入下一个阶段
self.constructMapDict() # 生成代对字典
self.parseDefineLine() # 分析文件
# 寻找代对表和标记导入的表
def sth4Nth(self):
"""
something for nothing, 代对表和导入表需要有
"""
# 获得所有需要导出的表
for sheetName in self.xbook.getSheetNames():
if sheetName.startswith(config.EXPORT_PREFIX_CHAR):
self.__exportSheetName.append(sheetName)
# 检查是否有代对表
if not self.xbook.getSheetBySheetName(config.EXPORT_MAP_SHEET):
self.xlsxClear(config.EXPORT_ERROR_NOMAP)
# 检查导出表数量
if len(self.__exportSheetName) == 0:
xlsxError.error_input(config.EXPORT_ERROR_NOSHEET)
def constructMapDict(self):
"""
生成代对字典， 代对表只有一个
"""
mapDict = self.mapDict
sheet = self.xbook.getSheetBySheetName(config.EXPORT_MAP_SHEET)
for col in range(0, self.xbook.getRowCount(config.EXPORT_MAP_SHEET)):
colValues = self.xbook.getColValues(sheet, col)
if colValues:
for v in [
e for e in colValues[1:]
if e and isinstance(e, str) and e.strip()
]:
# print(v)
mapStr = v.replace('：', ":") # 中文"："和":"
try:
k, v = mapStr.split(":")
k = str.strip(k)
v = str.strip(v)
if k.startswith(config.EXPORT_PREFIX_CHAR) is False:
k = f'@{k}'
mapDict[k] = v
mapDict[v] = k
except Exception as errstr:
print("waring：需要检查代对表 第%d列, err=%s" % (col, errstr))
# 文件头检测
def parseDefineLine(self):
self.__checkDefine() # 检查定义是否正确
self.__checkData() # 检查数据是否符合规则
def __reCheck(self, head):
pattern = "(\w+)(\[.*])(\[\w+\])"
reGroups = re.compile(pattern).match(head)
if not reGroups:
return ()
return reGroups.groups()
def __checkDefine(self):
"""
第一行的个元素是否符合定义格式"name[signs][func]"以及key是否符合规定
"""
for sheetName in self.__exportSheetName:
print("检测表[%s]文件头(第一行)是否正确" % sheetName)
self.sheetKeys = []
headList = self.xbook.getRowValues(
self.xbook.getSheetBySheetName(sheetName),
config.EXPORT_DEFINE_ROW - 1)
enName = [] # 检查命名重复临时变量
self.headerDict[sheetName] = {}
for c, head in enumerate(headList):
if head is None or head.strip() == '': # 导出表的第一行None, 则这一列将被忽略
self.headerDict[sheetName][c] = None
continue
reTuple = self.__reCheck(head)
if len(reTuple) == 3: # 定义被分拆为三部分:name, signs, func, signs可以是空
name, signs, funcName = reTuple[0], reTuple[1][
1:-1], reTuple[2][1:-1]
for s in signs: # 符号定义是否在规则之内
if s not in config.EXPORT_ALL_SIGNS:
self.xlsxClear(config.EXPORT_ERROR_NOSIGN,
(config.EXPORT_DEFINE_ROW, c + 1))
if config.EXPORT_SIGN_GTH in signs: # 是否为key
self.sheetKeys.append(c)
if len(self.sheetKeys
) > config.EXPORT_KEY_NUMS: # key是否超过规定的个数
self.xlsxClear(config.EXPORT_ERROR_NUMKEY,
(config.EXPORT_DEFINE_ROW, c + 1))
if name not in enName: # name不能重复
enName.append(name)
else:
self.xlsxClear(config.EXPORT_ERROR_REPEAT,
(sheetName.encode(config.FILE_CODE),
config.EXPORT_DEFINE_ROW, c + 1))
if not hasFunc(funcName): # funcName是否存在
self.xlsxClear(config.EXPORT_ERROR_NOFUNC,
(xlsxtool.toGBK(funcName), c + 1))
else:
self.xlsxClear(config.EXPORT_ERROR_HEADER,
(sheetName.encode(config.FILE_CODE),
config.EXPORT_DEFINE_ROW, c + 1))
self.headerDict[sheetName][c] = (name, signs, funcName)
self.__onCheckDefine()
def __onCheckDefine(self):
if len(self.sheetKeys) != config.EXPORT_KEY_NUMS: # key也不能少
self.xlsxClear(config.EXPORT_ERROR_NOKEY,
("需要%d而只有%d" %
(config.EXPORT_KEY_NUMS, len(self.sheetKeys))))
print("文件头检测正确", time.ctime(time.time()))
def sheetName2Data(self):
"""
拿到所有要导出的表
"""
self.sheet2Data = {}
for sheetName in self.__exportSheetName:
if sheetName in self.mapDict:
self.sheet2Data[self.mapDict[sheetName]] = sheetName
def __checkData(self):
"""
列数据是否符合命名规范, 生成所需字典
"""
self.sheetName2Data()
self.dctDatas = g_dctDatas
self.hasExportedSheet = []
for dataName in self.sheet2Data:
print('开始处理表：%s' % dataName)
sheetName = self.sheet2Data[dataName]
sheet = self.xbook.getSheetBySheetName(sheetName)
rows = self.xbook.getRowCount(sheetName)
cols = self.xbook.getColCount(sheetName)
if dataName not in self.dctDatas:
self.dctDatas[dataName] = {}
self.dctData = self.dctDatas[dataName]
# for row in range(3, rows + 1):
for row in tqdm.tqdm(range(3, rows + 1), ncols=50):
keyName: str = None
rowval = self.xbook.getRowValues(sheet, row - 1)
childDict = {}
for col in range(1, cols + 1):
val = rowval[col - 1]
if val is not None:
val = (str(rowval[col - 1]), )
else:
val = ("", )
if self.headerDict[sheetName][col - 1] is None:
continue
name, sign, funcName = self.headerDict[sheetName][col - 1]
if '$' in sign and len(val[0]) > 0:
self.needReplace({'v': val[0], "pos": (row, col)})
if ',' in val[0]:
nv = val[0].strip()
vs = nv.split(',')
v = ''
for item in vs:
v += (self.mapDict[xlsxtool.GTOUC(
xlsxtool.val2Str(item))] + ',')
v = v[:-1] # 去掉最后的','
else:
# mapDict:key是unicode.key都要转成unicode
v = self.mapDict[xlsxtool.GTOUC(
xlsxtool.val2Str(val[0]))]
else:
v = val[0]
if config.EXPORT_SIGN_DOT in sign and v is None:
self.xlsxClear(config.EXPORT_ERROR_NOTNULL, (col, row))
sv = v
func = getFunc(funcName)
try:
v = func(self.mapDict, self.dctData, childDict, sv)
except Exception as errstr:
self.xlsxClear(config.EXPORT_ERROR_FUNC,
(errstr, funcName, sv, row, col))
for ss in sign.replace('$', ''):
if len(sv) == 0 and ss == '!':
continue
if ss == '!':
keyName = name
config.EXPORT_SIGN[ss](self, {
'tableName': dataName,
"v": v,
"pos": (row, col)
})
childDict[name] = v
if keyName is not None:
self.dctData[childDict[keyName]] = copy.deepcopy(childDict)
overFunc = self.mapDict.get('overFunc')
if overFunc is not None:
func = getFunc(overFunc)
self.dctData = func(self.mapDict, self.dctDatas, self.dctData,
dataName)
self.dctDatas[dataName] = self.dctData
self.tempKeys.clear()
g_dctDatas.update(self.dctDatas)
self.writeBody()
# 符号字典的相关设置EXPORT_SIGN
def isNotEmpty(self, cellData):
if cellData['v'] is None:
self.xlsxClear(config.EXPORT_ERROR_NOTNULL, (cellData['pos'], ))
def needReplace(self, cellData):
"""宏替代"""
v = cellData["v"].strip()
if isinstance(v, float): # 防止数字报错(1:string) mapDict 是unicode字符串
v = str(int(v))
vs = None
if ',' in v:
vs = v.split(',')
else:
vs = [v]
for v in vs:
if v not in self.mapDict: # 检测而不替换
self.xlsxClear(config.EXPORT_ERROR_NOTMAP,
(cellData['pos'], v))
def checkKey(self, cellData):
"""
检测是否有重复的键值
"""
if cellData['v'] not in self.tempKeys:
self.tempKeys.append(cellData['v'])
else:
self.xlsxClear(config.EXPORT_ERROR_REPKEY,
(cellData['tableName'], cellData['pos'],
(self.tempKeys.index(cellData['v']) + 3,
cellData['pos'][1]), cellData['v']))
def writeBody(self):
print('writeBody %s' % self.targets)
for exportTableName, datas in g_dctDatas.items():
if 'py' in self.targets:
print('export py')
ExportType.toPy(self.outfile, exportTableName, datas)
if 'json' in self.targets:
print('导出json配置')
ExportType.toJson(self.outfile, exportTableName, datas)
if 'lua' in self.targets:
ExportType.toLua(self.outfile, exportTableName, datas)
if 'C#' in self.targets or 'c#' in self.targets:
# 将一个excel上所有表声明文件放在一个C#文件内
fileName, _ = os.path.splitext(os.path.basename(self.infile))
exportTypes = {}
for exportTableName, _ in g_dctDatas.items():
exportTypes[exportTableName] = self.headerDict[
self.mapDict[exportTableName]]
ExportType.generateCSharpTypeFile(fileName, self.outfile,
exportTypes)
self.xlsxbyebye()
print("写完了time:", time.ctime(time.time()))
def xlsxClose(self):
"""
关闭文档
"""
if hasattr(self, "fileHandler"):
self.fileHandler.close()
self.xbook.close()
def xlsxClear(self, errno=0, msg=''):
"""
程序异常退出清理打开的Excel
"""
self.xlsxClose()
if errno > 0:
raise xlsxError.xe(errno, msg)
else:
sys.exit(1)
def xlsxbyebye(self):
"""
正常退出
"""
self.xlsxClose()
