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