Python+pandas編寫命令列指令碼操作excel的tips詳情

import logging
from logging.handlers import TimedRotatingFileHandler
from logging.handlers import RotatingFileHandler
class Log(object):
    STAND = "stand"   # 輸出到控制檯
    FILE = "file"     # 輸出到檔案
    ALL = "all"       # 輸出到控制檯和檔案

    def __init__(self, mode=STAND):
        self.LOG_FORMAT = "%(asctime)s - %(levelname)s - %(message)s"
        self.logger = logging.getLogger()
        self.init(mode)
    def debug(self, msg):
        self.logger.debug(msg)
    def info(self, msg):
        self.logger.info(msg)
    def warning(self, msg):
        self.logger.warning(msg)
    def error(self, msg):
        self.logger.error(msg)
    def init(self, mode):
        self.logger.setLevel(logging.DEBUG)
        if mode == "stand":
            # 輸出到控制檯 ------
            self.stand_mode()
        elif mode == "file":
            # 輸出到檔案 --------
            self.file_mode()
        elif mode == "all":
            # 輸出到控制檯和檔案
            self.stand_mode()
            self.file_mode()
    def stand_mode(self):
        stand_handler = logging.StreamHandler()
        stand_handler.setLevel(logging.DEBUG)
        stand_handler.setFormatter(logging.Formatter(self.LOG_FORMAT))
        self.logger.addHandler(stand_handler)

    def file_mode(self):
        '''
        filename：紀錄檔檔名的prefix；
        when：是一個字串，用於描述捲動週期的基本單位，字串的值及意義如下：
         「S」: Seconds
         「M」: Minutes
         「H」: Hours
         「D」: Days
         「W」: Week day (0=Monday)
         「midnight」: Roll over at midnight
        interval: 捲動週期，單位有when指定，比如：when='D',interval=1，表示每天產生一個紀錄檔檔案；
        backupCount: 表示紀錄檔檔案的保留個數；
        '''
        # 輸出到檔案 -----------------------------------------------------------
        # 按檔案大小輸出
        # file_handler = RotatingFileHandler(filename="my1.log", mode='a', maxBytes=1024 * 1024 * 5, backupCount=10, encoding='utf-8')  # 使用RotatingFileHandler類，捲動備份紀錄檔
        # 按時間輸出
        file_handler = TimedRotatingFileHandler(filename="my.log", when="D", interval=1, backupCount=10,
                                                encoding='utf-8')
        file_handler.setLevel(logging.DEBUG)
        file_handler.setFormatter(logging.Formatter(self.LOG_FORMAT))
        self.logger.addHandler(file_handler)
log = Log(mode=Log.STAND)

from utils.logUtil import log
if __name__ == '__main__':
    log.debug("debug msg")
    log.info("info msg")
    log.warning("warning msg")
    log.error("error msg")

# 使用說明 -----------------------------------
time.sleep(1)
print('===========================================================')
print('簡單說明：使用正規表示式拆分excel表中不規範的作者，初步提取對應需求欄位')
print('PS:')
print('1.資料夾下需要有一個excel（只放一個，名稱隨意），其中一列「作者」儲存著待拆分的作者')
print('2.拆分後的excel將新增幾列拆分結果列，以 <作者>[拆分] 作為列名標記')
print('===========================================================')
time.sleep(1)
# ------------------------------------------

# 輸入操作路徑 ----------------------------------------------------------------
operate_dir = input('請輸入excel目錄（旺柴）:')  # D:PycharmProjectsspiders圖片下載工具excel
operate_dir = os.path.abspath(operate_dir)
# operate_dir = os.path.abspath(r'C:Userscxstar46Desktop正規表示式題名拆分測試')
# -----------------------------------------------------------------------------

# 檢測excel數量，只能放一個，當只有一個excel時，提取它的路徑excel_path -------
log.info('檢查路徑下的檔案格式...')
excel_name = None
excel_count = 0
file_list = os.listdir(operate_dir)
for file in file_list:
    if file.endswith('.xlsx') or file.endswith('.xlx'):
        excel_count += 1
        excel_name = file
if excel_count == 0:
    log.error('資料夾下沒有excel')
    input('按任意鍵退出...')
    raise Exception(0)
if excel_count > 1:
    log.error("無法讀取excel，資料夾下有多個excel，或者excel未關閉")
    input('按任意鍵退出...')
    raise Exception(0)

# print(excel_name)
# raise Exception(1212)
# ----------------------------------------------------------------------
# print(excel_path)
# print(img_dir)

# 讀取excel ----------------------------------------
excel_path = os.path.join(operate_dir, excel_name)
# print(excel_path)
try:
    df = pd.read_excel(excel_path)
    df = df.where(df.notnull(), None)
except Exception as e:
    log.error(e)
    log.error('檔案不存在或已損壞...')
    input('按任意鍵退出...')
    raise Exception(0)
# -------------------------------------------------

# 列印excel行數
print(df.shape[0])

# 備份原始excel表 --------------------------------------------------------------
log.info('備份excel...')
bak_dir = '封面上傳前的備份'   # 備份資料夾的名稱
file_list = os.listdir(operate_dir)
if bak_dir not in file_list:
    os.makedirs(os.path.join(operate_dir, bak_dir))
bak_excel_path = os.path.join(os.path.join(operate_dir, bak_dir), "{}_{}".format(datetime.datetime.now().strftime("%Y-%m-%d_%H-%M-%S"), excel_name))
shutil.copyfile(excel_path, bak_excel_path)
# -----------------------------------------------------------------------------

try:
    raise Exception("執行業務報錯")
except Exception as e:
    import traceback
    log.error(traceback.format_exc())	# 記錄異常資訊
input('執行完畢，按任意鍵繼續...')

cover_ruid_col_name = "封面ruid"

# 沒有封面ruid這一列就建立
if cover_ruid_col_name not in df.columns.values:
    df.loc[:, cover_ruid_col_name] = None

# 讀取excel
excel_path = './封面上傳測試.xlsx'
df = pd.read_excel(excel_path)
review_col = "稽核結果"
# 沒有「稽核結果」這一列就建立
if review_col not in df.columns.values:
    df.loc[:, review_col] = None
for i in range(df.shape[0]):

	# 列印進度 ---------------------------------------------
    log.info("----------------------------------")
    log.info("當前進度： {} / {}".format(i+1, df.shape[0]))
    # ----------------------------------------------------
	# 執行表格插入業務
	# 判斷業務
	# 吧啦吧啦
	# 業務執行結果插入原表
	df.loc[i, "稽核結果"] = "好耶"

    # 階段性儲存 ----------------------------
    save_space = 200	# 每執行兩百行儲存一次
    if i+1 % save_space == 0 and i != 0:
        df.to_excel(excel_path, index=0)
        log.info("階段性儲存...")
    # -------------------------------------