After Thought

Using ExcelMapper

with 5 comments

ExcelMapper is a tool to create and access data from excel (*.xls and *.xlsx) as a collection of strongly typed objects.  Here is  brief description on how to use it:

BuildDtosFromExcel.exe is an exe file used to generate an assembly of strongly typed objects. Each class is equivalent to a worksheet in an excel file. For instance, User tab in excel is mapped to User class and each of the columns is mapped to properties in the User class.

UserExcel

To create a User class, run the following command:

BuildDTOsFromExcel User.xlsx

The command generates ExcelToDTOMapper.DTO.dll assembly that contains User class as shown below:

 namespace ExcelToDTOMapper.DTO
 {
   public class User
   {
     public System.Double Id { get; set; }
     public System.String LastName { get; set; }
     public System.String FirstName { get; set; }
     public System.DateTime DateOfBirth { get; set; }
   }
 }

If you want to create an assembly of different name, you can pass an [optional] assembly name. You can get the complete usage of BuildDTOsFromExcel by running BuildDTOsFromExcel /?.

You can now add this assembly as a reference to your project along with ExcelMapper.dll, Microsoft.Practices.ServiceLocation (available in the download) to access data from Excel.

ExcelMapperReference

Finally, add the following piece of code in your application.


  ExcelMapper.SetUp();

  IRepository repository = ServiceLocator.Current.GetInstance<IRepository>();

  foreach (User user in repository.Get<User>("Excel\\Users.xlsx", "User"))
  {
     Console.WriteLine("Hello {0} {1}", user.FirstName, user.LastName);
  }
 

ExcelMapper.SetUp() should be done only once in the whole application. If you are using StructureMap as your IoC tool, then you can add the ExcelMapperRegistry in your BootStrapper code as show belo:

public static void Initialize()
{
   ObjectFactory.Initialize(x =>
      {
          x.Scan(s =>
          {
              s.TheCallingAssembly();
              s.WithDefaultConventions();
          });
          x.AddRegistry<ExcelMapperRegistry>();
      }
   );
}

If your running on a 64 bit machine, remember to compile your code in x86 platform. Executing the above code results in the following output:

ExelMapperOutput

Since repository.Get<User>(“Excel\\Users.xlsx”, “User”) returns IEnumerable<User>, you can use Linq.

About these ads

Written by shashankshetty

May 9, 2009 at 2:56 pm

5 Responses

Subscribe to comments with RSS.

  1. […] You can read a brief description on how to use ExcelMapper here […]

  2. […] under: C#, ExcelMapper, Microsoft Excel | Tags: C#, Excel, ExcelMapper, Mic | You can now use ExcelMapper to insert records into Excel as well. Continuing with the same User example, we can insert a User […]

  3. I unzipped release zip and ran BuildDTOsFromExcel.exe but the following error is shown:

    Unhandled Exception: System.IO.FileLoadException: Could not load file or assembly ‘ExcelMapper, …

    BuildDTOsFromExcel and ExcelMapper.dll are in the same folder. I’ve tried specifying the dll path and running /? option but I get the same error.

    Jebb Burditt

    February 18, 2011 at 9:56 am

    • Jebb, I uploaded an unmerged version that is working, you can get the new version here: excelmapper

      Please let me know if there is any problem using this version.

      shashankshetty

      February 24, 2011 at 12:20 am

  4. This library is insane.. why there is so much abstraction? Interface for string variables (IFileConfiguration, IConnectionString)? ServiceLocator “pattern”? For what..? God damn, that’s just reading from excel file, not whole enterprisey-banking application.

    Paul

    February 21, 2012 at 5:39 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: