通过pyinstaller xx.py可以把本源代码编译成xx.exe，但在64位的系统编译成xx.exe的程序在32位的操作系统运行不了。
# coding=utf-8
from Tkinter import *
from tkFileDialog import *
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.styles import Border, Side, Font, Alignment # 设置字体和边框需要的模块
#from docx import Document
#from docx.shared import Pt
#from docx.shared import Inches
#from docx.oxml.ns import qn
#from docx.enum.text import WD_ALIGN_PARAGRAPH # 段落居中
import random #
import os
import sys
#import xlrd #读excel要用到xlrd模块
#import xlwt #写excel表要用到xlwt模块
from datetime import datetime
#from xlrd import xldate_as_tuple
import time
from copy import copy
class App:
def __init__(self, root):
frame = Frame(root) # container
frame.pack()
self.button1 = Button(frame, text="高危集团预警取数基础表", command=self.filefound1, width=20, height=1).grid(row=0, column=0)
# self.button1.pack()
self.button4 = Button(frame, text="运行", command=self.execuate, width=20, height=1).grid(row=3)
# self.button3.pack()
self.button5 = Button(frame, text="退出", command=frame.quit, width=20, height=1).grid(row=4)
# self.button4.pack()
self.e = Entry(frame)
self.e.grid(row=0, column=2)
self.e.delete(0, END) # 将输入框里面的内容清空
self.e.insert(0, '显示文件路径')
self.filepath1 = StringVar()
def filefound1(self):
self.filepath1 = askopenfilename()
print self.filepath1
self.e.delete(0, END) # 将输入框里面的内容清空
self.e.insert(0, self.filepath1)
return self.filepath1
def filefound2(self):
self.filepath2 = askopenfilename()
print self.filepath2
self.e1.delete(0, END) # 将输入框里面的内容清空
self.e1.insert(0, self.filepath2)
return self.filepath2
def filefound3(self):
self.filepath3 = askopenfilename()
print self.filepath3
self.e2.delete(0, END) # 将输入框里面的内容清空
self.e2.insert(0, self.filepath3)
return self.filepath3
def set_style(self, name, height, bold=False): # 一个name参数，一个高度参数，默认不加粗
style = xlwt.XFStyle() # 初始化样式
font = xlwt.Font() # 为样式创建字体
font.name = name # 'Times New Roman'
font.bold = bold # 加粗
font.color_index = 4 # 颜色
font.height = height # 高度
borders = xlwt.Borders() # 边框
borders.left = 1 # 上下左右
borders.right = 1
borders.top = 1
borders.bottom = 1
style.font = font # 定义字体属性
style.borders = borders # 定义边框属性
return style
def Month_of_extraction(self,xlsx_date): # 提取xlsx文件的月份数
Month = ''
if type(xlsx_date).__name__=="unicode":
XHXC_time = xlsx_date
else:
XHXC_time = xlsx_date.strftime("%Y-%m-%d")
if '/' in XHXC_time:
Month = XHXC_time.split('/')[1]
elif '-' in XHXC_time:
Month = XHXC_time.split('-')[1]
#print Month,type(xlsx_date),xlsx_date
return Month
def Current_Month_of_extraction(self): # 提取xlsx文件的月份数
XHXC_time = datetime.now().strftime("%Y-%m-%d")
Month = XHXC_time.split('-')[1]
return Month
def execuate(self):
# 配置字体格式为：样式（Times New Roman）、尺寸（11）、斜体（flase）、颜色（黑色）、粗体（flase），这里可以根据自己需求修改
font_style = Font(name='Times New Roman', size=11, italic=False, color='FF000000', bold=False)
#print self.filepath1, self.filepath2
wb_alarm = load_workbook(self.filepath1, data_only=True)
len_filepath1 = len(self.filepath1)
for i in range(len_filepath1 - 1, -1, -1):
if (self.filepath1[i] == '/'):
break
mother_path = u""
for j in range(0, i + 1):
mother_path += self.filepath1[j]
model_path = mother_path + u'基础数据//携转预警智慧平台数据统计表模板.xlsx'
wb_test = load_workbook(model_path, data_only=True) # 读取模板清单
sheet_test = wb_test.get_sheet_by_name(u'ZMB',) # 所有集团清单
sheet_YSZB = wb_alarm.get_sheet_by_name(u'YSZB',) # 西湖集团全量清单
sheet_XHJT = wb_alarm.get_sheet_by_name(u'XHJT',) # 西湖集团全量清单
sheet_YKJT = wb_alarm.get_sheet_by_name(u'YKJT',) # 西湖集团全量清单
sheet_XHXC = wb_alarm.get_sheet_by_name(u'XHXC',) # 西湖携出汇总清单
alarm_group_manager_dict = {u'':[u'',u'',u'']} #预警中集团与客户经理和区域的映射
################################################################################
ws = wb_test.get_sheet_by_name(wb_test.get_sheet_names()[0]) # # 读取模板清单
wb_new = Workbook()
ws_new = wb_new.active #获取第一个sheet
ws_new.title = u'统计' #设定一个sheet的名字
ws2_new = wb_new.create_sheet("Mysheet", 1) #设定sheet的插入位置 默认插在后面
ws2_new.title = u"20人以上集团详情" #设定一个sheet的名字 必须是Unicode
ws3_new = wb_new.create_sheet("Mysheet", 2) #设定sheet的插入位置 默认插在后面
ws3_new.title = u"要客20人以上集团详情" #设定一个sheet的名字 必须是Unicode
max_row=ws.max_row#最大行数
max_column=ws.max_column#最大列数
for i in range(1, sheet_XHJT.max_row+1): # 建立集团与客户经理和区域的字典对应关系 %%%%集团编号，集团客户经理，区域，集团级别，铁通对应分局
for j in range(1, 15):
ws_new.cell(i, j).value = sheet_test.cell(i, j).value
# bold=True加粗
# ws_new.cell(i, j).font = Font(name='微软雅黑', size=14, bold=True)
wm=zip(ws.merged_cells)#开始处理合并单元格
if len(wm)>0:
for i in range(0,len(wm)):
#cell2=str(wm[i]).replace('(<CellRange','').replace('>,)','') #(<CellRange A1:N1>,)
#print str(wm[i]),cell2
#cell2 = str(wm[i])[12:]
cell2=str(wm[i]).split(' ')[1][:-3]
print("MergeCell:%s"%cell2)
ws_new.merge_cells(cell2)
printout = 0
row_xc = 40
for m in range(1,max_row+1):
ws_new.row_dimensions[m].height=ws.row_dimensions[m].height
for n in range(1,1+max_column):
if n<27:
c=chr(n+64).upper()#ASCII字符,chr(65)='A'
else:
if n<677:
c=chr(divmod(n,26)[0]+64)+chr(divmod(n,26)[1]+64)
else:
c=chr(divmod(n,676)[0]+64)+chr(divmod(divmod(n,676)[1],26)[0]+64)+chr(divmod(divmod(n,676)[1],26)[1]+64)
i='%s%d'%(c,m)#单元格编号
if m==1:
#print("Modifycolumn%swidthfrom%dto%d"%(n,ws_new.column_dimensions[c].width,ws.column_dimensions[c].width))
ws_new.column_dimensions[c].width=ws.column_dimensions[c].width
try:
getattr(ws.cell(row=m,column=c),"value")
cell1=ws[i]#获取data单元格数据
ws_new[i].value=cell1.value#赋值到ws_new单元格
if cell1.has_style:#拷贝格式
ws_new[i].font=copy(cell1.font)
ws_new[i].border=copy(cell1.border)
ws_new[i].fill=copy(cell1.fill)
ws_new[i].number_format=copy(cell1.number_format)
ws_new[i].protection=copy(cell1.protection)
ws_new[i].alignment=copy(cell1.alignment)
except AttributeError as e:
if printout == 0:
print("cell(%s)is%s"%(i,e))
printout+=1
continue
#######################################################################################
group_manager_dict = {u'':[u'',u'',u'',u'',u'']} #集团与客户经理和区域的映射
YKJT_group_manager_dict = {u'':[u'',u'',u'',u'']} #要客集团与客户经理和区域的映射
print('sheet_XHJT.max_row = ',sheet_XHJT.max_row)
print u'读取-西湖分公司全量集团表（有对应客户经理、分局）.xlsx中…………'
for i in range(2, sheet_XHJT.max_row+1): # 建立集团与客户经理和区域的字典对应关系 %%%%集团编号，集团客户经理，区域，集团级别，铁通对应分局，GROUP_NAME
group_manager_dict[sheet_XHJT.cell(i, 1).value] = [sheet_XHJT.cell(i, 7).value,sheet_XHJT.cell(i, 9).value,sheet_XHJT.cell(i, 4).value,sheet_XHJT.cell(i, 10).value,sheet_XHJT.cell(i, 2).value]
#print(sheet_XHJT.cell(i, 1).value,':',sheet_XHJT.cell(i, 3).value,':',sheet_XHJT.cell(i, 9).value)
if i % 2000 == 0:
print ' ',round(1.0*i/(1.0*sheet_XHJT.max_row)*100,2),'%'
sheet_YKJT
print('sheet_YKJT.max_row = ',sheet_YKJT.max_row)
for i in range(2, sheet_YKJT.max_row+1): # 建立集团与客户经理和区域的字典对应关系 ##YKJT_group_manager_dict集团编号，集团客户经理，区域，集团级别，集团状态
YKJT_group_manager_dict[sheet_YKJT.cell(i, 1).value] = [sheet_YKJT.cell(i, 4).value,sheet_YKJT.cell(i, 16).value,sheet_YKJT.cell(i, 7).value,sheet_YKJT.cell(i, 10).value,sheet_YKJT.cell(i, 2).value]
#print(sheet_XHJT.cell(i, 1).value,':',sheet_XHJT.cell(i, 3).value,':',sheet_XHJT.cell(i, 9).value)
if i % 2000 == 0:
print ' ',round(1.0*i/(1.0*sheet_YKJT.max_row)*100,2),'%'
xicheng_YSZB = [0,0,set(),set(),set(),0,0,0,0,0]
xizi_YSZB = [0,0,set(),set(),set(),0,0,0,0,0]
sandun_YSZB = [0,0,set(),set(),set(),0,0,0,0,0]
liuxia_YSZB = [0,0,set(),set(),set(),0,0,0,0,0]
zhijiang_YSZB = [0,0,set(),set(),set(),0,0,0,0,0]
hangye_YSZB = [0,0,set(),set(),set(),0,0,0,0,0]
tietong_YSZB = [0,0,set(),set(),set(),0,0,0,0,0]
xiaoyuan_YSZB = [0,0,set(),set(),set(),0,0,0,0,0]
qita_YSZB = [0,0,set(),set(),set(),0,0,0,0,0]
yaoke_YSZB = [0,0,set(),set(),set(),0,0,0,0,0]
total_YSZB = [0,0,0,0,0,0,0,0,0,0]
xicheng_tietong = [0,0,set(),set(),set(),0,0,0,0,0]
xizi_tietong = [0,0,set(),set(),set(),0,0,0,0,0]
sandun_tietong = [0,0,set(),set(),set(),0,0,0,0,0]
liuxia_tietong = [0,0,set(),set(),set(),0,0,0,0,0]
zhijiang_tietong = [0,0,set(),set(),set(),0,0,0,0,0]
total_tietong = [0,0,0,0,0,0,0,0,0,0]
xicheng_yaoke = [0,0,set(),set(),set(),0,0,0,0,0]
xizi_yaoke = [0,0,set(),set(),set(),0,0,0,0,0]
sandun_yaoke = [0,0,set(),set(),set(),0,0,0,0,0]
liuxia_yaoke = [0,0,set(),set(),set(),0,0,0,0,0]
zhijiang_yaoke = [0,0,set(),set(),set(),0,0,0,0,0]
qita_yaoke = [0,0,set(),set(),set(),0,0,0,0,0]
total_yaoke = [0,0,0,0,0,0,0,0,0,0]
print('sheet_YSZB.max_row = ',sheet_YSZB.max_row)
alarm_groupID_NUM_dict = {0:0} #预警中GROUP_ID与个数的映射 ##统计每个GROUP_ID有多少人要携转
alarm_groupID_tietong_NUM_dict = {0:0} #预警中铁通GROUP_ID与个数的映射 ##统计每个GROUP_ID有多少人要携转
alarm_groupID_yaoke_NUM_dict = {0:0} #预警中要客GROUP_ID与个数的映射 ##统计每个GROUP_ID有多少人要携转
CODE_USER_ID_SET = set()
keys_in = 0
print u'……………………处理XC查询有号码中……………………'
for i in range(2, sheet_YSZB.max_row+1): # 处理XC查询有号码 集团与客户经理和区域的字典对应关系 %% GROUP_ID ，集团级别，GROUP_NAME
Group_level = sheet_YSZB.cell(i, 39).value #YSZB第三十九列
GROUP_ID = sheet_YSZB.cell(i, 35).value #YSZB第三十五列
GROUP_NAME = sheet_YSZB.cell(i, 36).value #YSZB第三十六列
PHONE_num = sheet_YSZB.cell(i, 30).value #YSZB第三十列
SANLEI_Members = sheet_YSZB.cell(i, 40).value #YSZB第四十列
GROUP_COUNTY_NAME = sheet_YSZB.cell(i, 41).value #YSZB第四十一列 有要客标记
CODE_USER_ID = sheet_YSZB.cell(i, 30).value #YSZB第三十列 用户编号
if CODE_USER_ID not in CODE_USER_ID_SET: #用户数剔除，只计算第一个
CODE_USER_ID_SET.add(CODE_USER_ID)
else:
continue
alarm_group_manager_dict[GROUP_ID] = [Group_level,GROUP_NAME,Group_level]
if i % 500 == 0:
print ' ',round(1.0*i/(1.0*sheet_YSZB.max_row)*100,2),'%'
##统计每个GROUP_ID有多少人要携转
if alarm_groupID_yaoke_NUM_dict.has_key(GROUP_ID):##统计每个GROUP_ID有多少人要携转
alarm_groupID_yaoke_NUM_dict[GROUP_ID] += 1
else:
alarm_groupID_yaoke_NUM_dict[GROUP_ID] = 1 ##统计每个GROUP_ID有多少人要携转
##统计要客每个GROUP_ID有多少人要携转
if alarm_groupID_NUM_dict.has_key(GROUP_ID):##统计每个GROUP_ID有多少人要携转
alarm_groupID_NUM_dict[GROUP_ID] += 1
else:
alarm_groupID_NUM_dict[GROUP_ID] = 1 ##统计每个GROUP_ID有多少人要携转
if group_manager_dict.has_key(GROUP_ID):
keys_in += 1
if group_manager_dict[GROUP_ID][1] ==u'西城': #0成员累计查询
xicheng_YSZB[0] += 1
xicheng_YSZB[2].add(GROUP_ID) #2成员累计查询
if Group_level is not None and('A' in Group_level or 'B' in Group_level):#3其中AB类
xicheng_YSZB[3].add(GROUP_ID)
else: #elif Group_level is not None and( 'C' in Group_level or 'D' in Group_level or 'E' in Group_level or 'F' in Group_level ):#4其中C-F
xicheng_YSZB[4].add(GROUP_ID)
if SANLEI_Members != u'集团普通成员':#5统计三类成员
xicheng_YSZB[5] += 1
if PHONE_num is not None and '1390571' in str(PHONE_num): #6#1390571成员数
xicheng_YSZB[6] += 1
elif group_manager_dict[GROUP_ID][1] ==u'西子': #0成员累计查询
xizi_YSZB[0] += 1
xizi_YSZB[2].add(GROUP_ID) #2成员累计查询
if Group_level is not None and('A' in Group_level or 'B' in Group_level): #3其中AB类
xizi_YSZB[3].add(GROUP_ID)
else: #elif Group_level is not None and( 'C' in Group_level or 'D' in Group_level or 'E' in Group_level or 'F' in Group_level ): #4其中C-F
xizi_YSZB[4].add(GROUP_ID)
if SANLEI_Members != u'集团普通成员': #5统计三类成员
xizi_YSZB[5] += 1
if PHONE_num is not None and '1390571' in str(PHONE_num): #6#1390571成员数
xizi_YSZB[6] += 1
elif group_manager_dict[GROUP_ID][1] ==u'三墩': #0成员累计查询
sandun_YSZB[0] += 1
sandun_YSZB[2].add(GROUP_ID) #2成员累计查询
if Group_level is not None and('A' in Group_level or 'B' in Group_level): #3其中AB类
sandun_YSZB[3].add(GROUP_ID)
else: #elif Group_level is not None and( 'C' in Group_level or 'D' in Group_level or 'E' in Group_level or 'F' in Group_level ): #4其中C-F
sandun_YSZB[4].add(GROUP_ID)
if SANLEI_Members != u'集团普通成员': #5统计三类成员
sandun_YSZB[5] += 1
if PHONE_num is not None and '1390571' in str(PHONE_num): #6#1390571成员数
sandun_YSZB[6] += 1
elif group_manager_dict[GROUP_ID][1] ==u'留下': #0成员累计查询
liuxia_YSZB[0] += 1
liuxia_YSZB[2].add(GROUP_ID) #2成员累计查询
if Group_level is not None and('A' in Group_level or 'B' in Group_level): #3其中AB类
liuxia_YSZB[3].add(GROUP_ID)
else: #elif Group_level is not None and( 'C' in Group_level or 'D' in Group_level or 'E' in Group_level or 'F' in Group_level ): #4其中C-F
liuxia_YSZB[4].add(GROUP_ID)
if SANLEI_Members != u'集团普通成员': #5统计三类成员
liuxia_YSZB[5] += 1
if PHONE_num is not None and '1390571' in str(PHONE_num): #6#1390571成员数
liuxia_YSZB[6] += 1
elif group_manager_dict[GROUP_ID][1] ==u'之江': #0成员累计查询
zhijiang_YSZB[0] += 1
zhijiang_YSZB[2].add(GROUP_ID) #2成员累计查询
if Group_level is not None and('A' in Group_level or 'B' in Group_level): #3其中AB类
zhijiang_YSZB[3].add(GROUP_ID)
else: #elif Group_level is not None and( 'C' in Group_level or 'D' in Group_level or 'E' in Group_level or 'F' in Group_level ): #4其中C-F
zhijiang_YSZB[4].add(GROUP_ID)
if SANLEI_Members != u'集团普通成员': #5统计三类成员
zhijiang_YSZB[5] += 1
if PHONE_num is not None and '1390571' in str(PHONE_num): #6#1390571成员数
zhijiang_YSZB[6] += 1
elif group_manager_dict[GROUP_ID][1] ==u'行业': #0成员累计查询
hangye_YSZB[0] += 1
hangye_YSZB[2].add(GROUP_ID) #2成员累计查询
if Group_level is not None and('A' in Group_level or 'B' in Group_level): #3其中AB类
hangye_YSZB[3].add(GROUP_ID)
else: #elif Group_level is not None and( 'C' in Group_level or 'D' in Group_level or 'E' in Group_level or 'F' in Group_level ): #4其中C-F
hangye_YSZB[4].add(GROUP_ID)
if SANLEI_Members != u'集团普通成员': #5统计三类成员
hangye_YSZB[5] += 1
if PHONE_num is not None and '1390571' in str(PHONE_num): #6#1390571成员数
hangye_YSZB[6] += 1
elif group_manager_dict[GROUP_ID][1] ==u'校园': #0成员累计查询
xiaoyuan_YSZB[0] += 1
xiaoyuan_YSZB[2].add(GROUP_ID) #2成员累计查询
if Group_level is not None and('A' in Group_level or 'B' in Group_level): #3其中AB类
xiaoyuan_YSZB[3].add(GROUP_ID)
else: #elif Group_level is not None and( 'C' in Group_level or 'D' in Group_level or 'E' in Group_level or 'F' in Group_level ): #4其中C-F
xiaoyuan_YSZB[4].add(GROUP_ID)
if SANLEI_Members != u'集团普通成员': #5统计三类成员
xiaoyuan_YSZB[5] += 1
if PHONE_num is not None and '1390571' in str(PHONE_num): #6#1390571成员数
xiaoyuan_YSZB[6] += 1
elif group_manager_dict[GROUP_ID][1] ==u'铁通': #0成员累计查询
tietong_YSZB[0] += 1
tietong_YSZB[2].add(GROUP_ID) #2成员累计查询
if Group_level is not None and('A' in Group_level or 'B' in Group_level): #3其中AB类
tietong_YSZB[3].add(GROUP_ID)
else: #elif Group_level is not None and( 'C' in Group_level or 'D' in Group_level or 'E' in Group_level or 'F' in Group_level ): #4其中C-F
tietong_YSZB[4].add(GROUP_ID)
if SANLEI_Members != u'集团普通成员': #5统计三类成员
tietong_YSZB[5] += 1
if PHONE_num is not None and '1390571' in str(PHONE_num): #6#1390571成员数
tietong_YSZB[6] += 1
##统计铁通每个GROUP_ID有多少人要携转
if alarm_groupID_tietong_NUM_dict.has_key(GROUP_ID):##2统计铁通每个GROUP_ID有多少人要携转
alarm_groupID_tietong_NUM_dict[GROUP_ID] += 1
else:
alarm_groupID_tietong_NUM_dict[GROUP_ID] = 1 ##2统计铁通每个GROUP_ID有多少人要携转
Tietong_Area = group_manager_dict[GROUP_ID][3] #铁通对应区域查询
if Tietong_Area==u'西城': #0成员累计查询
xicheng_tietong[0]+=1
xicheng_tietong[2].add(GROUP_ID) #2成员累计查询
if Group_level is not None and('A' in Group_level or 'B' in Group_level): #3其中AB类
xicheng_tietong[3].add(GROUP_ID)
else: #elif Group_level is not None and( 'C' in Group_level or 'D' in Group_level or 'E' in Group_level or 'F' in Group_level ): #4其中C-F
xicheng_tietong[4].add(GROUP_ID)
if SANLEI_Members != u'集团普通成员': #5统计三类成员
xicheng_tietong[5] += 1
if PHONE_num is not None and '1390571' in str(PHONE_num): #6#1390571成员数
xicheng_tietong[6] += 1
elif Tietong_Area==u'西子': #0成员累计查询
xizi_tietong[0]+=1
xizi_tietong[2].add(GROUP_ID) #2成员累计查询
if Group_level is not None and('A' in Group_level or 'B' in Group_level): #3其中AB类
xizi_tietong[3].add(GROUP_ID)
else: #elif Group_level is not None and( 'C' in Group_level or 'D' in Group_level or 'E' in Group_level or 'F' in Group_level ): #4其中C-F
xizi_tietong[4].add(GROUP_ID)
if SANLEI_Members != u'集团普通成员': #5统计三类成员
xizi_tietong[5] += 1
if PHONE_num is not None and '1390571' in str(PHONE_num): #6#1390571成员数
xizi_tietong[6] += 1
elif Tietong_Area==u'三墩': #0成员累计查询
sandun_tietong[0]+=1
sandun_tietong[2].add(GROUP_ID) #2成员累计查询
if Group_level is not None and('A' in Group_level or 'B' in Group_level): #3其中AB类
sandun_tietong[3].add(GROUP_ID)
else: #elif Group_level is not None and( 'C' in Group_level or 'D' in Group_level or 'E' in Group_level or 'F' in Group_level ): #4其中C-F
sandun_tietong[4].add(GROUP_ID)
if SANLEI_Members != u'集团普通成员': #5统计三类成员
sandun_tietong[5] += 1
if PHONE_num is not None and '1390571' in str(PHONE_num): #6#1390571成员数
sandun_tietong[6] += 1
elif Tietong_Area==u'留下': #0成员累计查询
liuxia_tietong[0]+=1
liuxia_tietong[2].add(GROUP_ID) #2成员累计查询
if Group_level is not None and('A' in Group_level or 'B' in Group_level): #3其中AB类
liuxia_tietong[3].add(GROUP_ID)
else: #elif Group_level is not None and( 'C' in Group_level or 'D' in Group_level or 'E' in Group_level or 'F' in Group_level ): #4其中C-F
liuxia_tietong[4].add(GROUP_ID)
if SANLEI_Members != u'集团普通成员': #5统计三类成员
liuxia_tietong[5] += 1
if PHONE_num is not None and '1390571' in str(PHONE_num): #6#1390571成员数
liuxia_tietong[6] += 1
elif Tietong_Area==u'之江': #0成员累计查询
zhijiang_tietong[0]+=1
zhijiang_tietong[2].add(GROUP_ID) #2成员累计查询
if Group_level is not None and('A' in Group_level or 'B' in Group_level): #3其中AB类
zhijiang_tietong[3].add(GROUP_ID)
else: #elif Group_level is not None and( 'C' in Group_level or 'D' in Group_level or 'E' in Group_level or 'F' in Group_level ): #4其中C-F
zhijiang_tietong[4].add(GROUP_ID)
if SANLEI_Members != u'集团普通成员': #5统计三类成员
zhijiang_tietong[5] += 1
if PHONE_num is not None and '1390571' in str(PHONE_num): #6#1390571成员数
zhijiang_tietong[6] += 1
elif group_manager_dict[GROUP_ID][1] ==u'要客': #0成员累计查询
yaoke_YSZB[0] += 1
yaoke_YSZB[2].add(GROUP_ID) #2成员累计查询
if Group_level is not None and('A' in Group_level or 'B' in Group_level): #3其中AB类
yaoke_YSZB[3].add(GROUP_ID)
else: #elif Group_level is not None and( 'C' in Group_level or 'D' in Group_level or 'E' in Group_level or 'F' in Group_level ): #4其中C-F
yaoke_YSZB[4].add(GROUP_ID)
if SANLEI_Members != u'集团普通成员': #5统计三类成员
yaoke_YSZB[5] += 1
if PHONE_num is not None and '1390571' in str(PHONE_num): #6#1390571成员数
yaoke_YSZB[6] += 1
else: #0成员累计查询 其他
qita_YSZB[0] += 1
qita_YSZB[2].add(GROUP_ID) #2成员累计查询
if Group_level is not None and('A' in Group_level or 'B' in Group_level): #3其中AB类
qita_YSZB[3].add(GROUP_ID)
else: #elif Group_level is not None and( 'C' in Group_level or 'D' in Group_level or 'E' in Group_level or 'F' in Group_level ): #4其中C-F
qita_YSZB[4].add(GROUP_ID)
if SANLEI_Members != u'集团普通成员': #5统计三类成员
qita_YSZB[5] += 1
if PHONE_num is not None and '1390571' in str(PHONE_num): #6#1390571成员数
qita_YSZB[6] += 1
#要客
elif u'08集团' == GROUP_COUNTY_NAME:
if YKJT_group_manager_dict.has_key(GROUP_ID): #要客 ##YKJT_group_manager_dict集团编号，集团客户经理，区域，集团级别，集团状态
if YKJT_group_manager_dict[GROUP_ID][1] ==u'西城': #0成员累计查询#要客
xicheng_yaoke[0] += 1
xicheng_yaoke[2].add(GROUP_ID) #2成员累计查询
if Group_level is not None and('A' in Group_level or 'B' in Group_level):#3其中AB类
xicheng_yaoke[3].add(GROUP_ID)
else: #elif Group_level is not None and( 'C' in Group_level or 'D' in Group_level or 'E' in Group_level or 'F' in Group_level ):#4其中C-F
xicheng_yaoke[4].add(GROUP_ID)
if SANLEI_Members != u'集团普通成员':#5统计三类成员
xicheng_yaoke[5] += 1
if PHONE_num is not None and '1390571' in str(PHONE_num): #6#1390571成员数
xicheng_yaoke[6] += 1
elif YKJT_group_manager_dict[GROUP_ID][1] ==u'西子': #0成员累计查询#要客
xizi_yaoke[0] += 1
xizi_yaoke[2].add(GROUP_ID) #2成员累计查询
if Group_level is not None and('A' in Group_level or 'B' in Group_level):#3其中AB类
xizi_yaoke[3].add(GROUP_ID)
else: #elif Group_level is not None and( 'C' in Group_level or 'D' in Group_level or 'E' in Group_level or 'F' in Group_level ):#4其中C-F
xizi_yaoke[4].add(GROUP_ID)
if SANLEI_Members != u'集团普通成员':#5统计三类成员
xizi_yaoke[5] += 1
if PHONE_num is not None and '1390571' in str(PHONE_num): #6#1390571成员数
xizi_yaoke[6] += 1
elif YKJT_group_manager_dict[GROUP_ID][1] ==u'三墩': #0成员累计查询#要客
sandun_yaoke[0] += 1
sandun_yaoke[2].add(GROUP_ID) #2成员累计查询
if Group_level is not None and('A' in Group_level or 'B' in Group_level):#3其中AB类
sandun_yaoke[3].add(GROUP_ID)
else: #elif Group_level is not None and( 'C' in Group_level or 'D' in Group_level or 'E' in Group_level or 'F' in Group_level ):#4其中C-F
sandun_yaoke[4].add(GROUP_ID)
if SANLEI_Members != u'集团普通成员':#5统计三类成员
sandun_yaoke[5] += 1
if PHONE_num is not None and '1390571' in str(PHONE_num): #6#1390571成员数
sandun_yaoke[6] += 1
elif YKJT_group_manager_dict[GROUP_ID][1] ==u'留下': #0成员累计查询#要客
liuxia_yaoke[0] += 1
liuxia_yaoke[2].add(GROUP_ID) #2成员累计查询
if Group_level is not None and('A' in Group_level or 'B' in Group_level):#3其中AB类
liuxia_yaoke[3].add(GROUP_ID)
else: #elif Group_level is not None and( 'C' in Group_level or 'D' in Group_level or 'E' in Group_level or 'F' in Group_level ):#4其中C-F
liuxia_yaoke[4].add(GROUP_ID)
if SANLEI_Members != u'集团普通成员':#5统计三类成员
liuxia_yaoke[5] += 1
if PHONE_num is not None and '1390571' in str(PHONE_num): #6#1390571成员数
liuxia_yaoke[6] += 1
elif YKJT_group_manager_dict[GROUP_ID][1] ==u'之江': #0成员累计查询#要客
zhijiang_yaoke[0] += 1
zhijiang_yaoke[2].add(GROUP_ID) #2成员累计查询
if Group_level is not None and('A' in Group_level or 'B' in Group_level):#3其中AB类
zhijiang_yaoke[3].add(GROUP_ID)
else: #elif Group_level is not None and( 'C' in Group_level or 'D' in Group_level or 'E' in Group_level or 'F' in Group_level ):#4其中C-F
zhijiang_yaoke[4].add(GROUP_ID)
if SANLEI_Members != u'集团普通成员':#5统计三类成员
zhijiang_yaoke[5] += 1
if PHONE_num is not None and '1390571' in str(PHONE_num): #6#1390571成员数
zhijiang_yaoke[6] += 1
else:#其他 #0成员累计查询 #要客
zhijiang_yaoke[0] += 1
zhijiang_yaoke[2].add(GROUP_ID) #2成员累计查询
if Group_level is not None and('A' in Group_level or 'B' in Group_level):#3其中AB类
zhijiang_yaoke[3].add(GROUP_ID)
else: #elif Group_level is not None and( 'C' in Group_level or 'D' in Group_level or 'E' in Group_level or 'F' in Group_level ):#4其中C-F
zhijiang_yaoke[4].add(GROUP_ID)
if SANLEI_Members != u'集团普通成员':#5统计三类成员
zhijiang_yaoke[5] += 1
if PHONE_num is not None and '1390571' in str(PHONE_num): #6#1390571成员数
zhijiang_yaoke[6] += 1
else: #没有在五个区域内的其他类
qita_yaoke[0] += 1
qita_yaoke[2].add(GROUP_ID) #2成员累计查询
if Group_level is not None and('A' in Group_level or 'B' in Group_level):#3其中AB类
qita_yaoke[3].add(GROUP_ID)
else: #elif Group_level is not None and( 'C' in Group_level or 'D' in Group_level or 'E' in Group_level or 'F' in Group_level ):#4其中C-F
qita_yaoke[4].add(GROUP_ID)
if SANLEI_Members != u'集团普通成员':#5统计三类成员
qita_yaoke[5] += 1
if PHONE_num is not None and '1390571' in str(PHONE_num): #6#1390571成员数
qita_yaoke[6] += 1
##########################################################################################
print(u'……………………保存YSZB统计结果文件中……………………')
##统计累计XC成员大于20集团数
#新建"20人以上AB类集团详情"表，保存数据
ws2_num=2
list_name = [u'集团编号',u'集团名',u'人数',u'客户经理',u'区域',u'集团级别']
for i in range(1,len(list_name)+1):
ws2_new.cell(1, i).value = list_name[i-1]
for key,value in alarm_groupID_NUM_dict.items():
if not group_manager_dict.has_key(key):
continue
if value>=20:
ws2_new.cell(ws2_num, 1).value = key
ws2_new.cell(ws2_num, 2).value = group_manager_dict[key][4]
ws2_new.cell(ws2_num, 3).value = value
ws2_new.cell(ws2_num, 4).value = group_manager_dict[key][0]
ws2_new.cell(ws2_num, 5).value = group_manager_dict[key][1]
ws2_new.cell(ws2_num, 6).value = group_manager_dict[key][2]
ws2_new.cell(ws2_num, 7).value = group_manager_dict[key][3]
ws2_num+=1
if group_manager_dict[key][1] ==u'西城'and value>=20:
xicheng_YSZB[1]+=1
elif group_manager_dict[key][1] ==u'西子'and value>=20:
xizi_YSZB[1]+=1
elif group_manager_dict[key][1] ==u'三墩'and value>=20:
sandun_YSZB[1]+=1
elif group_manager_dict[key][1] ==u'留下'and value>=20:
liuxia_YSZB[1]+=1
elif group_manager_dict[key][1] ==u'之江'and value>=20:
zhijiang_YSZB[1]+=1
elif group_manager_dict[key][1] ==u'行业'and value>=20:
hangye_YSZB[1]+=1
elif group_manager_dict[key][1] ==u'校园'and value>=20:
xiaoyuan_YSZB[1]+=1
elif group_manager_dict[key][1] ==u'铁通'and value>=20:
tietong_YSZB[1]+=1
elif value>=20:
qita_YSZB[1]+=1
print(u'……………………………………YSZB统计数据保存…………………………………………')
row_xz = 4
for i in range(2, 9):
if i==4 or i ==5 or i ==6 :
ws_new.cell(row_xz, i).value = len(xicheng_YSZB[i-2])
ws_new.cell(row_xz+1, i).value = len(xizi_YSZB[i-2])
ws_new.cell(row_xz+2, i).value = len(sandun_YSZB[i-2])
ws_new.cell(row_xz+3, i).value = len(liuxia_YSZB[i-2])
ws_new.cell(row_xz+4, i).value = len(zhijiang_YSZB[i-2])
ws_new.cell(row_xz+5, i).value = len(hangye_YSZB[i-2])
ws_new.cell(row_xz+6, i).value = len(tietong_YSZB[i-2])
ws_new.cell(row_xz+7, i).value = len(xiaoyuan_YSZB[i-2])
ws_new.cell(row_xz+8, i).value = len(qita_YSZB[i-2])
ws_new.cell(row_xz+9, i).value = len(yaoke_YSZB[i-2])
total_YSZB[i-2]= len(xicheng_YSZB[i-2])+len(xizi_YSZB[i-2])+len(sandun_YSZB[i-2])+len(liuxia_YSZB[i-2])+len(zhijiang_YSZB[i-2])+len(hangye_YSZB[i-2])+len(tietong_YSZB[i-2])+len(xiaoyuan_YSZB[i-2])+len(yaoke_YSZB[i-2])+len(qita_YSZB[i-2])
ws_new.cell(row_xz+10, i).value = total_YSZB[i-2]
else :
ws_new.cell(row_xz, i).value = xicheng_YSZB[i-2]
ws_new.cell(row_xz+1, i).value = xizi_YSZB[i-2]
ws_new.cell(row_xz+2, i).value = sandun_YSZB[i-2]
ws_new.cell(row_xz+3, i).value = liuxia_YSZB[i-2]
ws_new.cell(row_xz+4, i).value = zhijiang_YSZB[i-2]
ws_new.cell(row_xz+5, i).value = hangye_YSZB[i-2]
ws_new.cell(row_xz+6, i).value = tietong_YSZB[i-2]
ws_new.cell(row_xz+7, i).value = xiaoyuan_YSZB[i-2]
ws_new.cell(row_xz+8, i).value = qita_YSZB[i-2]
ws_new.cell(row_xz+9, i).value = yaoke_YSZB[i-2]
total_YSZB[i-2]= xicheng_YSZB[i-2]+xizi_YSZB[i-2]+sandun_YSZB[i-2]+liuxia_YSZB[i-2]+zhijiang_YSZB[i-2]+hangye_YSZB[i-2]+tietong_YSZB[i-2]+xiaoyuan_YSZB[i-2]+yaoke_YSZB[i-2]+qita_YSZB[i-2]
ws_new.cell(row_xz+10, i).value = total_YSZB[i-2]
print(u'……………………………………西湖铁通集团成员XZ高危预警数据保存…………………………………………')
#########################统计 铁通 累计XC成员大于20集团数#######################################
ws2_num = 0
for key,value in alarm_groupID_tietong_NUM_dict.items():
if not group_manager_dict.has_key(key):
continue
if group_manager_dict[key][1] ==u'西城'and value>=20:
xicheng_tietong[1]+=1
elif group_manager_dict[key][1] ==u'西子'and value>=20:
xizi_tietong[1]+=1
elif group_manager_dict[key][1] ==u'三墩'and value>=20:
sandun_tietong[1]+=1
elif group_manager_dict[key][1] ==u'留下'and value>=20:
liuxia_tietong[1]+=1
elif group_manager_dict[key][1] ==u'之江'and value>=20:
zhijiang_tietong[1]+=1
row_tt = 18
for i in range(2, 9):
if i==4 or i ==5 or i ==6 :
ws_new.cell(row_tt, i).value = len(xicheng_tietong[i-2])
ws_new.cell(row_tt+1, i).value = len(xizi_tietong[i-2])
ws_new.cell(row_tt+2, i).value = len(sandun_tietong[i-2])
ws_new.cell(row_tt+3, i).value = len(liuxia_tietong[i-2])
ws_new.cell(row_tt+4, i).value = len(zhijiang_tietong[i-2])
total_tietong[i-2]= len(xicheng_tietong[i-2])+len(xizi_tietong[i-2])+len(sandun_tietong[i-2])+len(liuxia_tietong[i-2])+len(zhijiang_tietong[i-2])
ws_new.cell(row_tt+5, i).value = total_tietong[i-2]
else :
ws_new.cell(row_tt, i).value = xicheng_tietong[i-2]
ws_new.cell(row_tt+1, i).value = xizi_tietong[i-2]
ws_new.cell(row_tt+2, i).value = sandun_tietong[i-2]
ws_new.cell(row_tt+3, i).value = liuxia_tietong[i-2]
ws_new.cell(row_tt+4, i).value = zhijiang_tietong[i-2]
total_tietong[i-2]= xicheng_tietong[i-2]+xizi_tietong[i-2]+sandun_tietong[i-2]+liuxia_tietong[i-2]+zhijiang_tietong[i-2]
ws_new.cell(row_tt+5, i).value = total_tietong[i-2]
##########################统计 要客 累计XC成员大于20集团数#######################################
print(u'……………………………………西湖要客集团成员XZ高危预警数据保存…………………………………………')
ws3_num=2
#list_name = [u'集团编号',u'集团名',u'人数',u'客户经理',u'区域',u'集团级别']
for i in range(1,len(list_name)+1):
ws3_new.cell(1, i).value = list_name[i-1]
for key,value in alarm_groupID_yaoke_NUM_dict.items():
if not YKJT_group_manager_dict.has_key(key):
continue
if value>=20:
ws3_new.cell(ws3_num, 1).value = key
ws3_new.cell(ws3_num, 2).value = YKJT_group_manager_dict[key][4]
ws3_new.cell(ws3_num, 3).value = value
ws3_new.cell(ws3_num, 4).value = YKJT_group_manager_dict[key][0]
ws3_new.cell(ws3_num, 5).value = YKJT_group_manager_dict[key][1]
ws3_new.cell(ws3_num, 6).value = YKJT_group_manager_dict[key][2]
#ws3_new.cell(ws3_num, 7).value = YKJT_group_manager_dict[key][3]
ws3_num+=1
if YKJT_group_manager_dict[key][1] ==u'西城'and value>=20:
xicheng_yaoke[1]+=1
elif YKJT_group_manager_dict[key][1] ==u'西子'and value>=20:
xizi_yaoke[1]+=1
elif YKJT_group_manager_dict[key][1] ==u'三墩'and value>=20:
sandun_yaoke[1]+=1
elif YKJT_group_manager_dict[key][1] ==u'留下'and value>=20:
liuxia_yaoke[1]+=1
elif YKJT_group_manager_dict[key][1] ==u'之江'and value>=20:
zhijiang_yaoke[1]+=1
elif value>=20:
qita_yaoke[1]+=1
row_yk = 28
for i in range(2, 9):
if i==4 or i ==5 or i ==6 :
ws_new.cell(row_yk, i).value = len(xicheng_yaoke[i-2])
ws_new.cell(row_yk+1, i).value = len(xizi_yaoke[i-2])
ws_new.cell(row_yk+2, i).value = len(sandun_yaoke[i-2])
ws_new.cell(row_yk+3, i).value = len(liuxia_yaoke[i-2])
ws_new.cell(row_yk+4, i).value = len(zhijiang_yaoke[i-2])
ws_new.cell(row_yk+5, i).value = len(qita_yaoke[i-2])
total_yaoke[i-2]= len(xicheng_yaoke[i-2])+len(xizi_yaoke[i-2])+len(sandun_yaoke[i-2])+len(liuxia_yaoke[i-2])+len(zhijiang_yaoke[i-2])+len(qita_yaoke[i-2])
ws_new.cell(row_yk+6, i).value = total_yaoke[i-2]
else :
ws_new.cell(row_yk, i).value = xicheng_yaoke[i-2]
ws_new.cell(row_yk+1, i).value = xizi_yaoke[i-2]
ws_new.cell(row_yk+2, i).value = sandun_yaoke[i-2]
ws_new.cell(row_yk+3, i).value = liuxia_yaoke[i-2]
ws_new.cell(row_yk+4, i).value = zhijiang_yaoke[i-2]
ws_new.cell(row_yk+5, i).value = qita_yaoke[i-2]
total_yaoke[i-2]= xicheng_yaoke[i-2]+xizi_yaoke[i-2]+sandun_yaoke[i-2]+liuxia_yaoke[i-2]+zhijiang_yaoke[i-2]+qita_yaoke[i-2]
ws_new.cell(row_yk+6, i).value = total_yaoke[i-2]
################################################################################################
xicheng_XHXC = [0,0,0,0,set(),set(),0,0,0,0]
xizi_XHXC = [0,0,0,0,set(),set(),0,0,0,0]
sandun_XHXC = [0,0,0,0,set(),set(),0,0,0,0]
liuxia_XHXC = [0,0,0,0,set(),set(),0,0,0,0]
zhijiang_XHXC = [0,0,0,0,set(),set(),0,0,0,0]
hangye_XHXC = [0,0,0,0,set(),set(),0,0,0,0]
tietong_XHXC = [0,0,0,0,set(),set(),0,0,0,0]
xiaoyuan_XHXC = [0,0,0,0,set(),set(),0,0,0,0]
qita_XHXC = [0,0,0,0,set(),set(),0,0,0,0]
total_XHXC = [0,0,0,0,0,0,0,0,0,0]
print('sheet_XHXC.max_row = ',sheet_XHXC.max_row)
print u'……………………读取-XHXC清单中……………………'
XHXC_groupID_NUM_dict = {u'':0} #预警中GROUP_ID与个数的映射 ##统计每个GROUP_ID有多少人要携转
Current_Month = self.Current_Month_of_extraction() # 提取当月的月份数
for i in range(2, sheet_XHXC.max_row+1): # 建立西湖携出清单 %%%%手机号码，集团编号，携出运营商，携出时间
PhoneNum = sheet_XHXC.cell(i, 1).value
XHXC_groupID = sheet_XHXC.cell(i, 8).value
Operator = sheet_XHXC.cell(i, 19).value
XHXC_membership_class = sheet_XHXC.cell(i, 11).value #集团级别 AB类
XHXC_membership_category = sheet_XHXC.cell(i, 12).value #成员类别 集团普通成员
Month = self.Month_of_extraction(sheet_XHXC.cell(i, 7).value) # 提取xlsx文件的月份数
#print PhoneNum,XHXC_groupID,Operator,XHXC_membership_class,Month
if XHXC_groupID_NUM_dict.has_key(XHXC_groupID):##统计每个GROUP_ID有多少人要携转
XHXC_groupID_NUM_dict[XHXC_groupID] += 1
else:
XHXC_groupID_NUM_dict[XHXC_groupID] = 1 ##统计每个GROUP_ID有多少人要携转
if group_manager_dict.has_key(XHXC_groupID):
#print PhoneNum,XHXC_groupID,Operator,XHXC_membership_class,Month
if group_manager_dict[XHXC_groupID][1] ==u'西城':
if Month == Current_Month:#0当月进度，月成员XC
xicheng_XHXC[0] += 1
if XHXC_membership_category != u'集团普通成员': #1当月进度，三类成员及首号段XC
xicheng_XHXC[1] += 1
xicheng_XHXC[2]+=1 #2累计成员XC
if XHXC_membership_category != u'集团普通成员' or (PhoneNum is not None and '1390571' in str(PhoneNum)): #3统计三类成员及首号段XC ,##1390571成员数
xicheng_XHXC[3] += 1
xicheng_XHXC[4].add(XHXC_groupID) #4累计涉及集团数
xicheng_XHXC[5].add(group_manager_dict[XHXC_groupID][0]) #5累计客户经理数
if Operator == u'DX': #6携出电信个数统计
xicheng_XHXC[6] += 1
elif Operator == u'LT': #7携出联通个数统计
xicheng_XHXC[7] += 1
xicheng_XHXC[8] += 1 #8携出联通个数统计
elif group_manager_dict[XHXC_groupID][1] ==u'西子':
if Month == Current_Month:#0当月进度，月成员XC
xizi_XHXC[0] += 1
if XHXC_membership_category != u'集团普通成员': #1当月进度，三类成员及首号段XC
xizi_XHXC[1] += 1
xizi_XHXC[2]+=1 #2累计成员XC
if XHXC_membership_category != u'集团普通成员' or (PhoneNum is not None and '1390571' in str(PhoneNum)): #3统计三类成员及首号段XC ,##1390571成员数
xizi_XHXC[3] += 1
xizi_XHXC[4].add(XHXC_groupID) #4累计涉及集团数
xizi_XHXC[5].add(group_manager_dict[XHXC_groupID][0]) #5累计客户经理数
if Operator == u'DX': #6携出电信个数统计
xizi_XHXC[6] += 1
elif Operator == u'LT': #7携出联通个数统计
xizi_XHXC[7] += 1
xizi_XHXC[8] += 1 #8携出联通个数统计
elif group_manager_dict[XHXC_groupID][1] ==u'三墩':
if Month == Current_Month:#0当月进度，月成员XC
sandun_XHXC[0] += 1
if XHXC_membership_category != u'集团普通成员': #1当月进度，三类成员及首号段XC
sandun_XHXC[1] += 1
sandun_XHXC[2]+=1 #2累计成员XC
if XHXC_membership_category != u'集团普通成员' or (PhoneNum is not None and '1390571' in str(PhoneNum)): #3统计三类成员及首号段XC ,##1390571成员数
sandun_XHXC[3] += 1
sandun_XHXC[4].add(XHXC_groupID) #4累计涉及集团数
sandun_XHXC[5].add(group_manager_dict[XHXC_groupID][0]) #5累计客户经理数
if Operator == u'DX': #6携出电信个数统计
sandun_XHXC[6] += 1
elif Operator == u'LT': #7携出联通个数统计
sandun_XHXC[7] += 1
sandun_XHXC[8] += 1 #8携出联通个数统计
elif group_manager_dict[XHXC_groupID][1] ==u'留下':
if Month == Current_Month:#0当月进度，月成员XC
liuxia_XHXC[0] += 1
if XHXC_membership_category != u'集团普通成员': #1当月进度，三类成员及首号段XC
liuxia_XHXC[1] += 1
liuxia_XHXC[2]+=1 #2累计成员XC
if XHXC_membership_category != u'集团普通成员' or (PhoneNum is not None and '1390571' in str(PhoneNum)): #3统计三类成员及首号段XC ,##1390571成员数
liuxia_XHXC[3] += 1
liuxia_XHXC[4].add(XHXC_groupID) #4累计涉及集团数
liuxia_XHXC[5].add(group_manager_dict[XHXC_groupID][0]) #5累计客户经理数
if Operator == u'DX': #6携出电信个数统计
liuxia_XHXC[6] += 1
elif Operator == u'LT': #7携出联通个数统计
liuxia_XHXC[7] += 1
liuxia_XHXC[8] += 1 #8携出联通个数统计
elif group_manager_dict[XHXC_groupID][1] ==u'之江':
if Month == Current_Month:#0当月进度，月成员XC
zhijiang_XHXC[0] += 1
if XHXC_membership_category != u'集团普通成员': #1当月进度，三类成员及首号段XC
zhijiang_XHXC[1] += 1
zhijiang_XHXC[2]+=1 #2累计成员XC
if XHXC_membership_category != u'集团普通成员' or (PhoneNum is not None and '1390571' in str(PhoneNum)): #3统计三类成员及首号段XC ,##1390571成员数
zhijiang_XHXC[3] += 1
zhijiang_XHXC[4].add(XHXC_groupID) #4累计涉及集团数
zhijiang_XHXC[5].add(group_manager_dict[XHXC_groupID][0]) #5累计客户经理数
if Operator == u'DX': #6携出电信个数统计
zhijiang_XHXC[6] += 1
elif Operator == u'LT': #7携出联通个数统计
zhijiang_XHXC[7] += 1
zhijiang_XHXC[8] += 1 #8携出联通个数统计
elif group_manager_dict[XHXC_groupID][1] ==u'行业':
if Month == Current_Month:#0当月进度，月成员XC
hangye_XHXC[0] += 1
if XHXC_membership_category != u'集团普通成员': #1当月进度，三类成员及首号段XC
hangye_XHXC[1] += 1
hangye_XHXC[2]+=1 #2累计成员XC
if XHXC_membership_category != u'集团普通成员' or (PhoneNum is not None and '1390571' in str(PhoneNum)): #3统计三类成员及首号段XC ,##1390571成员数
hangye_XHXC[3] += 1
hangye_XHXC[4].add(XHXC_groupID) #4累计涉及集团数
hangye_XHXC[5].add(group_manager_dict[XHXC_groupID][0]) #5累计客户经理数
if Operator == u'DX': #6携出电信个数统计
hangye_XHXC[6] += 1
elif Operator == u'LT': #7携出联通个数统计
hangye_XHXC[7] += 1
hangye_XHXC[8] += 1 #8携出联通个数统计
elif group_manager_dict[XHXC_groupID][1] ==u'铁通':
if Month == Current_Month:#0当月进度，月成员XC
tietong_XHXC[0] += 1
if XHXC_membership_category != u'集团普通成员': #1当月进度，三类成员及首号段XC
tietong_XHXC[1] += 1
tietong_XHXC[2]+=1 #2累计成员XC
if XHXC_membership_category != u'集团普通成员' or (PhoneNum is not None and '1390571' in str(PhoneNum)): #3统计三类成员及首号段XC ,##1390571成员数
tietong_XHXC[3] += 1
tietong_XHXC[4].add(XHXC_groupID) #4累计涉及集团数
tietong_XHXC[5].add(group_manager_dict[XHXC_groupID][0]) #5累计客户经理数
if Operator == u'DX': #6携出电信个数统计
tietong_XHXC[6] += 1
elif Operator == u'LT': #7携出联通个数统计
tietong_XHXC[7] += 1
tietong_XHXC[8] += 1 #8携出联通个数统计
elif group_manager_dict[XHXC_groupID][1] ==u'校园':
if Month == Current_Month:#0当月进度，月成员XC
xiaoyuan_XHXC[0] += 1
if XHXC_membership_category != u'集团普通成员': #1当月进度，三类成员及首号段XC
xiaoyuan_XHXC[1] += 1
xiaoyuan_XHXC[2]+=1 #2累计成员XC
if XHXC_membership_category != u'集团普通成员' or (PhoneNum is not None and '1390571' in str(PhoneNum)): #3统计三类成员及首号段XC ,##1390571成员数
xiaoyuan_XHXC[3] += 1
xiaoyuan_XHXC[4].add(XHXC_groupID) #4累计涉及集团数
xiaoyuan_XHXC[5].add(group_manager_dict[XHXC_groupID][0]) #5累计客户经理数
if Operator == u'DX': #6携出电信个数统计
xiaoyuan_XHXC[6] += 1
elif Operator == u'LT': #7携出联通个数统计
xiaoyuan_XHXC[7] += 1
xiaoyuan_XHXC[8] += 1 #8携出联通个数统计
else:
if Month == Current_Month:#0当月进度，月成员XC
qita_XHXC[0] += 1
if XHXC_membership_category != u'集团普通成员': #1当月进度，三类成员及首号段XC
qita_XHXC[1] += 1
qita_XHXC[2]+=1 #2累计成员XC
if XHXC_membership_category != u'集团普通成员' or (PhoneNum is not None and '1390571' in str(PhoneNum)): #3统计三类成员及首号段XC ,##1390571成员数
qita_XHXC[3] += 1
qita_XHXC[4].add(XHXC_groupID) #4累计涉及集团数
qita_XHXC[5].add(group_manager_dict[XHXC_groupID][0]) #5累计客户经理数
if Operator == u'DX': #6携出电信个数统计
qita_XHXC[6] += 1
elif Operator == u'LT': #7携出联通个数统计
qita_XHXC[7] += 1
qita_XHXC[8] += 1 #8携出联通个数统计
##########################保存XHXC统计结果文件中##############################
print(u'……………………保存XHXC统计结果文件中……………………')
##统计累计XC成员大于2集团数
for key,value in XHXC_groupID_NUM_dict.items():
if not group_manager_dict.has_key(key):
continue
if group_manager_dict[key][1] ==u'西城'and value>=2:
xicheng_XHXC[9]+=1
elif group_manager_dict[key][1] ==u'西子'and value>=2:
xizi_XHXC[9]+=1
elif group_manager_dict[key][1] ==u'三墩'and value>=2:
sandun_XHXC[9]+=1
elif group_manager_dict[key][1] ==u'留下'and value>=2:
liuxia_XHXC[9]+=1
elif group_manager_dict[key][1] ==u'之江'and value>=2:
zhijiang_XHXC[9]+=1
elif group_manager_dict[key][1] ==u'行业'and value>=2:
hangye_XHXC[9]+=1
elif group_manager_dict[key][1] ==u'校园'and value>=2:
xiaoyuan_XHXC[9]+=1
elif group_manager_dict[key][1] ==u'铁通'and value>=2:
tietong_XHXC[9]+=1
elif value>=2:
qita_XHXC[9]+=1
for i in range(2, 12):
if i==6 or i ==7 :
ws_new.cell(row_xc, i).value = len(xicheng_XHXC[i-2])
ws_new.cell(row_xc+1, i).value = len(xizi_XHXC[i-2])
ws_new.cell(row_xc+2, i).value = len(sandun_XHXC[i-2])
ws_new.cell(row_xc+3, i).value = len(liuxia_XHXC[i-2])
ws_new.cell(row_xc+4, i).value = len(zhijiang_XHXC[i-2])
ws_new.cell(row_xc+5, i).value = len(hangye_XHXC[i-2])
ws_new.cell(row_xc+6, i).value = len(tietong_XHXC[i-2])
ws_new.cell(row_xc+7, i).value = len(xiaoyuan_XHXC[i-2])
ws_new.cell(row_xc+8, i).value = len(qita_XHXC[i-2])
total_XHXC[i-2]= len(xicheng_XHXC[i-2])+len(xizi_XHXC[i-2])+len(sandun_XHXC[i-2])+len(liuxia_XHXC[i-2])+len(zhijiang_XHXC[i-2])+len(hangye_XHXC[i-2])+len(tietong_XHXC[i-2])+len(xiaoyuan_XHXC[i-2])+len(qita_XHXC[i-2])
ws_new.cell(row_xc+9, i).value = total_XHXC[i-2]
else :
ws_new.cell(row_xc, i).value = xicheng_XHXC[i-2]
ws_new.cell(row_xc+1, i).value = xizi_XHXC[i-2]
ws_new.cell(row_xc+2, i).value = sandun_XHXC[i-2]
ws_new.cell(row_xc+3, i).value = liuxia_XHXC[i-2]
ws_new.cell(row_xc+4, i).value = zhijiang_XHXC[i-2]
ws_new.cell(row_xc+5, i).value = hangye_XHXC[i-2]
ws_new.cell(row_xc+6, i).value = tietong_XHXC[i-2]
ws_new.cell(row_xc+7, i).value = xiaoyuan_XHXC[i-2]
ws_new.cell(row_xc+8, i).value = qita_XHXC[i-2]
total_XHXC[i-2]= xicheng_XHXC[i-2]+xizi_XHXC[i-2]+sandun_XHXC[i-2]+liuxia_XHXC[i-2]+zhijiang_XHXC[i-2]+hangye_XHXC[i-2]+tietong_XHXC[i-2]+xiaoyuan_XHXC[i-2]+qita_XHXC[i-2]
ws_new.cell(row_xc+9, i).value = total_XHXC[i-2]
# 提取当前时间
local_time = datetime.now()
local_str = local_time.strftime('%Y-%m-%d %H-%M-%S')
length = len(self.filepath1)
for i in range(length - 1, -1, -1):
if (self.filepath1[i] == '/'):
break
savepath1 = u""
savepath2 = u""
for j in range(0, i + 1):
savepath1 += self.filepath1[j]
savepath1 = savepath2
#print type(local_str), local_str
savepath1 = savepath1 + u'XZ预警建模统计' + local_str + u'.xlsx'
print u'保存文件为： ',savepath1
wb_new.save(savepath1) # 保存文件
########################################################
if __name__ == '__main__':
root = Tk()
root.title('携转分析App')
app = App(root)
root.mainloop()
9281
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
