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