254 lines
8.3 KiB
Python
254 lines
8.3 KiB
Python
#!/usr/bin/env python3
|
||
"""Parse Designing detailed Dehradun Agenda.xlsx into nested session/topic JSON."""
|
||
import json
|
||
import re
|
||
import zipfile
|
||
import xml.etree.ElementTree as ET
|
||
from pathlib import Path
|
||
|
||
XLSX = Path(
|
||
__import__('os').environ.get(
|
||
'DEHRADUN_AGENDA_XLSX',
|
||
'/Volumes/Storage/Downloads/Designing detailed Dehradun Agenda.xlsx',
|
||
)
|
||
)
|
||
NS = {'m': 'http://schemas.openxmlformats.org/spreadsheetml/2006/main'}
|
||
TOPIC_TYPES = {
|
||
'LECTURE',
|
||
'PANEL DISCUSSION',
|
||
'JAMA',
|
||
'INTRODUCTION',
|
||
'TALK',
|
||
'TALK + Q&A',
|
||
'CASE PRESENTATION+DISCUSSION',
|
||
'CASE PRESENTATION + DISCUSSION',
|
||
}
|
||
DAY_DATES = ['September 19, 2026', 'September 20, 2026']
|
||
|
||
|
||
def load_rows(path):
|
||
with zipfile.ZipFile(path) as z:
|
||
ss_root = ET.fromstring(z.read('xl/sharedStrings.xml'))
|
||
strings = []
|
||
for si in ss_root.findall('m:si', NS):
|
||
strings.append(''.join(t.text or '' for t in si.findall('.//m:t', NS)))
|
||
sheet = ET.fromstring(z.read('xl/worksheets/sheet1.xml'))
|
||
rows = []
|
||
for row in sheet.findall('m:sheetData/m:row', NS):
|
||
cells = {}
|
||
for c in row.findall('m:c', NS):
|
||
ref = c.get('r')
|
||
col = re.match(r'[A-Z]+', ref).group(0)
|
||
t = c.get('t')
|
||
v = c.find('m:v', NS)
|
||
is_el = c.find('m:is', NS)
|
||
val = ''
|
||
if t == 's' and v is not None:
|
||
val = strings[int(v.text)]
|
||
elif t == 'inlineStr' and is_el is not None:
|
||
val = ''.join(t2.text or '' for t2 in is_el.findall('.//m:t', NS))
|
||
elif v is not None:
|
||
val = v.text or ''
|
||
cells[col] = val
|
||
rows.append((int(row.get('r')), cells))
|
||
return rows
|
||
|
||
|
||
def clean(val):
|
||
return re.sub(r'[ \t]+', ' ', str(val or '')).replace('\xa0', ' ').strip()
|
||
|
||
|
||
def normalize_clock(part, day_index):
|
||
text = clean(part).replace(' :', ':')
|
||
m = re.search(r'(\d{1,2}):\s*(\d{2})\s*(AM|PM)?', text, re.I)
|
||
if not m:
|
||
return text
|
||
hour = int(m.group(1))
|
||
minute = int(m.group(2))
|
||
suffix = (m.group(3) or '').upper()
|
||
if day_index == 0 and 1 <= hour <= 6:
|
||
suffix = 'PM'
|
||
elif day_index >= 1:
|
||
if 9 <= hour <= 11:
|
||
suffix = 'AM'
|
||
elif hour == 12:
|
||
suffix = 'PM'
|
||
elif not suffix:
|
||
suffix = 'PM' if 1 <= hour <= 6 else 'AM'
|
||
hour12 = hour if hour <= 12 else hour - 12
|
||
return f'{hour12}:{minute:02d} {suffix}'
|
||
|
||
|
||
def normalize_range(raw, day_index):
|
||
text = clean(raw).replace(' :', ':')
|
||
if not text:
|
||
return ''
|
||
parts = re.split(r'\s*[–—-]\s*', text)
|
||
if len(parts) < 2:
|
||
return normalize_clock(text, day_index)
|
||
return f'{normalize_clock(parts[0], day_index)} – {normalize_clock(parts[1], day_index)}'
|
||
|
||
|
||
def minutes_between(range_str):
|
||
parts = re.split(r'\s*[–—]\s*', range_str)
|
||
if len(parts) < 2:
|
||
return ''
|
||
clocks = []
|
||
for part in parts[:2]:
|
||
m = re.search(r'(\d{1,2}):(\d{2})\s*(AM|PM)', part, re.I)
|
||
if not m:
|
||
return ''
|
||
hour = int(m.group(1)) % 12
|
||
if m.group(3).upper() == 'PM':
|
||
hour += 12
|
||
clocks.append(hour * 60 + int(m.group(2)))
|
||
delta = clocks[1] - clocks[0]
|
||
return str(delta) if delta > 0 else ''
|
||
|
||
|
||
def is_topic_type(theme):
|
||
return clean(theme).upper() in TOPIC_TYPES or clean(theme).upper().startswith('TALK')
|
||
|
||
|
||
def is_session_header(time, theme, title, faculty):
|
||
blob = '\n'.join([time, theme])
|
||
if re.search(r'^SESSION\s+', blob, re.I | re.M):
|
||
return True
|
||
if re.search(r'INTRODUCTION TO THE NOVEL LUNG HEALTH SUMMIT', blob, re.I):
|
||
return True
|
||
return False
|
||
|
||
|
||
def session_name_from(text):
|
||
lines = [clean(line) for line in str(text or '').splitlines() if clean(line)]
|
||
if not lines:
|
||
return ''
|
||
name = lines[0]
|
||
if len(lines) > 1:
|
||
extra = ' '.join(lines[1:])
|
||
name = f'{name} — {extra}'
|
||
return name
|
||
|
||
|
||
def faculty_role(topic_type, faculty):
|
||
role = clean(faculty).strip(' ,')
|
||
if not role:
|
||
return ''
|
||
kind = topic_type.upper()
|
||
if kind in {'PANEL DISCUSSION', 'JAMA'} and not re.match(r'panelists?:', role, re.I):
|
||
return f'Panelists: {role}'
|
||
return role
|
||
|
||
|
||
def parse_agenda(path):
|
||
rows = load_rows(path)
|
||
days = []
|
||
current_day = None
|
||
current_session = None
|
||
day_index = -1
|
||
|
||
def start_day(label, date):
|
||
nonlocal current_day, current_session, day_index
|
||
day_index = len(days)
|
||
current_day = {'label': label, 'date': date, 'sessions': []}
|
||
days.append(current_day)
|
||
current_session = None
|
||
|
||
def start_session(name):
|
||
nonlocal current_session
|
||
if not current_day:
|
||
start_day('Day 01', DAY_DATES[0])
|
||
current_session = {
|
||
'time': '',
|
||
'type': 'Main programme',
|
||
'title': '',
|
||
'name': name,
|
||
'topics': [],
|
||
'_times': [],
|
||
}
|
||
current_day['sessions'].append(current_session)
|
||
return current_session
|
||
|
||
def add_topic(topic_type, time_range, title, faculty):
|
||
if current_session is None:
|
||
return
|
||
if time_range:
|
||
current_session['_times'].append(time_range)
|
||
current_session['topics'].append({
|
||
'type': topic_type,
|
||
'duration': minutes_between(time_range) if time_range else '',
|
||
'title': title,
|
||
'role': faculty_role(topic_type, faculty),
|
||
})
|
||
|
||
for _, cells in rows:
|
||
time = clean(cells.get('B', ''))
|
||
theme = clean(cells.get('C', ''))
|
||
title = clean(cells.get('D', ''))
|
||
faculty = clean(cells.get('E', ''))
|
||
if not any([time, theme, title, faculty]):
|
||
continue
|
||
if time in {'Time'} or theme in {'Theme'}:
|
||
continue
|
||
|
||
day_match = re.match(r'DAY\s+(\d+)', time, re.I)
|
||
if day_match and not theme:
|
||
idx = int(day_match.group(1)) - 1
|
||
start_day(f'Day {idx + 1:02d}', DAY_DATES[idx] if idx < len(DAY_DATES) else '')
|
||
continue
|
||
|
||
if re.search(r'^TEA BREAK$', theme, re.I) or re.search(r'^TEA BREAK$', time, re.I):
|
||
sess = start_session('TEA BREAK')
|
||
sess['time'] = normalize_range(time, max(day_index, 0))
|
||
sess['topics'] = []
|
||
sess['_times'] = [sess['time']] if sess['time'] else []
|
||
continue
|
||
|
||
header_text = time if re.search(r'^SESSION\s+', time, re.I) else (
|
||
theme if re.search(r'INTRODUCTION TO THE NOVEL|SESSION\s+', theme, re.I) else ''
|
||
)
|
||
if header_text and not title and not faculty and not is_topic_type(theme):
|
||
start_session(session_name_from(header_text or theme or time))
|
||
continue
|
||
|
||
if is_session_header(time, theme, title, faculty) and not is_topic_type(theme):
|
||
start_session(session_name_from(theme or time))
|
||
if title or faculty:
|
||
add_topic('', normalize_range(time, max(day_index, 0)), title, faculty)
|
||
continue
|
||
|
||
if is_topic_type(theme) or title or faculty:
|
||
if current_session is None:
|
||
start_session(theme or title)
|
||
add_topic(
|
||
theme if is_topic_type(theme) else '',
|
||
normalize_range(time, max(day_index, 0)),
|
||
title,
|
||
faculty,
|
||
)
|
||
|
||
for day in days:
|
||
for sess in day['sessions']:
|
||
times = [t for t in sess.pop('_times', []) if t]
|
||
if times:
|
||
first = times[0].split(' – ')[0]
|
||
last = times[-1].split(' – ')[-1]
|
||
sess['time'] = f'{first} – {last}' if first != last or '–' in times[0] else times[0]
|
||
if sess['name'] == 'TEA BREAK':
|
||
sess['topics'] = []
|
||
return {'days': days}
|
||
|
||
|
||
def main():
|
||
data = parse_agenda(XLSX)
|
||
print(json.dumps(data, indent=2, ensure_ascii=False))
|
||
print('\nSUMMARY', file=__import__('sys').stderr)
|
||
for day in data['days']:
|
||
print(f"{day['label']} {day['date']} sessions={len(day['sessions'])}", file=__import__('sys').stderr)
|
||
for s in day['sessions']:
|
||
print(f" {s['time']} | {s['name'][:80]} | topics={len(s['topics'])}", file=__import__('sys').stderr)
|
||
|
||
|
||
if __name__ == '__main__':
|
||
main()
|