Spaces:
Sleeping
Sleeping
| import pandas as pd | |
| import io | |
| import matplotlib | |
| matplotlib.use('Agg') | |
| import matplotlib.pyplot as plt | |
| from openpyxl.styles import Alignment, Font, Border, Side, PatternFill | |
| from openpyxl.drawing.image import Image | |
| from openpyxl.chart import BarChart, PieChart, Reference, Series, PieChart3D | |
| def generate_complete_report_excel(results_data): | |
| from app import format_data_for_wide_export | |
| # 1. Prepare raw lists for DataFrames | |
| overview_data = [] | |
| subject_details = [] | |
| for student in results_data: | |
| usn = student.get('usn', '') | |
| name = student.get('student_name', '') | |
| sgpa_str = student.get('sgpa', '0') | |
| percentage_str = student.get('percentage', '0%') | |
| student_class = student.get('class', '') | |
| overall_result = 'P' if student_class != 'FAIL' else 'F' | |
| total_marks = student.get('total_marks', 0) | |
| try: sgpa = float(sgpa_str) if sgpa_str and str(sgpa_str).upper() != 'N/A' else 0.0 | |
| except: sgpa = 0.0 | |
| try: percentage = float(str(percentage_str).replace('%', '')) if percentage_str else 0.0 | |
| except: percentage = 0.0 | |
| try: total_marks_val = int(total_marks) | |
| except: total_marks_val = 0 | |
| failed_subjects = [] | |
| for sub in student.get('subjects', []): | |
| subj_code = sub.get('subject_code', '') | |
| subj_name = sub.get('subject_name', '') | |
| ia = sub.get('internal_marks', '0') | |
| ext = sub.get('external_marks', '0') | |
| tot = sub.get('total', '0') | |
| res = sub.get('result', '') | |
| try: ia_val = int(ia) | |
| except: ia_val = 0 | |
| try: ext_val = int(ext) | |
| except: ext_val = 0 | |
| try: tot_val = int(tot) | |
| except: tot_val = 0 | |
| full_subject = f"{subj_code} - {subj_name}" if subj_name else subj_code | |
| if res.upper() in ['F', 'A', 'NE', 'X']: | |
| failed_subjects.append(full_subject) | |
| subject_details.append({ | |
| 'USN': usn, 'Name': name, 'Subject_Code': subj_code, 'Subject_Name': subj_name, | |
| 'Full_Subject': full_subject, 'IA': ia_val, 'Ext': ext_val, 'Total': tot_val, 'Result': res.upper() | |
| }) | |
| overview_data.append({ | |
| 'USN': usn, 'Name': name, 'Section': 'Unassigned', 'Marks': total_marks_val, | |
| 'Percentage': percentage, 'Percentage_Str': percentage_str, 'SGPA': sgpa, 'Class': student_class, | |
| 'Overall_Result': 'Pass' if overall_result == 'P' else 'Fail', 'Failed_Count': len(failed_subjects), | |
| 'Failed_Subjects': ', '.join(failed_subjects) if failed_subjects else '' | |
| }) | |
| df_students = pd.DataFrame(overview_data) | |
| df_subs = pd.DataFrame(subject_details) | |
| output = io.BytesIO() | |
| with pd.ExcelWriter(output, engine='openpyxl') as writer: | |
| if df_students.empty: | |
| pd.DataFrame({'Message': ['No data available']}).to_excel(writer, sheet_name='Summary', index=False) | |
| output.seek(0) | |
| return output | |
| header_font = Font(bold=True, color="FFFFFF") | |
| dark_fill = PatternFill(start_color="2F75B5", end_color="2F75B5", fill_type="solid") | |
| light_red_fill = PatternFill(start_color="FFC7CE", end_color="FFC7CE", fill_type="solid") | |
| light_green_fill = PatternFill(start_color="C6EFCE", end_color="C6EFCE", fill_type="solid") | |
| center_align = Alignment(horizontal='center', vertical='center', wrap_text=True) | |
| thin_border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin')) | |
| def format_sheet(ws, is_standard=True): | |
| if is_standard: | |
| for cell in ws[1]: | |
| cell.font = header_font | |
| cell.fill = dark_fill | |
| cell.alignment = center_align | |
| cell.border = thin_border | |
| for row in ws.iter_rows(): | |
| for cell in row: | |
| cell.alignment = center_align | |
| cell.border = thin_border | |
| from openpyxl.utils import get_column_letter | |
| for col in ws.columns: | |
| max_length = 0 | |
| column = get_column_letter(col[0].column) | |
| for cell in col: | |
| try: | |
| if len(str(cell.value)) > max_length: | |
| max_length = len(cell.value) | |
| except: pass | |
| ws.column_dimensions[column].width = min(max_length + 2, 50) | |
| def write_standard_sheet(df, sheet_name, extra_cols=None): | |
| cols = ['USN', 'Name', 'Section', 'Marks', 'Percentage_Str'] | |
| rename_dict = {'USN': 'Student_ID', 'Percentage_Str': 'Percentage (%)'} | |
| if extra_cols: cols.extend(extra_cols) | |
| if df.empty: | |
| pd.DataFrame(columns=[rename_dict.get(c, c) for c in cols]).to_excel(writer, sheet_name=sheet_name, index=False) | |
| else: | |
| df_out = df[cols].copy() | |
| df_out.rename(columns=rename_dict, inplace=True) | |
| df_out.to_excel(writer, sheet_name=sheet_name, index=False) | |
| format_sheet(writer.sheets[sheet_name]) | |
| # --- 1. Summary --- | |
| total_students = len(df_students) | |
| passed = len(df_students[df_students['Overall_Result'] == 'Pass']) | |
| failed = len(df_students[df_students['Overall_Result'] == 'Fail']) | |
| absent = 0 | |
| pass_perc = (passed / total_students * 100) if total_students > 0 else 0 | |
| fail_1 = len(df_students[df_students['Failed_Count'] == 1]) | |
| fail_2 = len(df_students[df_students['Failed_Count'] == 2]) | |
| fail_3_plus = len(df_students[df_students['Failed_Count'] >= 3]) | |
| fcd = len(df_students[df_students['Class'] == 'FCD']) | |
| fc = len(df_students[df_students['Class'] == 'FC']) | |
| sc = len(df_students[df_students['Class'] == 'SC']) | |
| summary_rows = [ | |
| ['Metric', 'Value'], | |
| ['Total', total_students], | |
| ['Appeared', total_students], | |
| ['Passed', passed], | |
| ['Failed', failed], | |
| ['Absent', absent], | |
| ['Pass %', f"{pass_perc:.2f}%"], | |
| ['', ''], | |
| ['── Failure Breakdown ──', ''], | |
| ['1 Subject Fail', fail_1], | |
| ['2 Subject Fails', fail_2], | |
| ['3+ Subject Fails', fail_3_plus], | |
| ['', ''], | |
| ['── Category Breakdown ──', ''], | |
| ['First Class Distinction (≥70%)', fcd], | |
| ['First Class (60-69.99%)', fc], | |
| ['Second Class (50-59.99%)', sc] | |
| ] | |
| df_summary = pd.DataFrame(summary_rows) | |
| df_summary.to_excel(writer, sheet_name='Summary', index=False, header=False) | |
| format_sheet(writer.sheets['Summary'], is_standard=False) | |
| # Color specific summary rows | |
| ws_sum = writer.sheets['Summary'] | |
| for row in ws_sum.iter_rows(): | |
| if row[0].value == 'Metric': | |
| for c in row: c.font = header_font; c.fill = dark_fill | |
| elif isinstance(row[0].value, str) and row[0].value.startswith('──'): | |
| for c in row: c.font = Font(bold=True); c.fill = PatternFill(start_color="D9D9D9", end_color="D9D9D9", fill_type="solid") | |
| # --- 2. Overview --- | |
| records, display_headers = format_data_for_wide_export(results_data) | |
| ws_over = writer.book.create_sheet('Overview') | |
| ws_over.cell(row=1, column=1, value='USN').font = header_font; ws_over.cell(row=1, column=1).fill = dark_fill; ws_over.merge_cells('A1:A2') | |
| ws_over.cell(row=1, column=2, value='Name').font = header_font; ws_over.cell(row=1, column=2).fill = dark_fill; ws_over.merge_cells('B1:B2') | |
| col_idx = 3 | |
| for header_info in display_headers: | |
| is_elective = header_info["is_elective"] | |
| colspan = 5 if is_elective else 4 | |
| ws_over.cell(row=1, column=col_idx, value=header_info["header"]).font = header_font | |
| ws_over.cell(row=1, column=col_idx).fill = dark_fill | |
| ws_over.merge_cells(start_row=1, start_column=col_idx, end_row=1, end_column=col_idx + colspan - 1) | |
| sub_headers = ['Course', 'IA', 'Ex', 'Total', 'Pass/Fail'] if is_elective else ['IA', 'Ex', 'Total', 'Pass/Fail'] | |
| for i, sub_header in enumerate(sub_headers): | |
| cell = ws_over.cell(row=2, column=col_idx + i, value=sub_header) | |
| cell.font = header_font; cell.fill = dark_fill | |
| col_idx += colspan | |
| summary_start_col = col_idx | |
| summary_headers = ['NO OF SUBJECTS FAILED', 'NO OF SUBJECTS ABSENT', 'Percentage', 'Class', 'SGPA'] | |
| for i, h in enumerate(summary_headers): | |
| cell = ws_over.cell(row=1, column=summary_start_col + i, value=h) | |
| cell.font = header_font; cell.fill = dark_fill | |
| ws_over.merge_cells(start_row=1, start_column=summary_start_col + i, end_row=2, end_column=summary_start_col + i) | |
| row_idx = 3 | |
| for record in records: | |
| ws_over.cell(row=row_idx, column=1, value=record['USN']) | |
| ws_over.cell(row=row_idx, column=2, value=record['Name']) | |
| col_idx = 3 | |
| for header_info in display_headers: | |
| header_key, is_elective = header_info["header"], header_info["is_elective"] | |
| data = record['subjects_data'][header_key] | |
| if is_elective: | |
| ws_over.cell(row=row_idx, column=col_idx, value=data['Course']) | |
| ws_over.cell(row=row_idx, column=col_idx + 1, value=data['IA']) | |
| ws_over.cell(row=row_idx, column=col_idx + 2, value=data['Ex']) | |
| ws_over.cell(row=row_idx, column=col_idx + 3, value=data['Total']) | |
| pf_cell = ws_over.cell(row=row_idx, column=col_idx + 4, value=data['Pass/Fail']) | |
| if data['Pass/Fail'] == 'F': pf_cell.fill = light_red_fill | |
| col_idx += 5 | |
| else: | |
| ws_over.cell(row=row_idx, column=col_idx, value=data['IA']) | |
| ws_over.cell(row=row_idx, column=col_idx + 1, value=data['Ex']) | |
| ws_over.cell(row=row_idx, column=col_idx + 2, value=data['Total']) | |
| pf_cell = ws_over.cell(row=row_idx, column=col_idx + 3, value=data['Pass/Fail']) | |
| if data['Pass/Fail'] == 'F': pf_cell.fill = light_red_fill | |
| col_idx += 4 | |
| ws_over.cell(row=row_idx, column=col_idx, value=record['subjects_failed']) | |
| ws_over.cell(row=row_idx, column=col_idx + 1, value=record['subjects_absent']) | |
| ws_over.cell(row=row_idx, column=col_idx + 2, value=record['percentage']) | |
| class_cell = ws_over.cell(row=row_idx, column=col_idx + 3, value=record['class']) | |
| if record['class'] == 'FAIL': class_cell.fill = light_red_fill | |
| ws_over.cell(row=row_idx, column=col_idx + 4, value=record['sgpa']) | |
| row_idx += 1 | |
| format_sheet(ws_over, is_standard=False) | |
| # --- 3. Ranking (Marks) & 4. Ranking (SGPA) --- | |
| df_rank_marks = df_students[['USN', 'Name', 'Marks', 'Percentage_Str', 'Class', 'Overall_Result']].copy() | |
| df_rank_marks.sort_values(by='Marks', ascending=False, inplace=True) | |
| df_rank_marks['Class_Rank'] = range(1, len(df_rank_marks) + 1) | |
| df_rank_marks.rename(columns={'USN': 'Student_ID', 'Percentage_Str': 'Percentage'}, inplace=True) | |
| df_rank_marks.to_excel(writer, sheet_name='Ranking (Marks)', index=False) | |
| format_sheet(writer.sheets['Ranking (Marks)']) | |
| df_rank_sgpa = df_students[['USN', 'Name', 'SGPA', 'Percentage_Str', 'Class', 'Overall_Result']].copy() | |
| df_rank_sgpa.sort_values(by='SGPA', ascending=False, inplace=True) | |
| df_rank_sgpa['SGPA_Rank'] = range(1, len(df_rank_sgpa) + 1) | |
| df_rank_sgpa.rename(columns={'USN': 'Student_ID', 'Percentage_Str': 'Percentage'}, inplace=True) | |
| df_rank_sgpa.to_excel(writer, sheet_name='Ranking (SGPA)', index=False) | |
| format_sheet(writer.sheets['Ranking (SGPA)']) | |
| # --- 5. Subject Analysis --- | |
| subj_analysis = [] | |
| if not df_subs.empty: | |
| for subj in sorted(df_subs['Subject_Code'].unique()): | |
| sdf = df_subs[df_subs['Subject_Code'] == subj] | |
| total = len(sdf) | |
| absent_count = len(sdf[sdf['Result'] == 'A']) | |
| appeared_count = total - absent_count | |
| passed_count = len(sdf[sdf['Result'] == 'P']) | |
| failed_count = appeared_count - passed_count | |
| pass_p = (passed_count / appeared_count * 100) if appeared_count > 0 else 0 | |
| avg_marks = sdf[sdf['Result'] != 'A']['Total'].mean() if appeared_count > 0 else 0 | |
| subj_analysis.append({ | |
| 'Subject': subj, | |
| 'Total': total, | |
| 'Appeared': appeared_count, | |
| 'Absent': absent_count, | |
| 'Passed': passed_count, | |
| 'Failed': failed_count, | |
| 'Pass %': round(pass_p, 2), | |
| 'Average Marks': round(avg_marks, 2) | |
| }) | |
| df_subj_analysis = pd.DataFrame(subj_analysis) | |
| df_subj_analysis.to_excel(writer, sheet_name='Subject Analysis', index=False) | |
| ws_sa = writer.sheets['Subject Analysis'] | |
| format_sheet(ws_sa) | |
| # Color pass % column logic like in the image (optional, basic formatting done) | |
| for row in ws_sa.iter_rows(min_row=2, max_row=ws_sa.max_row, min_col=1, max_col=8): | |
| row[6].fill = light_green_fill # Pass % | |
| row[7].fill = PatternFill(start_color="DCE6F1", end_color="DCE6F1", fill_type="solid") # Avg Marks | |
| # Add Charts to Subject Analysis | |
| if not df_subj_analysis.empty: | |
| # Determine dynamic row for charts (so they don't overlap table) | |
| chart_start_row = ws_sa.max_row + 4 | |
| # Add summary stats box for Pass/Fail/Absent next to Pie (also acts as data source) | |
| ws_sa[f'V{chart_start_row}'] = 'Pass'; ws_sa[f'W{chart_start_row}'] = passed | |
| ws_sa[f'V{chart_start_row+1}'] = 'Fail'; ws_sa[f'W{chart_start_row+1}'] = failed | |
| ws_sa[f'V{chart_start_row+2}'] = 'Absent'; ws_sa[f'W{chart_start_row+2}'] = absent | |
| for r in range(chart_start_row, chart_start_row + 3): | |
| ws_sa[f'V{r}'].border = thin_border | |
| ws_sa[f'W{r}'].border = thin_border | |
| # 1. Pie Chart (Pass vs Fail Distribution) | |
| pie = PieChart() | |
| labels = Reference(ws_sa, min_col=22, min_row=chart_start_row, max_row=chart_start_row+2) | |
| data = Reference(ws_sa, min_col=23, min_row=chart_start_row-1, max_row=chart_start_row+2) | |
| pie.add_data(data, titles_from_data=False) | |
| pie.set_categories(labels) | |
| pie.title = "Pass vs Fail Distribution" | |
| pie.width = 10 | |
| pie.height = 7 | |
| ws_sa.add_chart(pie, f"A{chart_start_row}") | |
| # 2. Avg Marks Bar Chart | |
| bar_avg = BarChart() | |
| bar_avg.type = "col" | |
| bar_avg.style = 10 | |
| bar_avg.title = "Average Total Marks per Subject" | |
| bar_avg.x_axis.title = "Subject" | |
| bar_avg.y_axis.title = "Marks" | |
| bar_avg.width = 12 | |
| bar_avg.height = 7 | |
| data_avg = Reference(ws_sa, min_col=8, min_row=1, max_row=ws_sa.max_row) | |
| cats = Reference(ws_sa, min_col=1, min_row=2, max_row=ws_sa.max_row) | |
| bar_avg.add_data(data_avg, titles_from_data=True) | |
| bar_avg.set_categories(cats) | |
| ws_sa.add_chart(bar_avg, f"G{chart_start_row}") | |
| # 3. Pass % Bar Chart | |
| bar_pass = BarChart() | |
| bar_pass.type = "col" | |
| bar_pass.style = 11 | |
| bar_pass.title = "Subject-wise Pass Percentage" | |
| bar_pass.x_axis.title = "Subject" | |
| bar_pass.y_axis.title = "Pass %" | |
| bar_pass.width = 12 | |
| bar_pass.height = 7 | |
| data_pass = Reference(ws_sa, min_col=7, min_row=1, max_row=ws_sa.max_row) | |
| bar_pass.add_data(data_pass, titles_from_data=True) | |
| bar_pass.set_categories(cats) | |
| ws_sa.add_chart(bar_pass, f"P{chart_start_row}") | |
| # Add Subject Mapping Table (Place it below the charts) | |
| start_row = chart_start_row + 15 | |
| ws_sa.cell(row=start_row, column=1, value='Subject Code').font = header_font | |
| ws_sa.cell(row=start_row, column=1).fill = dark_fill | |
| ws_sa.cell(row=start_row, column=2, value='Subject Name').font = header_font | |
| ws_sa.cell(row=start_row, column=2).fill = dark_fill | |
| unique_subjects = df_subs[['Subject_Code', 'Subject_Name']].drop_duplicates().sort_values('Subject_Code') | |
| for idx, (_, row) in enumerate(unique_subjects.iterrows()): | |
| ws_sa.cell(row=start_row + 1 + idx, column=1, value=row['Subject_Code']).border = thin_border | |
| ws_sa.cell(row=start_row + 1 + idx, column=2, value=row['Subject_Name']).border = thin_border | |
| # --- 6. Ineligible Students --- | |
| if not df_subs.empty: | |
| df_ineligible = df_subs[df_subs['Result'].isin(['A', 'NE', 'X'])][['Full_Subject', 'USN', 'Name', 'IA', 'Ext', 'Result']] | |
| df_ineligible.rename(columns={'Full_Subject': 'Subject', 'USN': 'Student ID', 'Ext': 'External'}, inplace=True) | |
| df_ineligible.to_excel(writer, sheet_name='Ineligible Students', index=False) | |
| else: | |
| pd.DataFrame(columns=['Subject', 'Student ID', 'Name', 'IA', 'External', 'Result']).to_excel(writer, sheet_name='Ineligible Students', index=False) | |
| format_sheet(writer.sheets['Ineligible Students']) | |
| # --- 7. Category Breakdown --- | |
| df_cat = df_students[df_students['Class'].isin(['FCD', 'FC', 'SC'])][['USN', 'Name', 'Marks', 'Percentage_Str', 'Class']].copy() | |
| class_map = {'FCD': 'FCD (First Class Distinction)', 'FC': 'First Class', 'SC': 'Second Class'} | |
| df_cat['Class'] = df_cat['Class'].map(class_map) | |
| df_cat.rename(columns={'USN': 'University Seat Number', 'Percentage_Str': 'Percentage', 'Class': 'Category'}, inplace=True) | |
| df_cat.to_excel(writer, sheet_name='Category Breakdown', index=False) | |
| format_sheet(writer.sheets['Category Breakdown']) | |
| # 8-11 | |
| write_standard_sheet(df_students, 'Total Students') | |
| write_standard_sheet(df_students, 'Appeared') | |
| write_standard_sheet(df_students[df_students['Overall_Result'] == 'Pass'], 'Passed') | |
| write_standard_sheet(df_students[df_students['Overall_Result'] == 'Fail'], 'Failed', extra_cols=['Failed_Subjects']) | |
| # 12-14 | |
| write_standard_sheet(df_students[df_students['Failed_Count'] == 1], '1 Subject Fail', extra_cols=['Failed_Subjects']) | |
| write_standard_sheet(df_students[df_students['Failed_Count'] == 2], '2 Subject Fails', extra_cols=['Failed_Subjects']) | |
| write_standard_sheet(df_students[df_students['Failed_Count'] >= 3], '3+ Subject Fails', extra_cols=['Failed_Subjects']) | |
| # 15-17 | |
| write_standard_sheet(df_students[df_students['Class'] == 'FCD'], 'First Class Distinction') | |
| write_standard_sheet(df_students[df_students['Class'] == 'FC'], 'First Class') | |
| write_standard_sheet(df_students[df_students['Class'] == 'SC'], 'Second Class') | |
| output.seek(0) | |
| return output | |