C# Tool to Generate MS Word Document Mailbox List From MS Excel

Question

There is now a Follow up question to this question.

Abstract

The VBA solution didn't work on the clients computer because Office 2010 Starter Edition doesn't support VBA. I decided to try a C# solution instead. The portion of the tool presented in this question is the interface to the Excel interop library which allows access to Microsoft excel files. There is also a Microsoft Word interop interface that is not presented in this question. The full source code for this project including the UI can be found in my GitHub repository.

The portion of the application that is presented here opens an excel workbook file, finds the worksheets within the workbook and copies the worksheet with the apartment complex tenants into a DataTable. The user can then edit the data in the tenant data, adding, editing or deleting tenants and print or save lists of tenants for each building to Microsoft Word Documents. The edits are stored in the program until the user clicks a button in the UI to save them or exits the application. Each edit is applied separately when the data is saved to reduce the amount of time to save the updates. At the time of each edit the DataTable is updated for future edits, but each edit is stored in a list of updates.

A sanitized CSV version of the excel worksheet is presented at the bottom of the question. I don't have all the real data myself, but the sanitized version is used in testing and works fine.

Questions and Issues

The excel worksheet contains 178 rows of data with 23 columns. This takes approximately 8 seconds to load (measured within the program in debugging code that has been removed). This function is where the 8 seconds elapse:

    private void AddTenantDataToTenantTable(Excel.Range TenantDataRange,
        ref DataTable tenantTable, int headerLine, int firstColumn)
    {
        int columnCount = TenantDataRange.Columns.Count;
        int rowcount = TenantDataRange.Rows.Count;
        // This loop needs to be optimized, it takes almost 8 seconds
        for (int row = headerLine + 1; row <= rowcount; row++)
        {
            DataRow tenantData = tenantTable.NewRow();
            for (int column = firstColumn; column <= columnCount; column++)
            {
                tenantData[column - firstColumn] =
                    Convert.ToString(TenantDataRange.Cells[row, column].Value2);
            }
            tenantTable.Rows.Add(tenantData);
        }

    }

I'd appreciate any and all help with optimizing that loop for performance.

I'm also interested in the maintainability, naming consistency, reducing any cyclic complexity or object coupling. I've started using Visual Studio 2019 analytic tools, but I don't necessarily agree with some of the maintainability stats.

The Code:

The excel interface is presented first, any classes included in the code are presented after that.

CExcelInteropMethods.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Windows;
using Excel = Microsoft.Office.Interop.Excel;

namespace RentRosterAutomation
{
    // Provides the interface between the rest of the program and Microsoft excel
    class CExcelInteropMethods
    {
        // Excel objects
        private Excel.Application xlApp;
        private Excel.Workbook xlWorkbook;
        private Excel.Worksheet tenantRoster;

        // Classes written for this project
        private CPropertyComplex complex;
        private List<CApartment> TenantUpdates;     // Stored updates to be written
                                                    // by save button or program exit
        private bool worksheetChanged;
        private string tenantRosterName;
        private DataTable localTenantRoster;        // Local copy of excel worksheet
        private const int ApartmentColumn = 0;
        private const int TenantLastNameColumn = 3;
        private const int CoTenantLastNameColumn = 5;

        public CPropertyComplex Complex { get { return complex; } }
        public bool AlreadyOpenOtherApp { get; private set; }
        public bool HaveEditsToSave { get { return (TenantUpdates.Count > 0); } }
        public string WorkbookName { get; set; }

        public CExcelInteropMethods(string workBookName, string workSheetName)
        {
            worksheetChanged = false;
            WorkbookName = workBookName;
            tenantRosterName = workSheetName;
            TenantUpdates = new List<CApartment>();

            try
            {
                AlreadyOpenOtherApp = false;
                if (!string.IsNullOrEmpty(WorkbookName))
                {
                    AlreadyOpenOtherApp = WorkSheetIsOpenInOtherApp(WorkbookName);
                    if (!AlreadyOpenOtherApp)
                    {
                        GetExcelDataAndReportProgress();
                        ConstructComplexAndReport();
                        CloseWorkbookExitExcel();
                    }
                }
            }
            catch (Exception e)
            {
                string emsg = "CExcelInteropMethods Constructor failed while building Complex:" + e.Message;
                MessageBox.Show(emsg);
            }
        }

        public void SaveEditsCloseWorkbookExitExcel()
        {
            if (worksheetChanged)
            {
                SaveWorkBookEdits();
            }

            CloseWorkbookExitExcel();
        }

        public void CloseWorkbookExitExcel()
        {
            if (xlWorkbook == null || xlApp == null)
            {
                return;
            }

            xlWorkbook.Close();
            xlWorkbook = null;

            xlApp.Quit();
            xlApp = null;
        }

        public List<string> GetSheetNames()
        {
            List<string> sheetNames = new List<string>();

            StartExcelOpenWorkbook(false);
            if (xlWorkbook == null)
            {
                return null;
            }

            int SheetCount = xlWorkbook.Sheets.Count;
            for (int i = 1; i <= SheetCount; ++i)
            {
                string sheetname = xlWorkbook.Sheets[i].Name;
                sheetNames.Add(sheetname);
            }

            return sheetNames;
        }

        public void PreferencesUpdated(CUserPreferences preferences)
        {
            WorkbookName = preferences.RentRosterFile;
            tenantRosterName = preferences.RentRosterSheet;
            StartExcelOpenWorkbook(true);
        }

        public CMailboxListData GetMailboxData(CBuilding building)
        {
            CMailboxListData mailboxData = new CMailboxListData(building);
            List<int> apartmentNumbers = building.ApartmentNumbers;
            foreach (int aptNo in apartmentNumbers)
            {
                mailboxData.addApartmentData(new CApartment(aptNo));
            }

            return mailboxData;
        }

        public void DeleteTenant(int apartmentNumber)
        {
            CRenter tenant = new CRenter();
            TenantUpdates.Add(new CApartment(apartmentNumber, tenant));
            worksheetChanged = UdateTenantDataTable(apartmentNumber, tenant);
        }

        public void AddEditTenant(int apartmentNumber, CRenter tenant)
        {
            TenantUpdates.Add(new CApartment(apartmentNumber, tenant));
            worksheetChanged = UdateTenantDataTable(apartmentNumber, tenant);
        }

        public CRenter GetTenant(int apartmentNumber)
        {
            CRenter tenant = null;
            try
            {
                DataTable lTenantRoster = GetLocalTenantRoster();
                if (localTenantRoster != null)
                {
                    string searchString = "UnitNo = '" + apartmentNumber.ToString() + "'";
                    DataRow[] aptTenantData = lTenantRoster.Select(searchString);
                    tenant = FillTenantFromDataRow(aptTenantData);
                }
            }
            catch (Exception e)
            {
                MessageBox.Show("Exception in CExcelInteropMethods::GetTenant(): " + e.Message);
            }

            return tenant;
        }

        private void ConstructComplexAndReport()
        {
            if (localTenantRoster == null)
            {
                return;
            }
            List<CBuildingAndApartment> buildingAndApartments;
            Form_CurrentProgressStatus statusReport = new Form_CurrentProgressStatus();
            statusReport.MessageText = "Constructing Apartment Complex Data.";
            statusReport.Show();
            buildingAndApartments = CreateBuildingAndApartmentsList();
            complex = new CPropertyComplex("Anza Victoria Apartments, LLC", buildingAndApartments);
            statusReport.Close();
        }

        private void GetExcelDataAndReportProgress()
        {
            Form_CurrentProgressStatus statusReport = new Form_CurrentProgressStatus();
            statusReport.MessageText =
                "Starting Excel and Loading Tenant Data From Excel.";
            statusReport.Show();
            StartExcelOpenWorkbook(false);
            localTenantRoster = GetLocalTenantRoster();
            statusReport.Close();

        }

        // Creates data for a single tenant that can be edited from
        // the local data table.
        private CRenter FillTenantFromDataRow(DataRow[] aptTenantData)
        {
            CRenter tenant = new CRenter();

            tenant.LastName = aptTenantData[0].Field<string>("Last");
            tenant.FirstName = aptTenantData[0].Field<string>("First");
            tenant.CoTenantLastName = aptTenantData[0].Field<string>("Add OCC Last");
            tenant.HomePhone = aptTenantData[0].Field<string>("Ph #");
            tenant.CoTenantFirstName = aptTenantData[0].Field<string>("Add OCC First");
            tenant.RentersInsurancePolicy = aptTenantData[0].Field<string>("Renters Ins");
            tenant.LeaseStart = aptTenantData[0].Field<string>("Lease Start");
            tenant.LeaseEnd = aptTenantData[0].Field<string>("Lease End");
            tenant.Email = aptTenantData[0].Field<string>("Email");

            return tenant;
        }

        // Updates the local version of the data in the application.
        private bool UdateTenantDataTable(int apartmentNumber, CRenter tenant)
        {
            bool updated = false;
            try
            {
                DataTable lTenantRoster = GetLocalTenantRoster();
                string searchString = "UnitNo = '" + apartmentNumber.ToString() + "'";
                DataRow[] aptTenantData = lTenantRoster.Select(searchString);
                DataRow currentApartment = aptTenantData[0];
                currentApartment.BeginEdit();
                currentApartment["First"] = tenant.FirstName;
                currentApartment["Last"] = tenant.LastName;
                currentApartment["Add OCC Last"] = tenant.CoTenantLastName;
                currentApartment["Add OCC First"] = tenant.CoTenantFirstName;
                currentApartment["Ph #"] = tenant.HomePhone;
                currentApartment["Renters Ins"] = tenant.RentersInsurancePolicy;
                currentApartment["Lease Start"] = tenant.LeaseStart;
                currentApartment["Lease End"] = tenant.LeaseEnd;
                currentApartment["Email"] = tenant.Email;
                currentApartment.EndEdit();
                updated = true;
            }
            catch (Exception e)
            {
                MessageBox.Show("Exception in CExcelInteropMethods::updateDataTable(): " + e.Message);
            }

            return updated;
        }

        // Updates a row of data in the excel file
        private void UpdateColumnData(CApartment rowEdit, List<string> columnNames)
        {
            CRenter tenant = rowEdit.renter;
            Excel.Range currentRow = FindRowInWorkSheetForUpdate(rowEdit.ApartmentNumber);
            UpdateColumn(currentRow, "Last", tenant.LastName, columnNames);
            UpdateColumn(currentRow, "First", tenant.FirstName, columnNames);
            UpdateColumn(currentRow, "Add OCC First", tenant.CoTenantFirstName, columnNames);
            UpdateColumn(currentRow, "Add OCC Last", tenant.CoTenantLastName, columnNames);
            UpdateColumn(currentRow, "Ph #", tenant.HomePhone, columnNames);
            UpdateColumn(currentRow, "Renters Ins", tenant.RentersInsurancePolicy, columnNames);
            UpdateColumn(currentRow, "Lease Start", tenant.LeaseStart, columnNames);
            UpdateColumn(currentRow, "Lease End", tenant.LeaseEnd, columnNames);
            UpdateColumn(currentRow, "Email", tenant.Email, columnNames);
        }

        private DataTable GetLocalTenantRoster()
        {
            DataTable tenantRosterDt = localTenantRoster;
            if (tenantRosterDt == null)
            {
                int headerRow = 1;
                int firstColumn = 1;
                tenantRosterDt = ReadExcelIntoDatatble(headerRow, firstColumn);
            }

            return tenantRosterDt;
        }

        private void StartExcelOpenWorkbook(bool showErrorMessage)
        {
            if (xlApp != null && xlWorkbook != null)
            {
                return;
            }

            if (string.IsNullOrEmpty(WorkbookName))
            {
                if (showErrorMessage)
                {
                    MessageBox.Show("Please update your preferences by adding the excel file that contains the tenant roster");
                }
                return;
            }

            if (xlApp == null)
            {
                xlApp = new Excel.Application();
                xlApp.Visible = false;
                xlApp.DisplayAlerts = false;
            }

            if (xlWorkbook == null)
            {
                xlWorkbook = xlApp.Workbooks.Open(WorkbookName);
            }
        }

        private void OpenTenantRosterWorkSheet()
        {
            try
            {
                if (!string.IsNullOrEmpty(tenantRosterName))
                {
                    StartExcelOpenWorkbook(true);
                    List<string> sheetNames = GetSheetNames();
                    bool exists = sheetNames.Any(x => x.Contains(tenantRosterName));
                    if (!exists)
                    {
                        MessageBox.Show("The workbook " + WorkbookName + " does not contain the worksheet " + tenantRosterName);
                        tenantRoster = null;
                    }
                    else
                    {
                        tenantRoster = xlWorkbook.Worksheets[tenantRosterName];
                    }
                }
            }
            catch (Exception e)
            {
                localTenantRoster = null;
                string eMsg = "Function CExcelInteropMethods.openTenantRosterWorkSheet() failed: " + e.Message;
                MessageBox.Show(eMsg);
            }
        }

        // To enhance performance the excel worksheet is read once into a local
        // DataTable.
        private DataTable ReadExcelIntoDatatble(int HeaderLine, int ColumnStart)
        {
            try
            {
                StartExcelOpenWorkbook(true);
                OpenTenantRosterWorkSheet();
                if (tenantRoster == null)
                {
                    return null;
                }

                Excel.Range TenantDataRange = tenantRoster.UsedRange;
                DataTable tenantTable = CreateDataTableAddColumns(TenantDataRange,
                    HeaderLine, ColumnStart);
                AddTenantDataToTenantTable(TenantDataRange, ref tenantTable,
                    HeaderLine, ColumnStart);

                // We don't need access to the data in excel while the edits
                // are being made or the word documents are generated.
                CloseWorkbookExitExcel();

                return tenantTable;
            }
            catch (Exception ex)
            {
                string eMsg = "In ReadExcelToDatatble error: " + ex.Message;
                MessageBox.Show(ex.Message);
                return null;
            }
        }

        private DataTable CreateDataTableAddColumns(Excel.Range TenantDataRange,
            int headerLine, int firstColumn)
        {
            DataTable tenantTable = new DataTable();
            int columnCount = TenantDataRange.Columns.Count;

            for (int column = firstColumn; column <= columnCount; column++)
            {
                tenantTable.Columns.Add(Convert.ToString
                    (TenantDataRange.Cells[headerLine, column].Value2), typeof(string));
            }

            return tenantTable;
        }

        private void AddTenantDataToTenantTable(Excel.Range TenantDataRange,
            ref DataTable tenantTable, int headerLine, int firstColumn)
        {
            int columnCount = TenantDataRange.Columns.Count;
            int rowcount = TenantDataRange.Rows.Count;
            // This loop needs to be optimized, it takes almost 8 seconds
            for (int row = headerLine + 1; row <= rowcount; row++)
            {
                DataRow tenantData = tenantTable.NewRow();
                for (int column = firstColumn; column <= columnCount; column++)
                {
                    tenantData[column - firstColumn] =
                        Convert.ToString(TenantDataRange.Cells[row, column].Value2);
                }
                tenantTable.Rows.Add(tenantData);
            }

        }

        private void SaveWorkBookEdits()
        {
            if (!HaveEditsToSave)
            {
                return;
            }
            string eSaveMsg = "Can't save edits to " + WorkbookName;
            try
            {
                Form_CurrentProgressStatus SaveStatus = new Form_CurrentProgressStatus();
                SaveStatus.MessageText = "Saving updated tenants and apartments to Excel.";
                SaveStatus.Show();
                StartExcelOpenWorkbook(false);
                OpenTenantRosterWorkSheet();
                xlApp.Visible = false;
                xlApp.DisplayAlerts = false;

                if (tenantRoster == null)
                {
                    MessageBox.Show(eSaveMsg + " can't open the sheet " + tenantRosterName);
                    return;
                }
                List<string> columnNames = GetColumnNames();
                foreach (CApartment edit in TenantUpdates)
                {
                    UpdateColumnData(edit, columnNames);
                }
                xlWorkbook.Save();
                SaveStatus.Close();
                TenantUpdates.Clear();
            }
            catch (Exception ex)
            {
                string exSaveMsg = eSaveMsg + " : " + ex.Message;
                MessageBox.Show(eSaveMsg);
            }
        }

        private Excel.Range FindRowInWorkSheetForUpdate(int apartmentNumber)
        {
            Excel.Range currentRow = null;
            object oMissing = Missing.Value;

            try
            {
                Excel.Range UnitNoColumn = GetUnitColumn();
                if (UnitNoColumn != null)
                {
                    currentRow = UnitNoColumn.Find(apartmentNumber.ToString(), oMissing,
                        Excel.XlFindLookIn.xlValues, Excel.XlLookAt.xlPart,
                        Excel.XlSearchOrder.xlByRows, Excel.XlSearchDirection.xlNext, false,
                        oMissing, oMissing);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception in CExcelInteropMethods::getRowNumberForSave(): " +
                    ex.Message);
            }

            return currentRow;
        }

        void UpdateColumn(Excel.Range currentRow, string columnName, string newValue,
            List<string> columnNames)
        {
            int columnNumber = GetColumnNumber(columnName, columnNames);
            currentRow.Cells[1, columnNumber] = newValue;
        }

        // Get the apartment unit column for searching.
        private Excel.Range GetUnitColumn()
        {
            Excel.Range UnitColumn = null;

            try
            {
                string headerName = "UnitNo";
                Excel.Range headerRow = tenantRoster.UsedRange.Rows[1];

                foreach (Excel.Range cel in headerRow.Cells)
                {
                    if (cel.Text.ToString().Equals(headerName))
                    {
                        UnitColumn = tenantRoster.Range[cel.Address, cel.End[Excel.XlDirection.xlDown]];
                        return UnitColumn;
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception in CExcelInteropMethods::GetUnitColumn(): " +
                    ex.Message);
            }

            return UnitColumn;
        }

        private int GetColumnNumber(string columnName, List<string> columnNames)
        {
            int columnNumber = 1;

            foreach (string name in columnNames)
            {
                if (name.Equals(columnName))
                {
                    return columnNumber;
                }
                columnNumber++;
            }

            return columnNumber;
        }

        private List<string> GetColumnNames()
        {
            List<string> columnNames = new List<string>();

            try
            {
                Excel.Range headerRow = tenantRoster.UsedRange.Rows[1];
                foreach (Excel.Range cell in headerRow.Cells)
                {
                    columnNames.Add(cell.Text);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show("Exception in CExcelInteropMethods::GetColumnNames(): " +
                    ex.Message);
            }

            return columnNames;
        }

        private List<CBuildingAndApartment> CreateBuildingAndApartmentsList()
        {
            if (localTenantRoster == null)
            {
                return null;
            }
            List<CBuildingAndApartment> buildingAndApartments = new List<CBuildingAndApartment>();
            int LastDataRow = localTenantRoster.Rows.Count;

            for (int row = 0; row < LastDataRow; row++)
            {
                buildingAndApartments.Add(CreateBuildAndApartmentFromDataRow(row));
            }

            return buildingAndApartments;
        }

        private CBuildingAndApartment CreateBuildAndApartmentFromDataRow(int row)
        {
            DataRow dataRow = localTenantRoster.Rows[row];
            string streetAddress = dataRow.Field<string>("Street 1").ToString();
            string apartmentNumString = dataRow.Field<string>("UnitNo").ToString();

            int apartmentNumber;
            Int32.TryParse(apartmentNumString, out apartmentNumber);

            int firstSpace = streetAddress.IndexOf(' ');
            string streetNumber = streetAddress.Substring(0, firstSpace);
            int buildingNumber;
            Int32.TryParse(streetNumber, out buildingNumber);

            CBuildingAndApartment currentApt = new CBuildingAndApartment(buildingNumber,
                apartmentNumber, streetAddress);
            return currentApt;
        }

        // Check if there is any instance of excel open using the workbook.
        private bool WorkSheetIsOpenInOtherApp(string workBook)
        {
            Excel.Application TestOnly = null;
            bool isOpened = true;
            // There are 2 possible exceptions here, GetActiveObject will throw
            // an exception if no instance of excel is running, and
            // workbooks.get_Item throws an exception if the sheetname isn't found.
            // Both of these exceptions indicate that the workbook isn't open.
            try
            {
                TestOnly = (Excel.Application)System.Runtime.InteropServices.Marshal.GetActiveObject("Excel.Application");
                int lastSlash = WorkbookName.LastIndexOf('\\');
                string fileNameOnly = WorkbookName.Substring((lastSlash + 1));
                TestOnly.Workbooks.get_Item(fileNameOnly);
                TestOnly = null;
            }
            catch (Exception)
            {
                isOpened = false;
                if (TestOnly != null)
                {
                    TestOnly = null;
                }
            }
            return isOpened;
        }
    }
}

CPropertyComplex.cs

using System;
using System.Collections.Generic;
using System.Windows;

namespace RentRosterAutomation
{
    // Contains all the information about the apartment complex (5 buildings)
    class CPropertyComplex
    {
        private string propertyName;
        private List<int> allApartmentNumbers;
        private List<CBuilding> buildingList = new List<CBuilding>();

        public string PropertyName { get { return propertyName; } }
        public List<CBuilding> Buildings { get; private set; }
        public List<string> BuildingAddressList { get; private set; }
        public List<int> StreetNumbers { get; private set; }
        public List<int> AllApartmentNumbers { get { return allApartmentNumbers; } }
        public int MinApartmentNumber { get; private set; }
        public int MaxApartmentNumber { get; private set; }

        public CPropertyComplex(string PropertyName, List<CBuildingAndApartment> bldsAntApts)
        {
            propertyName = PropertyName;
            BuildingAddressList = new List<string>();
            allApartmentNumbers = new List<int>();
            StreetNumbers = new List<int>();
            CreateBuildingList(bldsAntApts);

            foreach (CBuilding building in Buildings)
            {
                BuildingAddressList.Add(building.FullStreetAddress);
                allApartmentNumbers.AddRange(building.ApartmentNumbers);
            }

            allApartmentNumbers.Sort();

            MinApartmentNumber = allApartmentNumbers[0];
            MaxApartmentNumber = allApartmentNumbers[allApartmentNumbers.Count - 1];
        }

        public CBuilding GetBuilding(int streetNumber)
        {
            CBuilding building;

            building = buildingList.Find(x => x.AddressStreetNumber == streetNumber);

            return building;
        }

        public CBuilding GetBuilding(string streetNumber)
        {
            int iStreetNumber = 0;

            try
            {
                if (Int32.TryParse(streetNumber, out iStreetNumber))
                {
                    return GetBuilding(iStreetNumber);
                }
                else
                {
                    MessageBox.Show("Non Numeric string passed into CBuilding::GetBuilding().");
                    return null;
                }
            }
            catch (Exception)
            {
                return null;
            }
        }

        public string FindBuildingByApartment(int apartmentNumber)
        {
            string buildingAddress = null;

            foreach (CBuilding building in Buildings)
            {
                buildingAddress = building.BuildingFromApartment(apartmentNumber);
                if (!string.IsNullOrEmpty(buildingAddress))
                {
                    return buildingAddress;
                }
            }

            return buildingAddress;
        }

        private void CreateBuildingList(List<CBuildingAndApartment> buildingAptList)
        {
            buildingList = new List<CBuilding>();
            string streetName = "Anza Avenue";

            foreach (CBuildingAndApartment entry in buildingAptList)
            {
                CBuilding found = buildingList.Find(x => x.AddressStreetNumber == entry.building);
                if (found != null)
                {
                    found.AddApartmentNumber(entry.apartment);
                }
                else
                {
                    CBuilding newBuilding = new CBuilding(entry.building, streetName);
                    newBuilding.AddApartmentNumber(entry.apartment);
                    buildingList.Add(newBuilding);
                }
            }

            foreach (CBuilding building in buildingList)
            {
                building.SortApartMentNumbers();
            }

            foreach (CBuilding building in buildingList)
            {
                StreetNumbers.Add(building.AddressStreetNumber);
            }

            Buildings = buildingList;
        }


    }
}

CRenter.cs

namespace RentRosterAutomation
{
    public class CRenter
    {
        public string LastName { get; set; }
        public string FirstName { get; set; }
        public string CoTenantLastName { get; set; }
        public string CoTenantFirstName { get; set; }
        public string Email { get; set; }
        public string HomePhone { get; set; }
        public string RentersInsurancePolicy { get; set; }
        public string LeaseStart { get; set; }
        public string LeaseEnd { get; set; }

        public CRenter()
        {
            FirstName = "";
            LastName = "";
            CoTenantLastName = "";
            CoTenantFirstName = "";
            Email = "";
            HomePhone = "";
            RentersInsurancePolicy = "";
            LeaseStart = "";
        }

        public CRenter(string lastName, string homePhone)
        {
            LastName = lastName;
            HomePhone = homePhone;
            FirstName = "";
            CoTenantLastName = "";
            CoTenantFirstName = "";
            Email = "";
            RentersInsurancePolicy = "";
            LeaseStart = "";
        }

        public string MailboxListOccupantEntry()
        {
            string fullTenantNameString = LastName;

            if (!string.IsNullOrEmpty(CoTenantLastName))
            {
                fullTenantNameString = fullTenantNameString + " // " + CoTenantLastName;
            }

            return fullTenantNameString;
        }

        public string mergedName()
        {
            return (!string.IsNullOrEmpty(FirstName)) ? FirstName + " " + LastName : LastName;
        }

    }
}

CApartment.cs

namespace RentRosterAutomation
{
    class CApartment
    {
        private CExcelInteropMethods excelInteropMethods = Program.excelInteropMethods;

        public int ApartmentNumber { get; private set; }
        public CRenter renter { get; private set; }

        public CApartment(int apartmentNumber)
        {
            ApartmentNumber = apartmentNumber;
            renter = excelInteropMethods.GetTenant(apartmentNumber);
        }

        public CApartment(int apartmentNumber, CRenter Renter)
        {
            ApartmentNumber = apartmentNumber;
            renter = Renter;
        }
    }
}

CMailboxListData.cs

using System.Collections.Generic;

namespace RentRosterAutomation
{
    // This class contains the data necessary to print a mailbox tenant list,
    // Each building in the complex contains 3 floors of apartments. The documnets
    // generated by Microsoft Word have 3 columns of data, one for each floor.
    class CMailboxListData
    {
        public int AddressStreetNumber { get; private set; }
        public List<CApartment> FirstFloor { get; private set; }
        public List<CApartment> SecondFloor { get; private set; }
        public List<CApartment> ThirdFloor { get; private set; }

        public CMailboxListData(int addressStreetNumber)
        {
            AddressStreetNumber = addressStreetNumber;
        }

        public CMailboxListData(CBuilding building)
        {
            AddressStreetNumber = building.AddressStreetNumber;
            FirstFloor = new List<CApartment>();
            SecondFloor = new List<CApartment>();
            ThirdFloor = new List<CApartment>();
        }

        public void addApartmentData(CApartment apartment)
        {
            if (apartment.ApartmentNumber >= 200 && apartment.ApartmentNumber < 300)
            {
                SecondFloor.Add(apartment);
            }
            else if (apartment.ApartmentNumber >= 300)
            {
                ThirdFloor.Add(apartment);
            }
            else
            {
                FirstFloor.Add(apartment);
            }

        }
    }
}

CBuildingAndApartment.cs

namespace RentRosterAutomation
{
    // One of these is contructed for each apartment in the complex (5 buildings, 177 apartments).
    // The list is used during the construction of the apartment complex object
    class CBuildingAndApartment
    {
        public int building;
        public int apartment;
        public string fullStreetAddress;

        public CBuildingAndApartment(int streetNumber, int Apartment, string FullStreetAddress)
        {
            building = streetNumber;
            apartment = Apartment;
            fullStreetAddress = FullStreetAddress;
        }

    }
}

CBuilding.cs

using System.Collections.Generic;

namespace RentRosterAutomation
{
    // Represents one building in the apartment Complex
    class CBuilding
    {
        private List<int> aptNumbers;

        public int AddressStreetNumber { get; private set; }
        public string FullStreetAddress { get; private set; }
        public List<int> ApartmentNumbers { get { return aptNumbers; } }

        public CBuilding(int addressStreetNumber, string StreetName, List<int> apartmentNumbers)
        {
            AddressStreetNumber = addressStreetNumber;
            FullStreetAddress = addressStreetNumber.ToString() + " " + StreetName;
            aptNumbers = apartmentNumbers;
            aptNumbers.Sort();
        }

        public CBuilding(int addressStreetNumber, string StreetName)
        {
            AddressStreetNumber = addressStreetNumber;
            FullStreetAddress = addressStreetNumber.ToString() + " " + StreetName;
            aptNumbers = new List<int>();
        }

        public void AddApartmentNumber(int apartmentNumber)
        {
            if (!aptNumbers.Contains(apartmentNumber))
            {
                aptNumbers.Add(apartmentNumber);
            }
        }

        public void SortApartMentNumbers()
        {
            aptNumbers.Sort();
        }

        public bool IsApartmentInThisBuildin(int apartmentNumber)
        {
            return aptNumbers.Contains(apartmentNumber);
        }

        public string BuildingFromApartment(int apartmentNumber)
        {
            return (IsApartmentInThisBuildin(apartmentNumber)) ? FullStreetAddress : null;
        }
    }

}

Input Data

UnitNo,Unit Type,First,Last,Add OCC First,Add OCC Last,# of people,Ph #,Email,Parking,Renters Ins,Original Rent,Old Rent,Increase,Inc. Date,New,Lease Start,Lease End,Street 1,City,State,Zip,Refridgerator
101,,FName1,LastName1,,,,(XXX)XXX-XXXX,(YYY)XYZ-ZZZZ [email protected],,AAA 10/20/2016,,,,,,10/20/2016,12/31/2024,20809 Anza Ave.,HomeTown,XX,xxxxx,
102,,FName2,LastName2,,,,(XXX)XXX-XXXX,[email protected],,AAA 12/25/2017,,,,,,2/3/1981,,20809 Anza Ave.,HomeTown,XX,xxxxx,
103,,FName3,LastName3,,,,(XXX)XXX-XXXX,(ABC)XYZ-DEFG,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
104,,FName4,LastName4,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
105,,FName5,LastName5,,,,(XXX)XXX-XXXX,[email protected],,12/31/2017,,,,,,12/30/2017,,20809 Anza Ave.,HomeTown,XX,xxxxx,
106,,FName6,LastName6,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
107,,FName7,LastName7,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
108,,FName8,LastName8,XXXX,XX,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
109,,FName9,LastName9,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
110,,FName0,LastName0,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
111,,FName11,LastName11,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
112,,FName12,LastName12,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
113,,FName13,LastName13,XXXX,LastName,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
114,,FName14,LastName14,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
115,,FName15,LastName15,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
116,,FName6,LastName6,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
117,,FName7,LastName7,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
118,,FName8,LastName8,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
119,,FName9,LastName9,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
120,,FName0,LastName0,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
121,,FName1,LastName1,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
122,,FName2,LastName2,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
123,,FName3,LastName3,XXXX,LastName16,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
124,,FName4,LastName4,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
125,,FName5,LastName5,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
126,,FName6,LastName6,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
127,,LongFirstName,LongLastName,,,,(XXX)XXX-XXXX,,,SF 6/10/2019,,,,,,6/11/2019,12/31/2023,20829 Anza Ave.,HomeTown,XX,xxxxx,
128,,AnEvenLongerFirstName,AnEvenLongerLastName,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
129,,FName9,LastName9,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
130,,FName0,LastName0,XXXX,Tenant2Name,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
131,,FName1,LastName1,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
132,,FName2,LongLastName2Added,XXXX,Tenant2LongNameAdded,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
133,,FName3,LastName3,,,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
134,,FName4,LastName4,,,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
135,,FName5,LastName5,XXXX,XX,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
136,,FName6,LastName6,,,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
140,,,,,,,,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
141,,FName1,LastName1,XXXX,XXXXXX,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
142,,FName2,LastName2,,,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
143,,FName3,LastName3,,,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
144,,FName4,LastName4,,,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
145,,FName5,LastName5,XXXX,XXXXX,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
146,,FName6,LastName6,,,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
147,,FName7,LastName7,,,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
148,,FName8,LastName8,,,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
149,,FName9,LastName9,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
150,,FName0,LastName0,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
151,,FName1,LastName1,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
152,,FName2,LastName2,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
153,,FName3,LastName3,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
154,,FName4,LastName4,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
155,,FName5,LastName5,XXXX,XXXXXXX,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
156,,FName6,LastName6,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
157,,FName7,LastName7,XXXX,XXX,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
158,,FName8,LastName8,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
159,,FName9,LastName9,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
160,,FName0,LastName0,XXXX,XXXXX,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
161,,FName1,LastName1,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
162,,FName2,LastName2,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
163,,FName3,LastName3,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
164,,FName4,LastName4,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
165,,FName5,LastName5,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
166,,FName6,LastName6,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
167,,FName7,LastName7,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
168,,FName8,LastName8,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
169,,FName9,LastName9,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
170,,FName0,LastName0,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
171,,FName1,LastName1,XXXX,XXXX,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
172,,FName2,LastName2,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
201,,FName1,LastName1,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
202,,FName2,LastName2,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
203,,FName3,LastName3,,,,(XXX)XXX-XXXX,[email protected],,ALLS 8/23/2015,,,,,,8/23/2015,,20809 Anza Ave.,HomeTown,XX,xxxxx,
204,,FName4,LastName4,XXXX,XXXX,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
205,,FName5,LastName5,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
206,,FName6,LastName6,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
207,,FName7,LastName7,XXXX,XXXXX,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
208,,FName8,LastName8,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
209,,FName9,LastName9,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
210,,FName0,LastName0,XXXX,XXXXXXXX,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
211,,FName1,LastName1,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
212,,FName2,LastName2,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
213,,FName3,LastName3,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
214,,,,,,,,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
215,,FName5,LastName5,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
216,,FName6,LastName6,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
217,,FName7,LastName7,XXXX,CCDDFFGG,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
218,,FName8,LastName8,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
219,,,,,,,,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
220,,FName0,LastName0,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
221,,FName1,LastName1,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
222,,FName2,LastName2,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
223,,FName3,LastName3,XXXX,ABCDEFG,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
224,,FName4,LastName4,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
225,,FName5,LastName5,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
226,,FName6,LastName6,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
227,,FName7,LastName7,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
228,,FName8,LastName8,XXXX,XXX,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
229,,FName9,LastName9,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
230,,FName0,LastName0,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
231,,FName1,LastName1,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
232,,FName2,LastName2,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
233,,,,,,,,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
234,,FName4,LastName4,,,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
235,,FName5,LastName5,,,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
236,,FName6,LastName6,,,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
241,,,,,,,,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
242,,FName2,LastName2,,,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
243,,FName3,LastName3,,,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
244,,FName4,LastName4,,,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
245,,FName5,LastName5,,,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
246,,FName6,LastName6,,,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
247,,FName7,LastName7,,,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
248,,FName8,LastName8,,,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
249,,FName9,LastName9,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
250,,FName0,LastName0,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
251,,FName1,LastName1,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
252,,FName2,LastName2,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
253,,FName3,LastName3,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
254,,FName4,LastName4,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
255,,FName5,LastName5,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
256,,FName6,LastName6,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
257,,FName7,LastName7,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
258,,FName8,LastName8,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
259,,FName9,LastName9,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
260,,FName0,LastName0,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
261,,FName1,LastName1,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
262,,FName2,LastName2,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
263,,FName3,LastName3,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
264,,FName4,LastName4,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
265,,FName5,LastName5,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
266,,FName6,LastName6,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
267,,FName7,LastName7,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
268,,FName8,LastName8,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
269,,FName9,LastName9,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
270,,FName0,LastName0,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
271,,FName1,LastName1,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
272,,FName2,LastName2,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
302,,FName2,LastName2,XXXX,XXXX,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
303,,FName3,LastName3,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
304,,FName4,LastName4,XXXX,XXXXXXX,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
305,,FName5,LastName5,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
308,,FName8,LastName8,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
309,,FName9,LastName9,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
310,,FName0,LastName0,XXXX,XXXXXXX,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
311,,FName1,LastName1,,,,(XXX)XXX-XXXX,,,,,,,,,,,20809 Anza Ave.,HomeTown,XX,xxxxx,
322,,FName2,LastName2,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
323,,FName3,LastName3,XXXX,XXXXXX,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
324,,FName4,LastName4,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
325,,FName5,LastName5,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
328,,FName8,LastName8,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
329,,FName9,LastName9,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
330,,FName0,LastName0,XXXX,XXXXX,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
331,,FName1,LastName1,,,,(XXX)XXX-XXXX,,,,,,,,,,,20829 Anza Ave.,HomeTown,XX,xxxxx,
338,,FName8,LastName8,,,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
339,,FName9,LastName9,XXXX,XXXXX,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
340,,FName0,LastName0,,,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
341,,,,,,,,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
344,,FName4,LastName4,,,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
345,,FName5,LastName5,,,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
346,,FName6,LastName6,,,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
347,,FName7,LastName7,,,,(XXX)XXX-XXXX,,,,,,,,,,,20909 Anza Ave.,HomeTown,XX,xxxxx,
350,,FName0,LastName0,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
351,,FName1,LastName1,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
352,,FName2,LastName2,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
353,,FName3,LastName3,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
356,,FName6,LastName6,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
357,,FName7,LastName7,XXXX,XXX,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
358,,FName8,LastName8,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
359,,FName9,LastName9,,,,(XXX)XXX-XXXX,,,,,,,,,,,20929 Anza Ave.,HomeTown,XX,xxxxx,
362,,FName2,LastName2,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
363,,FName3,LastName3,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
364,,FName4,LastName4,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
365,,FName5,LastName5,XXXX,XXXXXXXX,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
368,,FName8,LastName8,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
369,,FName9,LastName9,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
370,,FName0,LastName0,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,
371,,FName1,LastName1,,,,(XXX)XXX-XXXX,,,,,,,,,,,20939 Anza Ave.,HomeTown,XX,xxxxx,

Seeing the naming convention (the C prefix for classes or type prefix on some variables) I assume your coding-background is c/c++, right? ;-] — t3chb0t, Jan 4, 2022 at 13:36
@t3chb0t Absolutely. K&R C at the university and the first 10 years, C++ starting about 8 years out of school. Pascal before C. Welcome back. — pacmaninbw, Jan 4, 2022 at 15:23

Heslacher · Accepted Answer · 2022-01-04 17:10:28Z

I managed to speed up reading the tenant data by making a single interop call to get all the data from the range and then iterating through that

private void AddTenantDataToTenantTable(Range TenantDataRange,
    ref System.Data.DataTable tenantTable, int headerLine, int firstColumn)
{
    int columnCount = TenantDataRange.Columns.Count;
    int rowcount = TenantDataRange.Rows.Count;

    var values = TenantDataRange.Value2;
    // This loop needs to be optimized, it takes almost 8 seconds
    for (int row = headerLine + 1; row <= rowcount; row++)
    {
        DataRow tenantData = tenantTable.NewRow();
        for (int column = firstColumn; column <= columnCount; column++)
        {
            tenantData[column - firstColumn] =
                //Convert.ToString(TenantDataRange.Cells[row, column].Value2);
                Convert.ToString(values[row, column]);
        }
        tenantTable.Rows.Add(tenantData);
    }

}

individual calls to Range.Cells[] are slow and making a separate call for each cell in the rows*cols block was the problem.

You can probably do the same for CreateDataTableAddColumns() although it is probably not a big performance hit at the moment.

Other Areas

Error Handling
An error which occurs when reading the tenant data can cause an orphaned instance of Excel

private System.Data.DataTable ReadExcelIntoDatatble(int HeaderLine, int ColumnStart)
{
    try
    {
        StartExcelOpenWorkbook(true);
        OpenTenantRosterWorkSheet();
        if (tenantRoster == null)
        {
            return null;
        }

        Range TenantDataRange = tenantRoster.UsedRange;
        System.Data.DataTable tenantTable = CreateDataTableAddColumns(TenantDataRange,
            HeaderLine, ColumnStart);
        AddTenantDataToTenantTable(TenantDataRange, ref tenantTable,
            HeaderLine, ColumnStart);

        // We don't need access to the data in excel while the edits
        // are being made or the word documents are generated.
        CloseWorkbookExitExcel();

        return tenantTable;
    }
    catch (Exception ex)
    {
        string eMsg = "In ReadExcelToDatatble error: " + ex.Message;
        MessageBox.Show(ex.Message);
        return null;
    }
}

Say we have an error in AddTenantDataToTenantTable(), then we drop down to the exception handler skipping past the CloseWorkbookExitExcel() call which leaves the excel instance running in background. This call should be in a finally block so that it is called whether we have an exception or not.

Coupling
There is an amount of coupling between parts of the system which do not need to know about each other.

The MessageBox() calls limit the places where CExcelInteropMethods and CPropertyComplex can be used. I would push for propagating the exceptions upwards to a higher level and reporting them there or, injecting an interface for reporting the exceptions which can have an implementation which calls MessageBox. With the current code, Automated Unit Testing error conditions for CExcelInteropMethods is a problem.

A CExcelInteropMethods instance in CApartment is iffy at the best of times but a hardcoded reference to an instance in Program heavily impacts reusability and maintenance.

Chained Constructors

public CBuilding(int addressStreetNumber, string StreetName, List<int> apartmentNumbers)
{
    AddressStreetNumber = addressStreetNumber;
    FullStreetAddress = addressStreetNumber.ToString() + " " + StreetName;
    aptNumbers = apartmentNumbers;
    aptNumbers.Sort();
}

public CBuilding(int addressStreetNumber, string StreetName)
{
    AddressStreetNumber = addressStreetNumber;
    FullStreetAddress = addressStreetNumber.ToString() + " " + StreetName;
    aptNumbers = new List<int>();
}

If we change the format for the FullStreetAddress, then it needs to be changed in two places - Maintainability issue. We should chain the constructors

public CBuilding(int addressStreetNumber, string StreetName)
        : this(addressStreetNumber, StreetName, new List<int>())
{
}

public CBuilding(int addressStreetNumber, string StreetName, List<int> apartmentNumbers)
{
    AddressStreetNumber = addressStreetNumber;
    FullStreetAddress = addressStreetNumber.ToString() + " " + StreetName;
    aptNumbers = apartmentNumbers;
    aptNumbers.Sort();
}

or, perhaps, use a default parameter

public CBuilding(int addressStreetNumber, string StreetName, List<int> apartmentNumbers = null)
{
    AddressStreetNumber = addressStreetNumber;
    FullStreetAddress = addressStreetNumber.ToString() + " " + StreetName;
    aptNumbers = apartmentNumbers ?? new List<int>();
    aptNumbers.Sort();
}

either way, we cut down the number of places where updates need to be made.

Naming
The recommendation for class naming in C# is to not use a C prefix.
The recommendation for parameters names is to use camelCasing.

I managed to speed up reading the tenant data by making a single interop call to get all the data from the range and then iterating through that - yeah, this applies to virtually all Excel operations even with the native VBA. — t3chb0t, Jan 4, 2022 at 14:40
Good review, I'll try the performance change in about an hour, I have to go through the review queues first. I've noticed the orphaned excel process, I've debugged the code enough to generally not see it, but it is something I worry about. The message boxes helped me debug, so I can probably remove most of them and maybe re-throw the exception after taking cleanup action. Any observations about the Word portion of the program? — pacmaninbw, Jan 4, 2022 at 15:33
Seems to be a 1000% improvement, from almost 8 seconds to 6 milliseconds. — pacmaninbw, Jan 4, 2022 at 20:25

t3chb0t · Accepted Answer · 2022-01-04 15:35:15Z

I also have a couple of suggestions...

CExcelInteropMethods is creating an Excel instance and destroys it in CloseWorkbookExitExcel. A more C#-ish approach would be to make this class IDisposable and rename this method to Dispose. Consequently removing the call to it from SaveEditsCloseWorkbookExitExcel and let it only save.

You can make you code less verbose by using var virtually for every variable. Something like

List<string> sheetNames = new List<string>();

would become

var sheetNames = new List<string>();

You ofen use the MessageBox for errors. I agree with AlanT that it would be better to let the exceptions bubble and handle them by the caller. If you hover still prefer to user the current pattern it's easier to work with them when you create a small helper. Let it take one generic parameter, the class and let the compiler figure out the calling method. You also shouldn't throw away the stacktrace. This has a huge potential of saving you from hours of debugging.

public static class ErrorHelper
{

    public static void ShowErrorMessage<T>(Exception exception, [CallerMemberName] string caller = "")
    {
        MessageBox.Show($"Exception in {typeof(T).Name}.{caller}: " + Environment.NewLine + exception);
    }
}

If you use one of the recent C# then you can protect yourself from nulls with nullable annotations. Since this method can return a null, you add a quesiton mark after the return type. Now the IDE will warn you that it might be dangerous to work with the result without a null-check. There's a lot more to this in Attributes for null-state static analysis interpreted by the C# compiler. Make sure to activate this analysis in the options.

        public CRenter? GetTenant(int apartmentNumber)
        {
            CRenter tenant = null;
            
            ...

            return tenant;
        }

CExcelInteropMethods looks like a god-class as it contains many utility and unrelated methods. Some of them are about the workbook in general while others are about the data. Try to split into smaller pieces that take care only of one concern like a TenantWorkbook only about the workbook and its contents (data-table). It could contain a collection of classes where each one represents a single worksheet.

I would move the floor logic from CMailboxListData to make it property of CApartment. I think this implementation should work quite well:

public int Floor => (ApartmentNumber / 100);

Then you can reduce it to only a single List<CApartment> and easly sort, filter or do anything with it later using LINQ.

As you've noticed I'm an old C/C++ programmer and prefer distinct types over 'var' I don't like 'auto' much in C++ either. I'll think about changing some of them. I agree with you about the 'god-class' almost 600 source lines is probably too much. Thanks for the hint about 'null' and I like the LINQ suggestion. — pacmaninbw, Jan 4, 2022 at 15:42
If I change the base class and change CloseWorkbookExitExcel to Dispose() then it is automatically called when ever the class goes out of scope or made null, correct? Dispose never actually needs to be called in the code? — pacmaninbw, Jan 4, 2022 at 17:34
@pacmaninbw yes and no. It'll be automatically called only if you put it inside a using statment. Alternatively you can use try/finally and then call Dispose from there. — t3chb0t, Jan 4, 2022 at 17:42

Stack Exchange Network

C# Tool to Generate MS Word Document Mailbox List From MS Excel

Abstract

Questions and Issues

The Code:

CExcelInteropMethods.cs

CPropertyComplex.cs

CRenter.cs

CApartment.cs

CMailboxListData.cs

CBuildingAndApartment.cs

CBuilding.cs

Input Data

2 Answers 2

Your Answer

Not the answer you're looking for? Browse other questions tagged
c#
performance
excel
cyclomatic-complexity
or ask your own question.

Linked

Hot Network Questions

C# Tool to Generate MS Word Document Mailbox List From MS Excel

Abstract

Questions and Issues

The Code:

CExcelInteropMethods.cs

CPropertyComplex.cs

CRenter.cs

CApartment.cs

CMailboxListData.cs

CBuildingAndApartment.cs

CBuilding.cs

Input Data

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged c#performanceexcelcyclomatic-complexity or ask your own question.

Linked

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
c#
performance
excel
cyclomatic-complexity
or ask your own question.