import os from openpyxl import Workbook from openpyxl.styles import ( Font, PatternFill, Alignment, Border, Side, GradientFill ) from openpyxl.utils import get_column_letter from openpyxl.formatting.rule import ColorScaleRule, DataBarRule from openpyxl.worksheet.hyperlink import Hyperlink from colorama import Fore, Style SCORE_COLORS = { (8, 10): ("1A7A1A", "E8F8E8"), # Green - dark text, light bg (6, 7): ("7A5A00", "FFF8E0"), # Amber (4, 5): ("7A3A00", "FFF0E0"), # Orange (0, 3): ("7A1A1A", "FFF0F0"), # Red } PRIORITY_COLORS = { "High": "C6EFCE", "Medium": "FFEB9C", "Low": "FFCCCC", } HEADERS = [ ("Rank", 5), ("Job Title", 28), ("Company", 20), ("Location", 18), ("Platform", 12), ("Salary Range", 18), ("Experience Required", 18), ("Posted Date", 14), ("Relevance Score", 16), ("ATS Before (%)", 14), ("ATS After (%)", 14), ("ATS Improvement", 14), ("Skills Match %", 14), ("Experience Match", 18), ("Application Priority", 18), ("Matching Skills", 35), ("Missing Skills", 30), ("Key Strengths", 30), ("Recommendation", 45), ("ATS Keywords", 35), ("Resume Generated", 16), ("Resume Path", 40), ("Job URL", 50), ("Application Status", 20), ("Notes", 30), ] class ExcelReporter: def __init__(self, output_path: str): self.output_path = output_path os.makedirs(os.path.dirname(output_path), exist_ok=True) def generate(self, assessed_jobs: list[dict]) -> str: print(f"\n{Fore.CYAN}Generating Excel report...{Style.RESET_ALL}") wb = Workbook() self._create_summary_sheet(wb, assessed_jobs) self._create_all_jobs_sheet(wb, assessed_jobs) self._create_top_matches_sheet(wb, assessed_jobs) self._create_tracker_sheet(wb, assessed_jobs) wb.save(self.output_path) print(f"{Fore.GREEN}Report saved: {self.output_path}{Style.RESET_ALL}") # Also emit a flat CSV next to the workbook for easy import / persistence. try: self.csv_path = self._write_csv(assessed_jobs) print(f"{Fore.GREEN}CSV saved: {self.csv_path}{Style.RESET_ALL}") except Exception as e: self.csv_path = "" print(f"{Fore.YELLOW}CSV export skipped: {e}{Style.RESET_ALL}") return self.output_path def _write_csv(self, jobs: list[dict]) -> str: """Write a flat one-row-per-job CSV alongside the Excel report.""" import csv csv_path = os.path.splitext(self.output_path)[0] + ".csv" columns = [ ("rank", "Rank"), ("title", "Job Title"), ("company", "Company"), ("location", "Location"), ("platform", "Platform"), ("salary", "Salary"), ("relevance_score", "Relevance Score"), ("application_priority", "Application Priority"), ("ats_score_before", "ATS Before (%)"), ("ats_score_after", "ATS After (%)"), ("ats_improvement", "ATS Improvement"), ("independent_jd_match", "Independent Score"), ("status", "Status"), ("resume_generated", "Resume Generated"), ("matching_skills", "Matching Skills"), ("missing_skills", "Missing Skills"), ("ats_keywords", "ATS Keywords"), ("recommendation", "Recommendation"), ("resume_path", "Resume Path"), ("resume_pdf_path", "Resume PDF Path"), ("url", "Job URL"), ] def _cell(v): if isinstance(v, (list, tuple)): return ", ".join(str(x) for x in v) if isinstance(v, dict): return "; ".join(f"{k}={val}" for k, val in v.items()) return "" if v is None else str(v) with open(csv_path, "w", encoding="utf-8-sig", newline="") as f: w = csv.writer(f) w.writerow([h for _, h in columns]) for i, job in enumerate(jobs, 1): row = [] for key, _ in columns: row.append(str(i) if key == "rank" else _cell(job.get(key, ""))) w.writerow(row) return csv_path # ────────────────────────────────────────────────────────────── # SHEET 1: SUMMARY DASHBOARD # ────────────────────────────────────────────────────────────── def _create_summary_sheet(self, wb: Workbook, jobs: list[dict]): ws = wb.active ws.title = "📊 Summary" ws.sheet_view.showGridLines = False total = len(jobs) high = sum(1 for j in jobs if j.get("relevance_score", 0) >= 8) medium = sum(1 for j in jobs if 6 <= j.get("relevance_score", 0) <= 7) low = sum(1 for j in jobs if j.get("relevance_score", 0) < 6) avg_score = sum(j.get("relevance_score", 0) for j in jobs) / total if total else 0 resumes_gen = sum(1 for j in jobs if j.get("resume_generated") == "Yes") # Title ws.merge_cells("B2:F2") ws["B2"] = "JOB SEARCH RESULTS — DASHBOARD" ws["B2"].font = Font(name="Calibri", size=18, bold=True, color="1648BE") ws["B2"].alignment = Alignment(horizontal="center") stats = [ ("Total Jobs Found", total, "4472C4", "FFFFFF"), ("High Priority (8-10)", high, "70AD47", "FFFFFF"), ("Good Match (6-7)", medium, "ED7D31", "FFFFFF"), ("Lower Priority (<6)", low, "FF0000", "FFFFFF"), ("Avg Relevance Score", f"{avg_score:.1f}/10", "7030A0", "FFFFFF"), ("Resumes Generated", resumes_gen, "1648BE", "FFFFFF"), ] row = 4 for label, value, bg, fg in stats: ws.merge_cells(f"B{row}:C{row}") ws[f"B{row}"] = label ws[f"B{row}"].font = Font(name="Calibri", size=11, bold=True, color=fg) ws[f"B{row}"].fill = PatternFill("solid", fgColor=bg) ws[f"B{row}"].alignment = Alignment(horizontal="left", indent=1) ws[f"D{row}"] = value ws[f"D{row}"].font = Font(name="Calibri", size=14, bold=True, color=bg) ws[f"D{row}"].alignment = Alignment(horizontal="center") row += 1 # Platform breakdown row += 1 ws[f"B{row}"] = "Jobs by Platform" ws[f"B{row}"].font = Font(name="Calibri", size=12, bold=True, color="1648BE") row += 1 from collections import Counter platform_counts = Counter(j.get("platform", "Unknown") for j in jobs) for platform, count in platform_counts.most_common(): ws[f"B{row}"] = platform ws[f"C{row}"] = count ws[f"B{row}"].font = Font(name="Calibri", size=11) ws[f"C{row}"].font = Font(name="Calibri", size=11, bold=True) row += 1 # Column widths ws.column_dimensions["B"].width = 28 ws.column_dimensions["C"].width = 20 ws.column_dimensions["D"].width = 15 # ────────────────────────────────────────────────────────────── # SHEET 2: ALL JOBS # ────────────────────────────────────────────────────────────── def _create_all_jobs_sheet(self, wb: Workbook, jobs: list[dict]): ws = wb.create_sheet("📋 All Jobs") self._write_jobs_sheet(ws, jobs, "ALL JOBS — RANKED BY RELEVANCE SCORE") # ────────────────────────────────────────────────────────────── # SHEET 3: TOP MATCHES (score >= 6) # ────────────────────────────────────────────────────────────── def _create_top_matches_sheet(self, wb: Workbook, jobs: list[dict]): top = [j for j in jobs if j.get("relevance_score", 0) >= 6] ws = wb.create_sheet("⭐ Top Matches") self._write_jobs_sheet(ws, top, f"TOP MATCHES (Score ≥ 6) — {len(top)} Jobs") # ────────────────────────────────────────────────────────────── # SHEET 4: APPLICATION TRACKER # ────────────────────────────────────────────────────────────── def _create_tracker_sheet(self, wb: Workbook, jobs: list[dict]): ws = wb.create_sheet("✅ Application Tracker") ws.sheet_view.showGridLines = False tracker_headers = [ ("Rank", 6), ("Job Title", 28), ("Company", 20), ("Platform", 12), ("Score", 8), ("Priority", 12), ("Applied Date", 14), ("Status", 20), ("HR Contact", 22), ("Interview Date", 15), ("Offer/Feedback", 30), ("Job URL", 50), ] # Header row self._write_header_row(ws, tracker_headers, row=1) top = [j for j in jobs if j.get("relevance_score", 0) >= 6] status_options = ["Not Applied", "Applied", "Shortlisted", "Interview Scheduled", "Offer Received", "Rejected", "On Hold"] for idx, job in enumerate(top, 1): r = idx + 1 score = job.get("relevance_score", 0) color = self._score_row_color(score) cells = [ idx, job.get("title", ""), job.get("company", ""), job.get("platform", ""), score, job.get("application_priority", ""), "", # Applied Date (to be filled) "Not Applied", "", # HR Contact "", # Interview Date "", # Offer/Feedback job.get("url", ""), ] for col, value in enumerate(cells, 1): cell = ws.cell(row=r, column=col, value=value) cell.fill = PatternFill("solid", fgColor=color) cell.font = Font(name="Calibri", size=10) cell.alignment = Alignment(wrap_text=True, vertical="top") if col == len(cells) and value: # URL column cell.hyperlink = value cell.font = Font(name="Calibri", size=10, color="0563C1", underline="single") cell.value = "Open Job" for col_idx, (_, width) in enumerate(tracker_headers, 1): ws.column_dimensions[get_column_letter(col_idx)].width = width ws.row_dimensions[1].height = 30 ws.freeze_panes = "A2" # ────────────────────────────────────────────────────────────── # SHARED HELPERS # ────────────────────────────────────────────────────────────── def _write_jobs_sheet(self, ws, jobs: list[dict], title: str): ws.sheet_view.showGridLines = False # Title row ws.merge_cells(f"A1:{get_column_letter(len(HEADERS))}1") ws["A1"] = title ws["A1"].font = Font(name="Calibri", size=14, bold=True, color="FFFFFF") ws["A1"].fill = PatternFill("solid", fgColor="1648BE") ws["A1"].alignment = Alignment(horizontal="center") ws.row_dimensions[1].height = 28 # Header row self._write_header_row(ws, HEADERS, row=2) for idx, job in enumerate(jobs, 1): r = idx + 2 score = job.get("relevance_score", 0) row_color = self._score_row_color(score) def _pct(val): if val is None or val == "": return "—" return f"{int(val)}%" ats_b = job.get("ats_score_before") ats_a = job.get("ats_score_after") improv = job.get("ats_improvement", 0) or 0 improv_str = f"+{improv}pp" if improv > 0 else ("—" if improv == 0 else f"{improv}pp") values = [ idx, # col 1 Rank job.get("title", ""), # col 2 Job Title job.get("company", ""), # col 3 Company job.get("location", ""), # col 4 Location job.get("platform", ""), # col 5 Platform job.get("salary", "Not specified"), # col 6 Salary Range job.get("experience_required", ""), # col 7 Experience Required job.get("posted_date", ""), # col 8 Posted Date score, # col 9 Relevance Score _pct(ats_b), # col 10 ATS Before (%) _pct(ats_a), # col 11 ATS After (%) improv_str, # col 12 ATS Improvement job.get("skills_match_percentage", ""), # col 13 Skills Match % job.get("experience_match", ""), # col 14 Experience Match job.get("application_priority", ""), # col 15 Application Priority job.get("matching_skills", ""), # col 16 Matching Skills job.get("missing_skills", ""), # col 17 Missing Skills job.get("key_strengths", ""), # col 18 Key Strengths job.get("recommendation", ""), # col 19 Recommendation job.get("ats_keywords", ""), # col 20 ATS Keywords job.get("resume_generated", "No"), # col 21 Resume Generated job.get("resume_path", ""), # col 22 Resume Path job.get("url", ""), # col 23 Job URL "Not Applied", # col 24 Application Status "", # col 25 Notes ] for col, value in enumerate(values, 1): cell = ws.cell(row=r, column=col, value=value) cell.fill = PatternFill("solid", fgColor=row_color) cell.font = Font(name="Calibri", size=10) cell.alignment = Alignment(wrap_text=True, vertical="top") cell.border = Border( bottom=Side(style="thin", color="D9D9D9"), right=Side(style="thin", color="D9D9D9"), ) # Score cell — colored badge (col 9) if col == 9: score_bg, score_fg = self._score_cell_style(score) cell.fill = PatternFill("solid", fgColor=score_bg) cell.font = Font(name="Calibri", size=11, bold=True, color=score_fg) cell.alignment = Alignment(horizontal="center", vertical="center") # ATS columns center-aligned (cols 10-12) if col in (10, 11, 12): cell.alignment = Alignment(horizontal="center", vertical="center") # URL → hyperlink (col 23) if col == 23 and value: cell.hyperlink = str(value) cell.font = Font(name="Calibri", size=10, color="0563C1", underline="single") cell.value = "View Job" # Priority coloring (col 15) if col == 15 and value in PRIORITY_COLORS: cell.fill = PatternFill("solid", fgColor=PRIORITY_COLORS[value]) cell.font = Font(name="Calibri", size=10, bold=True) ws.row_dimensions[r].height = 50 # Column widths for col_idx, (_, width) in enumerate(HEADERS, 1): ws.column_dimensions[get_column_letter(col_idx)].width = width ws.freeze_panes = "A3" ws.auto_filter.ref = f"A2:{get_column_letter(len(HEADERS))}{len(jobs) + 2}" def _write_header_row(self, ws, headers: list, row: int): for col, (label, _) in enumerate(headers, 1): cell = ws.cell(row=row, column=col, value=label) cell.font = Font(name="Calibri", size=11, bold=True, color="FFFFFF") cell.fill = PatternFill("solid", fgColor="1648BE") cell.alignment = Alignment(horizontal="center", vertical="center", wrap_text=True) cell.border = Border( bottom=Side(style="medium", color="FFFFFF"), right=Side(style="thin", color="8EAADB"), ) ws.row_dimensions[row].height = 35 def _score_row_color(self, score: int) -> str: if score >= 8: return "F0FFF0" elif score >= 6: return "FFFDE7" elif score >= 4: return "FFF3E0" return "FFF8F8" def _score_cell_style(self, score: int) -> tuple[str, str]: if score >= 8: return ("1A7A1A", "FFFFFF") elif score >= 6: return ("FF8C00", "FFFFFF") elif score >= 4: return ("CC5500", "FFFFFF") return ("CC0000", "FFFFFF")