Click here to Skip to main content
14,174,039 members
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

5.8K views
2 bookmarked
Posted 26 Aug 2016
Licenced CPOL

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

, 26 Aug 2016
Rate this:
Please Sign up or sign in to vote.
Using EPPlus to generate a spreadsheet that reads a CSV file and constructs a Lifetimeline (or Lifespanline, if you prefer) based on people's lifespans - for genealogists, historians, biographers, et al

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<contemporary> contemporaries = new List<contemporary>();
private List<contemporary> contemporariesOrderedByBirthYear = new List<contemporary>();
private int earliestBirthYear;
private ExcelWorksheet contemporaryWorksheet;
private int FULLNAME_COL = 1;
private int RELATIONSHIP_COL = 2;
</contemporary></contemporary></contemporary></contemporary>

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<string> _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);
    }
}
</string>

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

B. Clay Shannon
Founder Across Time & Space
United States United States
I am in the process of morphing from a software developer into a portrayer of Mark Twain. My monologue (or one-man play, entitled "The Adventures of Mark Twain: As Told By Himself" and set in 1896) features Twain giving an overview of his life up till then. The performance includes the relating of interesting experiences and humorous anecdotes from Twain's boyhood and youth, his time as a riverboat pilot, his wild and woolly adventures in the Territory of Nevada and California, and experiences as a writer and world traveler, including recollections of meetings with many of the famous and powerful of the 19th century - royalty, business magnates, fellow authors, as well as intimate glimpses into his home life (his parents, siblings, wife, and children).

Peripatetic and picaresque, I have lived in eight states; specifically, besides my native California (where I was born and where I now again reside) in chronological order: New York, Montana, Alaska, Oklahoma, Wisconsin, Idaho, and Missouri.

I am also a writer of both fiction (for which I use a nom de plume, "Blackbird Crow Raven", as a nod to my Native American heritage - I am "½ Cowboy, ½ Indian") and nonfiction, including a two-volume social and cultural history of the U.S. which covers important events from 1620-2006: http://www.lulu.com/spotlight/blackbirdcraven

You may also be interested in...

Pro

Comments and Discussions

 
PraiseAprreciation Pin
Member 1270598326-Aug-16 20:26
memberMember 1270598326-Aug-16 20:26 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web01 | 2.8.190524.3 | Last Updated 26 Aug 2016
Article Copyright 2016 by B. Clay Shannon
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid