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聲明為調用全局變量才能達到運行完函數生成變量的效果(可能還有其他更好方法)

　　得出運行結果