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() # 保存文件
