自定義的模塊English_score_config.py
'1-20': 1.5,
'21-40': 2,
'41-60': 1.5
# 自定義模塊進(jìn)行處理
# from config import English_score_config
from pathlib import Path
import re
# 獲取標(biāo)準(zhǔn)答案數(shù)據(jù)
def get_stander_answers(file_path):
# 從excel中獲取數(shù)據(jù)
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):
# 遍歷考生的選項(xiàng)
wb = load_workbook(file_path)
ws = wb.active
# 每一個(gè)選項(xiàng)和標(biāo)準(zhǔn)答案對比
total = 0
for number,temp in enumerate(zip(ws['B'][1:],answers)):
cell,answer = temp
# 如果一致号坡，就記錄這道題的分?jǐn)?shù)
if cell.value == answer:
# 記錄分?jǐn)?shù)
score = count_score(number+1)
total += score
ws.cell(row=number+2,column=3).value = '正確'
else:
ws.cell(row=number+2,column=3).value = '錯(cuò)誤'
ws['D1'].value = '總分'
ws['D2'].value = total
wb.save(file_path)
wb.close()
return total#返回分?jǐn)?shù)值
def write_all_student_score(data):
wb = Workbook()
ws = wb.create_sheet('英語選擇題分?jǐn)?shù)匯總',index=0)
ws.append(['姓名','成績'])
for row in data:
ws.append(row)
wb.save('英語選擇題分?jǐn)?shù)匯總.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)
# 獲取學(xué)生姓名 , (.*?)-可以匹配任意字符和任意多個(gè)
pattern = '/Users/andy/Desktop/高三英語選擇題答題卡/(.*?)英語選擇題答題卡.xlsx'
result = re.match(pattern,str(file_path))
# 0--匹配多有內(nèi)容
# 1--匹配第一個(gè)正咋表達(dá)真式的內(nèi)容
name = result.group(1)
data.append([name,total])#data為大列表,里面有很多小列表
# 將所有學(xué)生的成績寫入到excel
write_all_student_score(data)
說明:本文章為大熊自動(dòng)化辦公課程的學(xué)習(xí)筆記
