python实现自动化报表：用于Excel或多线程等

　　日常会有很多固定报表需要手动更新，本文将利用python实现多线程运行oracle代码，并利用xlwings包和numpy包将结果写入到指定excel模版(不改变模版内容)，并自动生成带日期命名的新excel。此外还添加了logging模块记录运行日志，以及利用try…except实现遇到错误自动重新运行。下面将介绍整个自动化的实现过程。

　　# -*- coding: utf-8 -*-

　　# Create time: 2019-10-16

　　# Update time: 2019-11-28

　　# Version: 1.0

　　# Version: 2.0 增加多线程/出错自动重新运行模块

　　# 导入模块

　　import cx_Oracle

　　import os

　　import pandas as pd

　　import pandas.io.sql as sql

　　import time

　　import openpyxl

　　import xlwings as xw

　　import logging

　　import re

　　import threading

　　# 获取工作目录

　　sqlpath = os.getcwd() + '\\' # 获取当前文件夹目录,若不正确则使用后面的代码直接输入 sqlpath = 'E:\\'

　　# 设置运行日志

　　logging.basicConfig(format='%(asctime)s - %(pathname)s] - %(levelname)s: %(message)s', level=logging.INFO, filename=sqlpath+'log.txt')

　　# 获取系统日期(用于命名文件)

　　time_start=time.time()

　　date = time.strftime("%Y%m%d")

　　# 定义变量

　　name = '经营日报' # 模版名称

　　print('开始运行: %s' %name)

　　# sql代码文件名

　　sql1 = sqlpath + 'hangxian_ri.sql'

　　sql2 = sqlpath + 'hangxian_zhou.sql'

　　sql3 = sqlpath + 'hangxian_yue.sql'

　　# 定义空DataFrame(函数中要使用,必须要先定义)

　　result1 = pd.DataFrame()

　　result2 = pd.DataFrame()

　　result3 = pd.DataFrame()

　　# 定义可以读取运行sql的函数

　　def read_run_write1(sql_name):

　　global result1 # 调用全局变量result1,使用global才能对全局变量进行修改

　　print('正在运行代码: %s' %sql_name)

　　with open(sql_name,encoding='utf-8-sig',mode='r') as f: # 读取oracle代码,中文编码utf-8-sig,

　　sql_list = f.read()

　　sql_list = re.sub(r'--.*', '', sql_list) # 去除注释

　　connection = cx_Oracle.connect('账号/密码@IP地址/数据库名称')

　　code = sql_list.replace('\n', ' ').replace(';','') # 将换行符转为空格,去除分号

　　for i in range(5): # 运行oracle代码,若运行失败则10秒自动重新运行

　　try:

　　result1 = sql.read_sql(code, connection) # 利用pd包的read_sql函数运行代码,返回DataFrame类型的结果

　　break # 代码运行成功则跳出循环

　　except:

　　print('代码%s运行出错,正在重新运行第%d次' %(sql_name,(i+1)))

　　time.sleep(10) # 代码运行失败则10秒后再重新运行

　　print('代码运行完成: %s' %sql_name)

　　def read_run_write2(sql_name):

　　global result2

　　print('正在运行代码: %s' %sql_name)

　　with open(sql_name,encoding='utf-8-sig',mode='r') as f:

　　sql_list = f.read()

　　sql_list = re.sub(r'--.*', '', sql_list) # 去除注释

　　connection = cx_Oracle.connect('账号/密码@IP地址/数据库名称')

　　code = sql_list.replace('\n', ' ').replace(';','') # 将换行符转为空格,去除分号

　　for i in range(5):

　　try:

　　result2 = sql.read_sql(code, connection)

　　break

　　except:郑州妇科医院 http://www.120zzkd.com/

　　print('代码%s运行出错,正在重新运行第%d次' %(sql_name,(i+1)))

　　time.sleep(10)

　　print('代码运行完成: %s' % sql_name)

　　def read_run_write3(sql_name):

　　global result3

　　print('正在运行代码: %s' %sql_name)

　　with open(sql_name,encoding='utf-8-sig',mode='r') as f:

　　sql_list = f.read()

　　sql_list = re.sub(r'--.*', '', sql_list) # 去除注释

　　connection = cx_Oracle.connect('账号/密码@IP地址/数据库名称')

　　code = sql_list.replace('\n', ' ').replace(';','') # 将换行符转为空格,去除分号

　　for i in range(5):

　　try:

　　result3 = sql.read_sql(code, connection)

　　break

　　except:

　　print('代码%s运行出错,正在重新运行第%d次' %(sql_name,(i+1)))

　　time.sleep(10)

　　print('代码运行完成: %s' % sql_name)

　　# 多线程运行sql代码

　　if __name__ == '__main__':

　　t1 = threading.Thread(target=read_run_write1, args=(sql1,)) # 调用函数,并传递参数sql1,注意当只有一个参数时,参数后面需要有逗号

　　t2 = threading.Thread(target=read_run_write2, args=(sql2,))

　　t3 = threading.Thread(target=read_run_write3, args=(sql3,))

　　t1.start() # 开始运行

　　t2.start()

　　t3.start()

　　t1.join() # 加入线程,若无join()则运行完一个主线程后则会自动跳出,运行下面的代码,而不会等待其他线程运行完成

　　t2.join()

　　t3.join()

　　# 写入数据

　　print('正在写入数据')

　　app = xw.App(visible=False,add_book=False) # visible=False后台打开Excel程序

　　wb = app.books.open(sqlpath+name+'.xlsx') # 打开给定只保留标题和公式的空模版

　　wb.sheets['单日'].range('A4').options(expand='table').value=result1[:].values # result[:].values写法可以去掉索引和标题,将result1写入到表格'单日',从A4单元格开始

　　wb.sheets['滚动一周'].range('A4').options(expand='table').value=result2[:].values

　　wb.sheets['月累计'].range('A4').options(expand='table').value=result3[:].values

　　filename = sqlpath+name+date+'.xlsx' # 命名新生成的excel

　　wb.save(filename) # 另存为新Excel,不改变模版

　　wb.close() # 关闭工作簿

　　print(filename+'已自动生成')

　　time_end=time.time()

　　print("程序运行时间：%.2f s" % (time_end-time_start))

　　logging.info("程序运行时间：%.2f s" % (time_end-time_start)) # 记录程序运行时间到运行日志log.txt中

　　python自动化中的一些难点

　　多线程无法同时对excel进行写入操作,如果直接在函数中运行完成后直接写入,并利用多线程运行,会报错com模块冲突

　　为了解决函数不能直接生成结果变量的问题, 应先定义空DataFrame,并在函数中global声明为调用全局变量才能达到运行完函数生成变量的效果(可能还有其他更好方法)

　　得出运行结果