python实现报表自动化1

python 实现报表自动化1

1. 报表介绍
   现有3张表，一张为交付表，一张为22年销售表，还有一张21年的销售表；
   交付表表头如下：
   
   21年、22年销售表头如下：
   
   报表目的，将22年和21年的购进金额添加到F、G列，并且将22年新增的终端名称累加到新增的交付表下面。

2. 自动化代码如下：

import pandas as pd

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

################################################## 修改文件名
#导入文件
df_22 = pd.read_excel('22年终端销售分析.xlsx')
df_21 = pd.read_excel('21年终端销售分析.xlsx')
df_jf = pd.read_excel('DDI直连清单-20220826.xlsx')

# 参数赋值
last_year = '21年8月购进'    ##############
this_year = '22年8月购进'    ##############
file_ddi = 'DDI直连清单-20220902.xlsx'      ##############################修改文件名


#屏蔽报错——SettingWithCopyWarning
pd.set_option('mode.chained_assignment', None)

#对齐参数
pd.set_option('display.unicode.ambiguous_as_wide', True)
pd.set_option('display.unicode.east_asian_width', True)

#处理交付表
#删除交付表21年与22年金额列
df_jf1 = df_jf.drop(labels=[last_year,this_year],axis = 1)  #删除交付表最后2列，方便后续合并，删除后不改变原表df_jx

#处理22年销售表
#为了找出新增终端，只保留：终端名称、省市区、终端类型几列， 并把删除后表头中”客户省、客户市、客户区“替换为”省、市、区“
df_22_1 = df_22[["终端名称","客户省","客户市","客户区","终端类型"]]
#删除df_22_1索引为0的行
df_22_1.drop(df_22_1.index[0], inplace=True)


#修改列名
df_22_1.rename(columns={'客户省':'省','客户市':'市','客户区':'区'}, inplace = True)

#将22年新增终端增到交付表中
#交付表与22表交集部分
df_jj = pd.merge(df_22_1, df_jf1, on=['终端名称'])
# df_jj.to_excel('df_jj.xlsx')

#差集——用22销售表减去22年与交付表的交集，得到22年7月新增部分终端
df_xinzeng = pd.concat([df_22_1,df_jj]).drop_duplicates(subset=['终端名称'],keep=False)

#删除交集与差集合并后的多余列
df_xinzeng = df_xinzeng.drop(labels=['省_x','市_x', '区_x', '终端类型_x','省_y' ,'市_y', '区_y', '终端类型_y'],axis = 1)
# df_xinzeng.to_excel('df_xinzeng.xlsx')

#将新增的终端放到交付表中
df_jf_res = pd.merge(df_jf1, df_xinzeng, on=['终端名称','省','市','区','终端类型'], how='outer')
# df_jf_res.to_excel('2.xlsx')


#将21年金额放到交付表
#删除21年索引为0的行
df_21.drop(df_21.index[0], inplace=True)

#筛选df_jf_res需要的两列
df_21=df_21[["终端名称","合计.1"]]

# 合并df_jf_res与df_21中的金额
df_jf_res = pd.merge(df_jf_res,df_21,how="left",on="终端名称")

#将合并表的21年金额更名
df_jf_res.rename(columns={'合计.1':last_year}, inplace = True)

#将21年金额列数值中”，“替换掉
df_jf_res[last_year] = df_jf_res[last_year].str.replace(',','')
df_jf_res[last_year] = pd.to_numeric(df_jf_res[last_year],errors='raise')
# 将交付表中21年金额为空的填充为0
df_jf_res.fillna(0,inplace=True)



#将22年金额放到交付表
#删除22年索引为0的行
df_22.drop(df_22.index[0], inplace=True)
#筛选df_jf_res需要的两列
df_22=df_22[["终端名称","合计.1"]]

# 合并df_jf_res与df_22中的金额
df_jf_res = pd.merge(df_jf_res,df_22,how="left",on="终端名称")

#将合并表的22年金额更名
df_jf_res.rename(columns={'合计.1':this_year}, inplace = True)

#将22年金额列数值中”,“替换掉
df_jf_res[this_year] = df_jf_res[this_year].str.replace(',','')
df_jf_res[this_year] = pd.to_numeric(df_jf_res[this_year],errors='raise')
# 将22年金额为空的填充为0
df_jf_res.fillna(0,inplace=True)


#输出文件
df_jf_res.to_excel(file_ddi,index=False)


#修改列宽
file_path = './DDI直连清单-20220902.xlsx'      ####################修改文件名
sheet_name = 'Sheet1'

with pd.ExcelWriter(file_path, engine='xlsxwriter') as writer:
    # 数据传给Excel的writer
    df_jf_res.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,0, 60)
    worksheet.set_column(5,6, 15)    # .set_column(a,b,c)函数三个参数，a代表起始列，b代表终止列，c为列宽
    # worksheet.set_column(6, 6, 15)
    writer.save()   # 保存文件