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

背景：

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

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 ('原始数据读取完毕')

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='卤味鸭脖品牌分析' #往第二行第二列插入‘卤味鸭脖品牌分析’几个字

自定义一个插入函数，后面往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)

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)

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)

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)

3.深度分析品牌表

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

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

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)

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)

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)

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')

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