Have you ever had an existing application using Entity Framework and then needed to import some data from spreadsheets? Maybe it was even IMPORTANT that the import go through your existing entities. For example, a customer may want to upload updated lookup tables or import sales data from another system that can’t be integrated. This is exactly the scenario that XlsToEF is designed to handle.

Why import via your entities?

There are few reasons why you might want to import using your existing entities:

  1. You have business logic in your entities.  For instance, maybe you have a firstname, lastname, and fullname field, with logic in your entities updating the fullname field when either firstname or lastname is updated. Importing this data through your entities means that your existing logic to update the full name field will work automatically. Any rules you might have in your entity to prevent invalid data entry will be automatically run as well. If you have entities with a lot of logic, you would lose a lot by not using these entities.
  2. Your entities and database mappings already understand your database in a programmatic way.  For instance, your mappings are already set to wire your entities into your database, and they already deal with any differences between the two. In addition, you already have defined in your mappings how your database relationships and primary keys are set up.  Having this information in code means this information is available for use. Meaning, for example, we programmatically find the column marked as your primary key, and can let you know the entity has an auto-increment id and cannot impart that field, or let you know that the entity does, in fact, need you to send in a value for that id…all before actually hitting the database.  

Why use XlsToEF?

XlsToEf is meant to make importing Xls data via your entities quick and easy. Data configuration is simple and straightforward, and then the import itself is a one-liner. 

This is a complete example of a simple use case:

var importer = new XlsxToTableImporter(myDbContext);

var importMatchingData = new DataMatchesForImport
    FileName = "c:\foo.xlsx",        // path to the uploaded file
    Sheet = "Sheet 2",               // sheet in the excel doc you want to import
    Selected =                       // entity fields (or just a placeholder for the field, if you use  
        new List<XlsToEfColumnPair>  //   the custom method below) mapping to the columns in the spreadsheet
            new XlsToEfColumnPair{EfName="Id", XlsName="xlsCol5"},
            new XlsToEfColumnPair{EfName="ProductCategory", XlsName="xlsCol1"},
            new XlsToEfColumnPair{EfName="ProductName", XlsName="xlsCol2"},

// does the mapping, returns success, or information about failures
return await _xlsxToTableImporter.ImportColumnData<Order>(importMatchingData); 

Things to note here: only the most basic configuration is set up. A bit of information about the excel file, a configuration specifying what entity fields excel columns map to, and then the import. For a lot of cases, this will be all you need to do!

If yours is more than a basic case, there are many advanced features available to tailor the import process: you can customize individual fields or write your own lookups, and even have the ability to pull out some information about the mapped fields and columns in the spreadsheet that you could use to make a dynamic matcher. (You can find out more about these on the project’s page: https://github.com/HeadspringLabs/XlsToEF )

Why NOT Use XlsToEf?

XlsToEf is a fairly specialized tool, and there are quite a few cases in which it might not be a good fit:

If you don’t already have EF mappings, you may not want to add them just to use XlsToEf.  There are many other database importing tools, and this one’s value is really in its close connection to Entity Framework, allowing you to leverage your existing code quickly.

If you are importing a large amount of data, and import speed matters. XlsToEF’s value is that it uses EF and all of your existing entity-based logic to import data. This means that XlsToEF is at best only going to be as fast as EF at importing your data. EF really shines at working with a smaller number of entities in a domain-rich manner. If what you need is to dump many thousands of rows into your database as fast as possible, you are going to find a project tailored to bulk updates like Bulk Writer is a better fit.

In summary

XlsToEf provides a quick way for you to import Excel data using your existing rich domain to bring the data into your system. If you are not importing a very large amount of data, this can enable you to write an easy and domain-aware data import tool from Excel with just a few lines of code.

Let's Talk

Have a tech-oriented question? We'd love to talk.