Librecalc automatic grading of columns

We can use the following Pyoo based python script to automatically assign grades based on CGPA values in a column.

For example, consider the following file with CGPAs listed in the column B

If we want to assign grades based on  the CGPA values, we can use the script attached.

	#!/usr/bin/env python3
	"""
	Tested on python 3.6
	Modify the path variable to point to the location of the librecalc file.
	Modify the grade dict to suit the grades that you want to assign.
	"""
	import pyoo
	path=""
	desktop = pyoo.Desktop('localhost', 2002)
	doc = desktop.open_spreadsheet(path)
	sheet = doc.sheets[0]
	column=input('Enter the column header of which grading is needed \n')
	sheet = doc.sheets[0]
	col=0
	try:
	while (sheet[0,col].value != column):
	col=col+1
	except IndexError:
	print("Invalid column name")
	quit()
	"""
	Please modify the following dictionary to
	meet your grading standard
	"""
	grade_dict={
	"Ten": "S+",
	"Between 9 and 10": "A",
	"Between 8 and 9" : "B",
	"Between 7 and 8" : "C",
	"Between 6 and 7" : "D",
	"Between 5 and 6" : "E",
	"Less than 5" : "F"
	}
	i=0
	sheet[i,col+1].value = "Grades"
	sheet[i,col+1].background_color=sheet[i,col].background_color
	sheet[i,col+1].text_color=sheet[i,col].text_color
	sheet[i,col+1].font_weight=sheet[i,col].font_weight
	sheet[i,col+1].border_width=sheet[i,col].border_width
	sheet[i,col+1].border_color=sheet[i,col].border_color
	i=i+1
	while(sheet[i,col].value != ''):
	if(int(sheet[i,col].value) == 10):
	sheet[i,col+1].value = grade_dict["Ten"]
	elif(int(sheet[i,col].value) < 10 and int(sheet[i,col].value) >= 9):
	sheet[i,col+1].value = grade_dict["Between 9 and 10"]
	elif(int(sheet[i,col].value) < 9 and int(sheet[i,col].value) >= 8):
	sheet[i,col+1].value = grade_dict["Between 8 and 9"]
	elif(int(sheet[i,col].value) < 8 and int(sheet[i,col].value) >= 7):
	sheet[i,col+1].value = grade_dict["Between 7 and 8"]
	elif(int(sheet[i,col].value) < 7 and int(sheet[i,col].value) >= 6):
	sheet[i,col+1].value = grade_dict["Between 6 and 7"]
	elif(int(sheet[i,col].value) < 6 and int(sheet[i,col].value) >= 5):
	sheet[i,col+1].value = grade_dict["Between 5 and 6"]
	elif(int(sheet[i,col].value) < 5):
	sheet[i,col+1].value = grade_dict["Less than 5"]
	else:
	sheet[i,col+1].value = "Invalid grade"
	print("Assigned " + str(i) +"th grade")
	sheet[i,col+1].background_color=sheet[i,col].background_color
	sheet[i,col+1].text_color=sheet[i,col].text_color
	sheet[i,col+1].font_weight=sheet[i,col].font_weight
	sheet[i,col+1].border_width=sheet[i,col].border_width
	sheet[i,col+1].border_color=sheet[i,col].border_color
	i=i+1
	doc.save()
	doc.close()

view raw grading_column_pyoo.py hosted with ❤ by GitHub

The script needs Pyoo to be installed, the steps for the same can be seen here .

Please note that to be able to run the script the following command needs to be run in the terminal,

  
 
soffice --accept="socket,host=localhost,port=2002;urp;" --norestore --nologo --nodefault
 

If the script is saved as column_grade.py run the script from the terminal.

$ python column_grade.py Enter the column header of which grading is needed CGPA Assigned 1th grade Assigned 2th grade Assigned 3th grade Assigned 4th grade Assigned 5th grade Assigned 6th grade Assigned 7th grade Assigned 8th grade Assigned 9th grade Assigned 10th grade Assigned 11th grade Assigned 12th grade Assigned 13th grade Assigned 14th grade

The file should get updated and look as below.