描述
- 有许多文件夹，里面可能有成百上千个文件
- 内容主要分为sql文件 和 excel文件
- excel文件记录sql文件信息，同时是sql文件的描述
需求
- 由于许多人提交不规范 找出sql文件和excel文件记录信息有差异的地方，主要是名称
- 将sql文件从这些文件夹中汇总，加上数字前缀，同时若为特定脚本，在汇总文件夹中存放时，需要另建文件夹加以区分
- 将所有excel汇总，同时将不规范格式的excel进行处理汇总
1 sql文件和excel信息比对脚本
#!/usr/bin/python
# -*- coding:utf8 -*-
import os
import openpyxl
from openpyxl import Workbook,load_workbook
sql_list=[]
excel_list=[]
for root,dirs,files in os.walk(r"."):
for file in files:
this_file=os.path.join(root,file)
if ".sql" in this_file or ".dmp" in this_file:
index=file.find('.')
sql_list.append(file[:index])
if ".xl" in this_file and "~" not in this_file:
excel_list.append(this_file)
sql_list_in_excel=[]
for excel_name in excel_list:
wb = load_workbook(excel_name)
#ws = wb.get_sheet_by_name(wb.sheetnames[0])
ws=wb.worksheets[0]
rows=ws.max_row
cols=ws.max_column
col_number=5
for i in range(2,cols+1):
val=ws.cell(row=1,column=i).value
if val and '脚本' in val:
col_number=i
break
for i in range(2,rows+1):
val=ws.cell(row=i,column=col_number).value
if val and 'None' not in val:
index=val.find('.')
if index != -1:
sql_list_in_excel.append(val[:index])
else:
sql_list_in_excel.append(val)
wb.close()
#print(val[:index])
print("-----------sql文件比excel中多-----------")
for this_sql in sql_list:
if this_sql not in sql_list_in_excel:
print(this_sql)
print("-----------excel比sql文件多-------------")
for this_excel in sql_list_in_excel:
if this_excel not in sql_list:
print(this_excel)
#pyinstaller打包后，防止闪退
wait=input()
2 汇总文件脚本
#!/usr/bin/python
# -*- coding:utf8 -*-
import os
import shutil
print('just waiting...')
if os.path.exists('scripts'):
shutil.rmtree('scripts')
os.mkdir('scripts')
if os.path.exists('excels'):
shutil.rmtree('excels')
os.mkdir('excels')
i=0
j=0
for root,dirs,files in os.walk(r'.'):
dirs[:] = [d for d in dirs if d not in "scripts"]
dirs[:] = [d for d in dirs if d not in "excels"]
for f in files:
f_name=os.path.join(root,f)
index=f_name.rfind('\\')
this_dir=f_name[:index]
index=this_dir.rfind('\\')
this_dir=this_dir[index+1:]
#print(this_dir)
if '.sql' in f_name or '.dmp' in f_name:
if '仅' in f_name or '执行' in f_name:
this_path='scripts'+'/'+this_dir
if not os.path.exists(this_path):
os.mkdir(this_path)
i=i+1
tar_file_name=str(i)+'_'+f
f_tar=this_path+'/'+tar_file_name
shutil.copyfile(f_name,f_tar)
else:
i=i+1
tar_file_name=str(i)+'_'+f
f_tar='scripts/'+tar_file_name
shutil.copyfile(f_name,f_tar)
if '.xl' in f_name and '~' not in f_name:
j=j+1
tar_file_name=str(j)+'_'+f
f_tar='excels/'+tar_file_name
shutil.copyfile(f_name,f_tar)
3 按格式合并excel脚本
#!/usr/bin/python
# -*- coding: utf8 -*-
import os
import openpyxl
from openpyxl import load_workbook
def format_date(this_date):
this_date=str(this_date)
this_date=this_date.rstrip()
this_len=len(this_date)
#print(this_len)
if this_len==19:
return(this_date[:10])
if this_len==10:
return(this_date[0:4]+'-'+this_date[5:7]+'-'+this_date[8:])
if this_len==8:
return(this_date[0:4]+'-'+this_date[4:6]+'-'+this_date[6:])
else:
return(this_date)
if os.path.exists('all_xl.xlsx'):
os.remove('all_xl.xlsx')
#存放excel文件的目录为./excels
excel_list=[]
for root,dirs,files in os.walk(r"./excels"):
excel_list[:]=files
#print(excel_list)
s_wb = openpyxl.Workbook()
s_ws = s_wb.active
s_ws.append(['主体域','对象列表','表名','对象修改类型','脚本','变更内容','发起人','修改时间','脚本类型','小组审核人'])
for this_excel in excel_list:
xl_file_name="./excels/"+this_excel
wb = load_workbook(xl_file_name)
ws=wb.worksheets[0]
#print(ws.merged_cells)
#resolve irregularities
if ws.max_column == 14:
for row in range(2,ws.max_row+1):
val_row_list=[]
none_count=0
for i in range(1,ws.max_column+1):
if str(ws.cell(row,i).value) == 'None':
none_count+=1
if none_count == 14:
break
val_row_list.append(ws.cell(row,1).value)
val_row_list.append('表')
val_row_list.append(ws.cell(row,2).value)
val_row_list.append(ws.cell(row,4).value)
val_row_list.append(ws.cell(row,3).value)
val_row_list.append(ws.cell(row,4).value)
val_row_list.append(ws.cell(row,5).value)
val_row_list.append(ws.cell(row,8).value)
val_row_list.append(ws.cell(row,9).value)
val_row_list.append(ws.cell(row,11).value)
#print(val_row_list)
s_ws.append(val_row_list)
wb.close()
else:
for row in range(2,ws.max_row+1):
val_row_list=[]
none_count=0
for i in range(1,ws.max_column+1):
if str(ws.cell(row,i).value) == 'None':
none_count+=1
if none_count == 10:
break
for col in range(1,ws.max_column+1):
this_val=str(ws.cell(row,col).value)
#print(this_val)
if "None" == str(this_val):
number=row
while "None" == str(this_val) and number>1:
number=number-1
this_val=str(ws.cell(int(number),col).value)
val_row_list.append(this_val)
else:
val_row_list.append(this_val)
s_ws.append(val_row_list)
wb.close()
#start merging
rows=['']+list(s_ws.rows)
index=2
row_count=len(rows)
while index < row_count:
value=rows[index][4].value
for this_index,row in enumerate(rows[index+1:],index+1):
if not (row[4].value==None or row[4].value==value):
break
else:
s_ws.merge_cells('E'+str(index)+':E'+str(this_index))
break
s_ws.merge_cells('E'+str(index)+':E'+str(this_index-1))
index=this_index
for row in range(2,s_ws.max_row+1):
value_addr='H'+str(row)
s_ws[value_addr]=format_date(s_ws[value_addr].value)
s_wb.save('all_xl.xlsx')
说明
- 使用pyinstaller打包成exe文件
- 脚本1单独打包 脚本2和3一起打包
-
打包时候确保有相关库: openpyxl
workspace.png
