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

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

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());
            }
        }
    }
}