从BUG工具redmine上获取数据后借助python模块pycha 画出BUG分析类报表，利用xlsxwriter

#__author__ = 'xu.duan'
# -*- coding: utf-8 -*-
#######################################################################
#
import xlsxwriter,time,datetime
from redmine import Redmine
from functools import reduce
import sys
import os
import os.path
import getopt
import csv

reload(sys)
sys.setdefaultencoding('gb18030')

#set redmine
def Set_Redmine():
    REDMINE_URL = 'http://100.69.177.149:3000'
    REDMINE_KEY = 'b549cac9132fb3ef8b2d3ed898dfb1f6e641b2f3'
    redmine = Redmine(REDMINE_URL,key=REDMINE_KEY)
    return redmine

#获取所有issues方法，可以用*arg定义
def Get_Issues(project_name,status_id='*',tracker_id=None):
    issues = Set_Redmine().issue.filter(project_id ='{0}'.format(project_name),status_id='{0}'.format(status_id),tracker_id='{0}'.format(tracker_id))
    return issues

#获取问题严重程度
def Get_Priority(project_name,status_id='*',tracker_id=None,priority_id=None,date=None,cf_26=None,cf_37=None):
    priority_issues = Set_Redmine().issue.filter(project_id ='{0}'.format(project_name),status_id='{0}'.format(status_id),tracker_id='{0}'.format(tracker_id),priority_id='{0}'.format(priority_id),created_on='><{0}'.format(date),cf_26='{0}'.format(cf_26),cf_37='{0}'.format(cf_37))
    return priority_issues

def Get_Viewer_Priority(project_name,status_id='*',tracker_id=None,priority_id=None,date=None):
    priority_issues = Set_Redmine().issue.filter(project_id ='{0}'.format(project_name),status_id='{0}'.format(status_id),tracker_id='{0}'.format(tracker_id),priority_id='{0}'.format(priority_id),created_on='><{0}'.format(date))
    return priority_issues


#按周获取问题
def Get_Week_Issues(project_name,status_id='*',tracker_id=None,date=None,cf_26=None,cf_37=None):
    priority_issues = Set_Redmine().issue.filter(project_id ='{0}'.format(project_name),status_id='{0}'.format(status_id),tracker_id='{0}'.format(tracker_id),created_on='><{0}'.format(date),cf_26='{0}'.format(cf_26),cf_37='{0}'.format(cf_37))
    return priority_issues

def Get_Week_Issues_closed(project_name,status_id='*',tracker_id=None,date=None,cf_26=None,cf_37=None):
    priority_issues = Set_Redmine().issue.filter(project_id ='{0}'.format(project_name),status_id='{0}'.format(status_id),tracker_id='{0}'.format(tracker_id),closed_on='><{0}'.format(date),cf_26='{0}'.format(cf_26),cf_37='{0}'.format(cf_37))
    return priority_issues

def Get_Viewer_Week_Issues(project_name,status_id='*',tracker_id=None,date=None):
    priority_issues = Set_Redmine().issue.filter(project_id ='{0}'.format(project_name),status_id='{0}'.format(status_id),tracker_id='{0}'.format(tracker_id),created_on='><{0}'.format(date))
    return priority_issues

def Get_Viewer_Week_Issues_closed(project_name,status_id='*',tracker_id=None,date=None):
    priority_issues = Set_Redmine().issue.filter(project_id ='{0}'.format(project_name),status_id='{0}'.format(status_id),tracker_id='{0}'.format(tracker_id),closed_on='><{0}'.format(date))
    return priority_issues
#按国家获取
def Get_Ancs_Issues(project_name,status_id=None,tracker_id=None,cf_26=None):
    priority_issues = Set_Redmine().issue.filter(project_id ='{0}'.format(project_name),status_id='{0}'.format(status_id),tracker_id='{0}'.format(tracker_id),cf_26='{0}'.format(cf_26))
    return priority_issues
#cf_26List=['Taiwan','Japan','Korea','German','Thailand']
#for i in cf_26List:
    #print len(Get_Ancs_Issues('nds-2-3-2',20,1,cf_26='%s'%i))

    

def Get_Issues_category(project_name,status_id='*',tracker_id=None,cf_26=None,category_id=None):
    priority_issues = Set_Redmine().issue.filter(project_id ='{0}'.format(project_name),status_id='{0}'.format(status_id),tracker_id='{0}'.format(tracker_id),cf_26='{0}'.format(cf_26),category_id='{0}'.format(category_id))
    return priority_issues


#Ninjia=========================================================
def Get_Priority_Category(project_name,status_id='*',tracker_id=None,priority_id=None,category_id=None):
    priority_issues = Set_Redmine().issue.filter(project_id ='{0}'.format(project_name),status_id='{0}'.format(status_id),tracker_id='{0}'.format(tracker_id),priority_id='{0}'.format(priority_id),category_id='{0}'.format(category_id))
    return priority_issues

def Get_Category(project_name,status_id='*',tracker_id=None,category_id=None):
    priority_issues = Set_Redmine().issue.filter(project_id ='{0}'.format(project_name),status_id='{0}'.format(status_id),tracker_id='{0}'.format(tracker_id),category_id='{0}'.format(category_id))
    return priority_issues

def Get_CF37(project_name,status_id='*',tracker_id=None,cf_37=None):
    priority_issues = Set_Redmine().issue.filter(project_id ='{0}'.format(project_name),status_id='{0}'.format(status_id),tracker_id='{0}'.format(tracker_id),cf_37='{0}'.format(cf_37))
    return priority_issues

def Get_Ninja_Week_BUG(project_name,status_id='*',tracker_id=None,date=None):
    priority_issues = Set_Redmine().issue.filter(project_id ='{0}'.format(project_name),status_id='{0}'.format(status_id),tracker_id='{0}'.format(tracker_id),created_on='><{0}'.format(date))
    return priority_issues

#aa = Get_Ninja_Week_BUG('redmine-test','*',11,'2015-01-01|2015-12-31')
#print len(aa)

'''
w= Get_Issues_category('nds-2-3-2','*',1,category_id=40) 

print len(w)


created_on='><2012-03-01|2012-03-07'
redmine = Set_Redmine()
i= Get_Issues('nds-2-3-2','*',tracker_id=1) 
c= Get_Issues('nds-2-3-2','closed',tracker_id=1) 
o= Get_Issues('nds-2-3-2','open',tracker_id=1) 
p= Get_Priority('nds-2-3-2','open',1,2) 
w= Get_Week_Issues('nds-2-3-2','*',1) 
print len(w)#len(i),len(o),len(c),len(p)

w= Get_Week_Issues('nds-2-3-2','closed',1,Week_Get(22)) #上上周
print Week_Get(-20)
'''
#定义开始时间和最后一天，定义是全局变量
current = datetime.datetime.now()  
start = datetime.date(current.year,1,1)  
laststart = datetime.date(current.year-1,1,1) 
laststart1 = datetime.date(current.year-2,1,1)   
last_day = datetime.date(current.year,12,31)  
isfirst = start.weekday()  
last_week = last_day.strftime('%W')  

  
  
weeks={}  
if isfirst !=0:  
    end = datetime.timedelta(7-start.weekday()-1)  
    weeks[0]=[start,start+end]  
start += datetime.timedelta(7 - start.weekday())  
def Print_Data(i):  
    days = datetime.timedelta(weeks=i)  
    end = start + days       
    if  i+1 == int(last_week):  
        weeks[i+1] = [end,last_day]  
    else:  
        weeks[i+1] = [end,end + datetime.timedelta(6)]  
  
def AllWeeks():  
    for i in range(0, int(last_week)):  
        Print_Data(i)  
    return weeks    
          
def WeekMain():  
  
    AllWeeks()  
    daylist= []
    weeklist = []
    daylist.append(u"%s年"%(current.year-2))
    daylist.append(u"%s年"%(current.year-1))
    weeklist.append('|'.join([str(laststart1),str(laststart)]))
    weeklist.append('|'.join([str(laststart),str(start)]))
    for (k,week) in weeks.items():  
        num = k+1<=9 and str(k+1) or str(k+1)
        if int(num) <= int(time.strftime("%W")):
            daylist.append(u"第%s周" %(num))
            weeklist.append('|'.join([str(week[0]),str(week[1])]))
    return daylist,weeklist

#GetThisweek
def Week_Get():
    d = datetime.date.today()
    dayto = datetime.date.today() - datetime.timedelta(days=((d.isoweekday())))
    sixdays = datetime.timedelta(days=6)
    dayfrom = dayto - sixdays
    return '|'.join([str(dayfrom), str(dayto)])
print Week_Get()

#获取周BUG总量，这个可以自己自定义
def Get_total_Week_Bug_closed(project_name,status_id='*',tracker_id=None,cf_26=None,cf_37=None):
    d,w=WeekMain()
    weeklist =[]
    xlist = []
    sum = 0
    #ThisWeekBug = Get_Week_Issues(project_name,status_id,tracker_id,Week_Get())
    for i in w:
        w = Get_Week_Issues_closed(project_name,status_id,tracker_id,i,cf_26,cf_37)
        weeklist.append(len(w))
    for x in weeklist:
        sum +=x
        xlist.append(sum)
    return xlist,weeklist

#ANCSWeekClosedBug,ThisWeekCloseBug=Get_total_Week_Bug_closed('nds-2-3-2','closed',1,cf_26='Japan')


def Get_total_Week_Bug(project_name,status_id='*',tracker_id=None,cf_26=None,cf_37=None):
    d,w=WeekMain()
    weeklist =[]
    xlist = []
    sum = 0
    #ThisWeekBug = Get_Week_Issues(project_name,status_id,tracker_id,Week_Get())
    for i in w:
        w = Get_Week_Issues(project_name,status_id,tracker_id,i,cf_26,cf_37)
        weeklist.append(len(w))
    for x in weeklist:
        sum +=x
        xlist.append(sum)
    return xlist,weeklist

def Get_NDSViewer_Week_Bug_closed(project_name,status_id='*',tracker_id=None):
    d,w=WeekMain()
    weeklist =[]
    xlist = []
    sum = 0
    #ThisWeekBug = Get_Week_Issues(project_name,status_id,tracker_id,Week_Get())
    for i in w:
        w = Get_Viewer_Week_Issues_closed(project_name,status_id,tracker_id,i)
        weeklist.append(len(w))
    for x in weeklist:
        sum +=x
        xlist.append(sum)
    return xlist,weeklist

def Get_NDSViewer_Week_Bug(project_name,status_id='*',tracker_id=None):
    d,w=WeekMain()
    weeklist =[]
    xlist = []
    sum = 0
    #ThisWeekBug = Get_Week_Issues(project_name,status_id,tracker_id,Week_Get())
    for i in w:
        w = Get_Viewer_Week_Issues(project_name,status_id,tracker_id,i)
        weeklist.append(len(w))
    for x in weeklist:
        sum +=x
        xlist.append(sum)
    return xlist,weeklist

#Get_Priority(project_name,status_id='*',tracker_id=None,priority_id=None,date=None):
def Get_Week_Priority_Bug(project_name,status_id='*',tracker_id=None,priority_id=None,cf_26=None,cf_37=None):
    d,w=WeekMain()
    weeklist =[]
    for i in w:
        w = Get_Priority(project_name,status_id,tracker_id,priority_id,i,cf_26,cf_37)
        weeklist.append(len(w))
    return weeklist



def Get_NDSViewer_Priority_Bug(project_name,status_id='*',tracker_id=None,priority_id=None):
    d,w=WeekMain()
    weeklist =[]
    for i in w:
        w = Get_Viewer_Priority(project_name,status_id,tracker_id,priority_id,i)
        weeklist.append(len(w))
    return weeklist
#ningja BUG    
def Ninja_Week_BUG(project_name,status_id='*',tracker_id=None):
    d,w=WeekMain()
    weeklist =[]
    xlist=[]
    sum = 0
    for i in w:
        w = Get_Ninja_Week_BUG(project_name,status_id,tracker_id,i)
        weeklist.append(len(w))
    for x in weeklist:
        sum +=x
        xlist.append(sum)
    return xlist
    

if __name__ == '__main__':
    #编写上周日期，用于测试报告name
    LastWeek = float(time.strftime("%W"+'.5'))-1
    _data =time.strftime("%Y_%m_%d", time.localtime())

    def ANCS():
        workbook = xlsxwriter.Workbook(r'ANCS Project Bug Statistical Analysis Report_CW%s_%s.xlsx'%(LastWeek,_data))
        #worksheet = workbook.add_worksheet()
        #ANCSsheet
        worksheet = workbook.add_worksheet('AUDICHN')#创建ANCS中国区域BUG
        format = workbook.add_format()
        format.set_border(1) 
        format_title=workbook.add_format({'bold': 1})    #定义format_title格式对象
        format_title.set_border(1)   #定义format_title对象单元格边框加粗(1像素)的格式
        format_title.set_bg_color('#A6FFFF')
        # ANCS'DATA========================================================================================
        headings = [u'周', u'日期','Immediate','Urgent','High','Normal','Low',u'本周发现',u'本周关闭',u'累计关闭',u'累计发现']
        CATEGORY_headings = [u'所属模块', u'累计关闭',u'累计BUG数']
        
        CATEGORY= ['3D','AXF','BMD','DTM','FTS','HAD','IPC','JV','Metadate','NAME','NAC','OTHER','POI','Routing','Shared','SLI','TMC','SPEECH']
        #Get china BUG on week
        ANCSDay,ANCSWeek=WeekMain()
        ANCSWeekClosedBug,ThisWeekCloseBug=Get_total_Week_Bug_closed('nds-2-3-2','closed',1,cf_26='China')
        ANCSWeekALLBug,ThisWeekALLBug=Get_total_Week_Bug('nds-2-3-2','*',1,cf_26='China')
        ANCSWeekLowBug=Get_Week_Priority_Bug('nds-2-3-2','*',1,1,cf_26='China')
        ANCSWeekNormalBug=Get_Week_Priority_Bug('nds-2-3-2','*',1,2,cf_26='China')
        ANCSWeekHighBug=Get_Week_Priority_Bug('nds-2-3-2','*',1,3,cf_26='China')
        ANCSWeekUrgentBug=Get_Week_Priority_Bug('nds-2-3-2','*',1,4,cf_26='China')
        ANCSWeekImmdDiateBug=Get_Week_Priority_Bug('nds-2-3-2','*',1,5,cf_26='China')
        ANCSChinaBug_ALL=Get_Ancs_Issues('nds-2-3-2','*',1,cf_26='China')
        ANCSChinaBug_CLOSE=Get_Ancs_Issues('nds-2-3-2','colsed',1,cf_26='China')
        #挂起BUG
        #ANCSChinaBug_20=Get_Ancs_Issues('nds-2-3-2',20,1,cf_26='China')
        ANCScategory3D=Get_Issues_category('nds-2-3-2','colsed',1,cf_26='China',category_id=40)
        ANCScategoryAXF=Get_Issues_category('nds-2-3-2','colsed',1,cf_26='China',category_id=116)
        ANCScategoryBMD=Get_Issues_category('nds-2-3-2','colsed',1,cf_26='China',category_id=5)
        ANCScategoryDTM=Get_Issues_category('nds-2-3-2','colsed',1,cf_26='China',category_id=257)
        ANCScategoryFTS=Get_Issues_category('nds-2-3-2','colsed',1,cf_26='China',category_id=83)
        ANCScategoryHAD=Get_Issues_category('nds-2-3-2','colsed',1,cf_26='China',category_id=260)
        ANCScategoryIPC=Get_Issues_category('nds-2-3-2','colsed',1,cf_26='China',category_id=7)
        ANCScategoryJV=Get_Issues_category('nds-2-3-2','colsed',1,cf_26='China',category_id=71)
        ANCScategoryMetadate=Get_Issues_category('nds-2-3-2','colsed',1,cf_26='China',category_id=69)
        ANCScategoryNAME=Get_Issues_category('nds-2-3-2','colsed',1,cf_26='China',category_id=3)
        ANCScategoryNAC=Get_Issues_category('nds-2-3-2','colsed',1,cf_26='China',category_id=10)
        ANCScategoryOTHER=Get_Issues_category('nds-2-3-2','colsed',1,cf_26='China',category_id=302)
        ANCScategoryPOI=Get_Issues_category('nds-2-3-2','colsed',1,cf_26='China',category_id=4)
        ANCScategoryRouting=Get_Issues_category('nds-2-3-2','colsed',1,cf_26='China',category_id=2)
        ANCScategoryShared=Get_Issues_category('nds-2-3-2','colsed',1,cf_26='China',category_id=236)
        ANCScategorySLI=Get_Issues_category('nds-2-3-2','colsed',1,cf_26='China',category_id=8)
        ANCScategoryTMC=Get_Issues_category('nds-2-3-2','colsed',1,cf_26='China',category_id=9)
        ANCScategorySPEECH=Get_Issues_category('nds-2-3-2','colsed',1,cf_26='China',category_id=183)
        #==============================================================================================
        ANCScategory3D_ALL=Get_Issues_category('nds-2-3-2','*',1,cf_26='China',category_id=40)
        ANCScategoryAXF_ALL=Get_Issues_category('nds-2-3-2','*',1,cf_26='China',category_id=116)
        ANCScategoryBMD_ALL=Get_Issues_category('nds-2-3-2','*',1,cf_26='China',category_id=5)
        ANCScategoryDTM_ALL=Get_Issues_category('nds-2-3-2','*',1,cf_26='China',category_id=257)
        ANCScategoryFTS_ALL=Get_Issues_category('nds-2-3-2','*',1,cf_26='China',category_id=83)
        ANCScategoryHAD_ALL=Get_Issues_category('nds-2-3-2','*',1,cf_26='China',category_id=260)
        ANCScategoryIPC_ALL=Get_Issues_category('nds-2-3-2','*',1,cf_26='China',category_id=7)
        ANCScategoryJV_ALL=Get_Issues_category('nds-2-3-2','*',1,cf_26='China',category_id=71)
        ANCScategoryMetadate_ALL=Get_Issues_category('nds-2-3-2','*',1,cf_26='China',category_id=69)
        ANCScategoryNAME_ALL=Get_Issues_category('nds-2-3-2','*',1,cf_26='China',category_id=3)
        ANCScategoryNAC_ALL=Get_Issues_category('nds-2-3-2','*',1,cf_26='China',category_id=10)
        ANCScategoryOTHER_ALL=Get_Issues_category('nds-2-3-2','*',1,cf_26='China',category_id=302)
        ANCScategoryPOI_ALL=Get_Issues_category('nds-2-3-2','*',1,cf_26='China',category_id=4)
        ANCScategoryRouting_ALL=Get_Issues_category('nds-2-3-2','*',1,cf_26='China',category_id=2)
        ANCScategoryShared_ALL=Get_Issues_category('nds-2-3-2','*',1,cf_26='China',category_id=236)
        ANCScategorySLI_ALL=Get_Issues_category('nds-2-3-2','*',1,cf_26='China',category_id=8)
        ANCScategoryTMC_ALL=Get_Issues_category('nds-2-3-2','*',1,cf_26='China',category_id=9)
        ANCScategorySPEECH_ALL=Get_Issues_category('nds-2-3-2','*',1,cf_26='China',category_id=183)
              
        data = [
        ANCSDay,ANCSWeek,ANCSWeekImmdDiateBug,ANCSWeekUrgentBug,ANCSWeekHighBug,ANCSWeekNormalBug,ANCSWeekLowBug,ThisWeekALLBug,ThisWeekCloseBug,ANCSWeekClosedBug,ANCSWeekALLBug
        
        ]
        category_data_closed=map(len,[
        ANCScategory3D,ANCScategoryAXF,ANCScategoryBMD,ANCScategoryDTM,ANCScategoryFTS,ANCScategoryHAD,ANCScategoryIPC,ANCScategoryJV,ANCScategoryMetadate,ANCScategoryNAME,ANCScategoryNAC,ANCScategoryOTHER,ANCScategoryPOI,ANCScategoryRouting,ANCScategoryShared,ANCScategorySLI,ANCScategoryTMC,ANCScategorySPEECH
        ])
        category_data_ALL=map(len,[
        ANCScategory3D_ALL,ANCScategoryAXF_ALL,ANCScategoryBMD_ALL,ANCScategoryDTM_ALL,ANCScategoryFTS_ALL,ANCScategoryHAD_ALL,ANCScategoryIPC_ALL,ANCScategoryJV_ALL,ANCScategoryMetadate_ALL,ANCScategoryNAME_ALL,ANCScategoryNAC_ALL,ANCScategoryOTHER_ALL,ANCScategoryPOI_ALL,ANCScategoryRouting_ALL,ANCScategoryShared_ALL,ANCScategorySLI_ALL,ANCScategoryTMC_ALL,ANCScategorySPEECH_ALL
        ])
        
        worksheet.write_row('A1', headings, format_title)
        worksheet.write_column('A2', data[0],format)
        worksheet.write_column('B2', data[1],format)
        worksheet.write_column('C2', data[2],format)
        worksheet.write_column('D2', data[3],format)
        worksheet.write_column('E2', data[4],format)
        worksheet.write_column('F2', data[5],format)
        worksheet.write_column('G2', data[6],format)
        worksheet.write_column('H2', data[7],format)
        worksheet.write_column('I2', data[8],format)
        worksheet.write_column('J2', data[9],format)
        worksheet.write_column('K2', data[10],format)
        worksheet.write_row('B%d'%(len(ANCSDay)+2),[u'合计'],format)
        #写入sum函数
        worksheet.write_formula('C%d'%(len(ANCSDay)+2), '=SUM(C2:C%d)'%(len(ANCSDay)+1),format)
        worksheet.write_formula('D%d'%(len(ANCSDay)+2), '=SUM(D2:D%d)'%(len(ANCSDay)+1),format)
        worksheet.write_formula('E%d'%(len(ANCSDay)+2), '=SUM(E2:E%d)'%(len(ANCSDay)+1),format)
        worksheet.write_formula('F%d'%(len(ANCSDay)+2), '=SUM(F2:F%d)'%(len(ANCSDay)+1),format)
        worksheet.write_formula('G%d'%(len(ANCSDay)+2), '=SUM(G2:G%d)'%(len(ANCSDay)+1),format)
        #插入所属类别
        worksheet.write_row('M1', CATEGORY_headings, format_title)
        for i in range(len(CATEGORY)):
            worksheet.write_column('M%d'%(i+2), [CATEGORY[i]], format)
        for i in range(len(category_data_closed)):
            worksheet.write_column('N%d'%(i+2), [category_data_closed[i]], format)
        for i in range(len(category_data_ALL)):
            worksheet.write_column('O%d'%(i+2), [category_data_ALL[i]], format)
        worksheet.write_row('M%d'%(len(category_data_ALL)+2),[u'未分类'],format)
        worksheet.write_row('M%d'%(len(category_data_ALL)+3),[u'合计'],format)
        worksheet.write_row('N%d'%(len(category_data_ALL)+3),[len(ANCSChinaBug_CLOSE)],format)
        worksheet.write_row('O%d'%(len(category_data_ALL)+3),[len(ANCSChinaBug_ALL)],format)

        worksheet.write_formula('N%d'%(len(category_data_ALL)+2), '=N%d-SUM(N2:N%d)'%(len(category_data_ALL)+3,len(category_data_ALL)+1),format)
        worksheet.write_formula('O%d'%(len(category_data_ALL)+2), '=O%d-SUM(O2:O%d)'%(len(category_data_ALL)+3,len(category_data_ALL)+1),format)
        #for i in xrange(0,len(category_data)):
            #worksheet.write_column('M%d'%(i+1),category_data[i],format)
        
        #######################################################################
        # Create an area chart.

        chart1 = workbook.add_chart({'type': 'line'})#线图
        chart2 = workbook.add_chart({'type': 'pie'})#圆饼图
        chart3 = workbook.add_chart({'type': 'column'})#柱状图
        # 累计发现
        chart1.add_series({
        'name':       '=AUDICHN!$K$1',
        'categories': '=AUDICHN!$A$2:$A$%d'%(len(ANCSDay)+1),
        'values':     '=AUDICHN!$K$2:$K$%d'%(len(ANCSDay)+1),
        'data_labels': {'value': True},
        'marker': {'type': 'diamond','size,': 4}
        })

        # 累计关闭.
        chart1.add_series({
        'name':       '=AUDICHN!$J$1',
        'categories': '=AUDICHN!$A$2:$A$%d'%(len(ANCSDay)+1),
        'values':     '=AUDICHN!$J$2:$J$%d'%(len(ANCSDay)+1),
        'data_labels': {'value': True},
        'marker': {'type': 'diamond','size,': 4}
        })

        chart2.add_series({
        'name':       'Pie sales data',
        'categories': '=AUDICHN!$C$1:$G$1',
        'values':     '=AUDICHN!$C$%d:$G$%d'%(len(ANCSDay)+2,len(ANCSDay)+2),
        'data_labels': {'value': True,'percentage': True}
        })

        chart3.add_series({
        'name':       '=AUDICHN!$N$1',
        'categories': '=AUDICHN!$M$2:$M$%d'%(len(category_data_ALL)+3),
        'values':     '=AUDICHN!$N$2:$N$%d'%(len(category_data_ALL)+3),
        'data_labels': {'value': True},
        'overlap':    50
        })

        chart3.add_series({
        'name':       '=AUDICHN!$O$1',
        'categories': '=AUDICHN!$M$2:$M$%d'%(len(category_data_ALL)+3),
        'values':     '=AUDICHN!$O$2:$O$%d'%(len(category_data_ALL)+3),
        'data_labels': {'value': True},
        'overlap':    50
        })



        # Add a chart title and some axis labels.
        chart1.set_title ({'name': u'AUDI_MIB2_Bug趋势'})
        chart1.set_x_axis({'name': 'the week'})
        chart1.set_y_axis({'name': 'Bug Number'})
        chart1.set_size({'width': 800, 'height': 348}) 
        chart2.set_size({'width': 344, 'height': 275}) 
        
        chart3.set_table()
        chart3.set_title ({'name': u'AUDI_MIB2_Bug所属模块分布图'})
        chart3.set_size({'width': 632, 'height': 374}) 

        # Set an Excel chart style.
        chart1.set_style(10)
        chart2.set_title ({'name': u'AUDI_MIB2_Bug严重程度'})
        chart3.set_style(10)
        # Insert the chart into the worksheet (with an offset).
        worksheet.insert_chart('A%d'%(len(ANCSDay)+5), chart1, {'x_offset': 25, 'y_offset': 10})
        worksheet.insert_chart('M%d'%(len(ANCSDay)+5), chart2, {'x_offset': 25, 'y_offset': 10})
        worksheet.insert_chart('S%d'%(len(ANCSDay)+5),chart3, {'x_offset': 25, 'y_offset': 10})

    #Japan################################################################
    ######################################################################
    ######################################################################
    ######################################################################
    ######################################################################
    ######################################################################
        worksheet = workbook.add_worksheet('AUDIPAN')#
        format = workbook.add_format()
        format.set_border(1) 
        format_title=workbook.add_format({'bold': 1})    #定义format_title格式对象
        format_title.set_border(1)   #定义format_title对象单元格边框加粗(1像素)的格式
        format_title.set_bg_color('#A6FFFF')
        # ANCS'DATA========================================================================================
        headings = [u'周', u'日期','Immediate','Urgent','High','Normal','Low',u'本周发现',u'本周关闭',u'累计关闭',u'累计发现']
        cf_26List_Title=[u'所属区域',u'累计挂起',u'累计关闭',u'累计发现']
        cf_26List=['Taiwan','Japan','Korea','German','Thailand']
        #Get china BUG on week
        ANCSDay,ANCSWeek=WeekMain()
        #ANCSWeekClosedBug,ThisWeekCloseBug=Get_total_Week_Bug_closed('nds-2-3-2','closed',1,cf_26='Japan')
        Japan,JapanThisWeekBug =  Get_total_Week_Bug_closed('nds-2-3-2','closed',1,cf_26='Japan')
        Taiwan,TaiwanThisWeekBug=  Get_total_Week_Bug_closed('nds-2-3-2','closed',1,cf_26='Taiwan')
        #print Japan,Taiwan
        Korea,KoreaThisWeekBug=  Get_total_Week_Bug_closed('nds-2-3-2','closed',1,cf_26='Korea')
        German,GermanThisWeekBug=  Get_total_Week_Bug_closed('nds-2-3-2','closed',1,cf_26='German')
        Thailand,ThailandThisWeekBug=  Get_total_Week_Bug_closed('nds-2-3-2','closed',1,cf_26='Thailand')
        ANCSWeekClosedBug =[Japan[i]+Taiwan[i]+Korea[i]+German[i]+Thailand[i] for i in range(len(Japan))]
        ThisWeekCloseBug = [JapanThisWeekBug[i]+TaiwanThisWeekBug[i]+KoreaThisWeekBug[i]+GermanThisWeekBug[i]+ThailandThisWeekBug[i] for i in range(len(JapanThisWeekBug))]
        #print ThisWeekCloseBug1
        #ANCSWeekALLBug,ThisWeekALLBug=Get_total_Week_Bug('nds-2-3-2','*',1,cf_26='Japan')
        JapanALL,JapanThisWeekALLBug =  Get_total_Week_Bug('nds-2-3-2','*',1,cf_26='Japan')
        TaiwanALL,TaiwanThisWeekALLBug=  Get_total_Week_Bug('nds-2-3-2','*',1,cf_26='Taiwan')
        #print Japan,Taiwan
        KoreaALL,KoreaThisWeekALLBug=  Get_total_Week_Bug('nds-2-3-2','*',1,cf_26='Korea')
        GermanALL,GermanThisWeekALLBug=  Get_total_Week_Bug('nds-2-3-2','*',1,cf_26='German')
        ThailandALL,ThailandThisWeekALLBug=  Get_total_Week_Bug('nds-2-3-2','*',1,cf_26='Thailand')
        ANCSWeekALLBug =[JapanALL[i]+TaiwanALL[i]+KoreaALL[i]+GermanALL[i]+ThailandALL[i] for i in range(len(Japan))]
        ThisWeekALLBug = [JapanThisWeekALLBug[i]+TaiwanThisWeekALLBug[i]+KoreaThisWeekALLBug[i]+GermanThisWeekALLBug[i]+ThailandThisWeekALLBug[i] for i in range(len(JapanThisWeekALLBug))]
        
        #ANCSWeekLowBug=Get_Week_Priority_Bug('nds-2-3-2','*',1,1,cf_26='Japan')
        JapanLow =  Get_Week_Priority_Bug('nds-2-3-2','*',1,1,cf_26='Japan')
        TaiwanLow=  Get_Week_Priority_Bug('nds-2-3-2','*',1,1,cf_26='Taiwan')
        KoreaLow =  Get_Week_Priority_Bug('nds-2-3-2','*',1,1,cf_26='Korea')
        GermanLow=  Get_Week_Priority_Bug('nds-2-3-2','*',1,1,cf_26='German')
        ThailandLow=  Get_Week_Priority_Bug('nds-2-3-2','*',1,1,cf_26='Thailand')
        ANCSWeekLowBug =[JapanLow[i]+TaiwanLow[i]+KoreaLow[i]+GermanLow[i]+ThailandLow[i] for i in range(len(JapanLow))]
        #ANCSWeekNormalBug=Get_Week_Priority_Bug('nds-2-3-2','*',1,2,cf_26='Japan')
        JapanNormal =  Get_Week_Priority_Bug('nds-2-3-2','*',1,2,cf_26='Japan')
        TaiwanNormal=  Get_Week_Priority_Bug('nds-2-3-2','*',1,2,cf_26='Taiwan')
        KoreaNormal =  Get_Week_Priority_Bug('nds-2-3-2','*',1,2,cf_26='Korea')
        GermanNormal=  Get_Week_Priority_Bug('nds-2-3-2','*',1,2,cf_26='German')
        ThailandNormal=  Get_Week_Priority_Bug('nds-2-3-2','*',1,2,cf_26='Thailand')
        ANCSWeekNormalBug =[JapanNormal[i]+TaiwanNormal[i]+KoreaNormal[i]+GermanNormal[i]+ThailandNormal[i] for i in range(len(JapanNormal))]
        #ANCSWeekHighBug=Get_Week_Priority_Bug('nds-2-3-2','*',1,3,cf_26='Japan')
        JapanHigh =  Get_Week_Priority_Bug('nds-2-3-2','*',1,3,cf_26='Japan')
        TaiwanHigh=  Get_Week_Priority_Bug('nds-2-3-2','*',1,3,cf_26='Taiwan')
        KoreaHigh =  Get_Week_Priority_Bug('nds-2-3-2','*',1,3,cf_26='Korea')
        GermanHigh=  Get_Week_Priority_Bug('nds-2-3-2','*',1,3,cf_26='German')
        ThailandHigh=  Get_Week_Priority_Bug('nds-2-3-2','*',1,3,cf_26='Thailand')
        ANCSWeekHighBug =[JapanHigh[i]+TaiwanHigh[i]+KoreaHigh[i]+GermanHigh[i]+ThailandHigh[i] for i in range(len(JapanHigh))]
        #ANCSWeekUrgentBug=Get_Week_Priority_Bug('nds-2-3-2','*',1,4,cf_26='Japan')
        JapanUrgent =  Get_Week_Priority_Bug('nds-2-3-2','*',1,4,cf_26='Japan')
        TaiwanUrgent=  Get_Week_Priority_Bug('nds-2-3-2','*',1,4,cf_26='Taiwan')
        KoreaUrgent =  Get_Week_Priority_Bug('nds-2-3-2','*',1,4,cf_26='Korea')
        GermanUrgent=  Get_Week_Priority_Bug('nds-2-3-2','*',1,4,cf_26='German')
        ThailandUrgent=  Get_Week_Priority_Bug('nds-2-3-2','*',1,4,cf_26='Thailand')
        ANCSWeekUrgentBug =[JapanUrgent[i]+TaiwanUrgent[i]+KoreaUrgent[i]+GermanUrgent[i]+ThailandUrgent[i] for i in range(len(JapanUrgent))]
        #ANCSWeekImmdDiateBug=Get_Week_Priority_Bug('nds-2-3-2','*',1,5,cf_26='Japan')
        JapanImmdDiate =  Get_Week_Priority_Bug('nds-2-3-2','*',1,5,cf_26='Japan')
        TaiwanImmdDiate=  Get_Week_Priority_Bug('nds-2-3-2','*',1,5,cf_26='Taiwan')
        KoreaImmdDiate =  Get_Week_Priority_Bug('nds-2-3-2','*',1,5,cf_26='Korea')
        GermanImmdDiate=  Get_Week_Priority_Bug('nds-2-3-2','*',1,5,cf_26='German')
        ThailandImmdDiate=  Get_Week_Priority_Bug('nds-2-3-2','*',1,5,cf_26='Thailand')
        ANCSWeekImmdDiateBug =[JapanImmdDiate[i]+TaiwanImmdDiate[i]+KoreaImmdDiate[i]+GermanImmdDiate[i]+ThailandImmdDiate[i] for i in range(len(JapanImmdDiate))]

        #ANCSJapanBug_ALL=Get_Ancs_Issues('nds-2-3-2','*',1,cf_26='Japan')
        JapanBug_ALL =  Get_Ancs_Issues('nds-2-3-2','*',1,cf_26='Japan')
        TaiwanBug_ALL=  Get_Ancs_Issues('nds-2-3-2','*',1,cf_26='Taiwan')
        KoreaBug_ALL =  Get_Ancs_Issues('nds-2-3-2','*',1,cf_26='Korea')
        GermanBug_ALL=  Get_Ancs_Issues('nds-2-3-2','*',1,cf_26='German')
        ThailandBug_ALL=  Get_Ancs_Issues('nds-2-3-2','*',1,cf_26='Thailand')
        ANCSJapanBug_ALL =len(JapanBug_ALL)+len(TaiwanBug_ALL)+len(KoreaBug_ALL)+len(GermanBug_ALL)+len(ThailandBug_ALL)
        #ANCSJapanBug_CLOSE=Get_Ancs_Issues('nds-2-3-2','colsed',1,cf_26='Japan')
        JapanBug_CLOSE =  Get_Ancs_Issues('nds-2-3-2','closed',1,cf_26='Japan')
        TaiwanBug_CLOSE=  Get_Ancs_Issues('nds-2-3-2','closed',1,cf_26='Taiwan')
        KoreaBug_CLOSE =  Get_Ancs_Issues('nds-2-3-2','closed',1,cf_26='Korea')
        GermanBug_CLOSE=  Get_Ancs_Issues('nds-2-3-2','closed',1,cf_26='German')
        ThailandBug_CLOSE=  Get_Ancs_Issues('nds-2-3-2','closed',1,cf_26='Thailand')
        ANCSJapanBug_CLOSE =len(JapanBug_CLOSE)+len(TaiwanBug_CLOSE)+len(KoreaBug_CLOSE)+len(GermanBug_CLOSE)+len(ThailandBug_CLOSE)

        Abroad_ALL_List=[]
        Abroad_Close_List=[]
        Abroad_20_List=[]
        for i in cf_26List:
            Abroad_ALL_List.append(len(Get_Ancs_Issues('nds-2-3-2','*',1,cf_26='%s'%i)))
            Abroad_Close_List.append(len(Get_Ancs_Issues('nds-2-3-2','closed',1,cf_26='%s'%i)))            
            Abroad_20_List.append(len(Get_Ancs_Issues('nds-2-3-2','20',1,cf_26='%s'%i)))
        #ANCScategory3D=Get_Issues_category('nds-2-3-2','colsed',1,cf_26='Japan',category_id=40)
        def add(x, y):
            return x + y
        ANCScategory3D_list=[]
        ANCScategoryAXF_list=[]
        ANCScategoryBMD_list=[]
        ANCScategoryDTM_list=[]
        ANCScategoryFTS_list=[]
        ANCScategoryHAD_list=[]
        ANCScategoryIPC_list=[]
        ANCScategoryJV_list=[]
        ANCScategoryMetadate_list=[]
        ANCScategoryNAME_list=[]
        ANCScategoryNAC_list=[]
        ANCScategoryOTHER_list=[]
        ANCScategoryPOI_list=[]
        ANCScategoryRouting_list=[]
        ANCScategoryShared_list=[]
        ANCScategorySLI_list=[]
        ANCScategoryTMC_list=[]
        ANCScategoryTMC_list=[]
        ANCScategorySPEECH_list=[]


        for i in cf_26List:
            ANCScategory3D_list.append(len(Get_Issues_category('nds-2-3-2','closed',1,cf_26='%s'%i,category_id=40)))
            ANCScategoryAXF_list.append(len(Get_Issues_category('nds-2-3-2','closed',1,cf_26='%s'%i,category_id=116)))
            ANCScategoryBMD_list.append(len(Get_Issues_category('nds-2-3-2','closed',1,cf_26='%s'%i,category_id=5)))
            ANCScategoryDTM_list.append(len(Get_Issues_category('nds-2-3-2','closed',1,cf_26='%s'%i,category_id=257)))
            ANCScategoryFTS_list.append(len(Get_Issues_category('nds-2-3-2','closed',1,cf_26='%s'%i,category_id=83)))
            ANCScategoryHAD_list.append(len(Get_Issues_category('nds-2-3-2','closed',1,cf_26='%s'%i,category_id=260)))
            ANCScategoryIPC_list.append(len(Get_Issues_category('nds-2-3-2','closed',1,cf_26='%s'%i,category_id=7)))
            ANCScategoryJV_list.append(len(Get_Issues_category('nds-2-3-2','closed',1,cf_26='%s'%i,category_id=7)))
            ANCScategoryMetadate_list.append(len(Get_Issues_category('nds-2-3-2','closed',1,cf_26='%s'%i,category_id=7)))
            ANCScategoryNAME_list.append(len(Get_Issues_category('nds-2-3-2','closed',1,cf_26='%s'%i,category_id=3)))
            ANCScategoryNAC_list.append(len(Get_Issues_category('nds-2-3-2','closed',1,cf_26='%s'%i,category_id=10)))
            ANCScategoryOTHER_list.append(len(Get_Issues_category('nds-2-3-2','closed',1,cf_26='%s'%i,category_id=10)))
            ANCScategoryPOI_list.append(len(Get_Issues_category('nds-2-3-2','closed',1,cf_26='%s'%i,category_id=4)))
            ANCScategoryRouting_list.append(len(Get_Issues_category('nds-2-3-2','closed',1,cf_26='%s'%i,category_id=2)))
            ANCScategoryShared_list.append(len(Get_Issues_category('nds-2-3-2','closed',1,cf_26='%s'%i,category_id=236)))
            ANCScategorySLI_list.append(len(Get_Issues_category('nds-2-3-2','closed',1,cf_26='%s'%i,category_id=8)))
            ANCScategoryTMC_list.append(len(Get_Issues_category('nds-2-3-2','closed',1,cf_26='%s'%i,category_id=9)))
            ANCScategorySPEECH_list.append(len(Get_Issues_category('nds-2-3-2','closed',1,cf_26='Taiwan',category_id=183)))
        ANCScategory3D =reduce(add, ANCScategory3D_list)
        ANCScategoryAXF =reduce(add,ANCScategoryAXF_list)
        ANCScategoryBMD =reduce(add,ANCScategoryBMD_list)  
        ANCScategoryDTM =reduce(add,ANCScategoryDTM_list)
        ANCScategoryFTS =reduce(add,ANCScategoryFTS_list)   
        ANCScategoryHAD =reduce(add,ANCScategoryHAD_list)
        ANCScategoryIPC =reduce(add,ANCScategoryIPC_list) 
        ANCScategoryJV =reduce(add,ANCScategoryJV_list)
        ANCScategoryMetadate =reduce(add,ANCScategoryMetadate_list)
        ANCScategoryNAME =reduce(add,ANCScategoryNAME_list)
        ANCScategoryNAC = reduce(add,ANCScategoryNAC_list)     
        ANCScategoryOTHER =reduce(add,ANCScategoryNAC_list)
        ANCScategoryPOI = reduce(add,ANCScategoryPOI_list)
        ANCScategoryRouting =reduce(add,ANCScategoryRouting_list)
        ANCScategoryShared =reduce(add,ANCScategoryShared_list)
        ANCScategorySLI = reduce(add,ANCScategorySLI_list)
        ANCScategoryTMC = reduce(add,ANCScategoryTMC_list)
        ANCScategorySPEECH =reduce(add,ANCScategorySPEECH_list)
        #==============================================================================================ALL
        ANCScategory3D_list_ALL=[]
        ANCScategoryAXF_list_ALL=[]
        ANCScategoryBMD_list_ALL=[]
        ANCScategoryDTM_list_ALL=[]
        ANCScategoryFTS_list_ALL=[]
        ANCScategoryHAD_list_ALL=[]
        ANCScategoryIPC_list_ALL=[]
        ANCScategoryJV_list_ALL=[]
        ANCScategoryMetadate_list_ALL=[]
        ANCScategoryNAME_list_ALL=[]
        ANCScategoryNAC_list_ALL=[]
        ANCScategoryOTHER_list_ALL=[]
        ANCScategoryPOI_list_ALL=[]
        ANCScategoryRouting_list_ALL=[]
        ANCScategoryShared_list_ALL=[]
        ANCScategorySLI_list_ALL=[]
        ANCScategoryTMC_list_ALL=[]
        ANCScategoryTMC_list_ALL=[]
        ANCScategorySPEECH_list_ALL=[]

        for i in cf_26List:
            ANCScategory3D_list_ALL.append(len(Get_Issues_category('nds-2-3-2','*',1,cf_26='%s'%i,category_id=40)))
            ANCScategoryAXF_list_ALL.append(len(Get_Issues_category('nds-2-3-2','*',1,cf_26='%s'%i,category_id=116)))
            ANCScategoryBMD_list_ALL.append(len(Get_Issues_category('nds-2-3-2','*',1,cf_26='%s'%i,category_id=5)))
            ANCScategoryDTM_list_ALL.append(len(Get_Issues_category('nds-2-3-2','*',1,cf_26='%s'%i,category_id=257)))
            ANCScategoryFTS_list_ALL.append(len(Get_Issues_category('nds-2-3-2','*',1,cf_26='%s'%i,category_id=83)))
            ANCScategoryHAD_list_ALL.append(len(Get_Issues_category('nds-2-3-2','*',1,cf_26='%s'%i,category_id=260)))
            ANCScategoryIPC_list_ALL.append(len(Get_Issues_category('nds-2-3-2','*',1,cf_26='%s'%i,category_id=7)))
            ANCScategoryJV_list_ALL.append(len(Get_Issues_category('nds-2-3-2','*',1,cf_26='%s'%i,category_id=7)))
            ANCScategoryMetadate_list_ALL.append(len(Get_Issues_category('nds-2-3-2','*',1,cf_26='%s'%i,category_id=7)))
            ANCScategoryNAME_list_ALL.append(len(Get_Issues_category('nds-2-3-2','*',1,cf_26='%s'%i,category_id=3)))
            ANCScategoryNAC_list_ALL.append(len(Get_Issues_category('nds-2-3-2','*',1,cf_26='%s'%i,category_id=10)))
            ANCScategoryOTHER_list_ALL.append(len(Get_Issues_category('nds-2-3-2','*',1,cf_26='%s'%i,category_id=10)))
            ANCScategoryPOI_list_ALL.append(len(Get_Issues_category('nds-2-3-2','*',1,cf_26='%s'%i,category_id=4)))
            ANCScategoryRouting_list_ALL.append(len(Get_Issues_category('nds-2-3-2','*',1,cf_26='%s'%i,category_id=2)))
            ANCScategoryShared_list_ALL.append(len(Get_Issues_category('nds-2-3-2','*',1,cf_26='%s'%i,category_id=236)))
            ANCScategorySLI_list_ALL.append(len(Get_Issues_category('nds-2-3-2','*',1,cf_26='%s'%i,category_id=8)))
            ANCScategoryTMC_list_ALL.append(len(Get_Issues_category('nds-2-3-2','*',1,cf_26='%s'%i,category_id=9)))
            ANCScategorySPEECH_list_ALL.append(len(Get_Issues_category('nds-2-3-2','*',1,cf_26='Taiwan',category_id=183)))
        ANCScategory3D_ALL =reduce(add, ANCScategory3D_list_ALL)
        ANCScategoryAXF_ALL =reduce(add,ANCScategoryAXF_list_ALL)
        ANCScategoryBMD_ALL =reduce(add,ANCScategoryBMD_list_ALL)  
        ANCScategoryDTM_ALL =reduce(add,ANCScategoryDTM_list_ALL)
        ANCScategoryFTS_ALL =reduce(add,ANCScategoryFTS_list_ALL)   
        ANCScategoryHAD_ALL =reduce(add,ANCScategoryHAD_list_ALL)
        ANCScategoryIPC_ALL =reduce(add,ANCScategoryIPC_list_ALL) 
        ANCScategoryJV_ALL =reduce(add,ANCScategoryJV_list_ALL)
        ANCScategoryMetadate_ALL =reduce(add,ANCScategoryMetadate_list_ALL)
        ANCScategoryNAME_ALL =reduce(add,ANCScategoryNAME_list_ALL)
        ANCScategoryNAC_ALL = reduce(add,ANCScategoryNAC_list_ALL)     
        ANCScategoryOTHER_ALL =reduce(add,ANCScategoryNAC_list_ALL)
        ANCScategoryPOI_ALL = reduce(add,ANCScategoryPOI_list_ALL)
        ANCScategoryRouting_ALL =reduce(add,ANCScategoryRouting_list_ALL)
        ANCScategoryShared_ALL =reduce(add,ANCScategoryShared_list_ALL)
        ANCScategorySLI_ALL = reduce(add,ANCScategorySLI_list_ALL)
        ANCScategoryTMC_ALL = reduce(add,ANCScategoryTMC_list_ALL)
        ANCScategorySPEECH_ALL =reduce(add,ANCScategorySPEECH_list_ALL)
              
        data = [
        ANCSDay,ANCSWeek,ANCSWeekImmdDiateBug,ANCSWeekUrgentBug,ANCSWeekHighBug,ANCSWeekNormalBug,ANCSWeekLowBug,ThisWeekALLBug,ThisWeekCloseBug,ANCSWeekClosedBug,ANCSWeekALLBug
        
        ]
        category_data_closed=[
        ANCScategory3D,ANCScategoryAXF,ANCScategoryBMD,ANCScategoryDTM,ANCScategoryFTS,ANCScategoryHAD,ANCScategoryIPC,ANCScategoryJV,ANCScategoryMetadate,ANCScategoryNAME,ANCScategoryNAC,ANCScategoryOTHER,ANCScategoryPOI,ANCScategoryRouting,ANCScategoryShared,ANCScategorySLI,ANCScategoryTMC,ANCScategorySPEECH
        ]
        category_data_ALL=[
        ANCScategory3D_ALL,ANCScategoryAXF_ALL,ANCScategoryBMD_ALL,ANCScategoryDTM_ALL,ANCScategoryFTS_ALL,ANCScategoryHAD_ALL,ANCScategoryIPC_ALL,ANCScategoryJV_ALL,ANCScategoryMetadate_ALL,ANCScategoryNAME_ALL,ANCScategoryNAC_ALL,ANCScategoryOTHER_ALL,ANCScategoryPOI_ALL,ANCScategoryRouting_ALL,ANCScategoryShared_ALL,ANCScategorySLI_ALL,ANCScategoryTMC_ALL,ANCScategorySPEECH_ALL
        ]


        worksheet.write_row('A1', headings, format_title)
        worksheet.write_column('A2', data[0],format)
        worksheet.write_column('B2', data[1],format)
        worksheet.write_column('C2', data[2],format)
        worksheet.write_column('D2', data[3],format)
        worksheet.write_column('E2', data[4],format)
        worksheet.write_column('F2', data[5],format)
        worksheet.write_column('G2', data[6],format)
        worksheet.write_column('H2', data[7],format)
        worksheet.write_column('I2', data[8],format)
        worksheet.write_column('J2', data[9],format)
        worksheet.write_column('K2', data[10],format)
        worksheet.write_row('B%d'%(len(ANCSDay)+2),[u'合计'],format)
        worksheet.write_formula('C%d'%(len(ANCSDay)+2), '=SUM(C2:C%d)'%(len(ANCSDay)+1),format)
        worksheet.write_formula('D%d'%(len(ANCSDay)+2), '=SUM(D2:D%d)'%(len(ANCSDay)+1),format)
        worksheet.write_formula('E%d'%(len(ANCSDay)+2), '=SUM(E2:E%d)'%(len(ANCSDay)+1),format)
        worksheet.write_formula('F%d'%(len(ANCSDay)+2), '=SUM(F2:F%d)'%(len(ANCSDay)+1),format)
        worksheet.write_formula('G%d'%(len(ANCSDay)+2), '=SUM(G2:G%d)'%(len(ANCSDay)+1),format)

        #插入所属类别
        worksheet.write_row('M1', CATEGORY_headings, format_title)
        for i in range(len(CATEGORY)):
            worksheet.write_column('M%d'%(i+2), [CATEGORY[i]], format)
        for i in range(len(category_data_closed)):
            worksheet.write_column('N%d'%(i+2), [category_data_closed[i]], format)
        for i in range(len(category_data_ALL)):
            worksheet.write_column('O%d'%(i+2), [category_data_ALL[i]], format)
        worksheet.write_row('M%d'%(len(category_data_ALL)+2),[u'未分类'],format)
        worksheet.write_row('M%d'%(len(category_data_ALL)+3),[u'合计'],format)
        worksheet.write_row('N%d'%(len(category_data_ALL)+3),[ANCSJapanBug_CLOSE],format)
        worksheet.write_row('O%d'%(len(category_data_ALL)+3),[ANCSJapanBug_ALL],format)

        worksheet.write_formula('N%d'%(len(category_data_ALL)+2), '=N%d-SUM(N2:N%d)'%(len(category_data_ALL)+3,len(category_data_ALL)+1),format)
        worksheet.write_formula('O%d'%(len(category_data_ALL)+2), '=O%d-SUM(O2:O%d)'%(len(category_data_ALL)+3,len(category_data_ALL)+1),format)
        
        worksheet.write_row('Q1', cf_26List_Title, format_title)
        worksheet.write_column('Q2', cf_26List,format)
        worksheet.write_column('Q%d'%(len(cf_26List)+1),[u'合计'],format)
        worksheet.write_column('R2',Abroad_20_List,format)
        worksheet.write_column('S2',Abroad_Close_List,format)
        worksheet.write_column('T2',Abroad_ALL_List,format)
        worksheet.write_formula('R%d'%(len(cf_26List)+1), '=SUM(R2:R%d)'%(len(cf_26List)),format)
        worksheet.write_formula('S%d'%(len(cf_26List)+1), '=SUM(S2:S%d)'%(len(cf_26List)),format)
        worksheet.write_formula('T%d'%(len(cf_26List)+1), '=SUM(T2:T%d)'%(len(cf_26List)),format)

        #######################################################################
        # Create an area chart.

        chart1 = workbook.add_chart({'type': 'line'})#线图
        chart2 = workbook.add_chart({'type': 'pie'})#圆饼图
        chart3 = workbook.add_chart({'type': 'column'})#柱状图
        # 累计发现
        chart1.add_series({
        'name':       '=AUDIPAN!$K$1',
        'categories': '=AUDIPAN!$A$2:$A$%d'%(len(ANCSDay)+1),
        'values':     '=AUDIPAN!$K$2:$K$%d'%(len(ANCSDay)+1),
        'data_labels': {'value': True},
        'marker': {'type': 'diamond','size,': 4}

        })

        # 累计关闭.
        chart1.add_series({
        'name':       '=AUDIPAN!$J$1',
        'categories': '=AUDIPAN!$A$2:$A$%d'%(len(ANCSDay)+1),
        'values':     '=AUDIPAN!$J$2:$J$%d'%(len(ANCSDay)+1),
        'data_labels': {'value': True},
        'marker': {'type': 'diamond','size,': 4}
        })

        chart2.add_series({
        'name':       'Pie sales data',
        'categories': '=AUDIPAN!$C$1:$G$1',
        'values':     '=AUDIPAN!$C$%d:$G$%d'%(len(ANCSDay)+2,len(ANCSDay)+2),
        'data_labels': {'value': True,'percentage': True}
        })

        chart3.add_series({
        'name':       '=AUDIPAN!$N$1',
        'categories': '=AUDIPAN!$M$2:$M$%d'%(len(category_data_ALL)+3),
        'values':     '=AUDIPAN!$N$2:$N$%d'%(len(category_data_ALL)+3),
        'data_labels': {'value': True},
        'overlap':    50
        })

        chart3.add_series({
        'name':       '=AUDIPAN!$O$1',
        'categories': '=AUDIPAN!$M$2:$M$%d'%(len(category_data_ALL)+3),
        'values':     '=AUDIPAN!$O$2:$O$%d'%(len(category_data_ALL)+3),
        'data_labels': {'value': True},
        'overlap':    50
        })



        # Add a chart title and some axis labels.
        chart1.set_title ({'name': u'ANCS_PAN_Bug趋势'})
        chart1.set_x_axis({'name': 'the week'})
        chart1.set_y_axis({'name': 'Bug Number'})
        chart1.set_size({'width': 800, 'height': 370}) 
        #chart3.set_y_axis({'log_base': 10})
        chart3.set_table()
        chart2.set_size({'width': 344, 'height': 275}) 
        chart3.set_title ({'name': u'AUDI_PAN_Bug所属模块分布图'})
        chart3.set_size({'width': 550, 'height': 364}) 

        # Set an Excel chart style.
        chart1.set_style(10)
        chart2.set_title ({'name': u'AUDI_PAN_Bug严重程度'})
        chart3.set_style(10)
        # Insert the chart into the worksheet (with an offset).
        worksheet.insert_chart('A%d'%(len(ANCSDay)+5), chart1, {'x_offset': 25, 'y_offset': 10})
        worksheet.insert_chart('M%d'%(len(ANCSDay)+5), chart2, {'x_offset': 25, 'y_offset': 10})
        worksheet.insert_chart('S%d'%(len(ANCSDay)+5),chart3, {'x_offset': 25, 'y_offset': 10})

        
    ###########################################################################################
    ###########################################################################################
    #####################################NinjaBUG##############################################
    ###########################################################################################
    ###########################################################################################
    ###########################################################################################

    ###########################################################################################
    ###########################################################################################
    ###########################################################################################
    ########################################NDSViewer##########################################
    ###########################################################################################
    ###########################################################################################
    ###########################################################################################
        worksheet = workbook.add_worksheet('NDSViewer')#创建ANCS中国区域BUG
        format = workbook.add_format()
        format.set_border(1) 
        format_title=workbook.add_format({'bold': 1})    #定义format_title格式对象
        format_title.set_border(1)   #定义format_title对象单元格边框加粗(1像素)的格式
        format_title.set_bg_color('#A6FFFF')
        # ANCS'DATA========================================================================================
        headings = [u'周', u'日期','Immediate','Urgent','High','Normal','Low',u'本周发现',u'本周关闭',u'累计关闭',u'累计发现']
        #Get china BUG on week
        ANCSDay,ANCSWeek=WeekMain()
        ANCSWeekClosedBug,ThisWeekCloseBug=Get_NDSViewer_Week_Bug_closed('an_nds-viewer','closed',1)
        ANCSWeekALLBug,ThisWeekALLBug=Get_NDSViewer_Week_Bug('an_nds-viewer','*',1)
        ANCSWeekLowBug=Get_NDSViewer_Priority_Bug('an_nds-viewer','*',1,1)
        ANCSWeekNormalBug=Get_NDSViewer_Priority_Bug('an_nds-viewer','*',1,2)
        ANCSWeekHighBug=Get_NDSViewer_Priority_Bug('an_nds-viewer','*',1,3)
        ANCSWeekUrgentBug=Get_NDSViewer_Priority_Bug('an_nds-viewer','*',1,4)
        ANCSWeekImmdDiateBug=Get_NDSViewer_Priority_Bug('an_nds-viewer','*',1,5)
        data = [
        ANCSDay,ANCSWeek,ANCSWeekImmdDiateBug,ANCSWeekUrgentBug,ANCSWeekHighBug,ANCSWeekNormalBug,ANCSWeekLowBug,ThisWeekALLBug,ThisWeekCloseBug,ANCSWeekClosedBug,ANCSWeekALLBug
        ]
         
        worksheet.write_row('A1', headings, format_title)
        worksheet.write_column('A2', data[0],format)
        worksheet.write_column('B2', data[1],format)
        worksheet.write_column('C2', data[2],format)
        worksheet.write_column('D2', data[3],format)
        worksheet.write_column('E2', data[4],format)
        worksheet.write_column('F2', data[5],format)
        worksheet.write_column('G2', data[6],format)
        worksheet.write_column('H2', data[7],format)
        worksheet.write_column('I2', data[8],format)
        worksheet.write_column('J2', data[9],format)
        worksheet.write_column('K2', data[10],format)
        worksheet.write_row('B%d'%(len(ANCSDay)+2),[u'合计'],format)
        #写入sum函数
        worksheet.write_formula('C%d'%(len(ANCSDay)+2), '=SUM(C2:C%d)'%(len(ANCSDay)+1),format)
        worksheet.write_formula('D%d'%(len(ANCSDay)+2), '=SUM(D2:D%d)'%(len(ANCSDay)+1),format)
        worksheet.write_formula('E%d'%(len(ANCSDay)+2), '=SUM(E2:E%d)'%(len(ANCSDay)+1),format)
        worksheet.write_formula('F%d'%(len(ANCSDay)+2), '=SUM(F2:F%d)'%(len(ANCSDay)+1),format)
        worksheet.write_formula('G%d'%(len(ANCSDay)+2), '=SUM(G2:G%d)'%(len(ANCSDay)+1),format)
        #绘图=========================================================================
        chart1 = workbook.add_chart({'type': 'line'})#线图
        chart2 = workbook.add_chart({'type': 'pie'})#圆饼图
        # 累计发现
        chart1.add_series({
        'name':       '=NDSViewer!$K$1',
        'categories': '=NDSViewer!$A$2:$A$%d'%(len(ANCSDay)+1),
        'values':     '=NDSViewer!$K$2:$K$%d'%(len(ANCSDay)+1),
        'data_labels': {'value': True},
        'marker': {'type': 'diamond'}
        })

        # 累计关闭.
        chart1.add_series({
        'name':       '=NDSViewer!$J$1',
        'categories': '=NDSViewer!$A$2:$A$%d'%(len(ANCSDay)+1),
        'values':     '=NDSViewer!$J$2:$J$%d'%(len(ANCSDay)+1),
        'data_labels': {'value': True},
        'marker': {'type': 'diamond'}
        })

        chart2.add_series({
        'name':       'Pie sales data',
        'categories': '=NDSViewer!$C$1:$G$1',
        'values':     '=NDSViewer!$C$%d:$G$%d'%(len(ANCSDay)+2,len(ANCSDay)+2),
        'data_labels': {'value': True,'percentage': True}
        })




        # Add a chart title and some axis labels.
        chart1.set_title ({'name': u'AUDI_MIB2_Bug趋势'})
        chart1.set_x_axis({'name': 'the week'})
        chart1.set_y_axis({'name': 'Bug Number'})
        chart1.set_size({'width': 777, 'height': 390}) 
        chart2.set_size({'width': 344, 'height': 275}) 


        # Set an Excel chart style.
        chart1.set_style(10)
        chart2.set_title ({'name': u'AUDI_MIB2_Bug严重程度'})
        
        # Insert the chart into the worksheet (with an offset).
        worksheet.insert_chart('A%d'%(len(ANCSDay)+5), chart1, {'x_offset': 25, 'y_offset': 10})
        worksheet.insert_chart('M%d'%(len(ANCSDay)+5), chart2, {'x_offset': 25, 'y_offset': 10})
        workbook.close()

    def Ninja():
        workbook = xlsxwriter.Workbook(r'Ninja Project Bug Statistical Analysis Report_CW%s_%s.xlsx'%(LastWeek,_data))
        #worksheet = workbook.add_worksheet()
        #ANCSsheet
        worksheet = workbook.add_worksheet('NinjaBUG')#创建ANCS中国区域BUG
        format = workbook.add_format()
        format.set_border(1) 
        format_title=workbook.add_format({'bold': 1})    #定义format_title格式对象
        format_title.set_border(1)   #定义format_title对象单元格边框加粗(1像素)的格式
        format_title.set_bg_color('#A6FFFF')
        # ANCS'DATA========================================================================================
        headings = [u'优先级&模块', u'Database','Guidance','Location','Map','Performance','Search',u'Route Calculation',u'Traffic',u'User data',u'合计']
        headings_Priority=['Low','Normal','High','Urgent','Immediate']
        Database_Priority = []
        Database_Priority_open = []
        Guidance_Priority=[]
        Guidance_Priority_open=[]
        Location_Priority=[]
        Location_Priority_open=[]
        Map_Priority=[]
        Map_Priority_open=[]
        Performance_Priority=[]
        Performance_Priority_open=[]
        Search_Priority=[]
        Search_Priority_open=[]
        RouteCalculation_Priority=[]
        RouteCalculation_Priority_open=[]
        Traffic_Priority_open=[]
        Traffic_Priority=[]
        Userdata_Priority=[]
        Userdata_Priority_open=[]
        #循环创建不同分类不同严重程度问题
        for i in range(1,6):
            NinjacategoryDatabase=Get_Priority_Category('redmine-test','*',11,i,category_id=117)
            Database_Priority.append(len(NinjacategoryDatabase)) 
            NinjacategoryDatabase_open=Get_Priority_Category('redmine-test','o',11,i,category_id=117)
            Database_Priority_open.append(len(NinjacategoryDatabase_open))            
            NinjacategoryGuidance=Get_Priority_Category('redmine-test','*',11,i,category_id=119)
            Guidance_Priority.append(len(NinjacategoryGuidance))            
            NinjacategoryGuidance_open=Get_Priority_Category('redmine-test','o',11,i,category_id=119)
            Guidance_Priority_open.append(len(NinjacategoryGuidance_open))            
            NinjacategoryLocation=Get_Priority_Category('redmine-test','*',11,i,category_id=120)
            Location_Priority.append(len(NinjacategoryLocation))            
            NinjacategoryLocation_open=Get_Priority_Category('redmine-test','o',11,i,category_id=120)
            Location_Priority_open.append(len(NinjacategoryLocation_open))            
            NinjacategoryMap=Get_Priority_Category('redmine-test','*',11,i,category_id=121)
            Map_Priority.append(len(NinjacategoryMap))            
            NinjacategoryMap_open=Get_Priority_Category('redmine-test','o',11,i,category_id=121)
            Map_Priority_open.append(len(NinjacategoryMap_open))            
            NinjacategoryPerformance=Get_Priority_Category('redmine-test','*',11,i,category_id=123)
            Performance_Priority.append(len(NinjacategoryPerformance))             
            NinjacategoryPerformance_open=Get_Priority_Category('redmine-test','o',11,i,category_id=123)
            Performance_Priority_open.append(len(NinjacategoryPerformance_open))            
            NinjacategorySearch=Get_Priority_Category('redmine-test','*',11,i,category_id=122)
            Search_Priority.append(len(NinjacategorySearch))            
            NinjacategorySearch_open=Get_Priority_Category('redmine-test','o',11,i,category_id=122)
            Search_Priority_open.append(len(NinjacategorySearch_open))            
            NinjacategoryRouteCalculation=Get_Priority_Category('redmine-test','*',11,i,category_id=126)
            RouteCalculation_Priority.append(len(NinjacategoryRouteCalculation))            
            NinjacategoryRouteCalculation_open=Get_Priority_Category('redmine-test','o',11,i,category_id=126)
            RouteCalculation_Priority_open.append(len(NinjacategoryRouteCalculation_open))            
            NinjacategoryTraffic=Get_Priority_Category('redmine-test','*',11,i,category_id=127)
            Traffic_Priority.append(len(NinjacategoryTraffic))              
            NinjacategoryTraffic_open=Get_Priority_Category('redmine-test','o',11,i,category_id=127)
            Traffic_Priority_open.append(len(NinjacategoryTraffic_open))            
            NinjacategoryUserdata=Get_Priority_Category('redmine-test','*',11,i,category_id=129)
            Userdata_Priority.append(len(NinjacategoryUserdata))            
            NinjacategoryUserdata_open=Get_Priority_Category('redmine-test','o',11,i,category_id=129)
            Userdata_Priority_open.append(len(NinjacategoryUserdata_open))
        #往excel中插入数据插入整体情况-pie
        worksheet.write_row('A1', headings, format_title)
        worksheet.write_column('A2', headings_Priority,format)
        worksheet.write_column('B2', Database_Priority,format)
        worksheet.write_column('C2', Guidance_Priority,format)
        worksheet.write_column('D2', Location_Priority,format)
        worksheet.write_column('E2', Map_Priority,format)
        worksheet.write_column('F2', Performance_Priority,format)
        worksheet.write_column('G2', Search_Priority,format)
        worksheet.write_column('H2', RouteCalculation_Priority,format)
        worksheet.write_column('I2', Traffic_Priority,format)
        worksheet.write_column('J2', Userdata_Priority,format)
        for i in range(0,len(headings_Priority)):
            worksheet.write_formula('K%d'%(i+2), '=SUM(A%d:J%d)'%(i+2,i+2),format)#计算总计
        #往excel中插入数据插入当前情况-pie
        worksheet.write_row('A40', headings, format_title)
        worksheet.write_column('A41', headings_Priority,format)
        worksheet.write_column('B41', Database_Priority_open,format)
        worksheet.write_column('C41', Guidance_Priority_open,format)
        worksheet.write_column('D41', Location_Priority_open,format)
        worksheet.write_column('E41', Map_Priority_open,format)
        worksheet.write_column('F41', Performance_Priority_open,format)
        worksheet.write_column('G41', Search_Priority_open,format)
        worksheet.write_column('H41', RouteCalculation_Priority_open,format)
        worksheet.write_column('I41', Traffic_Priority_open,format)
        worksheet.write_column('J41', Userdata_Priority_open,format)
        for i in range(0,len(headings_Priority)):
            worksheet.write_formula('K%d'%(i+41), '=SUM(A%d:J%d)'%(i+41,i+41),format)#计算总计
        #插入模块BUG-column
        category_ids=[117,119,120,121,123,122,126,127,129]
        headings_category=[u'模块/状态','OPEN','CLOSE',u'合计']
        ALLissue=[]
        openissue=[]
        closeissue=[]
        for i in category_ids:
            NinjaALLissue = Get_Category('redmine-test','*',11,i)
            ALLissue.append(len(NinjaALLissue))
            Ninjaopenissue = Get_Category('redmine-test','open',11,i)
            openissue.append(len(Ninjaopenissue))
            Ninjacloseissue = Get_Category('redmine-test','closed',11,i)
            closeissue.append(len(Ninjacloseissue))
        worksheet.write_column('M1', headings, format)
        worksheet.write_row('M1',headings_category,format_title)
        #for i in range(0,len(category_ids)):
        worksheet.write_column('N2', openissue,format)
        worksheet.write_column('O2', closeissue,format)
        worksheet.write_column('P2', ALLissue,format)
        worksheet.write_formula('N%d'%(len(category_ids)+2), '=SUM(N2:N%d)'%len(category_ids),format)
        worksheet.write_formula('O%d'%(len(category_ids)+2), '=SUM(O2:O%d)'%len(category_ids),format)
        worksheet.write_formula('P%d'%(len(category_ids)+2), '=SUM(P2:P%d)'%len(category_ids),format)
        #插入归属模块==============================================
        CF37=['EG','DB','AW','Not determined']
        CF37_t=[u'归属\状态','OPEN','CLOSE']
        CF37open=[]
        CF37close=[]
        worksheet.write_row('R1',CF37_t,format_title)
        for i in CF37:
            NinjaOpen = Get_CF37('redmine-test','open',11,i)
            CF37open.append(len(NinjaOpen))
            Ninjaclose = Get_CF37('redmine-test','colsed',11,i)
            CF37close.append(len(Ninjaclose))
        #for i in range(0,len(CF37)):
        worksheet.write_column('R2', CF37,format)
        worksheet.write_column('S2', CF37open,format)
        worksheet.write_column('T2', CF37close,format)
        worksheet.write_row('R%d'%(len(CF37)+2), [u'合计'],format)
        worksheet.write_formula('S%d'%(len(CF37)+2), '=SUM(S2:S%d)'%len(CF37),format)
        worksheet.write_formula('T%d'%(len(CF37)+2), '=SUM(T2:T%d)'%len(CF37),format)
        #掺入weekBUG===============================================
        NinjiaDay,NinjiaWeek=WeekMain()
        NinjiaWeekClosedBug=Ninja_Week_BUG('redmine-test','closed',11)
        NinjiaWeekOpenBug=Ninja_Week_BUG('redmine-test','open',11)
        NinjiaWeekALLBug=Ninja_Week_BUG('redmine-test','*',11)
        #print NinjiaWeekALLBug
        WEEK_headres=[u'总量\周','OPEN','CLOSE',u'BUG总量']
        
        worksheet.write_row('V1', WEEK_headres, format_title)
        worksheet.write_column('V2',NinjiaDay,format)
        worksheet.write_column('W2',NinjiaWeekOpenBug,format)
        worksheet.write_column('X2',NinjiaWeekClosedBug,format)
        worksheet.write_column('Y2',NinjiaWeekALLBug,format)
        #绘制图形
        chart1 = workbook.add_chart({'type': 'pie'})#圆饼图
        chart1_open = workbook.add_chart({'type': 'pie'})#圆饼图
        chart2 = workbook.add_chart({'type': 'column', 'subtype': 'stacked'})#柱状图
        chart3 = workbook.add_chart({'type': 'column', 'subtype': 'stacked'})#柱状图
        chart4 = workbook.add_chart({'type': 'area', 'subtype': 'stacked'})#线图
        
        chart1.add_series({
        'name':       'Pie sales data',
        'categories': '=NinjaBUG!$A$2:$A$6',
        'values':     '=NinjaBUG!$K$2:$K$6',
        'data_labels': {'value': True,'percentage': True}
        })
        chart1.set_size({'width': 498, 'height': 366}) 
        chart1.set_title ({'name': u'Ninja bug 整体严重情况分布图'})
        
        chart1_open.add_series({
        'name':       'Pie sales data',
        'categories': '=NinjaBUG!$A$41:$A$45',
        'values':     '=NinjaBUG!$K$41:$K$45',
        'data_labels': {'value': True,'percentage': True}
        })
        chart1_open.set_size({'width': 498, 'height': 366}) 
        chart1_open.set_title ({'name': u'现状bug严重情况分布图'})

        chart2.add_series({
        'name':       '=NinjaBUG!$N$1',
        'categories': '=NinjaBUG!$M$2:$M$10',
        'values':     '=NinjaBUG!$N$2:$N$10',
        'data_labels': {'value': False}
        })

        chart2.add_series({
        'name':       '=NinjaBUG!$O$1',
        'categories': '=NinjaBUG!$M$2:$M10',
        'values':     '=NinjaBUG!$O$2:$O10',
        'data_labels': {'value': False}
        })
        chart2.set_size({'width': 498, 'height': 366}) 
        chart2.set_title ({'name': u'Ninja bug 模块状态统计'})
        chart2.set_style(10)

        chart3.add_series({
        'name':       '=NinjaBUG!$S$1',
        'categories': '=NinjaBUG!$R$2:$R$5',
        'values':     '=NinjaBUG!$S$2:$S$5',
        'data_labels': {'value': False}
        })

        chart3.add_series({
        'name':       '=NinjaBUG!$T$1',
        'categories': '=NinjaBUG!$R$2:$R5',
        'values':     '=NinjaBUG!$S$2:$S5',
        'data_labels': {'value': False}
        })
        chart3.set_size({'width': 318, 'height': 330}) 
        chart3.set_title ({'name': u'Ninja bug 归属状态统计'})
        chart3.set_style(10)
        #绘制线图
        chart4.add_series({
        'name':       '=NinjaBUG!$W$1',
        'categories': '=NinjaBUG!$V$2:$V$%d'%(len(NinjiaWeekOpenBug)+1),
        'values':     '=NinjaBUG!$W$2:$W$%d'%(len(NinjiaWeekOpenBug)+1),
        'data_labels': {'value': False}
        })

        chart4.add_series({
        'name':       '=NinjaBUG!$X$1',
        'categories': '=NinjaBUG!$V$2:$V$%d'%(len(NinjiaWeekClosedBug)+1),
        'values':     '=NinjaBUG!$X$2:$X$%d'%(len(NinjiaWeekClosedBug)+1),
        'data_labels': {'value': False}
        })
        chart4.set_size({'width': 498, 'height': 366}) 
        chart4.set_title ({'name': u'Ninja bug weekly状态统计'})
        chart4.set_style(10)

        worksheet.insert_chart('A18', chart1, {'x_offset': 25, 'y_offset': 10})
        worksheet.insert_chart('I18',chart2, {'x_offset': 25, 'y_offset': 10})
        worksheet.insert_chart('P18', chart3, {'x_offset': 25, 'y_offset': 10})
        worksheet.insert_chart('Z1', chart4, {'x_offset': 25, 'y_offset': 10})
        worksheet.insert_chart('A47', chart1_open, {'x_offset': 25, 'y_offset': 10})
        workbook.close()
    ANCS()