How to Generate a "Contemporaries" (Lifetimeline) Spreadsheet Using EPPlus

• Download source - 2 KB
• Download ContemporariesExeAndDLL.zip - 429.4 KB

Visualizing Overlapping Lifespans

To visualize when a person lived in relation to another person (whether or not they were related - in the familial or any other sense), timelines can be helpful; this utility (source and .exe/.dll attached, separately) allows you to do that, and is probably most interesting to genealogists, historians, biographers, etc.

You could use this utility to read parsed genealogy files, show when famous painters, poets, politicians, or punters lived in relation to each other, or ... wherever your interests/imagination takes you with it.

First of all, you need at least one .csv file with contents like the following:

• John Marshall Clemens, Father, 1798, 1847
• Jane Lampton Clemens, Mother, 1803, 1890
• Samuel Clemens ("Mark Twain"), Self, 1835, 1910
• Olivia "Livy" Clemens, Wife, 1845, 1904
• Langdon Clemens, Son, 1870, 1872
• Susy "Modoc" Clemens, Daughter, 1872, 1896
• Clara "Bay" Clemens, Daughter, 1874, 1962
• Jane "Jean" Clemens, Daughter, 1880, 1909
• Orion Clemens, Brother, 1825, 1897
• Pamela Clemens Moffett, Sister, 1827, 1904
• Henry Clemens, Brother, 1838, 1858
• Nina Gabrilowitsch, Granddaughter, 1910, 1966
• Jervis Langdon, Father-in-Law, 1809, 1870
• Charley Langdon, Brother-in-Law, 1849, 1916
• Bret Harte, Frienemy, 1836, 1902
• Joel Chandler Harris, Friend, 1848, 1908
• George Washington Cable, Friend, 1844, 1925
• James Redpath, Associate, 1833, 1891
• Ulysses S. Grant, Friend, 1822, 1885
• William Dean Howells, Friend, 1837, 1920
• Henry H. Rodgers, Friend, 1840, 1909
• Joe Twichell, Friend, 1838, 1918
• Horace Bixby, Friend, 1826, 1912
• Calvin Higbie, Friend, 1830, 1906

Each line contains four comma-separated values, to wit: Name, Relationship, Year of Birth, Year of Death.

The utility is written in such a way that one of the lines can be a person who is viewed as "special"; he or she has the relationship "Self" (see the third entry in the list above, "Mark Twain"). If there is such a person designated in the file, their data is bolded, and their timeline is given a different color than the others. If you want a "flat" Lifetimeline, simply don't have anybody in the file with the relationship of "Self"

Here is how the spreadsheet looks when using the list shown above:

And after scrolling horizontally to the subject's lifetime:

Now for the code. First, create a class that represents the contents of the CSV file:

public class Contemporary
{
    public string FullName { get; set;}
    public string Relationship { get; set; }
    public int BirthYear { get; set; }
    public int DeathYear { get; set; }
}

On the main form of this Winforms utility, I added these declarations:

private readonly List contemporaries = new List();
private List contemporariesOrderedByBirthYear = new List();
private int earliestBirthYear;
private ExcelWorksheet contemporaryWorksheet;
private int FULLNAME_COL = 1;
private int RELATIONSHIP_COL = 2;

The lion's (or line's) share of the code takes place within the sole button's click event handler:

private void btnLoadFile_Click(object sender, EventArgs e)
{
    DialogResult result = openFileDialog.ShowDialog();
    if (result == DialogResult.OK)
    {
        string fileName = openFileDialog.FileName;
        try
        {
            var csvLines = File.ReadAllLines(fileName).ToList();
            PopulateContempList(csvLines);
            OrderListByBirthYear();
            ConstructSheet(fileName);
            MessageBox.Show("done!");
        }
        catch (IOException)
        {
        }
    }
}

I populate the list of Contemporary this way:

private void PopulateContempList(List _fileContents)
{
    foreach (String line in _fileContents)
    {
        var c = new Contemporary();
        string[] elements = line.Split(',');
        c.FullName = elements[0];
        c.Relationship = elements[1];
        c.BirthYear = Convert.ToInt32(elements[2]);
        c.DeathYear = Convert.ToInt32(elements[3]);
        contemporaries.Add(c);
    }
}

Ordering of the list (which could be in any random order, date-wise) and assigning a value to the earliest date is accomplished as follows:

private void OrderListByBirthYear()
{
    contemporariesOrderedByBirthYear = contemporaries.OrderBy(contemp => contemp.BirthYear).ToList();
    Contemporary firstContemporary = contemporariesOrderedByBirthYear[0];
    earliestBirthYear = firstContemporary.BirthYear;
}

The spreadsheet generation code (which uses the EPPlus library) is:

private void ConstructSheet(string filename)
{
    int rowToPop = 0;
    var outputDir = @"C:\Contemporaries\";
    Directory.CreateDirectory(outputDir);
    var fileName = filename.Substring(0, filename.Length - 4);
    fileName = StripDriveAndFolders(fileName);
    fileName = String.Format("{0}{1}", outputDir, fileName);
    var fileNameWithExtension = String.Format("{0}.xlsx", fileName);
    var file = new FileInfo(outputDir + "placeholder");
    using (var package = new ExcelPackage(file))
    {
        contemporaryWorksheet = package.Workbook.Worksheets.Add("Contemporaries"); //fileName);
        foreach (Contemporary c in contemporariesOrderedByBirthYear)
        {
            rowToPop++;
            var birthYearColumn = c.BirthYear - earliestBirthYear + 3;
            var deathYearColumn = birthYearColumn + (c.DeathYear - c.BirthYear); 
            using (var fullNameCell = contemporaryWorksheet.Cells[rowToPop, FULLNAME_COL])
            {
                fullNameCell.Value = String.Format("{0} ({1}-{2})", c.FullName, c.BirthYear, c.DeathYear);
                fullNameCell.Style.WrapText = false;
                fullNameCell.Style.Font.Size = 12;
                if (c.Relationship.ToUpper().Contains("SELF"))
                {
                    fullNameCell.Style.Font.Bold = true;
                }
            }

            using (var relationShipCell = contemporaryWorksheet.Cells[rowToPop, RELATIONSHIP_COL])
            {
                relationShipCell.Value = c.Relationship;
                relationShipCell.Style.WrapText = false;
                relationShipCell.Style.Font.Size = 12;
                if (c.Relationship.ToUpper().Contains("SELF"))
                {
                    relationShipCell.Style.Font.Bold = true;
                    relationShipCell.Value = "--";
                }
            }

            using (var birthYearCell = contemporaryWorksheet.Cells[rowToPop, birthYearColumn])
            {
                birthYearCell.Value = c.BirthYear;
                birthYearCell.Style.WrapText = false;
                birthYearCell.Style.Font.Size = 12;
                if (c.Relationship.ToUpper().Contains("SELF"))
                {
                    birthYearCell.Style.Font.Bold = true;
                }
            }

            using (var deathYearCell = contemporaryWorksheet.Cells[rowToPop, deathYearColumn])
            {
                deathYearCell.Value = c.DeathYear;
                deathYearCell.Style.WrapText = false;
                deathYearCell.Style.Font.Size = 12;
                if (c.Relationship.ToUpper().Contains("SELF"))
                {
                    deathYearCell.Style.Font.Bold = true;
                }
            }

            // Now give the "living years" a background color
            using (var livingYearsRange = contemporaryWorksheet.Cells[rowToPop, birthYearColumn+1, rowToPop, 

deathYearColumn - 1])
            {
                // Make the subject's timeline stand out
                if (c.Relationship.ToUpper().Contains("SELF"))
                {
                    livingYearsRange.Style.Font.Bold = true;
                    livingYearsRange.Style.Fill.PatternType = ExcelFillStyle.DarkVertical;
                    livingYearsRange.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(242, 178, 17));
                }
                else
                {
                    livingYearsRange.Style.Fill.PatternType = ExcelFillStyle.LightHorizontal;
                    livingYearsRange.Style.Fill.BackgroundColor.SetColor(Color.ForestGreen);
                }
            }
        }
        contemporaryWorksheet.View.FreezePanes(2, 2);
        contemporaryWorksheet.Cells.AutoFitColumns();
        filename = fileNameWithExtension; 
        if (File.Exists(filename))
        {
            File.Delete(filename);
        }
        Stream stream = File.Create(filename);
        package.SaveAs(stream);
        stream.Close();
        package.Save();
    }
}

The utility to strip out the drive letter through the last folder is simply:

private string StripDriveAndFolders(string fileName)
{
    int lastBackwhackPos = fileName.LastIndexOf('\\');
    return fileName.Substring(lastBackwhackPos+1);
}

You can download the .exe and .dll to run as-is; if you want to tweak the code (change the fonts, colors, styles, add more features or whatever, download the source

NOTE: When you run this utility, the generated spreadsheet (*.xlsx) file is placed in a folder it creates on your hard drive named "Contemporaries"; for example, the spreadsheet shown in the screen shots is in C:\Contemporaries\[BaseNameOfCSVFile].xlsx, in my case C:\Contemporaries\SLCContemporaries.xlsx, because the name of the CSV file is SLCContemporaries.csv