xlwings - 报表自动换算、汇总

---

需求

excel报表自动汇总。

现状

解决方案

1.基于Python的xlwings包，完成报表数据转换、换算、汇总。
2.使用PyInstaller将代码打包，降低使用门坎。

环境 & 工具

Win7
Excel
Anaconda3

1. xlwings 0.16.0
2. pandas 0.23.3
3. PyInstaller 3.6

主函数代码流程图

代码

# -*- conding: utf-8 _*_
'''
autoGP.py
/ 假设Metrics为模版输出
'''

from xlwings import constants
import xlwings as xw
import pandas as pd
import datetime
import time
import os


now = lambda: time.perf_counter()
PATH = os.path.join(os.path.expanduser('~'),r'Desktop\Metrics')


def check(strDat):
    try:
        # 检查输入为整数
        int(strDat)
        # 检查输入是否为：年月
        datetime.datetime.strptime(strDat, '%Y%m')
    except (TypeError, ValueError):
        raise Exception('请按要求输入,如当前为5月，则输入:202005')
    else:
        return strDat

def getM(dat):
    return datetime.datetime.strptime(dat, '%Y%m').strftime('%b')

def getFil(dat):
    # gp sz+hk
    lis = [f for f in os.listdir(PATH) 
           if 'P4P+KA GP_' + dat == f.split('.')[0]]
    ## 检查
    if len(lis) == 0 or (len(lis) == 1 and 'P4P+KA GP' not in lis[0]):
        raise FileNotFoundError('指定路径下："P4P+KA GP_%s.xlsx"文件不存在\
                                \n补充文件后重新运行。' % dat)
    # gp sz, hk
    fil = [f for l in os.walk(PATH) for f in l[2]
           if 'GP Analysis-' + dat == f.split('.')[0]
           or 'P4P & KA Schedules ' + dat + ' - Janice' == f.split('.')[0]]
    ## check
    if len(fil) < 2:
        raise FileNotFoundError('指定路径下："GP Analysis-%s.xlsx"\
                                或"P4P & KA Schedules %s"文件不存在\
                                \n补充文件后重新运行。' % (dat,dat))
    ##
    lis += fil
    # book rate
    cnt = 0
    while not (('Ex rate ' 
           + (datetime.datetime.strptime(dat, '%Y%m')
              + datetime.timedelta(cnt)).strftime('%m-%Y') 
           + '_book rate.xlsx') in os.listdir(list(os.walk(PATH))[1][0])):
        cnt += 20
        ## 检查
        if cnt == 200:
            raise FileNotFoundError("指定路径下没找到 'book rate' 文件.\
                                    补充文件后重新运行。")
    name = ('Ex rate ' 
       + (datetime.datetime.strptime(dat, '%Y%m')
          + datetime.timedelta(cnt)).strftime('%m-%Y') 
       + '_book rate.xlsx')
    lis.append(name)
    # daily ring ratio
    lis_1 = [f for f in os.listdir(list(os.walk(PATH))[1][0]) 
             if '日环比' in f and '~' not in f]
    ## 检查
    if len(lis_1) == 0:
        raise FileNotFoundError('指定路径下没找到 *日环比* 文件.\
                                补充文件后重新运行')
    lis_1 = sorted(lis_1
                   , key=lambda x: os.path.getmtime(os.path.join(
                       list(os.walk(PATH))[1][0], x))
                   , reverse=True)
    lis.append(lis_1[0])
    return lis

def getSZ(wb, dat):
    sht = wb.sheets[dat]
    cntR = sht[0, 0].current_region.rows.count
    # 参数检查
    if '账户名称' not in sht['A1:AV1'].value:
        wb.close()
        raise ValueError('SZ GP表中 *账户名称* 不存在或错误，\
                         请检查excel后重新运行。')
    #
    df = pd.DataFrame(sht['A2:AV' + str(cntR)].value
                      , columns=sht['A1:AV1'].value)
    # 去重
    df = df.groupby(['账户名称']).sum()
    df.reset_index(inplace=True)
    return df

def getHK(wb, dat):
    sht = wb.sheets['P4P ' + dat]
    cntR = sht[4, 0].current_region.rows.count
    cntC = sht[4, 0].current_region.columns.count
    # 参数检查
    if '用户名' not in sht[4, :cntC].value:
        wb.close()
        raise ValueError("HK GP表中 *用户名* 不存在，请检查excel后重新动行。")
    #
    df1 = pd.DataFrame(sht[5:cntR+4, :cntC].value
                      , columns=sht[4, :cntC].value)
    # 去重
    df1 = df1.groupby(['用户名']).sum()
    df1.reset_index(inplace=True)
    return df1

def result(sz, hk, f, strDat):
    '''
    将sz, hk的gp汇总写入指定表单P4P

    Parameters
    ----------
    sz : TYPE
        DESCRIPTION.
    hk : TYPE
        DESCRIPTION.
    f : TYPE
        DESCRIPTION.
    strDat : TYPE
        DESCRIPTION.

    Raises
    ------
    ValueError
        DESCRIPTION.检查表头是否符合规范

    Returns
    -------
    None.

    '''
    try:
        wb = xw