파이썬, openpyxl(7) 퀴즈

1. 제목 행 넣기

2. 점수 넣기

3. 퀴즈2를 10점으로 바꾸기

4. sum함수를 이용하여 총점 계산하기

5. 성적 넣기(A,B,C,D, 출석이 5미만이면 F)

 

from openpyxl import Workbook
wb = Workbook()
ws = wb.active

ws.append(("학번", "출석", "퀴즈1", "퀴즈2", "중간고사", "기말고사", "프로젝트", "총점", "성적"))


scores = [
(1,10,8,5,14,26,12),
(2,7,3,7,15,24,18),
(3,9,5,8,8,12,4),
(4,7,8,7,17,21,18),
(5,7,8,7,16,25,15),
(6,3,5,8,8,17,0),
(7,4,9,10,16,27,18),
(8,6,6,6,15,19,17),
(9,10,10,9,19,30,19),
(10,9,8,8,20,25,20)]

for i in scores:
    ws.append(i)
    
for idx, cell in enumerate(ws["D"]):
    if idx == 0: #제목행의 경우 skip
        continue
    cell.value = 10
    
for idx, score in enumerate(scores, start = 2):
    sum_val = sum(score[1:]) - score[3] + 10
    ws.cell(row = idx, column = 8).value = "=sum(B{}:G{})".format(idx, idx)

        
    grade = None
    if sum_val >= 90:
        grade = "A"
    elif sum_val >= 80:
        grade = "B"
    elif sum_val >= 70:
        grade = "C"
    else:
        grade = "D"
    if score[1] <5:
        grade = "F"
    ws.cell(row = idx, column = 9).value = grade
    
wb.save("./Desktop/코딩공부/scores.xlsx")

scores = [
(1,10,8,5,14,26,12),
(2,7,3,7,15,24,18),
(3,9,5,8,8,12,4),
(4,7,8,7,17,21,18),
(5,7,8,7,16,25,15),
(6,3,5,8,8,17,0),
(7,4,9,10,16,27,18),
(8,6,6,6,15,19,17),
(9,10,10,9,19,30,19),
(10,9,8,8,20,25,20)]

 

출처 : 나도코딩