基于python的excel分析报告自动化输出_ppt分析报告自动化输出

背景：

接领导要求，要求产出几份关于外卖品类的分析报告，在需求数据和模板确定后，要按照这个模板生成多个品类的报告，每个品类相同的目标出一份报告，如下图所示：

1.读取原始数据

我的原始数据是从数据库里面汇总好了，再导出到excel里面，主要包括月份，近30天月售，门店数，品牌名称几个字段，

import numpy as np
import pandas as pd
import datetime,time
import os
import openpyxl as opxl
import xlrd
import xlwt

inputfile=r'D:\shiheng\市场数据\品类输出\原始数据.xlsx'
outputfile=r'D:\shiheng\市场数据\品类输出\卤味鸭脖报告.xlsx'#背景中展示的模板样子
df1=pd.read_excel(inputfile,sheet_name='卤味鸭脖')
print ('原始数据读取完毕')
1
2
3
4
5
6
7
8
9
10
11
12

2.品牌分析

2.1 开头插入

workbook=opxl.load_workbook(outputfile)
#sheet1=workbook.create_sheet(title='品牌分析') #因为模板已经有这个sheet，所以不需要创建
sheet1=workbook.get_sheet_by_name('品牌分析')
sheet1.cell(row=2,column=2).value='卤味鸭脖品牌分析' #往第二行第二列插入‘卤味鸭脖品牌分析’几个字
1
2
3
4

自定义一个插入函数，后面往excel中写入的时候可以用到

def insert_to_excel(sheet,data,rows,columns,r0,c0):
    for i in range(rows):
        for j in range(columns):
            sheet.cell(row=i+r0,column=j+c0).value=data.iloc[i,j]
            #print ('（%s,%s）插入成功'%i%j)
1
2
3
4
5

2.2 top20门店数

shops1=df1[df1['月份']==201911][['品牌名称','门店数']].groupby(['品牌名称'])['门店数'].sum().reset_index()
shops=shops1.sort_values('门店数',ascending=False).head(20)
#写入excel
rows1,columns1=shops.shape #查看行列数
insert_to_excel(sheet1,shops,rows1,columns1,r0=7,c0=2)
1
2
3
4
5

2.3 top20月售

sales1=df1[df1['月份']==201911][['品牌名称','近30天月售']].groupby(['品牌名称'])['近30天月售'].sum().reset_index()
sales=sales1.sort_values('近30天月售',ascending=False).head(20)
#写入excel
rows2,columns2=sales.shape
insert_to_excel(sheet1,sales,rows2,columns2,r0=7,c0=5)
1
2
3
4
5

2.4 top20开店数&关店数

#计算当月的门店数
brands_isopen1=df1[df1['月份']==201911][['品牌名称','门店数']]
brands_isopen1_gb=brands_isopen1.groupby(['品牌名称'])['门店数'].sum()
#计算上月的门店数
brands_isopen2=df1[df1['月份']==201910][['品牌名称','门店数']].rename(columns={'门店数':'上月门店数'})
brands_isopen2_gb=brands_isopen2.groupby(['品牌名称'])['上月门店数'].sum()
#合并并计算出新增门店数=当月-上月
brands_isopen=pd.merge(brands_isopen1_gb,brands_isopen2_gb,how='left',on='品牌名称').reset_index()
brands_isopen['新增门店数']=brands_isopen['门店数']-brands_isopen['上月门店数']
#根据上一步的计算结果筛选出新增数最多的top20品牌，注意这里是降序排列
open=brands_isopen[brands_isopen['新增门店数']>0][['品牌名称','新增门店数']].sort_values('新增门店数',ascending=False).rename(columns={'新增门店数':'新店数'}).head(20)
#同理，根据上一步结果筛选出新增门店《0（即闭店）最多的top20
close1=brands_isopen[brands_isopen['新增门店数']<0][['品牌名称','新增门店数']].sort_values('新增门店数').head(20)
close1['关店数']=close1['新增门店数'].apply(lambda x:abs(x))
close=close1[['品牌名称','关店数']]
#将上面的开店top20 和关店top20品牌写入excel
rows3,columns3=open.shape
rows4,columns4=close.shape
insert_to_excel(sheet1,open,rows3,columns3,r0=7,c0=8)
insert_to_excel(sheet1,close,rows4,columns4,r0=7,c0=11)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

3.深度分析品牌表

3.1读取原始数据
该数据也为数据库计算好导出到excel的，也可以在python中连接数据库并存储数据。数据格式主要如下表所示，字段有品类，品牌名称，城市id，月售，城市名称字段。要利用该数据进行品牌城市分析。

df2=pd.read_excel(inputfile,sheet_name='月售top20bycity')
c1=df2[df2['品类']=='卤味鸭脖'].sort_values('门店数',ascending=False) #只读取卤味鸭脖品类的数据
1
2

3.2 品牌月售top20 分城市

gb2=c1.groupby(['brand_name'])['门店数','月售'].sum().reset_index()
brands_deep1=gb2.sort_values('月售',ascending=False).head(20)
brands_deep6=pd.merge(brands_deep1,brands_isopen[brands_isopen['新增门店数']>0][['品牌名称','新增门店数']],left_on='brand_name',right_on='品牌名称',how='left').drop(['品牌名称'],axis=1)
brands_deep7=pd.merge(brands_deep6,brands_isopen[brands_isopen['新增门店数']<0][['品牌名称','新增门店数']],left_on='brand_name',right_on='品牌名称',how='left').drop(['品牌名称'],axis=1)
brands_deep3=brands_deep7.rename(columns={'新增门店数_x':'11-12月新店','新增门店数_y':'11-12月关店'})
brands_deep3['11-12月关店']=brands_deep3['11-12月关店'].apply(lambda x:abs(x))
#要将城市改成如背景模板中的那样，“上海(356) 重庆(276) 深圳(258) 北京(251) 长沙(214) 天津(213)”，按照门店数降序排列城市，括号内为品牌数 
def combine(t1,t2):
    t=str(t1)+'('+str(t2)+')'
    return t
c1['城市信息']=c1.apply(lambda x:combine(x.城市名称,x.门店数),axis=1)
brands_deep2=pd.merge(brands_deep3,c1[['brand_name','城市信息']],on='brand_name',how='left')
top_brands=brands_deep1['brand_name'].drop_duplicates()

result1=[]
for brand in list(top_brands):
    dic1={}
    dic1['brand_name']=brand
    datasets=brands_deep2[brands_deep2['brand_name']==brand]
    city_list=[]
    for z in range(len(datasets)):
        m1=datasets['城市信息'].iloc[z]
        city_list.append(m1)
    city_str=str(city_list)
    dic1['city_info']=city_str.replace('[','').replace(']','').replace("'",'').replace(',','')
    result1.append(dic1)
print ('深入分析品牌表完成')
brands_deep4=pd.DataFrame(result1)
brands_deep5=pd.merge(brands_deep3,brands_deep4,on='brand_name',how='left') 
#写入数据
rows5,columns5=brands_deep5.shape
insert_to_excel(sheet1,brands_deep5,rows5,columns5,r0=32,c0=2)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

3.3 深度分析城市表

#城市名称   门店数  前top20
gb3=c1.groupby(['城市名称'])['门店数','月售'].sum().reset_index()
city_deep1=gb3.sort_values('门店数',ascending=False).head(20)

city_deep2=pd.merge(city_deep1[['城市名称']],c1,on='城市名称',how='left')

city_deep2['品牌信息']=city_deep2.apply(lambda x:combine(x.brand_name,x.门店数),axis=1)

top_citys=city_deep1['城市名称'].drop_duplicates()

result2=[]
for city in list(top_citys):
    dic2={}
    dic2['city']=city
    datasets=city_deep2[city_deep2['城市名称']==city]
    brands_list=[]
    for z in range(len(datasets)):
        m1=datasets['品牌信息'].iloc[z]
        brands_list.append(m1)
    brands_str=str(brands_list)
    dic2['brands_info']=brands_str.replace('[','').replace(']','').replace("'",'').replace(',','')
    result2.append(dic2)
print ('深度分析城市表完成')
city_deep3=pd.DataFrame(result2).rename(columns={'city':'城市名称'})
city_deep4=pd.merge(city_deep1,city_deep3,on='城市名称',how='left')
#写入数据
rows6,columns6=city_deep4.shape
insert_to_excel(sheet1,city_deep4,rows6,columns6,r0=57,c0=2)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

4.分城市的sheet表

#序号	店名	月售	地址	是否新店
df3=pd.read_excel(inputfile,sheet_name='营业详情bycity')
df4=df3[(df3['品类']=='卤味鸭脖')&(df3['月份']==201911)]
df5=pd.merge(city_deep1,df4,on='城市名称',how='left')
df6=df3[(df3['品类']=='卤味鸭脖')&(df3['月份']==201910)]
df7=pd.merge(df4[['stand_merchant_id']],df6[['stand_merchant_id','shop_name']],how='left',on='stand_merchant_id')
def  is_null(x):
    y=str(x)
    if y=='nan':
        return '是'
    else:
        return ''

#因为第一个《品牌分析》的模板的颜色已经定好，往里面写数据的时候就会按照已设定好的格式往里面写，但是后面分城市的sheet，因为每个品类每个城市的品牌数不一致，且每个品牌的门店数也不一致，因此无法定死单元格。只能循环计算每个品牌的门店数计算需要多少行，自动往下填充新的品牌需要的行数。
#并且，在上面的数据都已经成功填进excel对应的单元格内后，我们需要对单元格进行合并，颜色填充，加粗，斜体，颜色修改，字体等修改
from openpyxl.styles import PatternFill,Border,Protection,Font,Side,Alignment
border=Border(left=Side(border_style=None,color='FF000000'),right=Side(border_style=None,color='FF000000'),top=Side(border_style=None,color='FF000000'),bottom=Side(border_style=None,color='FF000000'))
from copy import copy
#把涉及到的几种背景颜色都列好
font1=Font(name='Calibri',size=24,color='FF4682B4',underline="single")
font2=Font(name='Calibri',size=20,color='FF2E8B57')
font3=Font(name='Calibri',size=14,color='FF2E8B57')
font4=Font(name='Calibri',size=16,color='FF00CD00')
font5=Font(name='Calibri',size=12,color='FF2E8B57')

fill1=PatternFill(fill_type='solid',fgColor='FF87CEFA')
align = Alignment(horizontal='center',vertical='center',wrap_text=True)

side1=Side(border_style='medium',color='FFDAA520')
border1 = Border(top=side1)
border2 = Border(bottom=side1)

df7['是否新店']=df7['shop_name'].apply(lambda x:is_null(x))
df8=pd.merge(df5,df7,on=['stand_merchant_id','shop_name'],how='left')[['城市名称','stand_merchant_id','brand_name','shop_name','近30天月售','shop_address','是否新店']].reset_index()

for city in list(top_citys):
    df_city_i=df8[df8['城市名称']==city]
    df_brands=df_city_i['brand_name'].drop_duplicates()
    sheet_i=workbook.create_sheet(city)
    #sheet_i=workbook.get_sheet_by_name(city)
    shops_cnt=df_city_i['stand_merchant_id'].drop_duplicates()
    newshops_cnt=df_city_i[df_city_i['是否新店']=='是']['stand_merchant_id'].drop_duplicates()
    city_shops_cnt=city_deep1[city_deep1['城市名称']==city]['门店数']

    sheet_i.cell(row=2,column=3).value='《《返回首页'
    sheet_i.cell(row=2,column=3).font=font1 #返回首页的格式，选用font1

    sheet_i.cell(row=3,column=3).value=city+'品牌分析(仅分析全国TOP20品牌)'
    sheet_i.cell(row=3,column=3).font=font2 #城市的格式，选用font2

    sheet_i.cell(row=5,column=3).value='品牌数'
    sheet_i.cell(row=5,column=3).fill=fill1
    sheet_i.cell(row=5,column=3).font=font3 #第5行第3列的格式
    sheet_i.cell(row=5,column=4).fill=fill1 #第5行第4列的格式
    sheet_i.cell(row=5,column=3).alignment=align 
    
    sheet_i.cell(row=5,column=5).value='11月门店数'
    sheet_i.cell(row=5,column=5).font=font3
    sheet_i.cell(row=5,column=5).fill=fill1
    sheet_i.cell(row=5,column=6).fill=fill1
    sheet_i.cell(row=5,column=5).alignment=align
    
    sheet_i.cell(row=5,column=7).value='10-11月新店数'
    sheet_i.cell(row=5,column=7).font=font3
    sheet_i.cell(row=5,column=7).fill=fill1
    sheet_i.cell(row=5,column=7).alignment=align
    
    sheet_i.cell(row=6,column=3).value=len(df_brands)
    sheet_i.cell(row=6,column=3).font=font4
    sheet_i.cell(row=6,column=3).fill=fill1
    sheet_i.cell(row=6,column=3).alignment=align
    
    sheet_i.cell(row=6,column=4).fill=fill1
    sheet_i.cell(row=6,column=5).value=len(shops_cnt)
    sheet_i.cell(row=6,column=5).font=font4
    sheet_i.cell(row=6,column=5).fill=fill1
    sheet_i.cell(row=6,column=5).alignment=align
    
    sheet_i.cell(row=6,column=6).fill=fill1
    sheet_i.cell(row=6,column=7).value=len(newshops_cnt)
    sheet_i.cell(row=6,column=7).font=font4
    sheet_i.cell(row=6,column=7).fill=fill1
    sheet_i.cell(row=6,column=7).alignment=align
    ##开始按照品牌门店数依次填充
    s0=10 #默认初始行为第10行
    for brand in list(df_brands):
        print (brand)
        df_brands_j=df_city_i[df_city_i['brand_name']==brand][['stand_merchant_id','shop_name','近30天月售','是否新店','shop_address']]
        df_brands_i=df_brands_j[['shop_name','近30天月售','是否新店','shop_address']].sort_values('近30天月售',ascending=False).reset_index(drop=True).reset_index()
        brand_shops_cnt=df_brands_j['stand_merchant_id'].drop_duplicates()
        brand_newshops_cnt=df_brands_j[df_brands_j['是否新店']=='是']['stand_merchant_id'].drop_duplicates()
        ri,ci=df_brands_i.shape
        sheet_i.cell(row=s0,column=3).value='品牌:'
        sheet_i.cell(row=s0,column=3).font=font4
        sheet_i.cell(row=s0,column=4).value=str(brand)
        sheet_i.cell(row=s0,column=4).font=font4
        
        sheet_i.cell(row=s0+2,column=3).value='11月门店数'
        sheet_i.cell(row=s0+2,column=3).font=font3
        sheet_i.cell(row=s0+2,column=3).fill=fill1
        sheet_i.cell(row=s0+2,column=3).alignment=align
        
        sheet_i.cell(row=s0+2,column=4).fill=fill1
        sheet_i.cell(row=s0+2,column=5).value='11月门店城市占比'
        sheet_i.cell(row=s0+2,column=5).font=font3
        sheet_i.cell(row=s0+2,column=5).fill=fill1
        sheet_i.cell(row=s0+2,column=5).alignment=align
        
        sheet_i.cell(row=s0+2,column=6).fill=fill1

        sheet_i.cell(row=s0+2,column=7).value='10-11月新门店数'
        sheet_i.cell(row=s0+2,column=7).font=font3
        sheet_i.cell(row=s0+2,column=7).fill=fill1
        sheet_i.cell(row=s0+2,column=7).alignment=align
        
        sheet_i.cell(row=s0+3,column=3).value=len(brand_shops_cnt)
        sheet_i.cell(row=s0+3,column=3).font=font5
        sheet_i.cell(row=s0+3,column=3).fill=fill1
        sheet_i.cell(row=s0+3,column=3).alignment=align
        
        sheet_i.cell(row=s0+3,column=4).fill=fill1
        sheet_i.cell(row=s0+3,column=5).value= "%.2f%%" % (100*len(brand_shops_cnt)/int(city_shops_cnt))
        sheet_i.cell(row=s0+3,column=5).font=font5
        sheet_i.cell(row=s0+3,column=5).fill=fill1
        sheet_i.cell(row=s0+3,column=5).alignment=align
        
        sheet_i.cell(row=s0+3,column=6).fill=fill1
        sheet_i.cell(row=s0+3,column=7).value=len(brand_newshops_cnt)
        sheet_i.cell(row=s0+3,column=7).font=font5
        sheet_i.cell(row=s0+3,column=7).fill=fill1
        sheet_i.cell(row=s0+3,column=7).alignment=align
        
        sheet_i.cell(row=s0+5,column=3).value='点击+，查看11月营业门店详情'
        sheet_i.cell(row=s0+5,column=3).font=font3
        
        sheet_i.cell(row=s0+7,column=3).value='序号'
        sheet_i.cell(row=s0+7,column=4).value='店名'
        sheet_i.cell(row=s0+7,column=5).value='月售'
        sheet_i.cell(row=s0+7,column=6).value='是否新店'
        sheet_i.cell(row=s0+7,column=7).value='地址'
        sheet_i.cell(row=s0+7,column=3).font=font5
        sheet_i.cell(row=s0+7,column=4).font=font5
        sheet_i.cell(row=s0+7,column=5).font=font5
        sheet_i.cell(row=s0+7,column=6).font=font5
        sheet_i.cell(row=s0+7,column=7).font=font5
        sheet_i.cell(row=s0+7,column=3).fill=fill1
        sheet_i.cell(row=s0+7,column=4).fill=fill1
        sheet_i.cell(row=s0+7,column=5).fill=fill1
        sheet_i.cell(row=s0+7,column=6).fill=fill1
        sheet_i.cell(row=s0+7,column=7).fill=fill1
        sheet_i.cell(row=s0+7,column=3).alignment=align
        sheet_i.cell(row=s0+7,column=4).alignment=align
        sheet_i.cell(row=s0+7,column=5).alignment=align
        sheet_i.cell(row=s0+7,column=6).alignment=align
        sheet_i.cell(row=s0+7,column=7).alignment=align
        
        s1=s0+8
        
        insert_to_excel(sheet_i,df_brands_i,ri,ci,s1,3)
        si=len(df_brands_i)
        print (si)
        s0=s1+si+3
        sheet_i.cell(row=s0,column=3).value='品牌:'
        sheet_i.cell(row=s0,column=4).value=str(brand)
        sheet_i.cell(row=s0+2,column=3).value='11月门店数'
        sheet_i.cell(row=s0+2,column=5).value='11月门店城市占比'
        sheet_i.cell(row=s0+2,column=7).value='新门店数'
        sheet_i.cell(row=s0+3,column=3).value=len(brand_shops_cnt)
        sheet_i.cell(row=s0+3,column=5).value="%.2f%%" % (100*len(brand_shops_cnt)/int(city_shops_cnt))
        sheet_i.cell(row=s0+3,column=7).value=len(brand_newshops_cnt)
        
        sheet_i.cell(row=s0+2,column=3).alignment=align
        sheet_i.cell(row=s0+2,column=5).alignment=align
        sheet_i.cell(row=s0+2,column=7).alignment=align
        sheet_i.cell(row=s0+3,column=3).alignment=align
        sheet_i.cell(row=s0+3,column=5).alignment=align
        sheet_i.cell(row=s0+3,column=7).alignment=align
        
        sheet_i.cell(row=s0+7,column=3).value='序号'
        sheet_i.cell(row=s0+7,column=4).value='店名'
        sheet_i.cell(row=s0+7,column=5).value='月售'
        sheet_i.cell(row=s0+7,column=6).value='是否新店'
        sheet_i.cell(row=s0+7,column=7).value='地址'
        sheet_i.cell(row=s0+7,column=3).alignment=align
        sheet_i.cell(row=s0+7,column=4).alignment=align
        sheet_i.cell(row=s0+7,column=5).alignment=align
        sheet_i.cell(row=s0+7,column=6).alignment=align
        sheet_i.cell(row=s0+7,column=7).alignment=align

        insert_to_excel(sheet_i,df_brands_i,ri,ci,s1,3)
        print (s0,s1)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191

5.设置超链接(城市链接&返回首页链接2个)

在所有数据和格式都完成后，我们还差超链接设置，查了一些资料

for x in range(57,77): #深度分析城市表的城市列表所在单元格行号
    link_from=workbook.get_sheet_by_name('品牌分析')['B'+str(x)]
    city_name=link_from.value
    link_to="#"+city_name+"!B1"
    print (link_to)
    link_from.hyperlink=link_to 

    link_from2=workbook.get_sheet_by_name(city_name).cell(row=2,column=3)
    link_to2="#品牌分析!B2"
    link_from2.hyperlink=link_to2

workbook.save(outputfile)
workbook.close()
print ('sucess')
1
2
3
4
5
6
7
8
9
10
11
12
13
14

以上，全部结束，其他品类报告只需要替换一下数据源的名称就可以。这是第一次写的比较完善的一份excel输出报告，有些地方可能写的很罗嗦，后面会继续改进。