# https://www.globalsino.com/ICs/page4853.html
# Calculation in a new Excel sheet, style, bold color.


from openpyxl import Workbook, load_workbook # ...
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font
import time

Scores = {
    "Yougui":{
        "Physics":99,
        "English":100,
        "Activity":98
        },
    "Joe":{
        "Physics":94,
        "English":95,
        "Activity":93
        },
    "Kim":{
        "Physics":89,
        "English":90,
        "Activity":88
        },
    "Mike":{
        "Physics":84,
        "English":85,
        "Activity":83
        }}
print(Scores)

wb = Workbook()
ws = wb.active
ws.title = "TheScores" # title
headings = (['Name'] + list(Scores['Yougui'].keys())) # keys()
ws.append(headings)

for person in Scores:
    TheScores = list(Scores[person].values())
    ws.append([person] + TheScores)

ws['A6'].value = "Average"

for col in range(2, len(Scores['Yougui'])+2):
    char = get_column_letter(col)
    # Average the score within B2 to B5
    ws[char+"6"] = f"=sum({char+'2'}:{char+'5'})/{len(Scores)}"

# Cannot make the entire row in bold by a single command
for col in range(1,6):
    ws[get_column_letter(col) + '1'].font = Font(bold=True, color = "00FF0000")

wb.save(r"C:\0Python\excel_Python_test5.xlsx")