private IEnumerable <KeyValuePair <int, string> > GetColumnMapping(ExcelWorksheet xlsWorksheet, IEnumerable <KeyValuePair <string, string> > columnPropertyMap) { if (xlsWorksheet != null && xlsWorksheet.Dimension != null && xlsWorksheet.Dimension.End != null && xlsWorksheet.Dimension.End.Column > 0 && xlsWorksheet.Dimension.End.Row > 0 && columnPropertyMap != null && columnPropertyMap.Any()) { IList <KeyValuePair <int, string> > colIndexAndPropMap = new List <KeyValuePair <int, string> >(); //Use For Loop instead of ForEach to avoid NULL RowHeadler.Text issue. for (int colIndex = 1; colIndex <= xlsWorksheet.Dimension.End.Column; colIndex++) { var xlsCell = xlsWorksheet.Cells[1, colIndex].FirstOrDefault(); if (xlsCell != null) { string columnHeader = xlsCell.Text; if (!string.IsNullOrWhiteSpace(columnHeader) && columnPropertyMap.Any(m => HelperFunc.IsEqualString(m.Key, columnHeader)) && !colIndexAndPropMap.Select(c => c.Value).Contains(columnHeader)) { string propName = columnPropertyMap .Where(m => HelperFunc.IsEqualString(m.Key, columnHeader)) .Select(m => m.Value).First(); colIndexAndPropMap.Add(new KeyValuePair <int, string>(colIndex, propName)); } } } if (colIndexAndPropMap.Count == columnPropertyMap.Count()) { return(colIndexAndPropMap); } else { IEnumerable <string> missingColumns = columnPropertyMap .Where(e => !colIndexAndPropMap.Select(c => c.Value).Contains(e.Value)) .Select(e => e.Value); _logger.LogError($"Following required columns are missing:{string.Join(", ", missingColumns)}"); throw new Exception("Following required columns are missing:" + string.Join(", ", missingColumns)); } } return(null); }
private T ConvertRowToEntity <T>(ExcelWorksheet xlsWorksheet, IEnumerable <KeyValuePair <int, string> > colCntAndPropMap, int rowNumber) { try { T objModel = (T)Activator.CreateInstance(typeof(T)); foreach (var item in colCntAndPropMap) { string value = (xlsWorksheet.Cells[rowNumber, item.Key]).Text; if (!string.IsNullOrWhiteSpace(value)) { HelperFunc.SetValue(objModel, item.Value, value); } } return(objModel); } catch (Exception ex) { _logger.LogError(ex, $"ConvertRowToEntity:{typeof(T).Name} RowNum:{rowNumber}"); } return(default);