82 lines
3.7 KiB
Python
82 lines
3.7 KiB
Python
import pandas as pd
|
|
import numpy as np
|
|
from io import BytesIO
|
|
|
|
|
|
pandas_json_format = 'table'
|
|
|
|
|
|
class dtypes:
|
|
students_table = {'Examplan.id': 'int64', 'PrüfungsNr.': 'int64', 'Titel': 'O', 'Nachname': 'O', 'Vorname': 'O', 'Leistung': 'O', 'Versuch': 'int64', 'Status': 'O', 'ECTS': 'O', 'Semester': 'O', 'Jahr': 'int64', 'Prüfungsperiode': 'int64', 'Vermerk': 'O', 'Thema': 'O', 'Beginn': 'O', 'Gepl. Ende': 'O', 'Tatsächl. Ende': 'O', 'Prüfungsart': 'O', 'Prüfungsform': 'O', 'LockVersion': 'O'}
|
|
his_header = 'O'
|
|
|
|
|
|
def read_excel(bytes: BytesIO) -> tuple[pd.DataFrame, pd.DataFrame]:
|
|
try:
|
|
excelfile = pd.ExcelFile(bytes, engine='openpyxl')
|
|
except:
|
|
return pd.DataFrame()
|
|
|
|
full_table = pd.read_excel(excelfile, header=None)
|
|
|
|
# Finde Indizes der Zeilen mit den Werten "startHISsheet" und "endHISsheet"
|
|
def find_row_with_value(df, value):
|
|
mask = full_table.iloc[:, 0] == value
|
|
if not mask.any():
|
|
raise ValueError(f'{value} nicht gefunden')
|
|
return mask.idxmax()
|
|
|
|
start_idx = find_row_with_value(full_table, 'startHISsheet')
|
|
end_idx = find_row_with_value(full_table, 'endHISsheet')
|
|
|
|
# Lade den HISinOne Header (die ersten start_idx+1 Zeilen)
|
|
his_header = pd.read_excel(excelfile, nrows=start_idx+1, header=None)
|
|
|
|
# Lade Studentendaten (zwischen start_idx+1 und end_idx)
|
|
students_table = pd.read_excel(excelfile,
|
|
header=start_idx + 1,
|
|
nrows=end_idx - start_idx - 2,
|
|
index_col='Matrikelnummer',
|
|
dtype=dtypes.students_table)
|
|
|
|
students_table = students_table.replace({np.nan: None})
|
|
his_header = his_header.replace({np.nan: None})
|
|
|
|
return students_table, his_header
|
|
|
|
|
|
def prepare_table(students_table: pd.DataFrame) -> pd.DataFrame:
|
|
|
|
# Erzeuge Tabelle zur Punkteeingabe
|
|
points_table = students_table
|
|
|
|
new_columns = {'Punkte': np.nan, 'Note': np.nan}
|
|
points_table = points_table.assign(**new_columns)
|
|
|
|
return points_table
|
|
|
|
|
|
def export(edited_df: pd.DataFrame, his_header: pd.DataFrame, exercise_columns: list[str], grades_table: pd.DataFrame) -> BytesIO:
|
|
edited_df['Leistung'] = edited_df['Note'].replace({np.nan: 'NT'})
|
|
export_students_table = edited_df.drop(columns=['Punkte', 'Note']+exercise_columns).reset_index()
|
|
|
|
# Examplan.id muss erste Spalte sein (HISinOne prüft Spaltennamen nicht)
|
|
export_students_table = export_students_table.reindex(columns=['Examplan.id'] + [col for col in export_students_table.columns if col != 'Examplan.id'])
|
|
|
|
export_full_table = pd.DataFrame(
|
|
np.vstack([
|
|
his_header.values, # HISinOne Header
|
|
np.array(list(export_students_table)), # Tabellenheader (Matrikelnummer, Nachname, Vorname, ...)
|
|
export_students_table.values, # Daten
|
|
np.array(['endHISsheet'] + [''] * (len(list(export_students_table))-1)) # HISinOne Footer
|
|
]),
|
|
columns=export_students_table.columns)
|
|
|
|
# Exportiere Tabelle
|
|
bytesio = BytesIO()
|
|
with pd.ExcelWriter(bytesio, engine='openpyxl') as writer:
|
|
export_full_table.to_excel(writer, sheet_name='First Sheet', header=False, index=False) # HISinOne Tabelle
|
|
edited_df[['Nachname', 'Vorname', 'Punkte', 'Note']+exercise_columns].to_excel(writer, sheet_name='Aufgaben') # Punktergebnisse als extra Sheet
|
|
grades_table.to_excel(writer, sheet_name='Notenschlüssel', header=False, index=False) # Notenberechnung als extra Sheet
|
|
return bytesio
|