Excel task

from faker import Faker
from openpyxl import Workbook, load_workbook
import random

# This script can be used to fill a sheet with a fake data
# The sheet has to contain 1st row with column titles (Student no, Student name etc.)
# To run this script you have to:
#   1. Specify the filename, ex. 'Excel_ex.xlsx'
#   2. Make sure that script file and sheet file are in the same directory
#   3. Install Faker and openpyxl libraries

# Libraries - quick installation guide:
# MAC: in Terminal type "pip install Faker" and then "pip install openpyxl"
# WINDOWS: run Command Prompt as a administrator, and then type same commands as in MAC


# -------------------------------------------------------------------- #
# -------------------------- MAIN SETTINGS --------------------------- #
# -------------------------------------------------------------------- #
filename = 'Excel_ex.xlsx'  # set a filename
fake_rows = 3000  # set a number of fake rows you want to create
# -------------------------------------------------------------------- #

# create Faker object and load the file
fake = Faker()
workbook = load_workbook(filename=filename)
sheet = workbook.active


def fill_table(rows):
    # Fill row by row with fake data, starting form row 2
    counter = 1  # student number
    for row in range(2, rows + 2):
        # STUDENT NO
        # set current cell - COL A
        student_no = sheet.cell(row=row, column=1)
        # add student number
        student_no.value = counter

        # STUDENT NAME
        # set current cell - COL B
        student_name = sheet.cell(row=row, column=2)
        # add fake name
        student_name.value = fake.first_name()

        # STUDENT SURNAME
        # set current cell - COL C
        student_surname = sheet.cell(row=row, column=3)
        # add fake surname
        student_surname.value = fake.last_name()

        # MATHS
        # set current cell - COL D
        maths = sheet.cell(row=row, column=4)
        # add random grade
        maths.number_format = '0%'
        maths.value = random.randint(20, 100) / 100

        # ENGLISH
        # set current cell - COL E
        english = sheet.cell(row=row, column=5)
        # add random grade
        english.number_format = '0%'
        english.value = random.randint(20, 100) / 100

        # SCIENCE
        # set current cell - COL F
        science = sheet.cell(row=row, column=6)
        # add random grade
        science.number_format = '0%'
        science.value = random.randint(20, 100) / 100

        # FRENCH
        # set current cell - COL G
        french = sheet.cell(row=row, column=7)
        # add random grade
        french.number_format = '0%'
        french.value = random.randint(20, 100) / 100

        # ICT
        # set current cell - COL H
        ict = sheet.cell(row=row, column=8)
        # add random grade
        ict.number_format = '0%'
        ict.value = random.randint(20, 100) / 100

        # TOTAL MARKS
        # set current cell - COL I
        total_marks = sheet[f'I{row}']
        # count total marks with SUM formula
        total_marks.value = f'=SUM(D{row}:H{row})*100'

        # AVERAGE MARK
        # set current cell - COL J
        average = sheet[f'J{row}']
        # count average mark with AVERAGE formula
        average.number_format = '0%'
        average.value = f'=AVERAGE(D{row}:H{row})'

        # increase counter = student number
        counter += 1


# Fill the table with fake data
fill_table(fake_rows)  # number of rows
# Save the file
workbook.save(filename="Excel_ex.xlsx")