A Utility to Fill Custom Data Model from Excel Sheet in Universal Apps
In a recent personal project I came across need of loading data from excel file in a Universal app. I was able to make some changes in the code found at MSDN forum and get it to work however I thought it would be a good idea to exposes a generic method that iterates through excel sheet rows and using column headers, fills custom class properties using Reflection, and returns a strongly typed list of user choice, List <T>
The code includes a Utility class, ExcelUtility with following method signature,
public static async Task<List<T>> LoadData<T>(StorageFile targetFile) where T : new()
As obvious this method takes a Storage File object (excel sheet) as input while defining the Type of list you want to be prepared and returns a strongly typed List<T> in an asynchronous way (using Task) and thus is awaitable.
How to consume this Utility in your project?
To make use of this utility in your own project, all you need to do is include ExcelUtility.cs in your universal app (change namespace as required), create a custom class (representing your Model) with property names matching headers in an excel sheet and simply call the method LoadData as follows,
List<YourClassName> lstItems = await ExcelUtility.LoadData<YourClassName>(file);
For example, that's how the sample implementation custom class and relative excel sheet looks like,
A word about Excel Date Time Format & Shared Strings
As excel file is iterated, properties are filled using Reflection and current implementation of Excel utility class takes care of int, string, double and DateTime properties (which you can extend as per your needs). Since Excel encodes its dates and times in a double where integral portion represents the days after January 1, 1900 and fraction part represents the time since midnight of the day referenced, I’ve made use of DateTime .NET framework class code found at DotNetFramework.org.
Also note that Excel makes use of a concept known as Shared Strings such that the duplicate strings in a file are stored once and its index is reused for space optimization. If you are keen to look into the code that iterates through excel content, you can study more about shared string here.
Here’s how the working sample looks like in execution,
- Note that,
- Current implementation is only valid for parsing simple data model from a single sheet however it can be extended as per your needs.
- Current implementation doesn't bring into consideration Exception handling
- The utility has not been tested for Windows Phone but it is believed to work just fine however you'll have to change logic for OpenFilePicker as it is slightly different in Windows Phone for Universal apps than Windows Store one.