首页 > 代码库 > python脚本从excel表到处数据,生成指定格式的文件
python脚本从excel表到处数据,生成指定格式的文件
#coding:gbk #导入处理excel的模块 import xlrd #定义哪些字段需要判断,只支持时间字段 toSureColArray = ['CREATE_TIME','MODIFY_TIME'] #确定某个字段是否在数组中 def isColInArray(colName, colArray): for i in range(0, len(colArray)): if (colName == colArray[i]): return 1 else: return 0 #定义子sheet字段属性映射处理函数 #入参为文件描述符、、源表列字段、和目表列字段 def childSheetColFunc(fOutput, sourceTabCol, destTabCol,destTableSureArray): for i in range(2,len(sourceTabCol)): if len(destTabCol[i]) != 0: if len(sourceTabCol[i]) != 0: #在子表中,如果浪潮int_id对应的直真字段英文名称为空,在destTableSureArry不存在INT_ID属性 destTableSureArray.append(str(destTabCol[i]).upper()) fOutput.write("\t\t\t\t\tA."+str(sourceTabCol[i]).upper()+" "+str(destTabCol[i]).upper()+",\n") else: continue else: continue #for i in range(0,len(destTableSureArray)): # print("******************") # print(str(destTableSureArray[i])) # print("******************") #对creat_time和modify_time做单独处理 for i in range(0,len(toSureColArray)): if isColInArray(toSureColArray[i],destTableSureArray) == 0: fOutput.write("\t\t\t\t\tSYSDATE "+toSureColArray[i]+",\n") destTableSureArray.append(toSureColArray[i]) #定义子sheet目标数据插入函数主键模块 #入参为文件描述符、源表列字段、目表源字段 def childSheetColDesKeyFunc(fOutput, sourceTabCol, destTabCol): #如果为空表,此处会报下标溢出错误,所以入参必须保证表中有数据 if len(sourceTabCol[2]) != 0: fOutput.write("\t\t\t\t<col name=\""+str(destTabCol[2]).upper()+"\">["+str(destTabCol[2]).upper()+"]</col>\n") fOutput.write("\t\t\t\t<col name=\"STATEFLAG\">[STATEFLAG]</col>\n") #定义子sheet目标函数插入函数非主键模块 def childSheetColDesNKeyFunc(fOutput, sourceTabCol, destTableSureArray): if len(sourceTabCol[2]) != 0: for i in range(1,len(destTableSureArray)): fOutput.write("\t\t\t\t<col name=\""+destTableSureArray[i]+"\">["+destTableSureArray[i]+"]</col>\n") else: for i in range(0,len(destTableSureArray)): fOutput.write("\t\t\t\t<col name=\""+destTableSureArray[i]+"\">["+destTableSureArray[i]+"]</col>\n") #定义子sheet处理函数 def childSheetFunc(sheetName, sourceTable, destTable): #根据sheet名打开表 shtInstance=workBook.sheet_by_name(sheetName) #存放最终哪些浪潮字段需要映射 destTableSureArray = [] langchaoTabCol=shtInstance.col_values(4) zhizhenTabCol=shtInstance.col_values(6) print("***********现在开始生成"+str(destTable).upper()+".xml 文件***********") path="e:\\"+destTable.upper()+".xml" fOutput=open(path,"w") fOutput.write("<?xml version=\"1.0\" encoding=\"gb2312\"?>\n") fOutput.write("<Conf>\n") fOutput.write("\t<stage id=\"1\" describe=\""+str(sourceTable).upper()+"\" doUpdate=\"$doUpdate$\">\n") fOutput.write("\t\t<drawout DBid=\"ZZ\">\n") fOutput.write("\t\t\t<![CDATA[\n") fOutput.write("\t\t\t\tSELECT\n") fOutput.write("\t\t\t\t\t0 STATEFLAG,\n") childSheetColFunc(fOutput, zhizhenTabCol, langchaoTabCol,destTableSureArray) #fOutput.write("\t\t\t\t\tSYSDATE STATEFLAG\n") fOutput.write("\t\t\t\tFROM INFORMIX."+str(sourceTable).upper()+" A\n") fOutput.write("\t\t\t\tWHERE 1=1\n") fOutput.write("\t\t\t\t\tAND ROW_NUM < 2\n") fOutput.write("\t\t\t]]>\n") fOutput.write("\t\t</drawout>\n") fOutput.write("\t\t<Load DBid=\"IRMS_143\" tableName=\""+str(destTable).upper()+"\"\n") fOutput.write("\t\t\t<Dimension>\n") childSheetColDesKeyFunc(fOutput, zhizhenTabCol, langchaoTabCol) fOutput.write("\t\t\t<Dimension>\n") fOutput.write("\t\t\t<Cols>\n") childSheetColDesNKeyFunc(fOutput, zhizhenTabCol, destTableSureArray) fOutput.write("\t\t\t<Cols>\n") fOutput.write("\t\t</Load>\n") fOutput.write("\t</stage>\n") fOutput.write("</Conf>\n") fOutput.close() print("***********"+str(destTable).upper()+".xml 文件生成完成***********") #根据主表获取子表信息并处理 def mainSheetAndProc(mainSheet): sheetName=mainSheet.col_values(2) sourceTabName=mainSheet.col_values(5) destTabName=mainSheet.col_values(3) curNum = 0 for i in range(2,len(sheetName)): if len(sheetName[i]) ==0: continue curNum += 1 print(">>>>>>开始生成第"+str(curNum)+"个文件") childSheetFunc(sheetName[i], sourceTabName[i], destTabName[i]) #入口函数 if __name__=="__main__": workBook=xlrd.open_workbook("E:\\内线比对模型.xls") mainSheet=workBook.sheets()[0] mainSheetAndProc(mainSheet)
python脚本从excel表到处数据,生成指定格式的文件
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。