planconver.py

from openpyxl import load_workbook
import json
wb = load_workbook(filename='vortragsplan-13.5..xlsx')
ws = wb['Tabelle1']


rooms = []
times = []

data = []


def cval(cell):
    cv = cell.value
    if cv:
        cv = cv.strip()
    return cv

for rows in ws.iter_cols(min_col=2, min_row=1, max_row=1):
    for cell in rows:
        room = cval(cell)
        if room:
            room = room.strip()
            if room:
                rooms.append(cell)

for rows in ws.iter_rows(min_col=1, min_row=1, max_row=ws.max_row + 1, max_col=1):
    for cell in rows:
        time = cval(cell)
        if time:
            times.append(cell)


for room in rooms:
    roomdata = {'name':room.value}
    timeslots = []
    for time in times:
        timename = time.value
        be = timename.split('-')
        timeslot = {'time': time.value, 'begin': be[0].strip(), 'end':be[1].strip()}

        lecture = {}
        col = room.col_idx
        row = time.row
        fs = cval(ws.cell(row=row, column=col))
        if fs:
            lecture['fs'] = fs
        titel = cval(ws.cell(row=row + 1, column=col))
        if titel:
            lecture['titel'] = titel
        name = cval(ws.cell(row=row + 2, column=col))
        if name:
            lecture['name'] = name
        teaser = cval(ws.cell(row=row + 3, column=col))
        if teaser:
            lecture['teaser'] = teaser
        aufz = cval(ws.cell(row=row + 3, column=col))
        if aufz:
            lecture['aufz'] = aufz

        if len(lecture) > 0:
            lecture['time'] = time.value
            lecture['room'] = room.value
            timeslot['lecture'] = lecture
        timeslots.append(timeslot)
    roomdata['times'] = timeslots
    data.append(roomdata)
print (data)
jsondata = {'schedule':data}
with open('../src/www/data/lectures.json', 'w') as outfile:
    json.dump(jsondata, outfile, indent=1)