python解析帆软cpt及frm文件（xml）获取源数据表及下游依赖表

#!/user/bin/evn python
import os,re,openpyxl
'''输入：帆软脚本文件路径输出：帆软文件检查结果Excel
'''
#获取来源表
def table_scan(sql_str):# remove the /* */ commentsq = re.sub(r"/\*[^*]*\*+(?:[^*/][^*]*\*+)*/", "", sql_str)# remove whole line -- and # commentslines = [line for line in q.splitlines() if not re.match("^\s*(--|#)", line)]# remove trailing -- and # commentsq = " ".join([re.split("--|#", line)[0] for line in lines])# split on blanks, parens and semicolonstokens = re.split(r"[\s)(;]+", q)# scan the tokens. if we see a FROM or JOIN, we set the get_next# flag, and grab the next one (unless it's SELECT).result = []get_next = Falsefor token in tokens:if get_next:if token.lower() not in ["", "select"]:#过滤掉因条件设置选择来源表而产生的脏数据非表名字符if '"+if' not in token and '"'not in token and '$if' not in token and '${if' not in token:result.append(token.replace('`',''))get_next = Falseget_next = token.lower() in ["from", "join"]#特殊单独情况处理：from后面来源表条件选择对应来源表，比如from ${if(XX,"来源表A","来源表B")}# print(result)if not result:tab_pat=re.compile(r'from.*?if\((.*?)\)',re.S)for i in re.findall(tab_pat,sql_str):temp_r=i.replace('"','').split(',')if '' not in temp_r and '/*' not in temp_r:result.append(temp_r[1].replace('`','').strip())result.append(temp_r[2].replace('`','').strip())return result#文件扫描，使用正则解析第一版，准确性不太高！
def file_scan(path):f_content=open(path,'r',encoding='utf-8').read()#1、数据集查询sqlgpat=re.compile('<TableDataMap>(.*?)</TableDataMap>',re.S)if_has_sqlg=re.findall(sqlgpat,f_content)rs_sql_list=[]if if_has_sqlg:#获取数据集名称以及数据集查询语句sqlspat=re.compile('<TableData name="(.*?)".*?<DatabaseName>\n<!\[CDATA\[(.*?)]]></DatabaseName>.*?<Query>\n<!\[CDATA\[(.*?)]]></Query>.*?</TableData>',re.S)rs1=re.findall(sqlspat,if_has_sqlg[0])for rsv in rs1:from_tables=[]if '"*/"' in rsv[1]:sql=rsv[1].split('*/')for ss in sql:from_tables.extend(table_scan(ss))else:from_tables.extend(table_scan(rsv[2]))rs_sql_list.append([rsv[0],rsv[1],rsv[2],set(from_tables)])# print(rsv[1])#2、js获取if_has_jsgpat=re.compile('<NameJavaScript name="(.*?)</NameJavaScript>',re.S)if_has_jsg=re.findall(if_has_jsgpat, f_content)rep_list = []  # 报表列表，去重if if_has_jsg:for jscon in if_has_jsg:# conturlpat=re.compile('<Content>.*?var\surl\s=.*?viewlet=(.*?.[cptfrm]{3})&.*?</Content>',re.S)conturlpat = re.compile('<Content>.*?viewlet=(.*?[cptfrm]{3})[&?].*?</Content>', re.S)if '<JavaScript class="com.fr.js.ReportletHyperlink">' in jscon:# rlpat=re.compile('<ReportletName extendParameters="true" showPI="true">\s<!\[CDATA\[(.*?)]]></ReportletName>',re.S)rlpat = re.compile( '<ReportletName .*?\[CDATA\[(.*?)]]></ReportletName>', re.S)rl=re.findall(rlpat,jscon)[0]# print(re.findall(rlpat,jscon))if rl not in rep_list:rep_list.append(rl)elif '<JavaScript class="com.fr.js.WebHyperlink">' in jscon:wlpat=re.compile('<URL>\s<!\[CDATA\[(.*?)]]></URL>',re.S)wl=re.findall(wlpat,jscon)[0]if wl not in rep_list:rep_list.append(wl)elif re.search(conturlpat,jscon):frl=re.findall(conturlpat,jscon)[0]print(frl)if not frl.startswith('/'):frl='/'+frlif frl not in rep_list:rep_list.append(frl)# elif '<JavaScript class="com.fr.js.JavaScriptImpl">'in jscon and('.cpt' in jscon or '.frm' in jscon) :#     print(jscon)if_has_cljpat=re.compile(r'<RHIframeSource.*?<Attr path="(.*?[cptfrm]{3}).*?</RHIframeSource>',re.S)f_has_clj=re.findall(if_has_cljpat,f_content)if f_has_clj:for v in f_has_clj:if v not in rep_list:rep_list.append(v)# print(rep_list)return rep_list,rs_sql_list#使用xml解析精准获取
def xml_scan(path):import xml.etree.ElementTree as ETtree = ET.parse(path)  # 打开xml文件dataset_iters = []  # 数据集名称，数据集数据库链接名，数据集查询语句，数据集来源sql表if list(tree.getroot().iter("TableDataMap")):# 数据集TableDataMap父节点table_map_content = list(tree.getroot().iter("TableDataMap"))[0]# 获取数据集查询名称dataset_iters_map = table_map_content.iter('TableData')for val in dataset_iters_map:# print('查询名称--',val.attrib.get("name"))dataset_name = val.attrib.get("name").strip()if len(list(val.iter("DatabaseName"))):# 帆软目前一个数据集查询框只能链接单个数据库，所以获取数据库链接名只有1个# print('查询数据库链接名--', list(val.iter("DatabaseName"))[0].text.strip())dataset_connect_name = list(val.iter("DatabaseName"))[0].text.strip()else:# print('查询数据库链接名--',None)dataset_connect_name = Noneif len(list(val.iter("Query"))):# 帆软目前一个数据集查询框只能链接单个数据库，所以获取数据库链接名只有1个,且只有一个sql查询窗口内容# print('查询数据查询语句--', list(val.iter("Query"))[0].text.strip())dataset_query = list(val.iter("Query"))[0].text.strip()from_tables = []if '"*/"' in dataset_query:sql = dataset_query.split('*/')for ss in sql:# print(ss)from_tables.extend(table_scan(ss))else:from_tables.extend(table_scan(dataset_query))else:# print('查询数据查询语句--', None)dataset_query = Nonefrom_tables=[]dataset_iters.append([dataset_name, dataset_connect_name, dataset_query,from_tables])urls = set()  # 报表全体下游调用URL集合js_contents = []  # js内容，内容清洗出来的URL，用于核对数据清洗是否准确# print(len(list(tree.iter("ReportletName"))))#js链接报表-网格报表-本地服务器local_url = [v.text.strip() for v in tree.iter("ReportletName")]if local_url:urls.update(local_url)# print(len(list(tree.iter("URL"))))  # js链接报表-网格报表-远程web链接web_url = [v.text.strip() for v in tree.iter("URL")]if web_url:urls.update(web_url)# print(len(list(val.iter("RHIframeSource"))))# js链接报表-tab框架挂载报表for v in tree.iter("RHIframeSource"):webframe_url = list(v.iter("Attr"))[0].attrib.get("path")# 去除URL尾巴参数if webframe_url and not webframe_url.endswith("frm") and not webframe_url.endswith("cpt"):rpat = re.compile(r'.*?[cptfrm]{3}', re.I)webframe_url = re.findall(rpat, webframe_url)[0]urls.update([webframe_url])elif webframe_url:urls.update([webframe_url])# print(len(list(val.iter("Content"))))for cv in list(tree.iter("Content")):contents = cv.texttemp_url = []# print(contents)http_ul_pat = re.compile(r'"(http.*?)"') #js内容里面挂载web超链接local_ul_pat = re.compile(r'viewlet=(.*?[cptfrm]{3})')#js内容里面挂载服务器本地绝对路径报表链接# print(re.findall(http_ul_pat,contents))# print(re.findall(local_ul_pat, contents))if re.findall(http_ul_pat, contents):urls.update(re.findall(http_ul_pat, contents))temp_url.extend(re.findall(http_ul_pat, contents))if re.findall(local_ul_pat, contents):# print(re.findall(local_ul_pat, contents))#处理挂载服务器本地链接路径，有些挂载绝对目录不规范a/b/c.cpt处理后输出/a/b/c.cptfor vl in re.findall(local_ul_pat, contents):if vl.startswith('/'):urls.update([vl])temp_url.append(vl)else:urls.update(['/'+vl])temp_url.append('/'+vl)js_contents.append([contents, temp_url])# print(js_contents)return dataset_iters,urls,js_contentsdef write_excel(list_tar,file_path):wb = openpyxl.Workbook()  # 新建工作簿sheet0=wb[wb.sheetnames[0]]sheet0.title=('引用报表列表')sheet1 = wb.create_sheet('来源mysql表')sheet2 = wb.create_sheet('帆软数据集查询及依赖明细')sheet3 = wb.create_sheet('帆软JS内容明细')sheet0['A1'] = '文件名'sheet0['B1'] = '依赖报表'sheet1['A1'] = '文件名'sheet1['B1'] = '依赖mysql表'sheet2['A1'] = '文件名'sheet2['B1'] = '数据集查询名称'sheet2['C1'] = '数据库链接名称'sheet2['D1'] = '数据集查询语句'sheet2['E1'] = '数据来源mysql表'sheet3['A1'] = '文件名'sheet3['B1'] = 'JS内容'sheet3['C1'] = 'JS解析URL'r=1k=1d=1x=1for index,item in enumerate(list_tar):print(('开始处理第 '+str(index+1)+' 个文件结果，共 '+str(len(list_tar))+' 个').center(50,'-'))# filename,dataset_iters, urls, js_contents# dataset_iters = []  # 数据集名称，数据集数据库链接名，数据集查询语句，数据集来源sql表target_file_name=item[0]cpt=item[2]sql=item[1]jsc=item[3]for id1,value in enumerate(sorted(cpt)):r=r+1sheet0.cell(row=r, column=1, value=target_file_name)sheet0.cell(row=r, column=2, value=value)sql_set=set()for id1,val in enumerate(sql):k = k + 1sql_set.update(val[3])sheet2.cell(row=k, column=1, value=target_file_name)sheet2.cell(row=k, column=2, value=val[0])sheet2.cell(row=k, column=3, value=val[1])sheet2.cell(row=k, column=4, value=val[2])sheet2.cell(row=k, column=5, value='\n'.join(val[3]))for id1,value in enumerate(sorted(sql_set)):d = d + 1sheet1.cell(row=d, column=1, value=target_file_name)sheet1.cell(row=d, column=2, value=value)for id1, value in enumerate(sorted(jsc)):if value[0] or value[1]:x = x +1sheet3.cell(row=x, column=1, value=target_file_name)sheet3.cell(row=x, column=2, value=value[0])sheet3.cell(row=x, column=3, value='\n'.join(value[1]))wb.save(file_path)wb.close() # excel使用完成需要关闭，否则会报错def main_scan(fr_path,result_path):rs_list=[]file_list=[]file_name=[]for dirpath, dirnames, filenames in os.walk(fr_path):for file in filenames:file_list.append(os.path.join(dirpath,file))file_name.append(os.path.join(dirpath,file).replace(r'【清空前缀:本地机扫描文件夹绝对路径】','').replace('\\','/'))for index,file in enumerate(file_list):print(('正在扫描第 '+str(index+1)+' 个文件，共 '+str(len(file_list))+' 个文件').center(50,'-'))try:dataset_iters,urls,js_contents = xml_scan(file)rs_list.append([file_name[index],dataset_iters,urls,js_contents])except:print('【文件扫描失败】：',file)print('文件扫描完毕，正在写入Excel'.ljust(50,'-'))write_excel(rs_list, result_path)if __name__ == '__main__':#帆软扫描文件夹绝对路径fr_path=r'【本地机扫描文件夹绝对路径】'#帆软扫描结果文件绝对路径result_path=r'【本地机结果路径】\scaning_result.xlsx'main_scan(fr_path, result_path)

扫描文件夹：

运行结果