Grasple Test to Microsoft Excel#
Below you can upload a .csv-file exported from a Grasple test. After review of the generated output you can converted the output to a Microsoft Excel file available for download.
No data is stored on the server, as all Python code is run within your local browser. All data will be cleared after you close the browser tab or restart the Thebe kernel.
If the order of question (pools) is fixed, the columns of the output will be in the same order as the order of the questions in the test. If the order of questions is not fixed, the columns of the output will be in any order.
If multiple tests are present in the file, the output will be generated for each test separately. The output will be saved in a single Excel file for each test. The name of the Excel file will be the name of the test, and the name of the sheet will be Results.
If at least one student has attempted the same test more than once, the output will contain a row for each attempt. The number of the attempt will be added after the name of the student, before the result of the first question (pool).
import pandas as pd
import ipywidgets as widgets
from IPython.display import display, HTML
import io
import base64
import micropip
await micropip.install("pathvalidate")
from pathvalidate import sanitize_filename
# Widgets
upload_label = widgets.HTML('<b>Grasple CSV: </b>')
upload = widgets.FileUpload(accept='.csv', multiple=False)
upload_row = widgets.HBox([upload_label, upload]) # Label in front of button
output = widgets.Output()
download_button = widgets.Button(
description="Generate Excel file",
disabled=True,
layout=widgets.Layout(width='auto')
)
output_link = widgets.Output()
download_row = widgets.HBox([download_button, output_link])
# Install xlsxwriter using micropip
await micropip.install("xlsxwriter")
def create_download_link(df, filename):
filename = sanitize_filename(filename)
"""Create a downloadable link for the DataFrame as an Excel file with formatted columns."""
with io.BytesIO() as buffer:
with pd.ExcelWriter(buffer, engine="xlsxwriter") as writer:
worksheet = writer.book.add_worksheet("Results")
# Define formats
right_align_format = writer.book.add_format({'align': 'right'})
right_align_format.set_border(1)
right_align_format.set_bg_color('#CCF1F4')
light_right_align_format = writer.book.add_format({'align': 'right'})
light_right_align_format.set_border(1)
light_right_align_format.set_bg_color('#E6F8FA')
bold_right_align_format = writer.book.add_format({'align': 'right',"bold": True})
bold_right_align_format.set_border(1)
bold_right_align_format.set_bottom(5)
bold_right_align_format.set_bg_color('#00B8C8')
left_align_format = writer.book.add_format({'align': 'left'})
left_align_format.set_border(1)
left_align_format.set_bg_color('#CCF1F4')
light_left_align_format = writer.book.add_format({'align': 'left'})
light_left_align_format.set_border(1)
light_left_align_format.set_bg_color('#E6F8FA')
bold_left_align_format = writer.book.add_format({'align': 'left',"bold": True})
bold_left_align_format.set_border(1)
bold_left_align_format.set_bottom(5)
bold_left_align_format.set_bg_color('#00B8C8')
center_align_format = writer.book.add_format({'align': 'center'})
center_align_format.set_border(1)
center_align_format.set_bg_color('#CCF1F4')
light_center_align_format = writer.book.add_format({'align': 'center'})
light_center_align_format.set_border(1)
light_center_align_format.set_bg_color('#E6F8FA')
bold_center_align_format = writer.book.add_format({'align': 'center',"bold": True})
bold_center_align_format.set_border(1)
bold_center_align_format.set_bottom(5)
bold_center_align_format.set_bg_color('#00B8C8')
worksheet.write(0, 0, "Student number", bold_right_align_format) # Write index with right alignment
worksheet.write(0, 1, "Student name", bold_left_align_format) # Write index with right alignment
num_index_levels = df.index.nlevels
if num_index_levels > 2:
worksheet.write(0, 2, "Test attempt", bold_center_align_format) # Write index with right alignment
last_id = -1
last_attempt = 1
for i, val in enumerate(df.index.values):
if val[0] != last_id:
last_id = val[0]
last_attempt = 1
else:
last_attempt += 1
if i % 2 == 0:
worksheet.write(i+1, 0, val[0], right_align_format) # Write index with right alignment
worksheet.write(i+1, 1, val[1], left_align_format)
if num_index_levels > 2:
worksheet.write(i+1, 2, last_attempt, center_align_format)
else:
worksheet.write(i+1, 0, val[0], light_right_align_format)
worksheet.write(i+1, 1, val[1], light_left_align_format)
if num_index_levels > 2:
worksheet.write(i+1, 2, last_attempt, light_center_align_format)
for col,name in enumerate(df.columns.values):
worksheet.write(0, col+num_index_levels, name, bold_center_align_format)
for row, val in enumerate(df[name].values):
if row % 2 == 0:
worksheet.write(row+1, col+num_index_levels, val, center_align_format)
else:
worksheet.write(row+1, col+num_index_levels, val, light_center_align_format)
worksheet.autofit()
buffer.seek(0)
b64 = base64.b64encode(buffer.read()).decode()
href = f'data:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;base64,{b64}'
return f'<a download="{filename}" href="{href}" target="_blank">Download Excel file</a>'
def on_download_click(b):
"""Handle the download button click."""
download_button.disabled = True # Disable after click
output_link.clear_output()
with output_link:
try:
# Create the download link
download_link = create_download_link(pvt)
# Display the download link
display(widgets.HTML(
f'<a download="output.xlsx" href="{download_link}" target="_blank">Download Excel</a>'
))
except Exception as e:
print("❌ Error creating download link:")
print(e)
def on_upload_change(change):
output.clear_output()
global pvt # Declare pvt as global to use it in the download button
if upload.value:
with output:
try:
# Retrieve the first file from the tuple
uploaded_file = upload.value[0] if isinstance(upload.value, tuple) else list(upload.value.values())[0]
if isinstance(uploaded_file, dict):
content = uploaded_file['content']
else:
content = uploaded_file[1]['content']
df = pd.read_csv(io.BytesIO(content))
df = df.drop(df[df['started_test'] == 0].index)
df['main_exercise_id'] = df['main_exercise_id'].astype(int)
print("✅ CSV successfully loaded.")
# extract the different tests from the dataframe
test_names = df['test_name'].unique()
print("The CSV contains the following test data:")
for test_name in test_names:
display(HTML(f"<h2>{test_name}</h2>"))
df_test = df[df['test_name'] == test_name]
# Convert to pivot table
question_pool_names_temp = df_test['question_pool_name'].unique()
question_pool_names = [
name for name in question_pool_names_temp if isinstance(name, str)
]
main_exercise_ids_temp = df_test['main_exercise_id'].unique()
main_exercise_ids = [
number for number in main_exercise_ids_temp if isinstance(number, str)
]
# get maximum number of attempts
attempts = pd.pivot_table(df_test, index="student_id",values="test_attempt_id", aggfunc="nunique")
max_attempts = attempts.values.max()
if max_attempts > 1:
indexer = ["student_id", "user_name","test_attempt_id"]
else:
indexer = ["student_id", "user_name"]
if question_pool_names:
pvt = pd.pivot_table(
df_test,
values="scored_points",
index=indexer,
aggfunc="sum",
fill_value=0,
columns="question_pool_name"
)
times = pd.pivot_table(
df_test,
values="exercise_received_at",
index="student_id",
aggfunc="min",
fill_value="missing",
columns="question_pool_name"
)
for name in question_pool_names:
remaining_rows = df.shape[0]
if remaining_rows > 1:
times = times.drop(times[times[name] == "missing"].index)
times = times.iloc[:1].T
times.rename(columns={times.columns[0]: 'times'}, inplace=True)
times.sort_values(by='times', inplace=True)
pvt = pvt[times.index]
else:
pvt = pd.pivot_table(
df_test,
values="scored_points",
index=indexer,
aggfunc="sum",
fill_value=0,
columns="main_exercise_id"
)
times = pd.pivot_table(
df_test,
values="exercise_received_at",
index="student_id",
aggfunc="min",
fill_value="missing",
columns="main_exercise_id"
)
for name in main_exercise_ids:
remaining_rows = df.shape[0]
if remaining_rows > 1:
times = times.drop(times[times[name] == "missing"].index)
times = times.iloc[:1].T
times.rename(columns={times.columns[0]: 'times'}, inplace=True)
times.sort_values(by='times', inplace=True)
pvt = pvt[times.index]
display(pvt)
# --- Download button and link for this DataFrame ---
btn = widgets.Button(description=f"Generate Excel for {test_name}", layout=widgets.Layout(width='auto'))
link_out = widgets.Output()
def make_on_click(pvt, link_out, test_name):
def on_click(b):
b.disabled = True
link_out.clear_output()
with link_out:
html_link = create_download_link(pvt, f"{test_name}.xlsx")
display(widgets.HTML(html_link))
return on_click
btn.on_click(make_on_click(pvt, link_out, test_name))
display(widgets.HBox([btn, link_out]))
except Exception as e:
print("❌ Error loading CSV:")
if 'started_test' in str(e):
print("Please make sure the CSV file contains the results of at least one Grasple test.")
else:
raise e
# Hide the download button initially
download_button.layout.display = 'none'
upload.observe(on_upload_change, names='value')
download_button.on_click(on_download_click)
# Display the widgets
display(upload_row, output)