import os
import pandas as pd
from openpyxl import load_workbook
from pptx import Presentation
from pptx.util import Inches, Pt
from langchain_groq import ChatGroq
from langgraph.graph import StateGraph
from dataclasses import dataclass, field
from typing import Dict, Any, Optional
# Set your Groq API key and initialize the LLM
GROQ_API_KEY = "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"
os.environ["GROQ_API_KEY"] = GROQ_API_KEY
llm = ChatGroq(model="mixtral-8x7b-32768")
# File paths for the Excel file and the output PowerPoint file
excel_file = "/content/drive/MyDrive/Data/school_results.xlsx"
ppt_file = "/content/drive/MyDrive/Data/Topper_Presentation.pptx"
# Define the state schema using a dataclass
@dataclass
class SchoolResultsState:
data: Dict[str, pd.DataFrame] = field(default_factory=dict)
toppers_df: Optional[pd.DataFrame] = None
result: str = ""
ppt_result: str = ""
# Agent 1: Reader Agent
def read_school_results(state: SchoolResultsState) -> Dict[str, Any]:
wb = load_workbook(excel_file)
classes = wb.sheetnames
data = {}
for class_name in classes:
if class_name != "Toppers": # Exclude previous Toppers sheet if exists
df = pd.read_excel(excel_file, sheet_name=class_name)
data[class_name] = df
return {"data": data}
# Agent 2: Toppers Finder Agent
def find_toppers(state: SchoolResultsState) -> Dict[str, Any]:
toppers_data = []
for class_name, df in state.data.items():
# Convert all columns except the first (assumed non-numeric) to numeric
numeric_cols = df.columns[1:]
df[numeric_cols] = df[numeric_cols].apply(pd.to_numeric, errors='coerce')
# Sum subject marks
df["Total"] = df[numeric_cols].sum(axis=1)
top_5_students = df.nlargest(5, "Total")
# Insert a 'Class' column as a list of the class name repeated for each row
top_5_students.insert(0, "Class", [class_name] * len(top_5_students))
toppers_data.append(top_5_students)
return {"toppers_df": pd.concat(toppers_data, ignore_index=True)}
# Agent 3: Verifier & Publisher Agent
def verify_and_publish(state: SchoolResultsState) -> Dict[str, Any]:
wb = load_workbook(excel_file)
if "Toppers" in wb.sheetnames:
del wb["Toppers"]
with pd.ExcelWriter(excel_file, engine="openpyxl", mode="a") as writer:
state.toppers_df.to_excel(writer, sheet_name="Toppers", index=False)
return {"result": "Toppers list updated successfully in the Excel file."}
# Agent 4: Export Topper to PowerPoint Agent
def export_topper_to_ppt(state: SchoolResultsState) -> Dict[str, Any]:
# Read the "Toppers" worksheet from the Excel file
toppers_df = pd.read_excel(excel_file, sheet_name="Toppers")
# Create a new PowerPoint presentation and add a blank slide
prs = Presentation()
blank_slide_layout = prs.slide_layouts[6]
slide = prs.slides.add_slide(blank_slide_layout)
# Add a header textbox with "Toppers Result"
header_left = Inches(0.5)
header_top = Inches(0.2)
header_width = prs.slide_width - Inches(1)
header_height = Inches(0.6)
header_box = slide.shapes.add_textbox(header_left, header_top, header_width,
header_height)
header_tf = header_box.text_frame
header_tf.text = "Toppers Result"
header_tf.paragraphs[0].font.size = Pt(12)
# Get slide dimensions (for table placement)
slide_width = prs.slide_width
slide_height = prs.slide_height
# Determine the number of rows (add one extra row for header) and columns
rows, cols = toppers_df.shape
table_rows = rows + 1
table_cols = cols
# Define margins for the table on the slide (placed below the header)
left = Inches(0.5)
top = Inches(1)
width = slide_width - Inches(1) # leave margin on both sides
height = slide_height - Inches(1.5) # leave margin; adjust as needed
# Add a table shape to the slide with the calculated position and size
table = slide.shapes.add_table(table_rows, table_cols, left, top, width,
height).table
# Set the header row in the table and adjust font size to 12 pt
for col_idx, column in enumerate(toppers_df.columns):
cell = table.cell(0, col_idx)
cell.text = str(column)
for paragraph in cell.text_frame.paragraphs:
paragraph.font.size = Pt(12)
# Populate the table with data from the toppers DataFrame and set font size
for row_idx, row in toppers_df.iterrows():
for col_idx, value in enumerate(row):
cell = table.cell(row_idx + 1, col_idx)
cell.text = str(value)
for paragraph in cell.text_frame.paragraphs:
paragraph.font.size = Pt(12)
# Save the PowerPoint presentation
prs.save(ppt_file)
return {"ppt_result": f"Presentation saved to {ppt_file}"}
# Define the graph workflow
workflow = StateGraph(SchoolResultsState)
workflow.add_node("read_school_results", read_school_results)
workflow.add_node("find_toppers", find_toppers)
workflow.add_node("verify_and_publish", verify_and_publish)
workflow.add_node("export_topper_to_ppt", export_topper_to_ppt)
# Define execution order: chain all agents in sequence
workflow.add_edge("read_school_results", "find_toppers")
workflow.add_edge("find_toppers", "verify_and_publish")
workflow.add_edge("verify_and_publish", "export_topper_to_ppt")
# Set entry point and finish point for the graph
workflow.set_entry_point("read_school_results")
workflow.set_finish_point("export_topper_to_ppt")
executable = workflow.compile()
# Execute the graph with the initial state
initial_state = SchoolResultsState()
final_state = executable.invoke(initial_state)
# Output the results from the final state
print(final_state["result"])
print(final_state["ppt_result"])
