import cx_Oracle, datetime, os
from openpyxl import Workbook
from openpyxl.styles import Font, Alignment
info = ['username', 'password', 'ip:port/name.oracle.docker']
align = Alignment(horizontal='center', vertical='center', wrap_text=True)
font = Font(u'宋体', size=11, bold=True, color='FF0000')
def link_oracle(area='龙湾区'):
wb = Workbook()
ws = wb.create_sheet(title="测试title", index=0)
connection = cx_Oracle.connect(*info)
cursor = connection.cursor()
sql = f'''
select max(e.cat_1_name),i.id, i.name,max(e.cat_2_name),count(info.inst_id),count(info.publicity_id) ,decode(nvl(sum(info.publicity_id),0),0,'测试未完成','测试已完成')
from w_wf i
left join v_inst_info info
on i.id = info.wf_id
and i.sts = 'VALID'
join app_wf_ext e
on e.workflow_id = i.id
and e.cat_1_name like '%{area}特色政策%'
group by i.id, i.name
order by 1
'''
cursor.execute(sql)
row = cursor.fetchall()
# print(row[0])
head = [
["以流转到公示或公示后续环节作为完成的标志"],
[],
['区域', 'ID', '名称', '部门', '申请单个数', '完成个数', '完成情况']
]
for head_row in head:
ws.append(head_row)
# 合并单元格
ws.merge_cells('A1:G2')
ws['A1'].alignment = align
# 设置表头文字颜色
for ro in ws['A1:G3']:
for cell in ro:
cell.font = font
for r in row:
ws.append(list(r))
cursor.close()
connection.close()
wb.save(f'{area}{current_time}.xlsx')
if __name__ == '__main__':
area_list = ["苍南县", "永嘉县", "鹿城区", "龙湾区", "瓯海区", "洞头区"]
# area_list = ["洞头区"]
# area_list = ["乐清市", "瑞安市", "文成县", "平阳县", "泰顺县", "瓯江口产业集聚区", "浙南科技城区", "浙南产业集聚区"]
current_time = datetime.datetime.now().strftime("%m%d%H%M")
if not os.path.exists(current_time):
os.mkdir(current_time)
os.chdir(current_time)
for a in area_list:
link_oracle(a)
