How to read data from excel sheet dynamically and add to a list in C#-CodePudding

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?

CodePudding user response：

To read an Excel file dynamically and map the columns to the fields, you can use the following approach:

First, you need to read the first row of the Excel file, which contains the column names. You can do this using the DataTable.Rows property.

Then, you can create a dictionary that maps the column names to the column indices. This will allow you to look up the column index for a given column name.

Next, you can iterate over the rows of the DataTable, and use the dictionary to look up the column indices for each field. You can then use these column indices to read the values from the DataTable and populate your model object.

Here's an example of how this could be implemented:

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

// Read the first row of the Excel file, which contains the column names
DataRow columnRow = tbl.Rows[0];

// Create a dictionary that maps column names to column indices
Dictionary<string, int> columnIndices = new Dictionary<string, int>();
for (int i = 0; i < columnRow.ItemArray.Length; i  )
{
    columnIndices[columnRow[i].ToString()] = i;
}

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

// Iterate over the rows of the DataTable
for (int i = 1; i < tbl.Rows.Count; i  )
{
    Model model = new Model();
    model.OrgName = tbl.Rows[i][columnIndices["OrgName"]].ToString();
    model.OrgNumber = tbl.Rows[i][columnIndices["OrgNumber"]].ToString();
    model.OrgLevel = tbl.Rows[i][columnIndices["OrgLevel"]].ToString();
    model.OrgState = tbl.Rows[i][columnIndices["OrgState"]].ToString();
    model.OrgText = tbl.Rows[i][columnIndices["OrgText"]].ToString();
    model.OrgCode = tbl.Rows[i][columnIndices["OrgCode"]].ToString();
    model.OrgStatus = tbl.Rows[i][columnIndices["OrgStatus"]].ToString();
    Listmodel.Add(model);
 }

This approach will allow you to read the Excel file dynamically, regardless of the position of the columns

CodePudding user response：

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