Read Excel To Class By Reflection

Question

I can't seem to find anything like this, but please link if I've missed it.

I'm trying to import a load of data from an Excel spreadsheet (xlsx). For each row I want to create a List(MyClass), where MyClass has a key/value pair of properties. For each column, I want to populate MyClass with Name = [Column Name], Value = [Column Value].

I started using OLEDB to extract the data from the spreadsheet into a DataTable, but I'm not sure if this is the best reader available. It's possible that the spreadsheet will have upwards of 50,000 rows, so I need to consider speed/performance.

This is all I have so far, I can't think how to go about populating MyClass with the column data.

var fileName = string.Format("C:/HAM.xlsx");
var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0;", fileName);

var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1]", connectionString);
var ds = new DataSet();
adapter.Fill(ds, "hardcatAssets");

var data = ds.Tables["hardcatAssets"].AsEnumerable();

foreach(var row in data)
{
    List<MyClass> assets = new List<MyClass>();
    // Do I need another foreach on row.columns?, and then add to assets.
}

MyClass

public class MyClass
{
    public string AssetCode { get; set; }

    public string Barcode { get; set; }

    public string Description { get; set; }

    public string RFIDTag { get; set; }
}

Excel Sheet

Columns: "Asset Code", "Barcode", "Description", "RFID Tag"

I've updated my original question, as I changed my mind on the structure of the object I wanted to store each row into. I'll post what I did below as an answer.

when someone talks about auto-serializing excel spreadsheets, alarm bells start ringing in my head. Is the spreadsheet filled out by humans? What safeguards will there be that the columns you expect are the columns that will be there? If it's not filled out by humans, why use excel at all — Gus, Feb 11 '14 at 16:21
Hi Gus, I too appreciate your concerns. However fortunately this spreadsheet is an extract from a HardCat Asset Management database, therefore columns wont be changing. This is the only form of data I've been provided by my client. Eventually HardCat will be replaced by the system I'm importing the data to (currently via web services) — Mark, Feb 11 '14 at 16:33
ok, if it's all you can get, you work with it. Have you seen http://stackoverflow.com/questions/15828/reading-excel-files-from-c-sharp?rq=1 ? The accepted answer there gives some nice suggestions, and examples — Gus, Feb 11 '14 at 17:49
I started using epplus.codeplex.com to work with Excel. It's a lot better for reading, because you don't wind up seeing any of the formatting tables in the file that you do with OLEDB reader. They've done a really good job with making the spreadsheet more OOP friendly as well. Give that a look as an alternative. — krillgar, Feb 12 '14 at 13:47
Hi Krillgar, That looks interesting, thanks. I've managed to get the outcome I wanted, as I'm pressed for time, but I'll consider this for the next part of my project. — Mark, Feb 12 '14 at 13:57

score 3 · Accepted Answer · edited May 23 '17 at 11:49

This is my answer below, which allowed me to capture each row of my spreadsheet into an object with properties of the same name as the spreadsheet columns (Minus spaces between words).

List<MyClass> assets = new List<MyClass>();
var fileName = string.Format("C:\\HAM.xlsx");
var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 12.0;", fileName);

var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
var ds = new DataSet();

adapter.Fill(ds, "hardcatAssets");
DataTable data = ds.Tables["hardcatAssets"];

foreach (DataRow row in data.Rows)
{
    MyClass asset = new MyClass();

    foreach(PropertyInfo pinfo in asset.GetType().GetProperties())
    {
        pinfo.SetValue(asset, row.Field<string>(this.SplitCamelCase(pinfo.Name)));
    }

    assets.Add(asset);
}

return assets;

I needed to add SplitCamelCase for use in reflection to match the class property name with the spreadsheet column name. This method essentially adds spaces between CamelCased words. This was found at Insert spaces between words on a camel-cased token.

Read Excel To Class By Reflection

1 Answers1