# Reading the uploaded Excel and generating a self-contained HTML quiz file with a start screen.
# Output: /mnt/data/test_conducatori_final.html
import pandas as pd, json, re
from pathlib import Path
xlsx_path = Path('/mnt/data/Pregatire asistenti - Conducator 2025 Intrebari.xlsx')
out_html = Path('/mnt/data/test_conducatori_final.html')
parsed_json = Path('/mnt/data/parsed_questions_final.json')
if not xlsx_path.exists():
raise FileNotFoundError("Excel file not found at: " + str(xlsx_path))
# Read first sheet, no header
df = pd.read_excel(xlsx_path, sheet_name=0, header=None, dtype=str)
df = df.fillna('')
# Determine start row (skip potential header row containing 'Întrebare' text)
start_row = 0
for i, row in df.iterrows():
joined = ' '.join([str(x).lower() for x in row.tolist() if x and str(x).strip()])
if 'întrebare' in joined or 'intrebare' in joined or 'question' in joined or 'nr' in joined:
start_row = i + 1
break
rows = df.iloc[start_row:].values.tolist()
questions = []
for row in rows:
# Expect at least 5 columns: A-E -> indexes 0..4
if len(row) < 5:
# if fewer columns, skip
continue
q = str(row[0]).strip()
a = str(row[1]).strip()
b = str(row[2]).strip()
c = str(row[3]).strip()
correct_raw = str(row[4]).strip().upper()
if not q or (not a and not b and not c):
continue
# Map correct letter to index
mapping = {'A':0,'B':1,'C':2,'0':0,'1':1,'2':2}
correct_index = mapping.get(correct_raw, None)
# If column E contains full text of correct answer instead of letter, find index by matching
if correct_index is None and correct_raw:
# try to match to one of the choices by normalized text
def norm(s): return re.sub(r'\s+',' ', str(s).strip()).lower()
n = norm(correct_raw)
opts = [norm(a), norm(b), norm(c)]
if n in opts:
correct_index = opts.index(n)
else:
# attempt partial match
for idx,opt in enumerate(opts):
if opt and opt in n or n in opt:
correct_index = idx
break
if correct_index is None:
# default to 0 if unknown (shouldn't happen often)
correct_index = 0
questions.append({"question": re.sub(r'\s+',' ', q), "choices":[re.sub(r'\s+',' ',a), re.sub(r'\s+',' ',b), re.sub(r'\s+',' ',c)], "correct_index": correct_index})
# Deduplicate by question text while preserving order
seen = set(); unique = []
for q in questions:
key = q['question'][:200]
if key in seen: continue
seen.add(key); unique.append(q)
questions = unique
# Save parsed JSON for inspection
parsed_json.write_text(json.dumps(questions, ensure_ascii=False, indent=2), encoding='utf-8')
if len(questions) == 0:
raise RuntimeError("No questions parsed from the Excel file. Please check the file format.")
# Prepare data JSON to embed in HTML
data_json = json.dumps(questions, ensure_ascii=False)
# Build HTML content with a start screen and required features
html = """<!doctype html>
<html lang="ro">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
<title>Test pentru Conducători</title>
<style>
body{font-family:Arial,Helvetica,sans-serif;background:#f6fbff;color:#0b2340;margin:0;padding:0;display:flex;align-items:center;justify-content:center;min-height:100vh;}
.card{background:#fff;border-radius:10px;box-shadow:0 8px 30px rgba(11,35,64,0.12);width:900px;max-width:96%;padding:28px;}
h1{margin:0 0 8px;font-size:22px;color:#08315a;text-align:center;}
.meta{color:#28507a;margin-bottom:16px;text-align:center;}
.center{display:flex;flex-direction:column;align-items:center;gap:12px;}
.start-desc{max-width:720px;text-align:center;color:#28507a;}
button.primary{background:#1e75b8;color:white;border:none;padding:12px 20px;border-radius:8px;font-size:16px;cursor:pointer;box-shadow:0 6px 18px rgba(30,117,184,0.18);}
button.secondary{background:#fff;color:#1e75b8;border:1px solid #cfe3f5;padding:10px 16px;border-radius:8px;font-size:14px;cursor:pointer;}
.question-area{min-height:160px;}
.question-text{font-size:18px;margin-bottom:12px;}
.choices{list-style:none;padding:0;margin:0;}
.choice{margin-bottom:10px;}
button.choice-btn{display:block;width:100%;text-align:left;padding:12px;border-radius:8px;border:1px solid #cfe3f5;background:#fff;cursor:pointer;font-size:15px;}
button.choice-btn.correct{border-color:#2e8b57;background:#e6faf0;}
button.choice-btn.wrong{border-color:#d9534f;background:#fdecea;}
.controls{display:flex;justify-content:space-between;align-items:center;margin-top:16px;}
.timer{font-weight:700;}
.progress{height:8px;background:#e6f0fb;border-radius:4px;overflow:hidden;margin-top:12px;}
.progress > div{height:100%;background:#2b7fc1;width:0%;transition:width 0.3s;}
.result{text-align:center;}
.wrong-list{text-align:left;margin-top:16px;}
.small{font-size:13px;color:#4b6b85;}
@media (max-width:600px){ .card{padding:16px;} h1{font-size:18px;} }
</style>
</head>
<body>
<div class="card" id="container" role="main" aria-labelledby="title">
<h1 id="title">Test pentru Conducători</h1>
<div id="startScreen" class="center">
<p class="meta">Test de verificare a cunoștințelor — 40 întrebări alese aleator din bancă, 60s / întrebare.</p>
<p class="start-desc">Reguli: testul este anonim. După terminare vei vedea scorul, statusul Admis/Respins (minim 28/40) și o listă cu întrebările la care ai greșit.</p>
<div style="display:flex;gap:12px;margin-top:8px;">
<button id="startBtn" class="primary">Începe testul</button>
<button id="previewBtn" class="secondary">Previzualizează întrebări (demo)</button>
</div>
</div>
<div id="quiz" style="display:none;">
<div class="meta small">Progres: <span id="qnum">0</span>/40</div>
<div class="question-area">
<div class="question-text" id="questionText"></div>
<ul class="choices" id="choicesList"></ul>
<div class="progress" aria-hidden="true"><div id="progressBar"></div></div>
</div>
<div class="controls">
<div class="timer">Timp: <span id="timer">60</span>s</div>
<div><button id="nextBtn" class="secondary" disabled>Următoarea întrebare</button></div>
</div>
</div>
<div id="final" style="display:none;" class="result">
<h2 id="finalResult"></h2>
<p id="scoreText"></p>
<div id="wrongContainer"></div>
<p class="small">Rezultatele sunt păstrate doar local în browser (anonim).</p>
<p class="small">Pentru promovare: minim 28/40 corecte.</p>
<div style="text-align:center;margin-top:12px;">
<button id="restartBtn" class="primary">Refă test (alt set de întrebări)</button>
</div>
</div>
</div>
<script>
const rawQuestions = """ + data_json + """;
const TOTAL_QUESTIONS = 40;
const TIME_PER_Q = 60; // seconds per question
const PASS_THRESHOLD = 28;
function shuffleArray(a){ for(let i=a.length-1;i>0;i--){ const j=Math.floor(Math.random()*(i+1)); let tmp=a[i]; a[i]=a[j]; a[j]=tmp;} return a; }
let quizQuestions = []; // will hold selected 40 questions
let currentIndex = 0;
let userAnswers = [];
let timer = TIME_PER_Q;
let timerInterval = null;
const startBtn = document.getElementById('startBtn');
const previewBtn = document.getElementById('previewBtn');
const quizDiv = document.getElementById('quiz');
const startScreen = document.getElementById('startScreen');
const finalDiv = document.getElementById('final');
const qnumSpan = document.getElementById('qnum');
const questionText = document.getElementById('questionText');
const choicesList = document.getElementById('choicesList');
const timerSpan = document.getElementById('timer');
const nextBtn = document.getElementById('nextBtn');
const progressBar = document.getElementById('progressBar');
const finalResult = document.getElementById('finalResult');
const scoreText = document.getElementById('scoreText');
const wrongContainer = document.getElementById('wrongContainer');
const restartBtn = document.getElementById('restartBtn');
function startQuizDemo(){ // quick demo with 5 questions to preview layout
const pool = rawQuestions.slice();
shuffleArray(pool);
quizQuestions = pool.slice(0, Math.min(5, pool.length)).map(q=>{
const idxMap = [0,1,2]; shuffleArray(idxMap);
const newChoices = idxMap.map(i=>q.choices[i]);
const correctText = q.choices[q.correct_index];
const newCorrect = newChoices.findIndex(c=>c===correctText);
return {question:q.question, choices:newChoices, correct_index:newCorrect};
});
userAnswers = new Array(quizQuestions.length).fill(null);
startScreen.style.display='none'; finalDiv.style.display='none'; quizDiv.style.display='block';
showQuestion(0);
}
function startQuiz(){
const pool = rawQuestions.slice(); shuffleArray(pool);
quizQuestions = pool.slice(0, Math.min(TOTAL_QUESTIONS, pool.length)).map(q=>{
const idxMap = [0,1,2]; shuffleArray(idxMap);
const newChoices = idxMap.map(i=>q.choices[i]);
const correctText = q.choices[q.correct_index];
const newCorrect = newChoices.findIndex(c=>c===correctText);
return {question:q.question, choices:newChoices, correct_index:newCorrect};
});
userAnswers = new Array(quizQuestions.length).fill(null);
startScreen.style.display='none'; finalDiv.style.display='none'; quizDiv.style.display='block';
showQuestion(0);
}
function showQuestion(i){
if(i>=quizQuestions.length){ finishQuiz(); return; }
currentIndex = i;
qnumSpan.textContent = (i+1) + "/" + quizQuestions.length;
const q = quizQuestions[i];
questionText.textContent = q.question;
choicesList.innerHTML = '';
q.choices.forEach((ch, idx)=>{
const li = document.createElement('li'); li.className='choice';
const btn = document.createElement('button'); btn.className='choice-btn'; btn.type='button';
btn.innerHTML = (String.fromCharCode(65+idx)) + '. ' + ch;
btn.onclick = ()=>selectAnswer(idx, btn);
btn.setAttribute('data-idx', idx);
choicesList.appendChild(li); li.appendChild(btn);
});
resetTimer();
nextBtn.disabled = true;
updateProgress();
}
function selectAnswer(idx, btn){
if(userAnswers[currentIndex] !== null) return;
userAnswers[currentIndex] = idx;
const q = quizQuestions[currentIndex];
const buttons = document.querySelectorAll('#choicesList .choice-btn');
buttons.forEach(b=> { const bi = parseInt(b.getAttribute('data-idx')); b.disabled = true;
if(bi === q.correct_index){ b.classList.add('correct'); }
if(bi === idx && bi !== q.correct_index){ b.classList.add('wrong'); }
});
clearInterval(timerInterval);
setTimeout(()=>{ nextQuestion(); }, 700);
}
function resetTimer(){
clearInterval(timerInterval);
timer = TIME_PER_Q;
timerSpan.textContent = timer;
timerInterval = setInterval(()=>{
timer--;
timerSpan.textContent = timer;
const pct = (1 - timer/TIME_PER_Q)*100;
progressBar.style.width = pct + '%';
if(timer<=0){
clearInterval(timerInterval);
// reveal correct and move on
const q = quizQuestions[currentIndex];
const buttons = document.querySelectorAll('#choicesList .choice-btn');
buttons.forEach(b=> { const bi = parseInt(b.getAttribute('data-idx')); b.disabled = true; if(bi === q.correct_index) b.classList.add('correct'); });
setTimeout(()=> nextQuestion(), 700);
}
}, 1000);
}
function nextQuestion(){ const next = currentIndex + 1; if(next < quizQuestions.length){ showQuestion(next); } else { finishQuiz(); } }
nextBtn.addEventListener('click', ()=>{ nextQuestion(); });
document.addEventListener('keydown', (e)=>{ if(finalDiv.style.display !== 'none') return; const k = e.key; if(['1','2','3'].includes(k)){ const btn = document.querySelector(`#choicesList .choice-btn[data-idx='${parseInt(k)-1}']`); if(btn) btn.click(); } });
function updateProgress(){ const pct = Math.round((currentIndex/quizQuestions.length)*100); progressBar.style.width = pct + '%'; nextBtn.disabled = false; }
function finishQuiz(){
clearInterval(timerInterval);
quizDiv.style.display = 'none'; finalDiv.style.display = 'block';
let correct = 0; const wrongs = [];
quizQuestions.forEach((q,i)=>{
const ua = userAnswers[i];
if(ua === q.correct_index) correct++;
else { wrongs.push({index:i, question:q.question, your: ua===null? null: q.choices[ua], correct: q.choices[q.correct_index]}); }
});
const passed = correct >= PASS_THRESHOLD;
finalResult.textContent = passed ? 'Admis' : 'Respins';
scoreText.textContent = `Ai obținut ${correct} / ${quizQuestions.length} corecte.`;
if(wrongs.length === 0){
wrongContainer.innerHTML = '<p>Toate răspunsurile sunt corecte — felicitări!</p>';
} else {
const div = document.createElement('div'); div.className='wrong-list';
const title = document.createElement('h3'); title.textContent = 'Întrebări greșite și răspunsurile corecte:'; div.appendChild(title);
wrongs.forEach(w=>{
const p = document.createElement('div'); p.style.marginBottom='10px';
const yourText = w.your === null ? '<em>Nu a răspuns</em>' : '<strong>Răspunsul tău:</strong> ' + escapeHtml(w.your);
p.innerHTML = `<div><strong>Întrebarea ${w.index+1}:</strong> ${escapeHtml(w.question)}</div>
<div style="margin-left:10px;color:#d9534f;">${yourText}</div>
<div style="margin-left:10px;color:#2e8b57;"><strong>Răspuns corect:</strong> ${escapeHtml(w.correct)}</div>`;
div.appendChild(p);
});
wrongContainer.appendChild(div);
}
}
function escapeHtml(text){ if(!text) return ''; return text.replace(/&/g,'&').replace(/</g,'<').replace(/>/g,'>'); }
restartBtn.addEventListener('click', ()=>{ location.reload(); });
startBtn.addEventListener('click', ()=>{ startQuiz(); });
previewBtn.addEventListener('click', ()=>{ startQuizDemo(); });
// Start with start screen visible
</script>
</body>
</html>
"""
out_html.write_text(html, encoding='utf-8')
print("Generated HTML saved to:", out_html)
print("Parsed JSON saved to:", parsed_json)
print("Number of parsed questions:", len(questions))
Pregatire asistenti 