A Silverlight control to parse Excel data into a flat collection

Many a times there is a need to import data from Excel files into a database or even just generally parse the Excel data into a typed collection in memory that can then be used for various purposes. I have written a Silverlight control that converts raw Excel text data and converts it into a flat collection of column and values i.e. Dictionary<string,List<string>> where each key represents a column name and the associated value i.e. List represents a list of values for that column. See below for a representation:

There is a strict one to one correspondence between values under different keys and it represents one row of the Excel data as shown below for e.g. in the above image, John’s last name is Gostick and date of birth is 10/11/1982 similarly, Mike’s last name is Bennett and date of birth is 11/12/1983.

The way this control works is, copy the excel data and paste into the text area as shown below, click “Next” and map the values in drop downs to the columns your client code expects and click “Import” to spit out the flat column value collection:

The reason to do this mapping is only one, the Excel data may not always be standardised sometimes there might be typos in the column headers or the column headers might be different to what your client code expects, so writing the client code to assume that the data will be always be standardised can cause runtime exceptions (mostly array index out of bounds type). Its best to let the user specify which columns in the Excel file correspond to which column that the client code expects.

The beauty of this control is that the mapping screen is entirely generated on the fly based on the number of expected columns your client code passes to it, this makes is very flexible to use under different situations. I have found using this control very helpful when i have to import Excel data into my database, although I have only tested this with flat objects i.e. objects that don’t have any relation to any other objects but I am certain if need be, the client code can be extended to deal with that situation as well. The only job of this control is parse the tab delimited data from Excel into a flat collection that can be used in the client code, what client code does with it is absolutely its own prerogative. Client code is also responsible for any mandatory column checks before using this flat collection data in the application.

Shown below is the overall architecture of this control and how it interacts with several helper classes:

Legend:
TDTP: TabDelimitedTextParser (the control XAML code behind),
CIM: ColumnToIndexMapping (helper class),
CCM: ComboboxToColumnMapping (helper class),
FCB: FlatCollectionBuilder (output builder class)

This control can be hosted in a Silverlight child window or a another usercontrol or a page etc. and the client code would need to pass it a List<string> which is a list of expected columns it expects to see in the flat collection and an Action<Dictionary<string, List<string>>> to execute once the output is ready, as shown below:

In the action method on the client side this flat data can be converted into a typed object, Student, let’s say and saved into database for instance. As shown in below sample:

public class StudentViewModel : ViewModelBase
{
    Popups.ImportPopup importPopup;

public void ImportData()
{
    List expectedcolumns = new List();
    expectedcolumns.Add(Helpers.Constants.FIRSTNAME);
    expectedcolumns.Add(Helpers.Constants.LASTNAME);
    expectedcolumns.Add(Helpers.Constants.DATEOFBIRTH);
    expectedcolumns.Add(Helpers.Constants.STUDENTCODE);
    importPopup = new Popups.ImportPopup(expectedcolumns, new Action<Dictionary<string, List>>(ColumnValuesReady));
    importPopup.Show();
}

private void ColumnValuesReady(Dictionary<string, List> columnValues)
{
    if (columnValues != null && columnValues.Count > 0)
    {
        importPopup.DialogResult = true;
        ImportData(columnValues);
    }
}

private void ImportData(Dictionary<string, List> columnValues)
{
    Services.StudentServiceClient svc = new Services.StudentServiceClient();
    svc.ImportStudentsCompleted += new EventHandler(svc_ImportStudentsCompleted);
    ObservableCollection students = ConvertToDTOs(columnValues);
    svc.ImportStudentsAsync(students);
}

private ObservableCollection ConvertToDTOs(Dictionary<string, List> flatColumnValues)
{
    ObservableCollection studentsToBeImported = new ObservableCollection();
    //parsing logic. Since the number of values under each key would always be the same (Excel data would be x rows   and y columns
    //but the number of cells under each column would always be same, you cannot select jagged data. With notepad its possible
    //so would need to handle that case)
    //get the iteration count from under any key here i have used the first key
    int numberOfStudents = flatColumnValues[Helpers.Constants.FIRSTNAME].Count;

    for (int i = 0; i < numberOfStudents; i++)
    {
        Services.StudentDTO student = new Services.StudentDTO();
        student.Id = Guid.NewGuid();
        student.FirstName = flatColumnValues[Helpers.Constants.FIRSTNAME][i];
        student.LastName = flatColumnValues[Helpers.Constants.LASTNAME][i];
        student.DateOfBirth = DateTime.Parse(flatColumnValues[Helpers.Constants.DATEOFBIRTH][i]);
        studentsToBeImported.Add(student);
    }

    return studentsToBeImported;
}

Get the full code, a reference Silverlight client application and more in depth details of this here.