def importDataFromExcel(file, year=2019):
wb = openpyxl.load_workbook(file, data_only=True, read_only=True)
sheet = wb._sheets[0]
locale.setlocale(locale.LC_ALL, 'sv_SE')
events = dict()
eventTypes = dict()
activityTypes = dict()
n = 0
et_name = None
try:
for cols in sheet.rows:
n += 1
if n <= 9:
continue
if cols[0].value is None and et_name is None:
continue
et_name = cols[0].value or et_name
et = eventTypes.get(et_name)
if et is None:
#logger.info(f"Event type {et_name}")
try:
et = EventType.objects.get(name=et_name)
except EventType.DoesNotExist:
et = EventType(name=et_name)
et.save()
eventTypes[et_name] = et
if cols[2].value is None:
break
event_name = f"{et_name} vecka {cols[1].value}"
date = cols[2].value
if year is not None:
date = date.replace(year=year)
event = events.get((event_name, date))
if event is None:
#logger.info(f"Event {event_name} {date}")
try:
event = Event.objects.get(name=event_name,
start_date=date,
type=et)
except Event.DoesNotExist:
event = Event(name=event_name,
start_date=date,
end_date=date,
type=et)
event.save()
coord = cols[19].value
if coord is not None:
if ' ' in coord:
first, last = coord.split(' ', maxsplit=1)
else:
first = coord
try:
coordinator = Member.objects.get(
user__first_name=first, user__last_name=last)
event.coordinators.add(coordinator)
except Member.DoesNotExist:
print(
f"Failed to find member {coord} to use as coordinator for {event_name}"
)
events[event_name] = event
at_name = cols[5].value
at = activityTypes.get(at_name)
if at is None:
#logger.info(f"Activity Type: {at_name}")
try:
at = ActivityType.objects.get(name=at_name)
except ActivityType.DoesNotExist:
at = ActivityType(name=at_name)
at.save()
activityTypes[at_name] = at
ebd = cols[18].value
activity = Activity(
name=f"{at_name} {calendar.day_name[date.weekday()]}",
event=event,
type=at,
earliest_bookable_date=ebd)
interval = cols[4].value.replace('—', '-').replace('–', '-') \
.replace(' ', '').replace('.', ':')
try:
(start_time, end_time) = interval.split('-')
except ValueError as e:
logger.error(e)
logger.error(interval)
#logger.info(f'{activity.name} {interval}')
(sh, sm) = start_time.split(':')
(eh, em) = end_time.split(':')
activity.start_time = datetime.time(hour=int(sh), minute=int(sm))
activity.end_time = datetime.time(hour=int(eh), minute=int(em))
activity.full_clean()
activity.save()
print(f'''Database row count:
{EventType.objects.all().count()} event types
{ActivityType.objects.all().count()} activity types
{Event.objects.all().count()} events
{Activity.objects.all().count()} activities'''.replace(
' ', ' '))
except:
print(f"Error on row {n}")
raise
