Question 86104 in ASP.NET Question posted in
The official documentation can be found here.

How to read data from excel sheet dynamically and add to a list in C# – Asp.net

I’m trying to upload some organization data using Excel sheet. Excel sheet has multiple columns and rows but the position of the column changes every single time of upload. I was able to manage to read the excel file if the column position remains the same every time but not sure how to dynamically the read and map the fields if the column positions are inconsistent.

I have a excel similar to this, the column names always remains the same but the position of the columns are inconsistent every time single time of upload:

|OrgName | OrgNumber| OrgLevel| OrgState | OrgText| OrgCode| OrgStatus |
| ABC    | 123      | 5       | NY       | ABC1   | N/A    | Active    |
| XYZ    | 456      | 6       | WA       | XYZ1   | N/A    | N/A       |
| ASD    | 789      | 8       | MA       | ASD1   | 4566   | Active    |
| JKL    | 852      | 7       | IL       | JKL1   | 7418   | Active    |

Sample code:

            DataSet dataSet = excelStream(files);
            DataTable tbl = dataSet.Tables[0];

            List<Model> Listmodel = new List<Model>();

            for (int i = 1; i<tbl.Rows.Count; i++)
            {
                Model model = new Model();
                model.OrgName = tbl.Rows[i][0].ToString();
                model.OrgNumber = tbl.Rows[i][1].ToString();
                model.OrgLevel = tbl.Rows[i][2].ToString();
                model.OrgState = tbl.Rows[i][3].ToString();
                model.OrgText = tbl.Rows[i][4].ToString();
                model.OrgCode = tbl.Rows[i][5].ToString();
                model.OrgStatus = tbl.Rows[i][7].ToString();
                Listmodel.Add(model);
             }

Now I need read the data dynamically to map the fields. Can any one help me please?

Tags:

2

Answers


  1. 0

    You can export your Excel as CSV which will always map columns starting from A1 that way you can iterate through CSV file and you won’t have problems with dynamically mapping

    Login or Signup to reply.
  2. 0

    You can use the name of columns to identify which column maps to which index,
    whenever a dynamic excel is uploaded no matter what will be the column sequence
    it will be find with name of column in quotes.

     DataSet dataSet = excelStream(files);
        DataTable tbl = dataSet.Tables[0];

        List<Model> Listmodel = new List<Model>();

        for (int i = 1; i<tbl.Rows.Count; i++)
        {
            Model model = new Model();
            model.OrgName = tbl.Rows[i]["OrgName"].ToString();
            model.OrgNumber = tbl.Rows[i]["OrgNumber"].ToString();
            model.OrgLevel = tbl.Rows[i]["OrgLevel"].ToString();
            model.OrgState= tbl.Rows[i]["OrgState"].ToString();
            model.OrgText = tbl.Rows[i]["OrgText"].ToString();
            model.OrgCode = tbl.Rows[i]["OrgCode"].ToString();
            model.OrgStatus = tbl.Rows[i]["OrgStatus"].ToString();
            Listmodel.Add(model);
         }
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search