Importing an Excel spreadsheet using C#

Here is a simple snippet which shows how to import data from an excel spreadsheet into your domains objects using c#. 

The following code snippets form part of a static helper class called ExcelImport.cs.  We make use of generics here, in that we assume the developer is aware of the type of object they are trying to populate from the excel spreadsheet. 

Snippet 1 - Static import Class, Parse<K> Method.

Our static class will have a generic method called "Parse<K>".  This method, as you can see, is a generic method which takes two parameters (fileName and workSheetName). The method makes use of the OleDbConnection and OleDbDataAdapter ADO classes to in effect "query" the spreadsheets data.

public static IEnumerable<K> Parse<K>(string fileName, string workSheetName) where K : class
{
    IEnumerable<K> list = new List<K>();
    string connectionString = string.Format("provider=Microsoft.Jet.OLEDB.4.0; data source={0};Extended Properties=Excel 8.0;", fileName);
    string query = string.Format("SELECT * FROM [{0}$]", workSheetName);

    DataSet data = new DataSet();
    using (OleDbConnection con = new OleDbConnection(connectionString))
    {
        con.Open();
        OleDbDataAdapter adapter = new OleDbDataAdapter(query, con);
        adapter.Fill(data);
        list = PopulateData<K>(data);
    }

    return list;
}

As you can see, this method hands off the actual population logic to a private static method called "PopulateData".  Parse<K> hands the type of 'K' down to the PopulateData method along with the new DataSet which has been read from the spreadsheet.

Snippet 2 -PopulateData<T> method.

private static List<T> PopulateData<T>(DataSet data) where T : class
{
    List<T> dtos = new List<T>();

    foreach (DataRow row in data.Tables[0].Rows)
    {
        T dto = Activator.CreateInstance<T>();

        PopulateFieldsFromDataRows(row, dto);
        dtos.Add(dto);
    }
    return dtos;
}

The PopulateData method instantiates a List<T> ('K' from the Parse<K> method) and iterates through each row in the DataSet.  It then uses the Activator.CreateInstance<>() to create a new dto (Of type T).  We then hand off to yet another method which uses reflection to map the data to the business object, called PopulateFieldsFromDataRows().

Snippet 2 -PopulateFieldsFromDataRows()  method.

private static void PopulateFieldsFromDataRows(DataRow row, object o)
{
    foreach (DataColumn col in row.Table.Columns)
    {

        string name = col.ColumnName;
        System.Reflection.FieldInfo field = o.GetType().GetField(name);
        if (field == null)
        {
            PropertyInfo prop = o.GetType().GetProperty(name);
            if (prop != null)
            {
                if (prop.CanWrite)
                {
                    if (prop.PropertyType.Equals(typeof(DateTime)))
                    {
                        DateTime d = (DateTime)row[name];
                        if (d.Equals(new DateTime(1900, 1, 1)))
                        {
                            d = DateTime.MinValue;
                        }

                        prop.SetValue(o, d, null);
                    }
                    else if (prop.PropertyType.Equals(typeof(int)))
                    {
                        prop.SetValue(o, Convert.ToInt32(row[name]), null);
                    }
                    else if (prop.PropertyType.Equals(typeof(decimal)))
                    {
                        prop.SetValue(o, Convert.ToDecimal(row[name]), null);
                    }
                    else
                    {
                        string value = row[name].ToString();
                        prop.SetValue(o, value, null);
                    }
                }
            }
        }
        else
        {
            if (field.FieldType.Equals(typeof(DateTime)))
            {
                DateTime d = (DateTime)row[name];
                if (d.Equals(new DateTime(1900, 1, 1)))
                {
                    d = DateTime.MinValue;
                }

                field.SetValue(o, d);
            }
            else if (field.FieldType.Equals(typeof(int)))
            {
                field.SetValue(o, Convert.ToInt32(row[name]));
            }
            else if (field.FieldType.Equals(typeof(decimal)))
            {
                field.SetValue(o, Convert.ToDecimal(row[name]));
            }
            else if (field.FieldType.Equals(typeof(bool)))
            {
                if (row[name] == DBNull.Value)
                {
                    field.SetValue(o, false);
                }
                else
                {
                    field.SetValue(o, Convert.ToBoolean(row[name]));
                }
            }
            else
            {
                field.SetValue(o, row[name]);
            }
        }
    }
}

Here, we simply reflect over the object and access any public fields, or properties.  We then try and populate the objects property/field based on its name.

Below is an example of how the static class is used.  I have implemented a CreatenewBusinessObject<> static class here, just to because I think that handing object creation off to a factory is a good thing ;).

class Program
{
    static void Main(string[] args)
    {
        IGenericObject o = BusinessObjectFactory.CreateNewBusinessObject<GenericObject>();
        GenericObject obj = new GenericObject();
        obj.Records = new List<GenericObjectDto>(ExcelImport.Parse<GenericObjectDto>("d:\\test.xls", "test"));

        foreach (GenericObjectDto dto in obj.Records)
        {
            Console.WriteLine(string.Format("FirstName: {0}, LastName:{1}", dto.FirstName, dto.LastName));
        }
    }
}

GenericObject classes:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ExcelImportSample
{
    public interface IGenericObject
    {
        string Name { get; }
        Guid Id { get; }
        List<GenericObjectDto> Records { get; set; }
    }

    public class GenericObject : IGenericObject
    {
        public GenericObject()
        {
            this.Id = new Guid();
        }

        public List<GenericObjectDto> Records
        {
            get;
            set;
        }

        public string Name
        {
            get
            {
                return "Test Object";
            }
        }

        public Guid Id
        {
            get;
            private set;
        }
    }

    public class GenericObjectDto
    {
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

    public static class BusinessObjectFactory
    {
        public static T CreateNewBusinessObject<T>() where T : class
        {
            return Activator.CreateInstance<T>();
        }
    }
}

Again, a very simple and elegant solution to reading an Excel spreadsheet into your c# application, but one step further.  By utilizing the power of Generics and Reflection we can simply hand off the processing to a static helper.

 

Have Fun !