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");
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;
}
}
using (var livingYearsRange = contemporaryWorksheet.Cells[rowToPop, birthYearColumn+1, rowToPop,
deathYearColumn - 1])
{
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
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