NASA Space Shuttle TV Schedule Transfer to Outlook Calendar

RalphHightower

4.77/5 (9 votes)

Dec 5, 2007

GPL3

15 min read

71233

1305

NASA Space Shuttle Mission Schedule reads Excel files published by NASA containing the television schedule for Space Shuttle missions and provides the ability to enter and update the schedule in Outlook’s Calendar.

Screenshot - NasaTvScheduleApp

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.

Requirements

Microsoft Office: Excel and Outlook
Microsoft Office Primary Interop Assemblies
- Office 2003 Update: Redistributable Primary Interop Assemblies
- 2007 Microsoft Office System Update: Redistributable Primary Interop Assemblies

Background

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.

NASA Links

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.

Screenshot of STS-122 TV Schedule rev. 0

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.

Using the NasaStsTVSchedule Class

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

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:
1. Open NASA TV Schedule - Opens an Excel file and fills the Excel DataGridView.
2. Select All - Selects all rows in the Excel DataGridView.
3. Unselect All - Unselects all rows in the Excel DataGridView.
4. 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:
1. 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.
2. 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
3. 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
4. Select All - Selects all rows in the Outlook DataGridView.
5. Unselect All - Unselects all rows in the Outlook DataGridView.
6. 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.
7. Transfer TV Schedule - Creates appointments in Outlook from the selected entries in the Excel DataGridView schedule.
8. Remove Selected Entries - Removes the selected appointments from Outlook.
9. Bulk Import - Opens multiple schedules and transfers the schedules to Outlook.
10. New Schedule Update - Provides an easy method to read a revised schedule file and update the Outlook Calendar.
11. Refresh Categories - Reloads the Outlook categories.
12. 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:

/// <summary>
/// Loads the Calendar entries from Outlook based
/// on the selected date + LookAheadWeeks (from the Settings)
/// weeks and categories selected
/// </summary>
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;
        // Set an end date x weeks from the Application
        // Specified Setting of LookAheadWeeks
        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();
        // Multiple categories will be checked and separated by an OR
        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 not the only category and not the last category
                if ((lowCategories != maxCategories) && 
                    (indexCategories < maxCategories))
                {
                    filterCategories.Append(" OR ");
                }
            }
        }

        string filterCalendar = filterDateSearchRange;
        // Put the date range search and categories search together
        if (filterCategories.Length > 0)
        {
            filterCalendar += " AND (" + filterCategories.ToString() + ")";
        }

        filterCategories = null;
        nmOutlook = outlook.GetNamespace("MAPI");
        //  Ralph Hightower - 20071104
        //  FolderClass, ItemClass, and AppointmentItemClass do not appear to work
        //  Use Folder, Item, and AppointmentItem instead
        //InteropOutlook.FolderClass olCalendarFolder = 
        //    nmOutlook.GetDefaultFolder(
        //     Microsoft.Office.Interop.Outlook.OlDefaultFolders.olFolderCalendar)
        //    as InteropOutlook.FolderClass;
        olCalendarFolder = nmOutlook.GetDefaultFolder(
          Microsoft.Office.Interop.Outlook.OlDefaultFolders.olFolderCalendar)
          as InteropOutlook.Folder;

        dgvOutlook.SuspendLayout();
        if (olCalendarFolder != null)
        {
            //InteropOutlook.ItemsClass calendarItems = 
            //  (InteropOutlook.ItemsClass)olCalendarFolder.
            //               Items.Restrict(filterCalendar);
            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:

/// <summary>
/// Adds the Appointment to the Outlook Calendar
/// </summary>
/// <param name="nasaTVSchedule">Class containing
///   the information for the appointment item</param>
/// <param name="reminder">Set a reminder if true</param>
/// <param name="categories">Outlook
///   categories to file this appointment under</param>
/// <param name="outlook">The Outlook application</param>
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:

/// <summary>
/// Deletes the Appointment from the Calendar
/// </summary>
/// <param name="dtStart">Start Time of the Appointment</param>
/// <param name="dtEnd">End Time of the Appointment</param>
/// <param name="subject">Subject of the Appointment</param>
/// <param name="site">Site of the Appointment</param>
/// <param name="outlook">Outlook Application
///                to avoid opening and closing repeatedly</param>
private void RemoveAppointment(DateTime dtStart, 
        DateTime dtEnd, string subject, string site,
        InteropOutlook.ApplicationClass outlook)
{
    //
    //    COM Exception cause: Single quotes in Subject
    //    causes RemoveAppointment to get a COM Exception
    //    in Calendar.Items.Restrict(filterAppt)
    //
    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");
        //  Ralph Hightower - 20071104
        //  FolderClass, ItemClass, and AppointmentItemClass do not appear to work
        //  Use Folder, Item, and AppointmentItem instead
        //  InteropOutlook.FolderClass olCalendarFolder = 
        //    nmOutlook.GetDefaultFolder(
        //    Microsoft.Office.Interop.Outlook.OlDefaultFolders.olFolderCalendar)
        //    as InteropOutlook.FolderClass;
        olCalendarFolder = nmOutlook.GetDefaultFolder(
          Microsoft.Office.Interop.Outlook.OlDefaultFolders.olFolderCalendar)
          as InteropOutlook.Folder;

        if (olCalendarFolder != null)
        {
            //InteropOutlook.ItemsClass calendarItems = 
            //  (InteropOutlook.ItemsClass)olCalendarFolder.Items.Restrict(filterCalendar);
            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;
    }
}

NasaStsTVSchedule Class

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.

/// <summary>
/// Method to open Nasa TV Schedule using Microsoft.Office.Interop.Excel
/// </summary>
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);
        //
        //    COM Exception: Print_Area is not defined in spreadsheet
        //         (My Downloads\NASA\STS-116\tvsched_reva.xls
        //
        InteropExcelRange = InteropExcelWorksheet.get_Range(
               Properties.Resources.NASA_PRINT_AREA, Type.Missing);
        printArea = (System.Array)InteropExcelRange.Cells.Value2;
        //    Don't show Excel application
        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:

/// <summary>
/// Formats the time of the weekdayMonthDay according to Excel method (Interop or Excel)
/// </summary>
/// <param name="row">Row of spreadsheet</param>
/// <param name="weekdayMonthDay">Column of spreadsheet</param>
/// <returns>Time as a string formatted similar to DateTime.ToString("hh:mm tt")</returns>
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

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.

/// <summary>
/// Read MASA TV Schedule
/// Could generate an InvalidFileFormatException
/// </summary>
/// <returns>NasaStsTVScheduleEntry of scheduling information for event</returns>
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++)
        {
            //    Could get an InvalidFileFormatException exception


            try
            {
                entryType = DecodeScheduleRow(CurrentRow);
            }
            catch (InvalidFileFormatException expInvalidFileFormat)
            {
                ProcessingError = expInvalidFileFormat;
                entryType = ScheduleType.error;
            }
        }
    }
    if (entryType == ScheduleType.scheduleEntry)
    {
        CurrentRow--;   //  CurrentRow is incremented before testing
                        //  the return type of DecodeScheduleCurrentRow()


        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

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.

/// <summary>
/// Decode entries in Nasa TV Schedule Excel spreadsheet
/// Could generate an InvalidFileFormatException
/// </summary>
/// <param name="row">Row for the event to decode</param>
/// <returns>Type of event</returns>
private ScheduleType DecodeScheduleRow(int row)
{
    ScheduleType typeEntry = ScheduleType.empty;

    object cellOrbit;

    if (CurrentRow < RowCount)
    {
        //  Year has not been initialized yet
        //  A revision or creation date is required
        //  in the spreadsheet before any headers are processed
        //  The revision/creation date is in the first few lines of the spreadsheet
        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

ProcessCellOrbit is called when column one of the current row contains a value and determines the type of record for the current row.

/// <summary>
/// Process schedule entry based on the content in column 1
/// This can have many different formats
/// 1. Comments
/// 2. Header Record (ORBIT, SUBJECT, SITE, MET, C[SD]T, E[SD]T, GMT
/// 3. Date Header (DAYOFWEEK, MONTH Day)
/// 4. Flight Day Header (FD \d*)
/// 5. Definitions (not processed)
/// </summary>
/// <param name="cellOrbit">Cell Value for Orbit column</param>
/// <param name="row">Current Row</param>
/// <returns>Type of schedule for the current row</returns>
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();
                //  Row contains "DEFINITION OF TERMS"
                //  which is the end of file; no schedule entries
                //  exist after this value. What remains are the
                //  definitions of the acronyms used in the schedule
                if (cellOrbitValue.Contains(Properties.Resources.NASA_DEFINITION_OF_TERMS))
                {
                    IsEOF = true;
                    typeEntry = ScheduleType.definitionOfTerms;
                }
                //  Header: ORBIT(1)   SUBJECT(3) SITE(4)   
                //               MET(6) C[SD]T(7)  E[SD]T(8)  GMT(9)
                //  Cell number in parenthesis
                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;
                        //  Have the Date, no need to check any other missionDay


                    }

                    //  if a date heading wasn't found, look
                    //  for Flight Day heading (FD \d .*/ FD \d)
                    if (MatchFlightDayHeader(cellOrbitValue))
                    {
                        typeEntry = ScheduleType.flightDayHeading;
                    }
                }
            }
            break;
        //  Row containing orbit value must have a entry and central time,
        //  besides mission elapsed time and eastern time
        case "System.Double":
            {
                //  Know that Orbit column has a number
                //  Do the columns, Subject, Central Time, Eastern Time,
                //  and GMT contain String, Double, Double, Double?
                if (IsRowScheduleEntry(row))
                    typeEntry = ScheduleType.scheduleEntry;
            }
            break;
        default:
            typeEntry = ScheduleType.empty;
            break;
    }
    return (typeEntry);
}

ProcessEntry

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.

/// <summary>
/// Creates a NasaStsTVScheduleEntry for schedule entries
/// </summary>
/// <param name="row">Row for the schedule to capture</param>
/// <returns>Event Schedule</returns>
private NasaStsTVScheduleEntry ProcessEntry(int row)
{
    //  Running into problems converting between timezones
    //  .Net does not have the capability
    //  TimeZone information is local time
    DateTime dtCentral = HeadingDate;
    DateTime dtBeginViewingTime;
    DateTime dtEndViewingTime = HeadingDate;

    Changed = false;
    //  Column 2 will contain an asterisk if an item has changed

    bool validEntry = false;

    NasaStsTVScheduleEntry entryRow = null;

    //  If there has been a flight missionDay heading process
    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);

        //  State variables for docking and in space
        //  are not reliable for schedule revisions
        //  published after launch or docking
        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);
        }
        //  watch for "NET L" usually means "Net Landing + some time"
        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 a special event was not found, get the start time for the next event


            if (dtEndViewingTime == DateTime.MinValue)
                dtEndViewingTime = ReadAhead();
            validEntry = true;
            //  This situation may not occur (except for STS
            //  Landing since though there are next events,
            //  the events are Net Landing + a time span
            //  If the end time occurs before the beginning time, assume 30 minutes
            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}".

NasaStsTvScheduleEntry

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`

ScheduleType

/// <summary>
/// Enum to interpret the different types of rows
/// in the Space Shuttle TV Schedule spreadsheet
/// empty: blank
/// columnHeading: for the row containing the column header for the schedule events
/// dateHeading: changes the Date
/// flightDayHeading: header for the Flight Day
/// scheduleEntry: the event with start and end times
///        The Subject may be on multiple lines in
///        the same column, other column entries will be
///        blank if the subject is continued
/// definitionOfTerms: end of file, definitions are skipped
/// </summary>
public enum ScheduleType
{
    empty, columnHeading, dateHeading, flightDayHeading, scheduleEntry,
        definitionOfTerms, error
};

Points of Interest

Time Zone Conversions

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.

Switching from Daylight Saving Time to Standard Time

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.

/// <summary>
/// The Nasa TV Schedule is Houston-centric.
/// This is an easy method to convert from Central to 
/// other time zones
/// There is a kludge for that 2 AM hour that
/// does not occur when Daylight Savings Time ends 
/// and Standard Time begins for Eastern Time
///
/// Uses TimeZoneInfo developed by Microsoft MSDN BCL Team
/// </summary>
/// <param name="dtConvert">Date of the event in Central Time Zone</param>
/// <param name="timeOfday">Time of the event in Central Time Zone</param>
/// <returns>DateTime in Viewer's Time Zone</returns>
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);
    // Kludge for Eastern Daylight Time transition to Eastern Standard Time
    if ((dtCentralTZ.Hour == dtViewingTZ.Hour) && 
        (ViewingTimeZoneTZ.DisplayName == Properties.Resources.TZ_US_EASTERN))
        dtViewingTZ = dtViewingTZ.AddHours(1);

    return (dtViewingTZ);
}

Regular Expressions

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+(?<Activity>BEGINS|ENDS)

/// <summary>
/// Helper method used by:
/// 1. ShuttleCrewSleepBegins
/// 2. ShuttleCrewWakeup
/// 3. ISSCrewSleepBegins
/// 4. ISSCrewWakeUp
/// 5. EVABegins
/// 6. EVAEnds
/// </summary>
/// <param name="rgSubjectVerbPattern">Regular expression
///   for required rgSubjectVerbPattern: 
//    Shuttle or ISS</param>
/// <param name="subject">Crew: Shuttle or ISS</param>
/// <param name="verb">CREW WAKE UP or CREW SLEEP BEGINS</param>
/// <param name="row">Row in TvScheduleCells with Subject to match</param>
/// <returns>true if Required Crew is in the desired Sleep or Wake Activity</returns>
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);
}

History

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