Dynamically read the row column value of excel sheet using EPPlus ExcelPackage

Question

Using EPPlus ExcelPackage to read the excel sheet

                    var newRow = table.NewRow();
                    newRow["Reference"] = row[rowNumber, 1].Text;
                    newRow["AssessmentTitle"] = row[rowNumber, 2].Text;
                    newRow["AssessmentDate"] = row[rowNumber, 3].Text;
                    newRow["AssessmentLabel"] = row[rowNumber, 4].Text;
                    newRow["Likelihood"] = row[rowNumber, 5].Text;
                    newRow["Impact"] = row[rowNumber, 6].Text;

In the above code, I have mentioned the rowNumber and column number to read the cell, however, now the header is dynamic. They can be alter in any order.

How do I read the value based on dynamic column ?

Answer 1

I think you could try create a dictionary to record column number and text of startrow. I tried as below:

            var fs = file.OpenReadStream();
            using ExcelPackage pck = new ExcelPackage(fs);
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            ExcelWorksheet ws = pck.Workbook.Worksheets[0];
            DataTable dt = new DataTable();
            //get the startrow,startcolumn,endcolumn of the sheet
            int mincolumn = ws.Dimension.Start.Column;
            int maxcolumn = ws.Dimension.End.Column;
            int startrow = ws.Dimension.Start.Row;
            //Create a dictionary record the column number and text of startrow
            var dic = new Dictionary<int, String>();
            for (int i= mincolumn;i<= maxcolumn; i++)
            {
                dt.Columns.Add(ws.Cells[startrow, i].Text);
                dic.Add(i, ws.Cells[startrow, i].Text);
            }           
            
            var newrow = dt.NewRow();
            
            foreach(var key in dic.Keys)
            {
                var value = dic[key];
                
                newrow[value] = ws.Cells[2, key].Text;
            }
            dt.Rows.Add(newrow);

Tried with the excel:

The Result: