Excel task 1

mail@pastecode.io avatar
unknown
python
3 years ago
2.1 kB
4
Indexable
Never
from faker import Faker
import random
from openpyxl import Workbook, load_workbook

# Main settings
fake = Faker()
workbook = load_workbook(filename="Excel_ex.xlsx")
sheet = workbook.active

counter = 1

for row in range(2, 502):

    # 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}']
    # add random grade
    total_marks.value = f'=SUM(D{row}:H{row})*100'

    # AVERAGE
    # set current cell - COL J
    average = sheet[f'J{row}']
    # add random grade
    average.number_format = '0%'
    average.value = f'=AVERAGE(D{row}:H{row})'

    # ----------------
    # increase counter
    counter += 1

workbook.save(filename="Excel_ex.xlsx")