Untitled

# Trying once more to fix and format the Excel sheet correctly

import openpyxl
from openpyxl.styles import Font

# Create a new workbook and active worksheet
wb = openpyxl.Workbook()
ws = wb.active

# Set column headers
headers = ["Subject", "Year", "Paper", "Date Attempted", "Raw Score (%)", 
           "Weakest Topics", "Main Mistakes", "Next Steps", "Redo Score (%)"]
ws.append(headers)

# Apply bold font to headers
for col in range(1, len(headers) + 1):
    ws.cell(row=1, column=col).font = Font(bold=True)

# Populate the worksheet with data
for year in years:
    for subject in subjects:
        if subject == "Maths (Edexcel)":
            for paper in maths_papers:
                ws.append([subject, year, paper, "", "", "", "", "", ""])
        else:
            for paper in papers:
                ws.append([subject, year, paper, "", "", "", "", "", ""])

# Adjust column width for better readability
for col in ws.columns:
    max_length = 0
    col_letter = col[0].column_letter  # Get column letter
    for cell in col:
        try:
            if cell.value:
                max_length = max(max_length, len(str(cell.value)))
        except:
            pass
    ws.column_dimensions[col_letter].width = max_length + 2  # Adjust width

# Save the properly formatted file
file_path = "/mnt/data/Past_Paper_Tracker_Formatted_v2.xlsx"
wb.save(file_path)

# Return the file path for download
file_path