day07该交作业了

#! /usr/bin/env python
# -*- coding: utf-8 -*-
import random
import os
import xlrd
import xlwt


dir_path = './excel'  # 文件路径
file_type = 'xls'  # 文件后缀


# 生成姓名的函数
def generate_name():
    first_name = '赵钱孙李周吴郑王冯陈褚卫蒋沈韩杨朱秦尤许何吕施张孔曹严华金魏陶姜'
    last_name = '豫章故郡洪都新府星分翼轸地接衡庐襟三江而带五湖'
    first_name = random.choice(first_name)
    last_name = "".join(random.choice(last_name) for i in range(2))
    return first_name + last_name


# 生成成绩
def generate_grade():
    return random.randint(0, 100)


# 生成文件
def generate_grade_excel(course, name_list):
    if not os.path.exists(dir_path):
        os.mkdir(dir_path)
    wb = xlwt.Workbook()  # 创建表格
    sheet = wb.add_sheet('sheet1')  # 添加工作表
    sheet.write(0, 0, '姓名')
    sheet.write(0, 1, '期中成绩')
    sheet.write(0, 2, '期末成绩')
    sheet.write(0, 3, '平时成绩')
    sheet.write(0, 4, '学科得分')
    for index in range(1, len(name_list) + 1):
        mid = generate_grade()  # 期中成绩
        fin = generate_grade()  # 期末成绩
        usual = generate_grade()  # 平时成绩
        subj = int((mid + fin) * 0.35 + usual * 0.3)  # 计算学科成绩并取整
        sheet.write(index, 0, name_list[index - 1])
        sheet.write(index, 1, mid)
        sheet.write(index, 2, fin)
        sheet.write(index, 3, usual)
        sheet.write(index, 4, subj)
        wb.save(dir_path + '/' + course + '.xls')


# 开始执行生成文件
def create_tables():
    namelist = [generate_name() for i in range(50)]
    for i in ['大学物理', '高等数学', '大学英语', 'Python程序设计']:
        generate_grade_excel(i, namelist)


# 获取文件名
def get_all_excel(walk_path):
    file_list = []
    for root_dir, sub_dir, files in os.walk(r'' + walk_path):
        for file in files:
            if file.endswith(file_type):
                file_name = os.path.join(root_dir, file)
                file_list.append(file_name)
    return file_list


# 读取所有文件学科成绩汇总
def summary():
    file_list = get_all_excel(dir_path)
    stu = {}
    cursor = [os.path.basename(file).replace('.xls', '') for file in file_list]
    for file in file_list:
        wb = xlrd.open_workbook(file)
        sheet = wb.sheet_by_index(0)
        for index in range(1, sheet.nrows):
            stu.setdefault(sheet.cell(index, 0).value, []).append(sheet.cell(index, 4).value)
    wb = xlwt.Workbook()
    sheet = wb.add_sheet('sheet1')
    sheet.write(0, 0, '姓名')
    sheet.write(0, 1, cursor[0])
    sheet.write(0, 2, cursor[1])

    sheet.write(0, 3, cursor[2])
    sheet.write(0, 4, cursor[3])
    index = 1
    for name, grades in stu.items():
        sheet.write(index, 0, name)
        sheet.write(index, 1, grades[0])
        sheet.write(index, 2, grades[1])
        sheet.write(index, 3, grades[2])
        sheet.write(index, 4, grades[3])
        index = index + 1
    wb.save(dir_path + '汇总成绩.xls')


if __name__ == '__main__':
    create_tables()
    summary()