How to read excel files row by row in c# using EPP

Question

In my MVC controller I am trying to read an excel line by line. But there is a catch.

I want to be able to map it to a list . And the model class contains 29 fields.

public class GroupMembershipUploadInput
{

    public string chpt_cd {get;set;}
    public string cnst_mstr_id {get;set;}
    public string cnst_prefix_nm {get;set;}
    public string cnst_first_nm {get;set;}
    public string cnst_middle_nm {get;set;}
    public string cnst_last_nm {get;set;}
    public string cnst_addr1_street1 {get;set;}
    public string cnst_addr1_street2 {get;set;}
    public string cnst_addr1_city {get;set;}
    public string cnst_addr1_state {get;set;}
    public string cnst_addr1_zip {get;set;}
    public string cnst_addr2_street1 {get;set;}
    public string cnst_addr2_street2 {get;set;}
    public string cnst_addr2_city {get;set;}
    public string cnst_addr2_state {get;set;}
    public string cnst_addr2_zip {get;set;}
    public string cnst_phn1_num {get;set;}
    public string cnst_phn2_num {get;set;}
    public string cnst_phn3_num {get;set;}
    public string cnst_email1_addr {get;set;}
    public string cnst_email2_addr {get;set;}
    public string job_title {get;set;}
    public string company_nm {get;set;}
    public string grp_cd {get;set;}
    public string grp_nm {get;set;}
    public string rm_ind {get;set;}
    public string notes {get;set;}
    public string stuart_cnst_grp_key {get;set;}
    public string created_by {get;set;}
    public string created_dt {get;set;}

}

public class ListGroupMembershipUploadInput
{
    public List<GroupMembershipUploadInput> GroupMembershipUploadInputList { get; set; }
}

The catch is

I should map the fields in the excel to this fields according to the column headers. Like here the column headers in excel can be

So, without hard coding I should be able to put the value under column header "First Name" to the model field "cnst_first_nm". and so on.

This column need not be in this order in excel also. So it is needed to be flexible.

I was trying like :

                ExcelPackage ep = new ExcelPackage(new FileInfo(savedFilePath));
                ExcelWorksheet ws = ep.Workbook.Worksheets["Sheet1"];

                ListGroupMembershipUploadInput gl = new ListGroupMembershipUploadInput();

                for (int i = 3; i <= ws.Dimension.End.Row; i++)
                {
                    GroupMembershipUploadInput gm = new GroupMembershipUploadInput();

                    for (int j = ws.Dimension.Start.Column; j <= ws.Dimension.End.Column; j++)
                    {
                            //gm.cnst_first_nm = ws.Cells[i, j].Value.ToString();
                    }
                    gl.Add(gm);

                }

I am not able to sort it out . I know somewhere I am missing something. What can be done ?

Answer 1

So, without hard coding I should be able to put the value under column header "First Name" to the model field "cnst_first_nm". and so on.

I'm not QUITE sure what you mean here but you will need some way to tell your code which excel header value maps to which property.

If it were me I would do this with a map from the header value to a Action<strng, GroupMembershipUploadInput> that sets the mapped property on a given GroupMembershipUploadInput object.

So I'd start with something like this to map all the 'setter' actions:

Dictionary<string, Action<string, GroupMembershipUploadInput>> MapFieldSetters { get; set; } = 
    new Dictionary<string, Action<string, GroupMembershipUploadInput>>()
    {
        { "First Name", (s,g) => g.cnst_first_nm = s },
        { "Last Name", (s,g) => g.cnst_Last_nm = s },
        //... <snip>
    };

We then need to find the index of each column header in the file we are importing. Assuming the headers are on the first row, and assuming we don't need to worry about skipping any fields, we can just do something like:

var columnHeadersInOrder = new List<string>();

for (int col = 1; col <= ws.Dimension.End.Column; col++)
{
    var headerValue = ws.Cells[1, col].Value.ToString();
    columnHeadersInOrder.Add(headerValue);
}

This gives us a List of all the column headers in the order they appear in the excel worksheet. We can now finish off your function by first retrieving the column header from our ordered list then retrieving & calling the Mapped Action<string,GroupMembershipUploadInput> to set the value on our object:

for (int i = 3; i <= ws.Dimension.End.Row; i++)
{
    GroupMembershipUploadInput gm = new GroupMembershipUploadInput();

    for (int j = ws.Dimension.Start.Column; j <= ws.Dimension.End.Column; j++)
    {
        var columnHeader = columnHeadersInOrder[j - ws.Dimension.Start.Column];
        var cellValue = ws.Cells[i, j].Value.ToString();
        MapFieldSetters[columnHeader](cellValue, gm);
    }
    gl.Add(gm);

}

Answer 2

A simple example

public void readXLS(string FilePath)
{
    FileInfo existingFile = new FileInfo(FilePath);
    using (ExcelPackage package = new ExcelPackage(existingFile))
    {
        //get the first worksheet in the workbook
        ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
        int colCount = worksheet.Dimension.End.Column;  //get Column Count
        int rowCount = worksheet.Dimension.End.Row;     //get row count
        for (int row = 1; row <= rowCount; row++)
        {
            for (int col = 1; col <= colCount; col++)
            {
                Console.WriteLine(" Row:" + row + " column:" + col + " Value:" + worksheet.Cells[row, col].Value.ToString().Trim());
            }
        }
    }
}