python实现报表自动化2

python实现报表自动化2

此报表展示直连家数的直连情况以及购进金额情况，涉及到的明细数据60万+，所用到的辅助名单也较多，制作过程比较繁琐；自动化代码如下：

import pandas as pd
import numpy as np

# 忽略warning警告
import warnings
warnings.filterwarnings('ignore')

#赋值
file_zl = '拜耳九州通名单直连情况表-20220801.xlsx'
lie_f = '7月流向购进金额'
lie_g = '（扣除T1-T2-T3）7月流向购进金额'
lie_h = 'DDI7月流向直连家数'
lie_i = '（扣除T1-T2-T3）DDI直连购进金额'


#读取报表
zl_1 = pd.read_excel(file_zl, sheet_name='拜耳&九州通名单-直连情况')
jf = pd.read_excel('22年X月流向购进金额与直连占比-测试.xlsx')
# 交付表中使用上面的值填充下边空白单元格
jf['百强'] = jf['百强'].fillna(method='ffill').iloc[0:10]
# 对齐参数
pd.set_option('display.unicode.ambiguous_as_wide', True)
pd.set_option('display.unicode.east_asian_width', True)


'''-----------------------------左半部分---------------------------'''
#家数、直连家数、占比赋值
# 用坐标给家数赋值
jf.iloc[0, 2] = zl_1.iloc[0,1]
jf.iloc[1, 2] = zl_1.iloc[4,1]
jf.iloc[2, 2] = zl_1.iloc[5,1]
jf.iloc[3, 2] = zl_1.iloc[9,1]
jf.iloc[4, 2] = zl_1.iloc[10,1]
jf.iloc[5, 2] = zl_1.iloc[11,1]
jf.iloc[7, 2] = zl_1.iloc[20,1]


# 用坐标给直连家数赋值
jf.iloc[0, 3] = zl_1.iloc[0,2]
jf.iloc[1, 3] = zl_1.iloc[4,2]
jf.iloc[2, 3] = zl_1.iloc[5,2]
jf.iloc[3, 3] = zl_1.iloc[9,2]
jf.iloc[4, 3] = zl_1.iloc[10,2]
jf.iloc[5, 3] = zl_1.iloc[11,2]
jf.iloc[7, 3] = zl_1.iloc[20,2]

# 用坐标给直连占比赋值
jf.iloc[0, 4] = zl_1.iloc[0,3]
jf.iloc[1, 4] = zl_1.iloc[4,3]
jf.iloc[2, 4] = zl_1.iloc[5,3]
jf.iloc[3, 4] = zl_1.iloc[9,3]
jf.iloc[4, 4] = zl_1.iloc[10,3]
jf.iloc[5, 4] = zl_1.iloc[11,3]
jf.iloc[7, 4] = zl_1.iloc[20,3]



''' ----------------------------- 右半部分 ------------------------ '''
#7月流向购进金额
# 读取百强sheet    需要在百强sheet中加一列“客户属性”，值全为“百强”
zl_baiqiang = pd.read_excel(file_zl, sheet_name='百强')
baiqiang = zl_baiqiang[['标准名称','客户属性','是否直连']]
baiqiang.to_excel('0百强名单.xlsx')

# 读取非百强sheet
zl_feibaiqiang = pd.read_excel(file_zl, sheet_name='非百强')
feibaiqiang = zl_feibaiqiang[['标准名称','客户属性','是否直连']]
# 替换列值，将‘备案连锁’替换为‘非百强-备案连锁’，将‘协议连锁’替换为‘非百强-协议连锁’
feibaiqiang.replace({"客户属性":{'备案连锁':'非百强-备案连锁','协议连锁':'非百强-协议连锁'}},inplace=True)
#将非百强sheet中的空值删掉
feibaiqiang = feibaiqiang.dropna(axis=0,subset = ['标准名称'])
feibaiqiang.to_excel('0非百强名单.xlsx')

# 读取商业sheet
zl_shangye = pd.read_excel(file_zl, sheet_name='商业')
shangye = zl_shangye[['标准名称','客户属性','是否直连']]
# 将“2022年属性”替换为“客户属性”
# shangye.rename(columns={'2022属性':'客户属性'}, inplace = True)
shangye.to_excel('0商业名单.xlsx')

#汇总百强-非百强-商业名单
heji = pd.merge(baiqiang,feibaiqiang,on=['标准名称','客户属性','是否直连'],how='outer')   #百强与非百强合集
heji_1 = pd.merge(heji,shangye,on=['标准名称','客户属性','是否直连'],how='outer')  #合集1与商业合集——百强-非百强-商业合集
heji_1.to_excel('1百强-非百强-商业名单汇总.xlsx')

#汇总直连商业名单
heji_2 = heji_1[heji_1['是否直连']=='是']
heji_2.to_excel('1直连名单汇总.xlsx')



# 读取流向明细
mx = pd.read_excel('流向明细.xlsx')
# 只筛选明细表中“卖方名称”“买方名称”“金额”三列
mx = mx[['卖方名称','买方名称','金额']]

mx.to_excel('2使用流向明细.xlsx')



## 6月流向购进金额F列
# 用买方名称V百强-非百强-商业汇总名单的客户属性
mx_1 = pd.merge(mx, heji_1, left_on='买方名称',right_on = '标准名称',how='left')     #相当于V客户属性,没有指定how，默认保留都存在的
#删除多余的“标准名称”列
mx_1 = mx_1.drop(labels='标准名称',axis=1)
#将客户属性为空白的替换为“其他”
mx_1.loc[mx_1['客户属性'].isnull(),'客户属性']='其他'
mx_1.loc[mx_1['是否直连'].isnull(),'是否直连']='否'
mx_1.to_excel('2明细1-属性.xlsx')

#用属性透视金额
df_1 = mx_1.groupby(['客户属性'])['金额'].sum().reset_index()
df_1.rename(columns={'客户属性':'百强类型'}, inplace = True)
df_1.to_excel('df_1.xlsx')

#赋值
jf_1 = pd.merge(jf,df_1,on=['百强类型'],how='left')
#修改表头，将金额替换为”7月流向购进金额“
jf_1.rename(columns={'金额':lie_f},inplace=True)
#计算合计
jf_1.iloc[7,5] = df_1['金额'].sum()
#空值替换为0
jf_1[lie_f].fillna(0,inplace=True)
jf_1.to_excel('jf_1.xlsx')



## （扣除T1-T2-T3）7月流向购进金额
#整理扣除上游是T1T2T3的流向
#用卖方名称VT1T2T3名单
#读取t1t2t3名单
t1t2t3 = pd.read_excel('T1T2T3.xlsx')
t1t2t3_1 = t1t2t3[['标准客户名称']]
t1t2t3_1.to_excel('1t1t2t3名单.xlsx')

#用卖方名称VT1T2T3名单
mx_2 = pd.merge(mx_1,t1t2t3_1,left_on='卖方名称',right_on='标准客户名称',how='left')
mx_2.loc[mx_2['标准客户名称'].isnull(),'标准客户名称']='否'
#筛选扣除T1T2T3的流向明细
mx_kouchu = mx_2[mx_2['标准客户名称']=='否']
mx_kouchu.to_excel('2明细-扣除t1t2t3.xlsx')


#对扣除t1t2t3的明细进行透视
df_2 = mx_kouchu.groupby(['客户属性'])['金额'].sum().reset_index()
df_2.rename(columns={'客户属性':'百强类型'},inplace=True)
df_2.to_excel('df_2.xlsx')
#赋值
jf_2 = pd.merge(jf_1,df_2,on=['百强类型'],how='left')
jf_2.rename(columns={'金额':lie_g},inplace=True)
jf_2.iloc[7,6] = df_2['金额'].sum()
#空值替换为0
jf_2[lie_g].fillna(0,inplace=True)
jf_2.to_excel('jf_2.xlsx')



## DDI7月流向直连家数

#链接直连名单与明细表
a1 = pd.merge(heji_2,mx_1,left_on='标准名称',right_on='买方名称',how='left')
#删除空白值
a1.dropna(axis=0,subset = ["买方名称"],inplace=True)
#按照 标准名称 去重
a1.drop_duplicates(subset=['标准名称'],inplace=True)
#按照客户属性透视计数
df_3 = a1.groupby(['客户属性_x'])['标准名称'].count().reset_index()
df_3.rename(columns={'客户属性_x':'百强类型'},inplace=True)   #替换列名
df_3.to_excel('df_3.xlsx')

#赋值
jf_3 = pd.merge(jf_2,df_3,on=['百强类型'],how='left')
jf_3.rename(columns={'标准名称':lie_h},inplace=True)    #替换列名
jf_3.iloc[7,7] = df_3['标准名称'].sum()
#空值替换为0
jf_3[lie_h].fillna(0,inplace=True)
jf_3.to_excel('jf_3.xlsx')



## （扣除T1-T2-T3）DDI直连购进金额
#筛选出扣除T1T2T3中，直连为“是”的流向明细
mx_zl_kouchut1t2t3 = mx_kouchu[mx_kouchu['是否直连']=='是']
# mx_zl_kouchut1t2t3.to_excel('00.xlsx')
#透视金额
df_4 = mx_zl_kouchut1t2t3.groupby(['客户属性'])['金额'].sum().reset_index()
df_4.rename(columns={'客户属性':'百强类型'},inplace=True)    #替换列名
df_4.to_excel('df_4.xlsx')

# #赋值
jf_4 = pd.merge(jf_3,df_4,on=['百强类型'],how='left')
jf_4.rename(columns={'金额':lie_i},inplace=True)    #替换列名
jf_4.iloc[7,8] = df_4['金额'].sum()
#空值替换为0
jf_4[lie_i].fillna(0,inplace=True)
# jf_4.to_excel('jf_4.xlsx')



'''----------------------------------输出文件-------------------------------'''
# 输出交付文件
jf_4.to_excel('22年7月流向购进金额与直连占比-Report.xlsx',index=None)

# 修改列宽
file_path = './22年7月流向购进金额与直连占比-Report.xlsx'   ####################修改文件名
sheet_name = 'Sheet1'

with pd.ExcelWriter(file_path, engine='xlsxwriter') as writer:
    # 数据传给Excel的writer
    jf_4.to_excel(writer, index=False, sheet_name=sheet_name)
    # 再从writer加载回该sheet
    worksheet = writer.sheets[sheet_name]
    # 循环每一列的列序号，设置列宽为20个单位（指Excel中的列宽单位）
    # for idx in range(df_jf_res.shape[1]):
    #     print(df_jf_res.shape[1])
    worksheet.set_column(0,4,12)
    worksheet.set_column(5,5,20)    # .set_column(a,b,c)函数三个参数，a代表起始列，b代表终止列，c为列宽
    worksheet.set_column(5, 5, 20)
    worksheet.set_column(6, 6, 35)
    worksheet.set_column(7, 7, 22)
    worksheet.set_column(8, 8, 36)
    # worksheet.set_column(6, 6, 15)
    writer.save()     # 保存文件