# -*- coding: utf-8 -*-
"""
Created on Thu Mar 19 08:42:16 2020

@author: Administrator
"""

from docx.shared import Pt
from docx.enum.style import WD_STYLE_TYPE
from docx.oxml.ns import qn
from docx.shared import Inches
from docx import Document
import xlsxwriter
import numpy as np
import pandas as pd
import os
import matplotlib.pyplot as plt
from openpyxl.styles import Alignment,Font
from openpyxl import Workbook, load_workbook
from openpyxl.drawing.image import Image
import openpyxl
import xlrd,xlsxwriter
from docx.shared import Pt
from math import ceil
import docx

plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
plt.rcParams['axes.unicode_minus'] = False # 用来正常显示负号

path = 'D:/_3G周报'
path_3g='D:/_3G周报/原始数据'+'//'
path_3g_busy='D:/_3G周报/登记数据'+'//'
os.chdir(path)


def huanhang(cell):
    cell.alignment = Alignment(horizontal='general',
                                vertical='bottom',
                                text_rotation=0,
                                wrap_text=True,
                                shrink_to_fit=True,
                                indent=0)
    cell.font = Font(name='Calibri',
                    size=11,
                    color='FF000000',
                    bold=False,
                    italic=False,
                    vertAlign=None,
                    underline='none',
                    strike=False)
def chuli(filename,sheetname):
    wb = load_workbook('./输出文件/'+filename)
    ws = wb[sheetname]
    a=list(ws[1])
    for cell in a:
        huanhang(cell)
    wb.save('./输出文件/'+ filename)
    print('喵喵已经把'+filename+'的'+sheetname+'自动转行好了')

def chuli2(filename,sheetname):
    wb = load_workbook('./输出文件/'+filename)
    ws = wb[sheetname]
    a=list(ws[2])
    for cell in a:
        huanhang(cell)
    wb.save('./输出文件/'+ filename)
    print('喵喵已经把'+filename+'的'+sheetname+'自动转行好了')


def jzjz(x,y):
    if x==0 and y==0:
        return '零话务零流量'
    elif x==0:
        return '零话务'
    elif y==0:
        return '零流量'
    elif x>2500/30*7:
        return '超级基站'
    elif x>600/30*7:
        return '普通基站'
    elif x<60/30*7 and y<10/30*7:
        return '话务流量超低基站'
    elif x<60/30*7 and y>10/30*7:
        return '话务超低基站'
    elif y<10/30*7:
        return '流量超低基站'
    elif y>10/30*7:
        return '低价值基站'
def hb(a,b,c,d):
    if a>b and c>d:
        return '话务流量双增长'
    elif a>b:
        return '话务增长'
    elif a<=b and c<=d:
        return '话务流量双降低'
    elif a<=b and c>d:
        return '流量增长'

def draw_bar_graph(col1,col2,country_list,pic_name):
    y1 = col1.T.values
    y2 = col2.T.values
    plt.figure(figsize=(12, 4))
    x1= range(0,len(country_list)) 
    x2 = [i+0.35 for i in x1] 
    plt.bar(x1,y1,color='g',width = 0.3,alpha=0.6,label= '上周')
    plt.bar(x2,y2,color='b',width = 0.3,alpha=0.6,label= '本周')
    for x,y in zip(x1,y1):
        plt.text(x, y*1.001, '%d' % y, ha='center', va= 'bottom',fontsize=8)
    for x,y in zip(x2,y2 ):
        plt.text(x, y*1.001, '%d' % y, ha='center', va= 'bottom',fontsize=8)
    plt.xlabel(pic_name)
    plt.xticks(range(0,len(country_list)),country_list)
    plt.ylabel('区县')
    plt.legend(loc='upper middle')
    plt.title(pic_name)
    plt.savefig('./pic/' + pic_name +".png",format='png', dpi=110)  
    plt.close()


def draw_all_pic(df1,df2,country_list):
    draw_bar_graph(df1['物理站址'],df2['物理站址'],country_list,'基站数量')
    draw_bar_graph(df1['上周语音话务量（erl）'],df2['本周语音话务量（erl）\t'],country_list,'话务量（erl）')
    draw_bar_graph(df1['上周DO数据流量（GB）\t'],df2['本周DO数据流量（GB）\t'],country_list,'DO流量(GB)')
    draw_bar_graph(df1['上周DO用户同时在线最大数'],df2['本周DO用户同时在线最大数'],country_list,'DO用户数')
    draw_bar_graph(df1['上周登记用户数'],df2['本周登记用户数'],country_list,'用户数')
    print('喵喵已经画好各区县汇总图片')


def huatu2(filename,sheetname):
    wb=load_workbook('./输出文件/'+filename)
    ws1=wb[sheetname]
    img = Image('./pic/' +"话务量（erl）.png")
    ws1.add_image(img, 'A1')
    img = Image('./pic/' + "DO流量(GB).png")
    ws1.add_image(img, 'A26')
    img = Image('./pic/' + "DO用户数.png")
    ws1.add_image(img, 'A51')
    img = Image('./pic/' + "用户数.png")
    ws1.add_image(img, 'A77')
    wb.save('./输出文件/'+filename)
    print('喵喵已经把'+filename+'的图贴好了')    

def jzsl(bzjzs,szjzs):
    if bzjzs-szjzs>0:
        return '增加'+str(round(bzjzs-szjzs,2))
    elif bzjzs-szjzs<0:
        return '减少'+str(round(szjzs-bzjzs,2))
    elif bzjzs-szjzs==0:
        return '不变'

   
files_3G = os.listdir(path_3g) 
zte_3G_b1 = [x for x in path_3g if 'b1' in x]
zte_3G_b2 = [x for x in path_3g if 'b2' in x]

files_3G_busy= os.listdir(path_3g_busy) 
zte_3G_b1_busy = [x for x in path_3g_busy if 'b1' in x]
zte_3G_b2_busy = [x for x in path_3g_busy if 'b2' in x]

df_3G = pd.DataFrame()
for file in files_3G:   
    df_tmp = pd.read_excel(path_3g + file,encoding = 'utf-8')
    df_tmp.fillna(0,inplace=True)
    df_tmp['DO: 小区RLP信息对象.最大用户数']=df_tmp['DO: 小区RLP信息对象.最大用户数'].replace('-',0)
    df_tmp['DO: 小区RLP信息对象.最大用户数']=df_tmp['DO: 小区RLP信息对象.最大用户数'].astype(float)
    df_tmp['DO: 小区前向RLP数据吞吐量(kbps)']=df_tmp['DO: 小区前向RLP数据吞吐量(kbps)'].replace('-',0)
    df_tmp['DO: 小区前向RLP数据吞吐量(kbps)']=df_tmp['DO: 小区前向RLP数据吞吐量(kbps)'].astype(float)
    df_tmp['开始时间']=df_tmp['开始时间'].map(lambda x:x[0:10])
    df_tmp['week']= pd.to_datetime(df_tmp['开始时间'])
    df_tmp['week']=df_tmp['week'].map(lambda x:x.isocalendar()[1])
    df_tmp['话务量']=df_tmp['1X: 小区CS呼叫话务量(Erl)']*24*7
    df_tmp['吞吐量']=df_tmp['DO: 小区前向RLP数据吞吐量(kbps)']*3600*24*7/8388608
    for i in range(0,len(df_tmp),1):
        df_tmp.loc[i,'扇区'] = df_tmp.loc[i,'BSSB'][-1:] + '_' + df_tmp.loc[i,'BTS'].split(']')[0].replace('[','')+'_'+ df_tmp.loc[i,'Cell'].split('_')[1]
        df_tmp.loc[i,'物理站址']=df_tmp.loc[i,'Cell'].split('J')[1].split('_')[0]
    df_tmp=df_tmp[['week','扇区','话务量','吞吐量','物理站址','DO: 小区RLP信息对象.最大用户数']]
    df_3G=df_3G.append(df_tmp)

df_3G_user=pd.DataFrame()
for file in files_3G_busy:
    df_tmp = pd.read_excel(path_3g_busy + file,encoding = 'utf-8')
    df_tmp.fillna(0,inplace=True)
    df_tmp['week']= pd.to_datetime(df_tmp['开始时间'])
    df_tmp['week']=df_tmp['week'].map(lambda x:x.isocalendar()[1])
    df_tmp['扇区']=df_tmp['BSS'].map(lambda x:x[-2:][0])+'_'+df_tmp['BTS'].map(lambda x:x.split('[')[0].rstrip())+'_'+df_tmp['Cell'].map(lambda x:str(x).replace(' ',''))    
    df_tmp=df_tmp.groupby(by='扇区',as_index=False).max()
    df_tmp=df_tmp[['week','扇区','定时登记成功次数']]
    df_3G_user=df_3G_user.append(df_tmp)

df_3G['标记']=df_3G['week'].map(str)+'_'+df_3G['扇区'].map(str)
df_3G_user['标记']=df_3G_user['week'].map(str)+'_'+df_3G_user['扇区'].map(str)
df_3G_user=df_3G_user.drop(columns=['week','扇区' ])
df_zte=pd.merge(df_3G,df_3G_user,how ='left',on = '标记',)
df_zte=df_zte.drop(columns=['标记'])

week_list = list(set(df_zte['week']))
df_lastweek =  df_zte[df_zte['week'] == week_list[0]]
df_lastweek = df_lastweek.drop(columns=['week'])
df_thisweek =  df_zte[df_zte['week'] == week_list[1]]
df_thisweek = df_thisweek.drop(columns=['week'])


df_lastweek =pd.pivot_table(df_lastweek,index=['物理站址'],
                                     values = ['话务量',  '吞吐量','DO: 小区RLP信息对象.最大用户数','定时登记成功次数'],
                                     aggfunc = {'话务量':np.sum,
                                                '吞吐量':np.sum,
                                                'DO: 小区RLP信息对象.最大用户数':np.sum,
                                                '定时登记成功次数':np.sum})
df_lastweek = df_lastweek.reset_index()

df_thisweek =pd.pivot_table(df_thisweek,index=['物理站址'],
                                     values = ['话务量',  '吞吐量','DO: 小区RLP信息对象.最大用户数','定时登记成功次数'],
                                     aggfunc = {'话务量':np.sum,
                                                '吞吐量':np.sum,
                                                'DO: 小区RLP信息对象.最大用户数':np.sum,
                                                '定时登记成功次数':np.sum})
df_thisweek = df_thisweek.reset_index()

df_lastweek['区县']=df_lastweek['物理站址'].map(lambda x:x[0:2])
df_thisweek['区县']=df_thisweek['物理站址'].map(lambda x:x[0:2])

df_la=df_lastweek
df_la.rename(columns={'DO: 小区RLP信息对象.最大用户数':'上周DO用户同时在线最大数',
                      '吞吐量':'上周DO数据流量（GB）	',
                      '定时登记成功次数':'上周登记用户数',
                      '话务量':'上周语音话务量（erl）'},inplace =True)
df_la=df_la[['物理站址','上周DO用户同时在线最大数','上周DO数据流量（GB）\t','上周登记用户数','上周语音话务量（erl）',]]

df_th=df_thisweek

df_th.rename(columns={'DO: 小区RLP信息对象.最大用户数':'本周DO用户同时在线最大数',
                      '吞吐量':'本周DO数据流量（GB）	',
                      '定时登记成功次数':'本周登记用户数',
                      '话务量':'本周语音话务量（erl）	'},inplace =True)
    
df_quan=pd.merge(df_la,df_th,how ='left',on = '物理站址',)

df_quan=df_quan[['物理站址','区县','上周语音话务量（erl）','本周语音话务量（erl）\t','上周DO数据流量（GB）\t','本周DO数据流量（GB）\t','本周DO用户同时在线最大数','本周登记用户数']]
df_quan['基站价值等级']=df_quan.apply(lambda x:jzjz(x['本周语音话务量（erl）\t'],x['本周DO数据流量（GB）\t']),axis =1 )
df_quan['周环比']=df_quan.apply(lambda x:hb(x['本周语音话务量（erl）\t'],x['上周语音话务量（erl）'],x['本周DO数据流量（GB）\t'],x['上周DO数据流量（GB）\t']),axis =1 )

df_yuanshishuju=pd.merge(df_lastweek,df_thisweek,how ='left',on = '物理站址',)
df_yuanshishuju['语音话务增长率']=(df_yuanshishuju['本周语音话务量（erl）\t']-df_yuanshishuju['上周语音话务量（erl）'])/df_yuanshishuju['上周语音话务量（erl）']
df_yuanshishuju['语音用户增长率']=(df_yuanshishuju['本周登记用户数']-df_yuanshishuju['上周登记用户数'])/df_yuanshishuju['上周登记用户数']
df_yuanshishuju['流量增长率']=(df_yuanshishuju['本周DO数据流量（GB）\t']-df_yuanshishuju['上周DO数据流量（GB）\t'])/df_yuanshishuju['上周DO数据流量（GB）\t']
df_yuanshishuju['在线用户数增长率']=(df_yuanshishuju['本周DO用户同时在线最大数']-df_yuanshishuju['上周DO用户同时在线最大数'])/df_yuanshishuju['上周DO用户同时在线最大数']
df_yuanshishuju = df_yuanshishuju.replace([np.inf, -np.inf], np.nan)
df_yuanshishuju.fillna(0, inplace=True)
df_yuanshishuju=df_yuanshishuju[['物理站址',
                                 '上周语音话务量（erl）',
                                 '本周语音话务量（erl）\t',
                                 '语音话务增长率',
                                 '上周登记用户数',
                                 '本周登记用户数',
                                 '语音用户增长率',
                                 '上周DO数据流量（GB）\t',
                                 '本周DO数据流量（GB）\t',
                                 '流量增长率',
                                 '上周DO用户同时在线最大数',
                                 '本周DO用户同时在线最大数',
                                 '在线用户数增长率']]

df_dishi=pd.pivot_table(df_quan,index=['区县'],
                                values = ['物理站址',  '上周语音话务量（erl）','本周语音话务量（erl）\t','上周DO数据流量（GB）\t','本周DO数据流量（GB）\t','本周DO用户同时在线最大数','本周登记用户数'],
                                aggfunc = {'物理站址':len,
                                           '上周语音话务量（erl）':np.sum,
                                           '本周语音话务量（erl）\t':np.sum,
                                           '上周DO数据流量（GB）\t':np.sum,
                                           '本周DO数据流量（GB）\t':np.sum,
                                           '本周DO用户同时在线最大数':np.sum,
                                           '本周登记用户数':np.sum})
df_dishi.reset_index(inplace =True)
df_dishi.loc['9'] = df_dishi.apply(lambda x: x.sum())
df_dishi.at['9','区县']='全市'
df_dishi['语音话务量环比(%)']=round((df_dishi['本周语音话务量（erl）\t']/df_dishi['上周语音话务量（erl）']-1),3)
df_dishi['DO流量环比(%)']=round((df_dishi['本周DO数据流量（GB）\t']/df_dishi['上周DO数据流量（GB）\t']-1),3)
df_dishi=df_dishi[['区县',
                   '物理站址',
                   '上周语音话务量（erl）',
                   '本周语音话务量（erl）\t',
                   '语音话务量环比(%)',
                   '上周DO数据流量（GB）\t',
                   '本周DO数据流量（GB）\t',
                   'DO流量环比(%)',
                   '本周DO用户同时在线最大数',
                   '本周登记用户数']]

df_jz=pd.pivot_table(df_quan,index=['区县'],
                             columns=["基站价值等级"],
                             aggfunc = {'基站价值等级':len})

df_jz.fillna(0,inplace=True)
df_jz.reset_index(inplace =True)
df_jz.loc['9'] = df_jz.apply(lambda x: x.sum())
df_jz.at['9','区县']='全市'

df_hul_top=df_yuanshishuju[['物理站址','上周语音话务量（erl）','本周语音话务量（erl）\t','语音话务增长率']]
df_hul_top = df_hul_top.sort_values('语音话务增长率', ascending=False)
df_hul_top=df_hul_top[df_hul_top['本周语音话务量（erl）\t']>20]
df_hul_top=df_hul_top[df_hul_top['上周语音话务量（erl）']>20]
df_hul_top=df_hul_top.head(20)
df_hul_top['语音话务增长率']=round(df_hul_top['语音话务增长率'],2)
df_hul_top.rename(columns={'语音话务增长率':'语音话务增长率(%)'},inplace =True)

df_yyuer_top=df_yuanshishuju[['物理站址','上周登记用户数','本周登记用户数','语音用户增长率']]
df_yyuer_top = df_yyuer_top.sort_values('语音用户增长率', ascending=False)
df_yyuer_top=df_yyuer_top[df_yyuer_top['上周登记用户数']>10]
df_yyuer_top=df_yyuer_top[df_yyuer_top['本周登记用户数']>10]
df_yyuer_top=df_yyuer_top.head(20)
df_yyuer_top['语音用户增长率']=round(df_yyuer_top['语音用户增长率'],2)
df_yyuer_top.rename(columns={'语音用户增长率':'语音用户增长率(%)'},inplace =True)

df_ll_top=df_yuanshishuju[['物理站址','上周DO数据流量（GB）\t','本周DO数据流量（GB）\t','流量增长率']]
df_ll_top = df_ll_top.sort_values('流量增长率', ascending=False)
df_ll_top=df_ll_top[df_ll_top['本周DO数据流量（GB）\t']>5]
df_ll_top=df_ll_top[df_ll_top['上周DO数据流量（GB）\t']>5]
df_ll_top=df_ll_top.head(20)
df_ll_top['流量增长率']=round(df_ll_top['流量增长率'],2)
df_ll_top.rename(columns={'流量增长率':'流量增长率(%)'},inplace =True)

df_duer_top=df_yuanshishuju[['物理站址','上周DO用户同时在线最大数','本周DO用户同时在线最大数','在线用户数增长率']]
df_duer_top = df_duer_top.sort_values('在线用户数增长率', ascending=False)
df_duer_top=df_duer_top[df_duer_top['本周DO用户同时在线最大数']>20]
df_duer_top=df_duer_top[df_duer_top['上周DO用户同时在线最大数']>20]
df_duer_top=df_duer_top.head(20)
df_duer_top['在线用户数增长率']=round(df_duer_top['在线用户数增长率'],2)
df_duer_top.rename(columns={'在线用户数增长率':'在线用户数增长率(%)'},inplace =True)




df_jz1=df_jz
df_jz1.columns=['区县','低价值基站','普通基站','流量超低基站','话务流量超低基站','话务超低基站','零流量','零话务零流量']
#a=df_th_p.columns
#print(a)
df_la_p=pd.pivot_table(df_lastweek,index=['区县'],
                                     values = ['物理站址',
                                               '上周语音话务量（erl）',
                                               '上周DO数据流量（GB）\t',
                                               '上周登记用户数',
                                               '上周DO用户同时在线最大数'],
                                     aggfunc = {'物理站址':len,
                                                '上周语音话务量（erl）':np.sum,
                                                '上周DO数据流量（GB）\t':np.sum,
                                                '上周登记用户数':np.sum,
                                                '上周DO用户同时在线最大数':np.sum})
df_la_p.reset_index(inplace =True)
df_la_p.loc['9'] = df_la_p.apply(lambda x: x.sum())
df_la_p.at['9','区县']='全市'

df_th_p=pd.pivot_table(df_thisweek,index=['区县'],
                                     values = ['物理站址',
                                               '本周语音话务量（erl）\t',
                                               '本周DO数据流量（GB）\t',
                                               '本周登记用户数',
                                               '本周DO用户同时在线最大数'],
                                     aggfunc = {'物理站址':len,
                                                '本周语音话务量（erl）\t':np.sum,
                                                '本周DO数据流量（GB）\t':np.sum,
                                                '本周登记用户数':np.sum,
                                                '本周DO用户同时在线最大数':np.sum})
df_th_p.reset_index(inplace =True)
df_th_p.loc['9'] = df_th_p.apply(lambda x: x.sum())
df_th_p.at['9','区县']='全市'


dishi_list = list(df_dishi['区县'])
draw_all_pic(df_la_p,df_th_p,dishi_list)

df_dishi['上周语音话务量（erl）'] = df_dishi['上周语音话务量（erl）'].astype(int)
df_dishi['本周语音话务量（erl）\t'] = df_dishi['本周语音话务量（erl）\t'].astype(int)
df_dishi['上周DO数据流量（GB）\t'] = df_dishi['上周DO数据流量（GB）\t'].astype(int)
df_dishi['本周DO数据流量（GB）\t'] = df_dishi['本周DO数据流量（GB）\t'].astype(int)


df_jz=df_jz.drop(columns = ['区县'])
# =============================================================================
with pd.ExcelWriter(path+'\\输出文件''\\'+'3G话务周报.xlsx') as writer:
    workbook = writer.book
    col_fmt = workbook.add_format({'bold': True,  # 字体加粗
                                   'font_size': 10,  # 字体大小
                                   'font_name': u'微软雅黑',  # 字体
                                   'num_format': '0.00',  # 数字格式
                                   'bg_color': '#E0FFFF',  # 单元格背景色
                                   'valign': 'vcenter',  # 垂直对齐方式
                                   'align': 'center',  # 水平对齐方式
                                   'top' : 2,  # 上边框，后面参数是线条宽度
                                   'left' : 2,  # 左边框
                                   'right' : 2,  # 右边框
                                   'bottom' : 2,  # 底边框
                                   'border' : 1 , # 边框
                                   'text_wrap': True})  # 自动换行，可在文本中加 '\n'来控制换行的位置
    col1_fmt = workbook.add_format({'bold': True,  # 字体加粗
                                   'font_size': 10,  # 字体大小
                                   'font_name': u'微软雅黑',  # 字体
                                   'num_format': '0.00',  # 数字格式
                                   'bg_color': '#CDCDC1',  # 单元格背景色
                                   'valign': 'vcenter',  # 垂直对齐方式
                                   'align': 'center',  # 水平对齐方式
                                   'top' : 2,  # 上边框，后面参数是线条宽度
                                   'left' : 2,  # 左边框
                                   'right' : 2,  # 右边框
                                   'bottom' : 2,  # 底边框
                                   'border' : 1 , # 边框
                                   'text_wrap': True})  # 自动换行，可在文本中加 '\n'来控制换行的位置
    col2_fmt = workbook.add_format({'bold': True,  # 字体加粗
                                   'font_size': 10,  # 字体大小
                                   'font_name': u'微软雅黑',  # 字体
                                   'num_format': '0.00',  # 数字格式
                                   'bg_color': '#FFC125',  # 单元格背景色
                                   'valign': 'vcenter',  # 垂直对齐方式
                                   'align': 'center',  # 水平对齐方式
                                   'top' : 2,  # 上边框，后面参数是线条宽度
                                   'left' : 2,  # 左边框
                                   'right' : 2,  # 右边框
                                   'bottom' : 2,  # 底边框
                                   'border' : 1 , # 边框
                                   'text_wrap': True})  # 自动换行，可在文本中加 '\n'来控制换行的位置
    col3_fmt = workbook.add_format({'bold': True,  # 字体加粗
                                   'font_size': 10,  # 字体大小
                                   'font_name': u'微软雅黑',  # 字体
                                   'num_format': '0.00',  # 数字格式
                                   'bg_color': '#F4A460',  # 单元格背景色
                                   'valign': 'vcenter',  # 垂直对齐方式
                                   'align': 'center',  # 水平对齐方式
                                   'top' : 2,  # 上边框，后面参数是线条宽度
                                   'left' : 2,  # 左边框
                                   'right' : 2,  # 右边框
                                   'bottom' : 2,  # 底边框
                                   'border' : 1 , # 边框
                                   'text_wrap': True})  # 自动换行，可在文本中加 '\n'来控制换行的位置
    noraml_fmt = workbook.add_format({"font_name": u"微软雅黑"})
    percent_fmt = workbook.add_format({'num_format': '0.00%'})
    header_fmt = workbook.add_format({'bold': True,
                                      'font_name': u'微软雅黑',
                                      'font_size': 16,  # 字体大小
                                      'align': 'center',  # 水平对齐方式
                                      'border' : 2 , # 边框
                                      'font_color': 'red',
                                      'valign': 'vcenter'}) #设置标题格式
    title_fmt = workbook.add_format({'bold': True,
                                    'font_name': u'微软雅黑',
                                    'align': 'center',  # 水平对齐方式
                                    'border' : 2 , # 边框
                                    'font_color': 'blue',
                                    'valign': 'vcenter'}) #设置标题格式

    red_fmt = workbook.add_format({'bg_color': '#FFE4E1',
                                   'num_format': '0.00%'})
    border_format = workbook.add_format({'border' : 1 })
# =============================================================================
#     
# =============================================================================
    df_dishi.to_excel(writer,
                      sheet_name=u'按县透视',
                      encoding='utf8',
                      header=False,
                      index=False,
                      startcol=0,
                      startrow=3)
    worksheet1 = writer.sheets[u'按县透视']
    
    df_jz.to_excel(writer,
                   sheet_name=u'按县透视',
                   encoding='utf8',
                   header=False,
                   index=False,
                   startcol=10,
                      startrow=3)
    worksheet1 = writer.sheets[u'按县透视']
    for col_num, value in enumerate(df_dishi.columns.values):
        worksheet1.write(2, col_num, value, col_fmt)
    for col_num, value in enumerate(df_jz.columns.values):
        worksheet1.write(2, col_num+10, value, col1_fmt)
    
    worksheet1.merge_range('A1:R1', u'全部基站话务量数据汇总', header_fmt)
    worksheet1.merge_range('C2:E2', u'语音话务量（erl）', col_fmt)
    worksheet1.merge_range('F2:H2', u'DO数据流量（GB）', col1_fmt)
    worksheet1.merge_range('K2:R2', u'基站价值', col2_fmt)
    worksheet1.merge_range('I2:J2', u'用户数', col3_fmt)

    l_end = len(df_dishi) + 3
    worksheet1.conditional_format(
        'A3:D%d' % l_end,
        {'type': 'no_blanks', 'format': noraml_fmt}) # 所有非空单元格设置为 noraml_fmt
    worksheet1.conditional_format(
        'F3:G%d' % l_end,
        {'type': 'no_blanks', 'format': noraml_fmt})
    worksheet1.conditional_format(
        'I3:R%d' % l_end,
        {'type': 'no_blanks', 'format': noraml_fmt})
    worksheet1.conditional_format(
        'E3:E%d'% l_end,
        {'type': 'no_blanks', 'format': percent_fmt}) # 话务量环比变化，设置为 percent_fmt
    worksheet1.conditional_format(
        'H3:H%d'% l_end,
        {'type': 'no_blanks', 'format': percent_fmt}) # DO流量环比变化，设置为 
    worksheet1.conditional_format(
        'E3:E%d'% l_end,
        {'type': 'cell', 'criteria': '<', 'value': 0, 'format': red_fmt})
    worksheet1.conditional_format(
        'H3:H%d'% l_end,
        {'type': 'cell', 'criteria': '<', 'value': 0, 'format': red_fmt})
    worksheet1.set_column('A:J', 10)
    # 加边框
    worksheet1.conditional_format('A4:R%d' % l_end, {'type': 'no_blanks', 'format': border_format})
    worksheet1.conditional_format('A2:B2', {'type': 'blanks', 'format': title_fmt})


# =============================================================================
#     
# =============================================================================
    df_quan.to_excel(writer,
                     sheet_name=u'全部基站清单',
                     encoding='utf8',
                     header=False,
                     index=False,
                     startcol=0,
                     startrow=3)
    worksheet2 = writer.sheets[u'全部基站清单']
    for col_num, value in enumerate(df_quan.columns.values):
        worksheet2.write(2, col_num, value, col_fmt)
    worksheet2.merge_range('A1:J1', u'全部基站话务量数据详单', header_fmt)
    worksheet2.merge_range('C2:D2', u'语音话务量（erl）', title_fmt)
    worksheet2.merge_range('E2:F2', u'DO数据流量（GB）', title_fmt)
    worksheet2.merge_range('I2:J2', u'基站价值', title_fmt)
    worksheet2.merge_range('G2:H2', u'用户数', title_fmt)
    l_end = len(df_quan) + 3
    worksheet2.conditional_format(
        'A3:J%d' % l_end,
        {'type': 'no_blanks', 'format': noraml_fmt}) # 所有非空单元格设置为 noraml_fmt
    worksheet2.set_column('A:A', 28.5)
    worksheet2.set_column('I:I', 16.63)
    worksheet2.set_column('J:J', 14.5)
    worksheet2.conditional_format('A4:J%d' % l_end, {'type': 'no_blanks', 'format': border_format})
# =============================================================================
#     
# =============================================================================
    df_yuanshishuju.to_excel(writer,
                     sheet_name=u'top小区原始数据',
                     encoding='utf8',
                     header=False,
                     index=False,
                     startcol=0,
                     startrow=3)
    worksheet5 = writer.sheets[u'top小区原始数据']
    for col_num, value in enumerate(df_yuanshishuju.columns.values):
        worksheet5.write(2, col_num, value, col_fmt)
    worksheet5.merge_range('A1:M1', u'top小区原始数据', header_fmt)
    worksheet5.merge_range('B2:D2', u'话务量',  col_fmt)
    worksheet5.merge_range('E2:G2', u'1x登记用户数', col1_fmt)
    worksheet5.merge_range('H2:J2', u'DO流量', col2_fmt)
    worksheet5.merge_range('K2:M2', u'DO登记用户数', col3_fmt)
    l_end = len(df_yuanshishuju) + 3
    worksheet5.conditional_format(
        'A4:M%d' % l_end,
        {'type': 'no_blanks', 'format': noraml_fmt})
    worksheet5.set_column('A:A', 28.5)
    worksheet5.conditional_format('A4:M%d' % l_end, {'type': 'no_blanks', 'format': border_format})
    worksheet5.conditional_format(
        'D3:D%d'% l_end,
        {'type': 'no_blanks', 'format': percent_fmt}) # 话务量环比变化，设置为 percent_fmt
    worksheet5.conditional_format(
        'G3:G%d'% l_end,
        {'type': 'no_blanks', 'format': percent_fmt}) # DO流量环比变化，设置为 
    worksheet5.conditional_format(
        'J3:J%d'% l_end,
        {'type': 'no_blanks', 'format': percent_fmt}) # DO流量环比变化，设置为 
    worksheet5.conditional_format(
        'M3:M%d'% l_end,
        {'type': 'no_blanks', 'format': percent_fmt}) # DO流量环比变化，设置为 
    worksheet5.conditional_format('A2:B2', {'type': 'blanks', 'format': title_fmt})


   
# =============================================================================
#     
# =============================================================================
    df_hul_top.rename(columns={'物理站址':'物理站址名称',
                               '上周语音话务量（erl）':'语音话务量上周',
                               '本周语音话务量（erl）\t':'语音话务量本周',
                               '语音话务增长率(%)':'增长率（取周话务量>20Erl的基站）'},inplace =True)
    df_yyuer_top.rename(columns={'物理站址':'物理站址名称',
                               '上周登记用户数':'语音登记用户数上周',
                               '本周登记用户数':'语音登记用户数本周',
                               '语音用户增长率(%)':'语音用户增长率（取语音忙时登记用户数>10个的基站）'},inplace =True)
    df_ll_top.rename(columns={'物理站址':'物理站址名称',
                              '上周DO数据流量（GB）\t':'DO流量上周',
                              '本周DO数据流量（GB）\t':'DO流量本周',
                              '流量增长率(%))':'增长率（取周总流量>5GB的基站）'},inplace =True)
    df_duer_top.rename(columns={'物理站址':'物理站址名称',
                                '上周DO用户同时在线最大数':'DO在线用户数上周',
                                '本周DO用户同时在线最大数':'DO在线用户数本周',
                                '在线用户数增长率(%)':'在线用户数增长率（取DO在线用户数>10个的基站）'},inplace =True)
    df_hul_top.to_excel(writer,
                      sheet_name=u'top小区汇总',
                      encoding='utf8',
                      header=False,
                      index=False,
                      startcol=0,
                      startrow=2)
    worksheet6 = writer.sheets[u'top小区汇总']
    
    df_yyuer_top.to_excel(writer,
                   sheet_name=u'top小区汇总',
                   encoding='utf8',
                   header=False,
                   index=False,
                   startcol=5,
                   startrow=2)
    worksheet6 = writer.sheets[u'top小区汇总']
    df_ll_top.to_excel(writer,
                   sheet_name=u'top小区汇总',
                   encoding='utf8',
                   header=False,
                   index=False,
                   startcol=0,
                   startrow=25)
    worksheet6 = writer.sheets[u'top小区汇总']
    df_duer_top.to_excel(writer,
                   sheet_name=u'top小区汇总',
                   encoding='utf8',
                   header=False,
                   index=False,
                   startcol=5,
                   startrow=25)
    worksheet6 = writer.sheets[u'top小区汇总']
    
    for col_num, value in enumerate(df_hul_top.columns.values):
        worksheet6.write(1, col_num, value, col_fmt)
    for col_num, value in enumerate(df_yyuer_top.columns.values):
        worksheet6.write(1, col_num+5, value, col1_fmt)
    for col_num, value in enumerate(df_ll_top.columns.values):
        worksheet6.write(24, col_num, value, col2_fmt)
    for col_num, value in enumerate(df_duer_top.columns.values):
        worksheet6.write(24, col_num+5, value, col3_fmt)
    worksheet6.set_column('A:A', 28.5)
    worksheet6.set_column('F:F', 28.5)
    worksheet6.conditional_format('A2:D22', {'type': 'no_blanks', 'format': border_format})
    worksheet6.conditional_format('A26:D45', {'type': 'no_blanks', 'format': border_format})
    worksheet6.conditional_format('F2:I22', {'type': 'no_blanks', 'format': border_format})
    worksheet6.conditional_format('F26:I45', {'type': 'no_blanks', 'format': border_format})

    book = writer.book     # 将图片插入到excel表格中 
    sheet = book.add_worksheet('各区县用户数及流量')
# =============================================================================
#     
# =============================================================================
    df_lastweek.to_excel(writer,
                     sheet_name=u'上周数据',
                     encoding='utf8',
                     header=False,
                     index=False,
                     startcol=0,
                     startrow=2)
    worksheet3 = writer.sheets[u'上周数据']
    for col_num, value in enumerate(df_lastweek.columns.values):
        worksheet3.write(1, col_num, value, col_fmt)
    worksheet3.merge_range('A1:F1', u'上周基站话务量数据详单', header_fmt)
    l_end = len(df_lastweek) + 2
    worksheet3.conditional_format(
        'A3:F%d' % l_end,
        {'type': 'no_blanks', 'format': noraml_fmt})
    worksheet3.set_column('A:A', 28.5)
    worksheet3.conditional_format('A3:J%d' % l_end, {'type': 'no_blanks', 'format': border_format})
# =============================================================================
#     
# =============================================================================
    df_thisweek.to_excel(writer,
                     sheet_name=u'本周数据',
                     encoding='utf8',
                     header=False,
                     index=False,
                     startcol=0,
                     startrow=2)
    worksheet4 = writer.sheets[u'本周数据']
    for col_num, value in enumerate(df_thisweek.columns.values):
        worksheet4.write(1, col_num, value, col_fmt)
    worksheet4.merge_range('A1:F1', u'本周基站话务量数据详单', header_fmt)
    l_end = len(df_thisweek) + 2
    worksheet4.conditional_format(
        'A3:F%d' % l_end,
        {'type': 'no_blanks', 'format': noraml_fmt})
    worksheet4.set_column('A:A', 28.5)
    worksheet4.conditional_format('A3:J%d' % l_end, {'type': 'no_blanks', 'format': border_format})
huatu2('3G话务周报.xlsx','各区县用户数及流量')
# =============================================================================
# 
# =============================================================================
document = Document()
# 定义文档默认字体style_1为楷体
document.styles['Normal'].font.name = u'华文楷体'
document.styles['Normal']._element.rPr.rFonts.set(qn('w:eastAsia'), u'华文楷体')
document.styles['Normal'].font.size = Pt(14)
style_1 = document.styles['Normal']




title = document.add_heading('', level=0).add_run('3G话务量周报')
title.font.name = u'华文楷体'
title._element.rPr.rFonts.set(qn('w:eastAsia'), u'华文楷体')
title.font.size = Pt(25)

# 添加第一段标题
head1 = document.add_heading('', level=1).add_run('基本信息')
head1.font.name = u'华文楷体'
head1._element.rPr.rFonts.set(qn('w:eastAsia'), u'华文楷体')
head1.font.size = Pt(20)

# 添加第一段的1小节标题
head2 = document.add_heading('', level=2).add_run('1.1 基站数量')
head2.font.name = u'华文楷体'
head2._element.rPr.rFonts.set(qn('w:eastAsia'), u'华文楷体')
head2.font.size = Pt(16)
#添加图片
document.add_picture('./pic/基站数量' + ".png", width=Inches(6))    
# 添加基站数量描述
document.add_paragraph('本周基站数{jzs_rate}个,本周物理站址数{jzs_level}个。'.format(
        jzs_rate=jzsl(df_th_p.loc['9','物理站址'],df_la_p.loc['9','物理站址']),
        jzs_level = int(df_th_p.loc['9','物理站址'])),
    style=style_1)
# 添加第一段的2小节标题
head2 = document.add_heading('', level=2).add_run('1.2 忙时登记用户数')
head2.font.name = u'华文楷体'
head2._element.rPr.rFonts.set(qn('w:eastAsia'), u'华文楷体')
head2.font.size = Pt(16)
#添加图片
document.add_picture('./pic/用户数' + ".png", width=Inches(6))
document.add_paragraph('本周1x用户数{rrc_rate},本周1x用户数{rrc_level}。'.format(
        rrc_rate=jzsl(df_th_p.loc['9','本周登记用户数'],df_la_p.loc['9','上周登记用户数']),
        rrc_level = int(df_th_p.loc['9','本周登记用户数'])),
    style=style_1)


# 添加第一段的3小节标题
head2 = document.add_heading('', level=2).add_run('1.3 忙时DO用户数')
head2.font.name = u'华文楷体'
head2._element.rPr.rFonts.set(qn('w:eastAsia'), u'华文楷体')
head2.font.size = Pt(16)
#添加图片
document.add_picture('./pic/DO用户数' + ".png", width=Inches(6))
document.add_paragraph('本周DO用户数{ll_rate},本周DO用户数{ll_level}。'.format(
        ll_rate=jzsl(df_th_p.loc['9','本周DO用户同时在线最大数'],df_la_p.loc['9','上周DO用户同时在线最大数']),
        ll_level = df_th_p.loc['9','本周DO用户同时在线最大数']),
    style=style_1)

# 添加第一段的4小节标题
head2 = document.add_heading('', level=2).add_run('1.4 话务量（erl')
head2.font.name = u'华文楷体'
head2._element.rPr.rFonts.set(qn('w:eastAsia'), u'华文楷体')
head2.font.size = Pt(16)
#添加图片
document.add_picture('./pic/话务量（erl）' + ".png", width=Inches(6))
document.add_paragraph('本周话务量{prb_rate}（erl）,本周话务量{prb_level}（erl）。'.format(
        prb_rate=jzsl(df_th_p.loc['9','本周语音话务量（erl）\t'],df_la_p.loc['9','上周语音话务量（erl）']),
        prb_level = round(df_th_p.loc['9','本周语音话务量（erl）\t'],2)),
    style=style_1)


# 添加第一段的5小节标题
head2 = document.add_heading('', level=2).add_run('1.5 DO流量（GB）')
head2.font.name = u'华文楷体'
head2._element.rPr.rFonts.set(qn('w:eastAsia'), u'华文楷体')
head2.font.size = Pt(16)
#添加图片
document.add_picture('./pic/DO流量(GB)' + ".png", width=Inches(6))
document.add_paragraph('本周DO流量{ty_rate}(GB),本周DO流量{ty_level}(GB)。'.format(
        ty_rate=jzsl(df_th_p.loc['9','本周DO数据流量（GB）\t'],df_la_p.loc['9','上周DO数据流量（GB）\t']),
        ty_level = round(df_th_p.loc['9','本周DO数据流量（GB）\t'],2)),
    style=style_1)

#a=df_la_p.columns
#print(a)
document.add_page_break()
document.save(path+'\\输出文件''\\'+'3G话务周报.docx')
print('喵喵已经做好3G话务周报了')