自定义的模块English_score_config.py

    '1-20': 1.5,
    '21-40': 2,
    '41-60': 1.5

# 自定义模块进行处理
# from config import English_score_config
from pathlib import Path
import re

# 获取标准答案数据
def get_stander_answers(file_path):
    # 从excel中获取数据
    wb = load_workbook(file_path)
    ws = wb.active
    data = [cell.value for cell in ws['B'][1:]]
    return data

def count_score(qid):
    for key,value in English_score_config.items():
        temp = key.split('-')
        if int(temp[0]) <= qid <= int(temp[1]):
            return value


def get_student_score(file_path,answers):
    # 遍历考生的选项
    wb = load_workbook(file_path)
    ws = wb.active
    # 每一个选项和标准答案对比
    total = 0
    for number,temp in enumerate(zip(ws['B'][1:],answers)):
        cell,answer = temp
        # 如果一致，就记录这道题的分数
        if cell.value == answer:
            # 记录分数
            score = count_score(number+1)
            total += score
            ws.cell(row=number+2,column=3).value = '正确'
        else:
            ws.cell(row=number+2,column=3).value = '错误'

    ws['D1'].value = '总分'
    ws['D2'].value = total
    wb.save(file_path)
    wb.close()
    return total#返回分数值

def write_all_student_score(data):
    wb = Workbook()
    ws = wb.create_sheet('英语选择题分数汇总',index=0)
    ws.append(['姓名','成绩'])
    for row in data:
        ws.append(row)
    wb.save('英语选择题分数汇总.xlsx')
    wb.close()

if __name__ == '__main__':
    answers = get_stander_answers('/Users/andy/Desktop/高三英语选择题答案.xlsx')
    # 遍历文件
    data = []
    for file_path in Path('/Users/andy/Desktop/高三英语选择题答题卡(文件夹)/').iterdir():
#         print(type(file_path))
#         <class 'pathlib.WindowsPath'>
        total = get_student_score(file_path,answers)
        # 获取学生姓名 , (.*?)-可以匹配任意字符和任意多个
        pattern = '/Users/andy/Desktop/高三英语选择题答题卡/(.*?)英语选择题答题卡.xlsx'
        result = re.match(pattern,str(file_path))
#         0--匹配多有内容
#         1--匹配第一个正咋表达真式的内容
        name = result.group(1)
        data.append([name,total])#data为大列表,里面有很多小列表
    # 将所有学生的成绩写入到excel
    write_all_student_score(data)

说明:本文章为大熊自动化办公课程的学习笔记