excel整理日报

#!/usr/bin/env python3
# -*- coding:utf-8 -*-
# __author__ = 'eason'

import xlrd
import xlwt
import os


def summary():
    '''
    将多人的日报整理到同一个excel文件中report_summary.xls
    :return:
    '''
    cur_path = os.getcwd()
    report_list = []
    for file in os.listdir(cur_path):
        if file.split('.')[1].__contains__('xls'):  #回头要添加一个保护。否则当出现没有.的文件或文件夹时会报错。
            file_path = cur_path + '\' + file
            data = xlrd.open_workbook(file_path)
            table_report = data.sheets()[0]
            row_num = table_report.nrows
            for r in range(1,row_num):
                report_row = table_report.row_values(r)
                if report_row[0] in ['', '        '] or report_row[1] in ['', '        ']:  #当某行没有填写姓名或日期。跳过。
                    continue
                report_list.append(report_row)

    report_list = sorted(report_list, key=lambda x: x[3]) #目前是通过所在项目排序。后续改成先后通过日期，项目，名字排序
    xls = xlwt.Workbook()
    sht1 = xls.add_sheet(r'日报汇总')
    for r, report in enumerate(report_list):
        for c, value in enumerate(report):
            sht1.write(r, c, value)
    xls.save('./report_summary.xls')

def get_stat_result(report_list, person, date='按周统计'):
    '''
    统计分析核心代码。通过参数个数支持按天和按周统计。
    :param report_list:
    :param person:
    :param date:
    :return:
    '''
    #定义过程变量和结果变量
    work_time_1date = 0  #工作时长
    effective_work_time_1date = 0  #生产工时
    theory_work_time_1date = 7  #理论生产工时

    testCase_write_sum = 0  #测试用例编写量
    testCas_operate_sum = 0
    bug_submit_sum = 0
    bug_verificate_sum = 0

    testCase_write_time = 0  #测试用例编写时长
    testCas_operate_time = 0
    bug_submit_time = 0
    bug_verificate_time = 0

    for line in report_list:
        cur_person = line[0]
        cur_date = line[1]
        if cur_person == person and (cur_date == date or date=='按周统计'):  #仅当当前行的姓名和日期都满足统计要求时才纳入统计。否则跳过。
            job_type = line[4]
            job_time = line[5]
            if job_time == '' or job_type == '':
                continue
            if job_type != '其它':  #当任务类型为其它时，不算入生产工时
                effective_work_time_1date += job_time
            work_time_1date += job_time

            if job_type in ['功能用例执行', '用例编写', 'bug验证']:  #当任务类型属于这3种类型才统计产出效率
                testCase_write_count = line[6]
                print('testCase_write_count',testCase_write_count,cur_person,cur_date)
                testCas_operate_count = line[7]
                bug_submit_count = line[8]
                bug_verificate_count = line[9]
                if testCase_write_count not in ['', '        ', 0]:  #异常值进行保护逻辑
                    testCase_write_sum += testCase_write_count
                    testCase_write_time += job_time
                if testCas_operate_count not in ['', '        ', 0]:
                    testCas_operate_sum += testCas_operate_count
                    testCas_operate_time += job_time
                if bug_submit_count not in ['', '        ', 0]:
                    bug_submit_sum += bug_submit_count
                    bug_submit_time += job_time
                if bug_verificate_count not in ['', '        ', 0]:
                    bug_verificate_sum += bug_verificate_count
                    bug_verificate_time += job_time

    testCase_write_count_per_hour = float(
        testCase_write_sum) / testCase_write_time if testCase_write_time != 0 else 'NA'  #当有该任务类型的时间投入时才统计效率。反则为NA
    testCas_operate_count_per_hour = float(
        testCas_operate_sum) / testCas_operate_time if testCas_operate_time != 0 else 'NA'
    bug_submit_count_per_hour = float(bug_submit_sum) / bug_submit_time if bug_submit_time != 0 else 'NA'
    bug_verificate_count_per_hour = float(
        bug_verificate_sum) / bug_verificate_time if bug_verificate_time != 0 else 'NA'

    saturation_degree = float(effective_work_time_1date) / theory_work_time_1date  #计算工作饱和度
    time_stats = [work_time_1date, effective_work_time_1date, saturation_degree]  #工作时长相关指标
    quantity_stats = [testCase_write_sum, testCas_operate_sum, bug_submit_sum, bug_verificate_sum,
                      bug_verificate_count_per_hour]  #工作产出相关指标
    effectiveness_stats = [testCase_write_count_per_hour, testCas_operate_count_per_hour, bug_submit_count_per_hour]  #工作效率相关指标

    stat_result = [person, date] + time_stats + quantity_stats + effectiveness_stats
    return stat_result

def statistic_by_person_week(report_list, person_list):
    '''
    对指定人员列表按人按周统计（默认所有记录为同一周的记录）
    :param report_list:
    :param person_list:
    :return:
    '''
    stat_result_list = []
    for person in person_list:
        stat_result = get_stat_result(report_list, person)  #分析单人的统计结果
        stat_result_list.append(stat_result)
    return stat_result_list

def statistic_by_person_date(report_list, person_list, date_list):
    '''
    对指定人员列表和日期列表进行按人按天统计
    :param report_list:
    :param person_list:
    :param date_list:
    :return:
    '''
    stat_result_list = []
    for person in person_list:
        for date in date_list:
            stat_result = get_stat_result(report_list, person, date)  #分析单人单天的统计结果
            stat_result_list.append(stat_result)
    return stat_result_list


def xls_write_data(data_list, sht):
    '''
    保存分析结果
    :param data_list:
    :param sht:
    :return:
    '''
    for r, time_stat_result in enumerate(data_list):
        #从list中读取不同统计指标的值
        person = time_stat_result[0]
        date = str(time_stat_result[1])
        work_time_1date = time_stat_result[2]
        effective_work_time_1date = time_stat_result[3]
        saturation_degree = time_stat_result[4]
        testCase_write_sum = time_stat_result[5]
        testCas_operate_sum = time_stat_result[6]
        bug_submit_sum = time_stat_result[7]
        bug_verificate_sum = time_stat_result[8]
        bug_verificate_count_per_hour = time_stat_result[9]
        testCase_write_count_per_hour = time_stat_result[10]
        testCas_operate_count_per_hour = time_stat_result[11]
        bug_submit_count_per_hour = time_stat_result[12]

        #将统计指标写入excel
        sht.write(r, 0, person)
        sht.write(r, 1, date)
        sht.write(r, 2, work_time_1date)
        sht.write(r, 3, effective_work_time_1date)
        sht.write(r, 4, saturation_degree)
        sht.write(r, 5, testCase_write_sum)
        sht.write(r, 6, testCas_operate_sum)
        sht.write(r, 7, bug_submit_sum)
        sht.write(r, 8, bug_verificate_sum)
        sht.write(r, 9, bug_verificate_count_per_hour)
        sht.write(r, 10, testCase_write_count_per_hour)
        sht.write(r, 11, testCas_operate_count_per_hour)
        sht.write(r, 12, bug_submit_count_per_hour)


def statistic_by_person():
    '''
    按人统计
    :return:
    '''
    summary_file = './report_summary.xls'
    data = xlrd.open_workbook(summary_file)
    table_report = data.sheets()[0]
    row_num = table_report.nrows
    report_list = []
    person_list = []
    date_list = []
    for r in range(1, row_num):  #第一行是标题行，跳过
        report_row = table_report.row_values(r)
        person = report_row[0]
        date = report_row[1]
        report_list.append(report_row)  #获取行记录列表
        person_list.append(person)  #获取人名列表
        date_list.append(date)  #获取日期列表
    person_list = list(set(person_list))  #去重
    date_list = list(set(date_list))


    stat_result_list_by_date = statistic_by_person_date(report_list, person_list, date_list)  #按人按天统计
    stat_result_list_by_week = statistic_by_person_week(report_list, person_list)  #按人按周统计

    #保存分析结果
    xls = xlwt.Workbook()
    sht1 = xls.add_sheet(r'按人按天分析结果')
    xls_write_data(stat_result_list_by_date, sht1)
    sht2 = xls.add_sheet(r'按人按周分析结果')
    xls_write_data(stat_result_list_by_week, sht2)
    xls.save('./report_summary1.xls')

if __name__=="__main__":
    summary()  #将多人的日报整理到同一个excel文件中report_summary.xls
    statistic_by_person() #进行统计分析。分析按人、按项目、按天、按周的指标