import  xlsxwriter,xlrd

'''

思路：

1.获取数据

2.整合数据

3.写入文件

'''

#筛选

def filt(category,table,filt_name=None,res=0):

    ncol = table.ncols

    for i in range(0,ncol):

        col_value=set(table.col_values(i))

        if category == table.col_values(i)[0]:

            for j in table.col_values(i):

                if filt_name == j:

                    res= res+1

            break

    return res,col_value

#写数据 i代表插入表格的起始位置

def write_data(category,i=1):

    data_cate = list(filt(category,table1)[1])

    data_cate.pop(data_cate.index(category))

    data_child=[]

    for cate_name in data_cate:

        data_child.append(cate_name)

        data_child.append(filt(category,table1,cate_name)[0])

        i=i+1

        worksheet.write_row("A{}".format(i),data_child)

        data_child=[]

#打开数据所在excel

data = xlrd.open_workbook("bug_list.xlsx")

table1 = data.sheets()[0]

#填写表头

workbook = xlsxwriter.Workbook("report.xlsx")

worksheet = workbook.add_worksheet("测试报告")

heads1=["项目名称","bug数量"]

heads2=["开发","bug数量"]

heads3=["bug状态","bug数量"]

worksheet.write_row("A1",heads1)

worksheet.write_row("A10",heads2)

worksheet.write_row("A17",heads3)

#填写具体数据

write_data("项目名称")

write_data("开发",10)

write_data("bug状态",17)

#柱状图

chart1 = workbook.add_chart({"type":"column"})

chart1.add_series({

    "name":"=测试报告!$B$1",

    "categories":"=测试报告!$A$2:$A$4",

    "values":"=测试报告!$B$2:$B$4"

})

chart1.set_title({"name":"柱状图"})

chart1.set_x_axis({"name":"项目名称"})

chart1.set_y_axis({"name":"bug数量"})

chart1.set_style(13)

worksheet.insert_chart("D5",chart1)

#饼图

chart2= workbook.add_chart({"type":"pie"})

chart2.add_series({

    "categories":"=测试报告!$A$11:$A$14",

    "values":"=测试报告!$B$11:$B$14",

    "points":[

        {"fill":{"color":"red"}},

        {"fill":{"color":"yellow"}},

        {"fill":{"color":"blue"}},

        {"fill":{"color":"green"}},

    ]

})

chart2.set_title({"name":"饼状图"})

chart2.set_style(10)

worksheet.insert_chart("D23",chart2)

workbook.close()