Convert a specific (Rosetta schedule) spreadsheet to a corresponding iCalendar (.ics) file

xls2ics.py

#! /usr/bin/env python

import pandas as pd
import datetime, pytz
import icalendar

def xls2ics(inputfile,outputfile):

	"""Converts a Rosetta schedule spreadsheet into a corresponding ICS
	calendar file."""
	
	xl = pd.ExcelFile(inputfile)
	
	if 'Timeline' not in xl.sheet_names:
		sys.exit('ERROR: Timeline worksheet not found in Excel file %s') % (inputfile)
		
	df = xl.parse('Timeline') # parse the worksheet "Timeline" into pandas
	
	# Remove rows with no STP entry but with an MTP (only used to flag MTP boundaries)
	df = df.loc[df['LTP'].isnull() | (df['LTP'].notnull() & df['STP'].notnull())]
	
	# Some cells have been merged to allow multiple events on the same day
	# Ensure we have repeated Date entries for each row
	df['Date']=df['Date'].fillna(method='pad')
	
	# Set notes column to blanks for missing elements
	df['Notes'] = df['Notes'].fillna('')
	
	# Merge planning and s/c activity into a single "event" column
	df['event']=pd.Series( df['Planning Activity'], index=df.index)
	mask=df['Spacecraft Activity'].notnull()
	df['event'][mask]=df['Spacecraft Activity'][mask]

	# Remove all entries with no planning or spacecraft event
	df=df[df['event'].notnull()]

	# Now have basic data in a form we can use, loop through events and write
	# to an iCalendar file

	# Initialise the calendar
	cal = icalendar.Calendar()
	cal.add('prodid','-//Rosetta master schedule//lunartech.org//')
	cal.add('version', '2.0') # version 2.0 is the current iCal standard
	
	# Loop through the events and add to the calendar
	for index,row in df.iterrows():
	
		event = icalendar.Event()
		
		if pd.isnull(row['Start']): # no start time listed
			start_time=row['Date'] # set time to midnight
		else:
			start_time = datetime.datetime.combine(row['Date'], row['Start'])
			
		start_time = start_time.replace(tzinfo=pytz.utc) # all times are in UTC

		if (str(row['End']).lower() == 'tbd') or (pd.isnull(row['End'])):
			end_time = start_time
		else:
			if row['End'] < row['Start']:
				print 'Warning: end time before start time - assuming next day!'
				end_time = datetime.datetime.combine(row['Date'], row['End']) + datetime.timedelta(days=1)
			else:
				end_time = datetime.datetime.combine(row['Date'], row['End'])

		end_time = end_time.replace(tzinfo=pytz.utc)

		event.add('summary',row['event'])		
		event.add('description',row['Notes'])
		event.add('dtstart',start_time)
		event.add('dtstamp',start_time)
		event.add('dtend',end_time)
		
		cal.add_component(event) # add event to calendar
	
	# Write to an ics file
	f = open(outputfile, 'w')
	f.write(cal.to_ical())
	f.close()
	
	print 'Info: %i events written to file %s' % (len(cal.subcomponents), outputfile)

	return