Introduction
I wanted an easier way to keep track of the television schedule for Space Shuttle missions. Prior to my seeing a need for this application, I manually entered key events, such as launch and landing, and the flight day highlights into my calendar in Outlook.
NASA publishes the television schedules for Space Shuttle missions as a PDF file and as an Excel spreadsheet. In addition, NASA makes frequent revisions to the schedule during a mission. NASA broadcasts the Space Shuttle missions on satellite, and their programming is available on streaming video from their website and affiliates, direct broadcast satellite, such as Direct TV or DISH Network, and on many cable systems.
During a Space Shuttle mission, NASA frequently revises their television schedule. There can be fifteen to twenty revisions for a mission, and each mission can have as many as two hundred events. That clearly demands a solution to automate.
Since Microsoft published the Visual Studio Tools for Office, I saw this as a perfect opportunity to create a program that reads the television schedules in Excel format and adds the schedule to my Calendar in Outlook as appointments.
The project is also located on Microsoft's open source hosting website, CodePlex, at NASA Space Shuttle TV Schedule Transfer to Outlook Calendar.
- Microsoft Office: Excel and Outlook
- Microsoft Office Primary Interop Assemblies
Since this was my first project creating an application that uses Microsoft's Visual Tools for Office, I was not sure which path to pursue, Microsoft.Office.Tools.Excel
or Microsoft.Office.Interop.Excel
. During the development of the program, I kept having problems opening a file using Microsoft.Office.Tools.Excel
; but I had success using Microsoft.Office.Interop.Excel
.
NASA's schedules have a few headers of interest that the class NasaStsTvSchedule
interprets.
- Revision Header
- Date Header
- Flight Day Header
- Event Header
The Revision Header appears before any other header, and has the creation or revision date for the schedule. It appears in the first few rows explaining how to receive the satellite signal. The class captures the revision header to get the year of the mission using GetCreationRevisionDate
.
Date Headers have the day of the week, followed by the month, and then the day. An entry may be "MONDAY, OCTOBER 22". The date is for the day from the perspective in Houston, TX. ProcessDateHeader
sets the month and day of a DateTime
.
Flight Day Headers are the flight day of the mission, and the format is "FD #", optionally followed as "/ FD (#+1)".
Event Header is the header that describes the columns for the events, and includes the orbit number, subject, site, mission elapsed time, Central Time, Eastern Time, and Greenwich Mean Time. On occasions, the schedules have also included Moscow time. The columns always appear in the same column from mission to mission. However, when an Event Header is encountered, it sets the column index for each item of the information to capture.
Below is a screenshot of revision 0 for the STS-122 mission scheduled for launch on December 6, 2007. Rows containing the prelaunch entries are hidden.
The Download Files
For this CodeProject article, the source files are divided into two zip files, NasaTvScheduleSrc.zip (application code) and NasaTvScheduleSrcSetup.zip (installation code).
NasaTvScheduleSrc.zip contains the application code. During the development of the program, I needed to convert between time zones, something that was lacking in .NET 2.0. The MSDN Base Class Library Team published the Time Zones in the .NET Framework [Anthony Moore], providing the solution I needed to convert between time zones with their TimeZoneInfo
class, which is included in .NET 3.5.
NasaTvScheduleSrcSetup.zip contains the Setup project and the Office 2003 and Office 2007 Primary Interop Assemblies. It uses code and techniques described in the MSDN article Deploying Visual Studio 2005 Tools for Office Solutions Using Windows Installer (Part 1 of 2).
NasaTvScheduleApp.zip contains the output from the three projects used to create the application that was published in the original publication of the article.
NasaTvScheduleSetup.zip contains the setup application used to install the application.
NasaStsTVSchedule
uses Microsoft.Office.Interop.Excel
to open the Excel spreadsheet and get the cells containing the schedule.
Create the class NasaStsTVSchedule
that reads and processes the television schedule file in the Excel spreadsheet by:
tvSchedule = new NasaStsTVSchedule(excelFile, viewingTimeZone);
The viewingTimeZone
is the display value in SOFTWARE\Microsoft\Windows NT\CurrentVersion\Time Zones. The time zone for Eastern Standard Time is "(GMT-05:00) Eastern Time (US & Canada)".
The ReadScheduleRow
returns a NasaStsTVScheduleEntry
that contains the following information necessary for adding to the Outlook Calendar:
BeginDate
- Beginning date and time EndDate
- Ending date and time Subject
- The event that is televised Orbit
- The number of orbits completed Site
- The location where the event occurs FlightDay
- The flight day of the mission Changed
- Indicates if this entry was changed from the prior published schedule TypeEntry
- The ScheduleType
of the record returned; scheduleEntry
has the information necessary for scheduling information; and error
should be checked to determine if an error occurred when reading the Excel file
The NASA TV Schedule application uses the NasaStsTVSchedule
class to read the Excel spreadsheet and fills a DataGridView
. A checkbox is included in each row to indicate that the row containing the schedule information will be added to the Calendar in Outlook as an appointment. Another checkbox is used to set the reminder for the appointment entry.
A second DataGridView
contains the appointment items in Outlook that matches the specified search conditions. The search conditions are the selected categories and the selected date plus four weeks ahead. A checkbox column indicates the appointment items that will be removed from Outlook.
The application accesses Outlook using the Microsoft.Office.Interop.Outlook
COM objects.
using InteropOutlook = Microsoft.Office.Interop.Outlook;
There are fifteen controls that controls the application to read the schedules and manage the appointments in Outlook.
- Excel Controls:
- Open NASA TV Schedule - Opens an Excel file and fills the Excel
DataGridView
. - Select All - Selects all rows in the Excel
DataGridView
. - Unselect All - Unselects all rows in the Excel
DataGridView
. - Excel
DataGridView
- Contains the event schedule from the television schedule; includes checkboxes for selecting the row and adding a reminder to Outlook when transferring the event to Outlook.
- Outlook Controls:
- Begin Date of Mission -
DateTimePicker
sets the start date for Outlook to search for appointments; the end date is four weeks from the start date. - Viewing Time Zone -
ComboBox
containing the time zones available. The selected time zone is used to convert the time from Central Time for the Excel DataGridView
- Outlook Categories -
CheckListBox
containing the categories that the appointments can be associated with. I have a category, "NASA STS TV Schedule", created for use in creating the appointments - Select All - Selects all rows in the Outlook
DataGridView
. - Unselect All - Unselects all rows in the Outlook
DataGridView
. - Smart Select - Uses the beginning date and time in the first and last entries in the Excel
DataGridView
, and selects those entries in the Outlook DataGridView
when the event falls within the time period of the Excel schedule. - Transfer TV Schedule - Creates appointments in Outlook from the selected entries in the Excel
DataGridView
schedule. - Remove Selected Entries - Removes the selected appointments from Outlook.
- Bulk Import - Opens multiple schedules and transfers the schedules to Outlook.
- New Schedule Update - Provides an easy method to read a revised schedule file and update the Outlook Calendar.
- Refresh Categories - Reloads the Outlook categories.
- Outlook
DataGridView
- Contains the Outlook Calendar entries matching the search conditions of the Outlook Categories and date search range.
There are several key functions in the application that do the bulk of the work in reading the schedule file and maintaining the Outlook Schedule by adding and removing appointments:
OpenNasaTvSchedule()
uses the Microsoft OpenFileDialog
to select an Excel file to load, and calls LoadExcelSchedule(excelSchedule)
to read the schedule file. LoadExcelSchedule(excelSchedule)
reads and interprets the Excel schedule file to populate the DataGridView
containing the NASA mission schedule. LoadOutlookSchedule()
loads the DataGridView
containing the Outlook Calendar entries for the specified date range and Outlook Categories. RemoveOutlookEntries()
deletes the specified entries from Outlook's Calendar. TransferExcelToOutlook()
adds the specified entries from the NASA schedule DataGridView
to Outlook's Calendar. SmartSelect()
uses the first and last rows in the NASA schedule DataGridView
to get a date/time span, and uses the date and time to select those entries in Outlook's DataGridView
that fall within that date/time span. SelectAllExcel()
selects all entries in the NASA Schedule DataGridView
. UnselectAllExcel
does the opposite. SelectAllOutlook()
selects all entries in the Outlook DataGridView
. UnselectAllOutlook
does the opposite.
Since I have saved television schedules and the revisions for the Space Shuttle missions, STS-115, STS-116, STS-117, STS-118, STS-120, STS-121, and STS-122, BulkImport
provided an easy method to transfer the mission schedules from Excel to Outlook.
The code that fills the Outlook DataGridView
:
protected void LoadOutlookSchedule()
{
dgvOutlook.Rows.Clear();
InteropOutlook.ApplicationClass outlook = null;
InteropOutlook.NameSpace nmOutlook = null;
InteropOutlook.Folder olCalendarFolder = null;
try
{
outlook = new Microsoft.Office.Interop.Outlook.ApplicationClass();
DateTime dtStart = dtpOutlook.Value;
dtStart = dtStart.Date;
const int daysInWeek = 7;
DateTime dtEnd = dtStart.AddDays(daysInWeek *
Properties.Settings.Default.LookAheadWeeks);
string filterDateSearchRange = "([Start] >= '" +
dtStart.ToString("g", CultureInfo.CurrentCulture) +
"' AND [End] <= '" +
dtEnd.ToString("g", CultureInfo.CurrentCulture) + "')";
StringBuilder filterCategories = new StringBuilder();
string categories = GetSelectedCategories();
if (categories.Length > 0)
{
string[] category = categories.Split(';');
int indexCategories;
int maxCategories = category.GetUpperBound(0);
int lowCategories = category.GetLowerBound(0);
for (indexCategories = lowCategories; indexCategories
<= maxCategories; indexCategories++)
{
filterCategories.Append("[Categories] = " +
category[indexCategories]);
if ((lowCategories != maxCategories) &&
(indexCategories < maxCategories))
{
filterCategories.Append(" OR ");
}
}
}
string filterCalendar = filterDateSearchRange;
if (filterCategories.Length > 0)
{
filterCalendar += " AND (" + filterCategories.ToString() + ")";
}
filterCategories = null;
nmOutlook = outlook.GetNamespace("MAPI");
olCalendarFolder = nmOutlook.GetDefaultFolder(
Microsoft.Office.Interop.Outlook.OlDefaultFolders.olFolderCalendar)
as InteropOutlook.Folder;
dgvOutlook.SuspendLayout();
if (olCalendarFolder != null)
{
InteropOutlook.Items calendarItems =
(InteropOutlook.ItemsClass)
olCalendarFolder.Items.Restrict(filterCalendar);
calendarItems.Sort("[Start]", Type.Missing);
foreach (InteropOutlook.AppointmentItem apptItem in calendarItems)
{
dgvOutlook.Rows.Add(false, apptItem.Start,
apptItem.End, apptItem.Subject, apptItem.Location);
}
}
}
catch (COMException comExp)
{
MessageBox.Show(comExp.Message + comExp.StackTrace,
Properties.Resources.ERR_COM_EXCEPTION,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation,
MessageBoxDefaultButton.Button1, (MessageBoxOptions)0);
if (Properties.Settings.Default.CopyExceptionsToClipboard)
Clipboard.SetText(comExp.Message + comExp.StackTrace,
TextDataFormat.Text);
}
finally
{
dgvOutlook.ResumeLayout();
dgvOutlook.Refresh();
olCalendarFolder = null;
nmOutlook = null;
outlook = null;
}
}
Below is the code to add the schedule to Outlook:
private void AddAppointment(NasaStsTVScheduleEntry nasaTVSchedule,
bool reminder, string categories,
InteropOutlook.ApplicationClass outlook)
{
try
{
string selectedCategories = categories.Replace(";", ", ");
InteropOutlook.AppointmentItem appt =
outlook.CreateItem(
Microsoft.Office.Interop.Outlook.OlItemType.olAppointmentItem)
as InteropOutlook.AppointmentItem;
appt.Start = nasaTVSchedule.BeginDate;
appt.End = nasaTVSchedule.EndDate;
appt.Subject = nasaTVSchedule.Subject;
appt.Location = nasaTVSchedule.Site;
appt.BusyStatus = Microsoft.Office.Interop.Outlook.OlBusyStatus.olFree;
appt.Categories = selectedCategories;
appt.ReminderSet = reminder;
if (reminder)
appt.ReminderMinutesBeforeStart = 15;
appt.Importance =
Microsoft.Office.Interop.Outlook.OlImportance.olImportanceNormal;
appt.BusyStatus =
Microsoft.Office.Interop.Outlook.OlBusyStatus.olFree;
appt.Save();
nasaTVSchedule = null;
}
catch (COMException comExp)
{
MessageBox.Show(comExp.Message + comExp.StackTrace,
Properties.Resources.ERR_COM_EXCEPTION,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation,
MessageBoxDefaultButton.Button1, (MessageBoxOptions)0);
if (Properties.Settings.Default.CopyExceptionsToClipboard)
Clipboard.SetText(comExp.Message +
comExp.StackTrace, TextDataFormat.Text);
}
finally
{
nasaTVSchedule = null;
}
}
The code to remove a schedule entry from Outlook:
private void RemoveAppointment(DateTime dtStart,
DateTime dtEnd, string subject, string site,
InteropOutlook.ApplicationClass outlook)
{
string filterAppt = "([Start] = '" + dtStart.ToString("g",
CultureInfo.CurrentCulture) + "') " +
"AND ([End] = '" + dtEnd.ToString("g",
CultureInfo.CurrentCulture) + "') " +
"AND ([Subject] = '" + subject.Replace("'", "''") + "') " +
"AND ([Location] = '" + site + "')";
InteropOutlook.NameSpace nmOutlook = null;
InteropOutlook.Folder olCalendarFolder = null;
try
{
nmOutlook = outlook.GetNamespace("MAPI");
olCalendarFolder = nmOutlook.GetDefaultFolder(
Microsoft.Office.Interop.Outlook.OlDefaultFolders.olFolderCalendar)
as InteropOutlook.Folder;
if (olCalendarFolder != null)
{
InteropOutlook.Items calendarItems =
(InteropOutlook.ItemsClass)olCalendarFolder.Items.Restrict(filterAppt);
calendarItems.Sort("[Start]", Type.Missing);
foreach (InteropOutlook.AppointmentItem apptItem in calendarItems)
{
apptItem.Delete();
}
}
}
catch (COMException comExp)
{
MessageBox.Show(comExp.Message + comExp.StackTrace,
Properties.Resources.ERR_COM_EXCEPTION,
MessageBoxButtons.OK, MessageBoxIcon.Exclamation,
MessageBoxDefaultButton.Button1, (MessageBoxOptions)0);
if (Properties.Settings.Default.CopyExceptionsToClipboard)
Clipboard.SetText(comExp.Message + comExp.StackTrace, TextDataFormat.Text);
}
finally
{
olCalendarFolder = null;
nmOutlook = null;
}
}
To access the Excel COM objects, NasaStsTVSchedule
uses Microsoft.Office.Interop.Excel
:
using InteropExcel = Microsoft.Office.Interop.Excel;
Public methods of NasaStsTvSchedule | Description |
---|
NasaStsTVSchedule(string excelFile, string viewingTimeZone) | Initializes class with the filename of the NASA TV Schedule and Viewing Time Zone |
NasaStsTVScheduleEntry ReadScheduleRow() | Returns the NasaStsTVScheduleEntry containing scheduling details of the event |
bool EOF() | Returns true if the end of the spreadsheet has been reached |
void Close() | Closes the spreadsheet and Excel |
bool InSpace() | Returns true if the shuttle is in orbit |
bool IsDocked() | Returns true if the shuttle is docked to ISS (not reliable after docking with revised schedules) |
Below is the code that opens the Excel file containing the schedule and returns an Array
of rows of cells containing the schedule. Normally, all spreadsheet files created by NASA has the name, Print_Area, defined as the group of cells that contains the schedule. However, on occasion, NASA forgets to define the name; in those cases, an InvalidFileFormatException
is thrown. The exception is caught, and the error message is passed back to the application in the NasaStsTvScheduleEntry
that the ReadScheduleRow
returns.
public System.Array OpenExcelFile(string NasaTVScheduleFile)
{
System.Array printArea = null;
SuccessfullyOpened = false;
try
{
InteropExcelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
InteropExcelWorkbook = (InteropExcel.WorkbookClass)
InteropExcelApplication.Workbooks.Open(NasaTVScheduleFile,
false, true, Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing);
InteropExcelSheets = InteropExcelWorkbook.Worksheets;
InteropExcelWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)
InteropExcelSheets.get_Item(1);
InteropExcelRange = InteropExcelWorksheet.get_Range(
Properties.Resources.NASA_PRINT_AREA, Type.Missing);
printArea = (System.Array)InteropExcelRange.Cells.Value2;
InteropExcelApplication.Visible = false;
SuccessfullyOpened = true;
return (printArea);
}
catch (COMException comException)
{
if (comException.TargetSite.Name ==
Properties.Resources.EXP_COMEXCEPTION_INTEROPEXCEL_OPENEXCELFILE_GETRANGE)
{
string explanation = Properties.Resources.INVALIDFILEFORMAT_NO_PRINT_AREA;
throw new InvalidFileFormatException(String.Format(explanation,
NasaTVScheduleFile), comException);
}
else
{
if (Properties.Settings.Default.CopyExceptionsToClipboard)
Clipboard.SetText(comException.Message + CRLF +
comException.StackTrace, TextDataFormat.Text);
throw;
}
}
}
The code that closes the Excel file and quits the Excel application:
public void Close()
{
if (InteropExcelWorkbook != null)
InteropExcelWorkbook.Close(false, Type.Missing, Type.Missing);
if (InteropExcelApplication != null)
{
InteropExcelApplication.DisplayAlerts = false;
InteropExcelApplication.Quit();
}
}
Code that converts time in Excel to DateTime
:
private string ExcelFormatTime(int row, int cell)
{
string formattedTime = "";
switch (ExcelTypeInterface)
{
case ExcelInterface.InteropExcel:
formattedTime = DateTime.FromOADate((double)
TvScheduleCells.GetValue(row, cell)).ToString("hh:mm tt");
break;
case ExcelInterface.ToolsExcel:
formattedTime = ToolsExcelIF.FormatTime(TvScheduleCells, row, cell);
break;
default:
throw new ArgumentException(Properties.Resources.ERR_EXCEL_FORMAT_TIME,
Properties.Resources.ERR_ARGUMENT_TYPE_EXCEL);
}
return (formattedTime);
}
ReadScheduleRow
opens the television schedule file if it has not been opened yet, and advances through the spreadsheet one row at a time. It calls DecodeScheduleRow
to determine and decode the various types of headers. If the current row is a scheduleEntry
, ProcessEntry
is called to create the NasaStsTVScheduleEntry
record to return.
public NasaStsTVScheduleEntry ReadScheduleRow()
{
ScheduleType entryType = ScheduleType.empty;
NasaStsTVScheduleEntry dataRow = null;
if (!SuccessfullyOpened)
{
try
{
OpenNasaTvSchedule();
}
catch (InvalidFileFormatException invalidFile)
{
NasaStsTVScheduleEntry error = new NasaStsTVScheduleEntry(
DateTime.MinValue, DateTime.MinValue, false,
invalidFile.Message, 0, invalidFile.StackTrace,
"", ScheduleType.error);
return (error);
}
}
if (SuccessfullyOpened)
{
for (; !EOF() && (entryType != ScheduleType.scheduleEntry)
&& (entryType != ScheduleType.error); CurrentRow++)
{
try
{
entryType = DecodeScheduleRow(CurrentRow);
}
catch (InvalidFileFormatException expInvalidFileFormat)
{
ProcessingError = expInvalidFileFormat;
entryType = ScheduleType.error;
}
}
}
if (entryType == ScheduleType.scheduleEntry)
{
CurrentRow--;
dataRow = ProcessEntry(CurrentRow);
if (dataRow == null)
{
entryType = ScheduleType.empty;
}
CurrentRow++;
}
else if (entryType == ScheduleType.error)
{
dataRow = new NasaStsTVScheduleEntry(DateTime.MinValue, DateTime.MinValue, false,
ProcessingError.Message, 0, "", "", ScheduleType.error);
}
return (dataRow);
}
DecodeScheduleRow
returns the type of record for the current row. The year of the mission is set in the first few rows of the spreadsheet before any header records are encountered. The year is captured by GetCreationRevisionDate
if cell 1 of the current row is a string matching the date format of "MM/DD/YY". ProcessCellOrbit
returns the type of the record.
private ScheduleType DecodeScheduleRow(int row)
{
ScheduleType typeEntry = ScheduleType.empty;
object cellOrbit;
if (CurrentRow < RowCount)
{
if (Year == 0)
{
GetCreationRevisionDate();
}
cellOrbit = TvScheduleCells.GetValue(row, OrbitColumnHeader);
if (cellOrbit != null)
{
try
{
typeEntry = ProcessCellOrbit(cellOrbit, row);
}
catch (InvalidFileFormatException)
{
typeEntry = ScheduleType.error;
}
}
else
{
if (IsRowScheduleEntry(row))
typeEntry = ScheduleType.scheduleEntry;
}
}
else
{
IsEOF = true;
}
return (typeEntry);
}
ProcessCellOrbit
is called when column one of the current row contains a value and determines the type of record for the current row.
private ScheduleType ProcessCellOrbit(object cellOrbit, int row)
{
ScheduleType typeEntry = ScheduleType.empty;
System.Type cellOrbitType = cellOrbit.GetType();
switch (cellOrbitType.FullName)
{
case "System.String":
{
string cellOrbitValue = (string)cellOrbit.ToString();
if (cellOrbitValue.Contains(Properties.Resources.NASA_DEFINITION_OF_TERMS))
{
IsEOF = true;
typeEntry = ScheduleType.definitionOfTerms;
}
else if (cellOrbitValue == Properties.Resources.NASA_ORBIT)
{
typeEntry = ScheduleType.columnHeading;
ProcessOrbitHeader(row);
}
else
{
if (MatchDateHeader(cellOrbitValue))
{
try
{
HeadingDate = ProcessDateHeader(cellOrbitValue);
typeEntry = ScheduleType.dateHeading;
}
catch (InvalidFileFormatException expInvalidFileFormat)
{
ProcessingError = expInvalidFileFormat;
}
finally
{
if (ProcessingError != null)
typeEntry = ScheduleType.error;
}
break;
}
if (MatchFlightDayHeader(cellOrbitValue))
{
typeEntry = ScheduleType.flightDayHeading;
}
}
}
break;
case "System.Double":
{
if (IsRowScheduleEntry(row))
typeEntry = ScheduleType.scheduleEntry;
}
break;
default:
typeEntry = ScheduleType.empty;
break;
}
return (typeEntry);
}
ProcessEntry
gathers the information from the current row and the previous row that set the month and day from the Date Header record. It calls ReadAhead()
to get the ending time and date for the current event. There are some events that have a duration longer than the next scheduled event, such as crew sleep periods and EVAs; for those events, the next occurring events are skipped until the matching ending event is encountered. On a similar note, there are some events that do not last until the next scheduled event. These events are the Flight Day Highlights (which usually last 30 to 45 minutes), press conferences, interviews, and mission briefings. For these events, GuesstimateFixedEvents
uses the array EventTimes
to get the duration for the events.
private NasaStsTVScheduleEntry ProcessEntry(int row)
{
DateTime dtCentral = HeadingDate;
DateTime dtBeginViewingTime;
DateTime dtEndViewingTime = HeadingDate;
Changed = false;
bool validEntry = false;
NasaStsTVScheduleEntry entryRow = null;
if (!((HeadingDate.Year == 1) && (HeadingDate.Month == 1)
&& (HeadingDate.Day == 1)
&& (HeadingDate.Hour == 0) && (HeadingDate.Minute == 0)))
{
object cellTwo = TvScheduleCells.GetValue(row, 2);
if (cellTwo != null)
{
System.Type cellTwoType = cellTwo.GetType();
if (cellTwoType.FullName == Properties.Resources.SYSTEM_STRING)
{
string cellTwoValue = cellTwo.ToString();
Changed = (cellTwoValue == Properties.Resources.NASA_CHANGED);
}
}
Subject = GetMultiLineSubject(row);
if (Subject.Contains(Properties.Resources.NASA_DOCKING))
{
if (!Subject.Contains(Properties.Resources.NASA_VTR_PLAYBACK))
{
Docked = true;
}
}
else if (Subject.Contains(Properties.Resources.NASA_UNDOCKING) ||
Subject.Contains(Properties.Resources.NASA_UNDOCKS))
{
if (!Subject.Contains(Properties.Resources.NASA_VTR_PLAYBACK))
{
Docked = false;
}
}
else if (Subject == Properties.Resources.NASA_LAUNCH)
{
if (!Subject.Contains(Properties.Resources.NASA_VTR_PLAYBACK))
{
InOrbit = true;
}
}
else if (Subject.Contains(Properties.Resources.NASA_LANDING))
{
if (!Subject.Contains(Properties.Resources.NASA_VTR_PLAYBACK))
{
InOrbit = false;
Landed = true;
}
}
if (TvScheduleCells.GetValue(row, SiteColumHeader) != null)
Site = TvScheduleCells.GetValue(row, SiteColumHeader).ToString();
else
{
if (Docked)
Site = Properties.Resources.NASA_ISS;
else
Site = Properties.Resources.NASA_STS;
if (Subject.Contains(Properties.Resources.NASA_CREW_SLEEP_BEGINS) ||
Subject.Contains(Properties.Resources.NASA_CREW_WAKE_UP))
{
if (ISSCrewSleep(row) || ISSCrewWakeUp(row))
Site = Properties.Resources.NASA_ISS;
if (ShuttleCrewSleep(row) || ShuttleCrewWakeUp(row))
Site = Properties.Resources.NASA_STS;
}
}
if (TvScheduleCells.GetValue(row, MissionElapsedTimeColumnHeader) != null)
{
MissionElapsedTime = TvScheduleCells.GetValue(row,
MissionElapsedTimeColumnHeader).ToString();
MissionDurationTime.Set(TvScheduleCells, row, MissionElapsedTimeColumnHeader);
}
if (TvScheduleCells.GetValue(row, OrbitColumnHeader) != null)
Orbit = (System.Double)TvScheduleCells.GetValue(row, OrbitColumnHeader);
if (TvScheduleCells.GetValue(row, FlightDayColumnHeader) != null)
FlightDay = TvScheduleCells.GetValue(row, FlightDayColumnHeader).ToString();
if (TvScheduleCells.GetValue(row, CentralTimeColumnHeader) != null)
{
CentralTime = ExcelFormatTime(row, CentralTimeColumnHeader);
dtBeginViewingTime = ConvertFromCentralTzToViewingTz(dtCentral, CentralTime);
dtEndViewingTime = GuesstimateFixedEvents(Subject, dtBeginViewingTime);
if (dtEndViewingTime == DateTime.MinValue)
dtEndViewingTime = ReadAhead();
validEntry = true;
if (dtBeginViewingTime > dtEndViewingTime)
dtEndViewingTime = dtBeginViewingTime.AddHours(1);
entryRow = new NasaStsTVScheduleEntry(dtBeginViewingTime,
dtEndViewingTime, Changed, Subject,
Orbit, Site, FlightDay, ScheduleType.scheduleEntry);
}
}
if (validEntry)
{
return (entryRow);
}
else
return (null);
}
Exceptions Generated
NasaStsTVSchedule
throws an InvalidFileFormatException
based on one of two conditions:
- No schedule revision or created date was found. The revision or created date is needed to get the year of the mission. If no date is found before one of the regular header records is found,
InvalidFileFormatException
is thrown. The reason given is: "Creation or Revision Date was not found in file {0}. Year of the mission cannot be determined." - NASA schedules normally have a cell range named "Print_Area" that defines the range of the schedule. If the cell range named Print_Area is not defined in the spreadsheet, an
InvalidFileFormatException
is thrown. The reason is: "Range, Print_Area, is not defined in the NASA STS TV Schedule File {0}".
The NasaStsTvScheduleEntry
contains all the information needed to create a schedule in Outlook.
Public methods, properties of NasaStsTvScheduleEntry | Description |
---|
NasaStsTvScheduleEntry(DateTime entryBeginDateTime, DateTime entryEndDateTime, bool entryRevised, string entrySubject, double entryOrbit, string entrySite, string entryFlightDay, ScheduleType entryType) | Constructor for the event schedule with beginning and ending date and time, revision indicator, subject, orbit number of event, location of event, flight day, and type of schedule entry |
DateTime BeginDate | Date and time for the start of the event |
DateTime EndDate | Date and time for the end of the event |
string Subject | Subject of event |
double Orbit | Returns the orbit number of the event |
string Site | Location of the event |
string FlightDay | The flight day that the event occurs on |
bool Changed | Indicates if the event has changed from the prior published schedule |
ScheduleType TypeEntry | Returns the type of entry: columnHeading , dateHeading , flightDayHeading , scheduleEntry , empty , or error |
public enum ScheduleType
{
empty, columnHeading, dateHeading, flightDayHeading, scheduleEntry,
definitionOfTerms, error
};
The schedule is published from a Houston, Texas point of view since that is the location of the Johnson Space Center. I did not want to do the date arithmetic to advance the day when midnight arrived in Eastern Time and Houston, TX was still 11 PM. I decided to have the program use time zone conversions. That was when I discovered that .NET 2.0 did not handle conversions between time zones! One can do conversions between the local time zone and Universal Coordinated Time all day long; but there is no method to convert between time zones in .NET 2.0. I searched on MSDN, and found the TimeZoneInfo
that the Base Class Library team had published on their blog. The .NET 3.5 Framework has the TimeZoneInfo
, but it was in beta release when I was developing this program.
During the mission of STS-120 from October 23, 2007 through November 7, 2007, there were events scheduled during that hour period where “time does not exist”, the hour of transition from Daylight Saving Time to Standard Time on November 4, 2007. The time in Houston, Texas was still in Daylight Saving Time, and South Carolina was in Standard Time. I had to put a special case in the routine that converted Central Time to the viewer’s time zone for Eastern Time.
private DateTime ConvertFromCentralTzToViewingTz(DateTime dtConvert,
string timeOfday)
{
string convertTime = timeOfday.Trim();
DateTime dtCentralTZ = dtConvert.Date;
DateTime dtTimeOfDay = DateTime.Parse(convertTime, CultureInfo.CurrentCulture);
dtCentralTZ = dtCentralTZ.Add(dtTimeOfDay.TimeOfDay);
DateTime dtViewingTZ = TimeZoneInfo.ConvertTimeZoneToTimeZone(dtCentralTZ,
JohnsonSpaceCenterTZ, ViewingTimeZoneTZ);
if ((dtCentralTZ.Hour == dtViewingTZ.Hour) &&
(ViewingTimeZoneTZ.DisplayName == Properties.Resources.TZ_US_EASTERN))
dtViewingTZ = dtViewingTZ.AddHours(1);
return (dtViewingTZ);
}
I had experience with regular expressions from when I programmed on Unix systems. Regular expressions got a workout in this program.
Regular expressions were used for the following:
- Date Header to capture the month and day of the event
- Revision Date to capture the year of the mission
- EVA Activity - Beginning and Ending
- Flight Day Header
- Flight Day Highlights - a recap of that day's events
- ISS Crew Sleep Activity - Sleep Period Begins and Wake Up
- Shuttle Crew Sleep Activity - Sleep Period Begins and Wake Up
An innovative use of regular expressions was used in the function SubjectVerbPatternMatch
, where it was used to pair up beginning and ending activities, such as “SHUTTLE CREW SLEEP BEGINS” with “SHUTTLE CREW WAKE UP”. Similarly, the same function was used to match the ISS crew sleep period with ISS crew wake up call, and to pair up "EVA BEGINS" with "EVA ENDS". The ReadAhead
logic for crew sleep period beginnings or EVA beginnings would search for crew wake ups or EVA endings to get the ending times. The Subject was either <Shuttle> <ISS> or <Eva>, and the Activity was “crew sleep begins” and “crew wake up”, or “begins” or “ends” for EVA activity.
Regular expressions #1 and #2 look the same, however, in #1, the Shuttle
is mandatory while ISS
is optional; this is used for the shuttle crew sleep periods and wakeup calls. In regular expression #2, Shuttle
is optional and ISS
is mandatory, and is used for the ISS crew sleep periods and crew wakeup calls. Below are the regular expressions that were used:
- (?<
Shuttle
>ATLANTIS|DISCOVERY|ENDEAVOUR)(?:\s*/?\s*)?(?<ISS
>ISS)?(?:\s*)(?<Activity
>CREW SLEEP BEGINS|CREW WAKE UP) - (?<
Shuttle
>ATLANTIS|DISCOVERY|ENDEAVOUR)?(?:\s*/?\s*)?(?<ISS
>ISS)(?:\s*)(?<Activity
>CREW SLEEP BEGINS|CREW WAKE UP) - (?<
Eva
>EVA)\s+(?<Number>#\d+)\s+(?<Activit
y>BEGINS|ENDS)
private bool SubjectVerbPatternMatch(Regex rgSubjectVerbPattern,
string subject, string verb, int row)
{
string entry = TvScheduleCells.GetValue(row, SubjectColumnHeader).ToString();
Match mtchSubjectVerb = rgSubjectVerbPattern.Match(entry);
GroupCollection grpcollSubjectVerb = mtchSubjectVerb.Groups;
bool matchSubjectVerb = grpcollSubjectVerb[subject].Success &&
(grpcollSubjectVerb[Properties.Resources.IX_ACTIVITY].Success &&
(grpcollSubjectVerb[Properties.Resources.IX_ACTIVITY].ToString() == verb));
return (matchSubjectVerb);
}
- December 3, 2007. Initial version.
- December 21, 2007.
- Fixed bug in finding shuttle crew wake up calls when the shuttle crew and ISS crew have different times for wake up calls.
- Added New Schedule Update based on a CodeProject reader's suggestion. That was an excellent suggestion in making the program easier to use.
- Added installation setup project and setup application for download.
- December 23, 2007
- Fixed formatting in HTML markup of Regular Expressions
- December 29, 2007
- Added section about
InvalidFileFormatException
. - Fixed misspelling of Endeavour in regular expressions, restore mission month, and year after
ReadAhead(...)
; tightened rules for New Year rollover. - Updated source code, installation code, and compiled code.
- December 01, 2008
- Added latest downloads link