Uploading excel sheet and mapping it to different model classes

Question

I am working with uploading of excel files. I converted it to datatable as follows.

public static DataTable ConvertExcelFileToDataTable(HttpPostedFileBase upload)
{
    // ExcelDataReader works with the binary Excel file, so it needs a FileStream
    // to get started. This is how we avoid dependencies on ACE or Interop:
    Stream stream = upload.InputStream;
    IExcelDataReader reader = null;
    if (upload.FileName.EndsWith(".xls"))
    {
        reader = ExcelReaderFactory.CreateBinaryReader(stream);
    }
    else if (upload.FileName.EndsWith(".xlsx"))
    {
        reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
    }
    reader.IsFirstRowAsColumnNames = true;
    DataTable dtProductCatalog = reader.AsDataSet().Tables[0];
    reader.Close();

    return dtProductCatalog;
}

Now i want to create a mapper type thing that will contain that which datatable column will convert to which model. And then convert the datatable into respective list of models. I am not sure how to start with it.

Answer 1

I'd use a model factory which will be responsible for returning you your desired model. You can then add each model to a list.

In the below example, we have a BaseModel class which holds properties common to all models if needed. But has an overrideable populate method. Then create the sub classes which inherit your base model for each model type you have. Below i've just done Model1.

public class BaseModel
{
    //Common Properties here.
    public virtual void PopulateData(DataTable data)
    {
        //Override
    }
}

public class Model1 : BaseModel
{
    //Model 1 Properties here.
    public string Name { get; set; }

    public override void PopulateData(DataTable data)
    {
       //Set all model values here from datatable.      
    }
}



public class ModelFactory
{
    private BaseModel _model;

    public BaseModel ReturnModelByName(string modelName, DataTable dtProductCatalog)
    {
        switch (modelName)
       {
            case "Model1":
                _model = new Model1();
                _model.PopulateData(dtProductCatalog);
                break;

            case "Model2": //etc....
                break;
        }      
        return _model;
    }
}

Then use the ModelFactory class to return you a populated instance of the model you want based upon the model name.

You can call the factory for each model.

var modelFactory = new ModelFactory();
var modelList = new List<BaseModel>();
var myModel = modelFactory.ReturnModelForColumn("Model1", dtProductCatalog);

modelList.Add(myModel);

Hope that points you in the right direction.