public static List <TItem> GetRecords <TItem>(this ExcelWorksheet sheet, ExcelMap <TItem> map = null)
where TItem : class
{
if (sheet == null)
{
return(new List <TItem>());
}
if (map == null)
{
map = GetMap <TItem>(sheet);
}
var items = new List <TItem>();
var start = map.Header + 1;
var endDimension = map.MappingDirection == ExcelMappingDirectionType.Horizontal
? sheet.Dimension.End.Row
: sheet.Dimension.End.Column;
for (var rowOrColumn = start; rowOrColumn <= endDimension; rowOrColumn++)
{
var item = GetItem(sheet, rowOrColumn, map);
items.Add(item);
}
return(items);
}
private static TItem GetItem <TItem>(ExcelWorksheet sheet, int rowOrColumn, ExcelMap <TItem> map)
where TItem : class
{
var item = Activator.CreateInstance <TItem>();
foreach (var mapping in map.Mapping)
{
if ((map.MappingDirection == ExcelMappingDirectionType.Horizontal && mapping.Key > sheet.Dimension.End.Column) ||
(map.MappingDirection == ExcelMappingDirectionType.Vertical && mapping.Key > sheet.Dimension.End.Row))
{
throw new ArgumentOutOfRangeException(nameof(rowOrColumn),
$"Key {mapping.Key} is outside of the sheet dimension using direction {map.MappingDirection}");
}
var value = (map.MappingDirection == ExcelMappingDirectionType.Horizontal)
? sheet.GetValue(rowOrColumn, mapping.Key)
: sheet.GetValue(mapping.Key, rowOrColumn);
if (value != null)
{
// Test nullable
var type = mapping.Value.PropertyType.IsValueType
? Nullable.GetUnderlyingType(mapping.Value.PropertyType) ?? mapping.Value.PropertyType
: mapping.Value.PropertyType;
var convertedValue = (type == typeof(string))
? value.ToString().Trim()
: Convert.ChangeType(value, type);
mapping.Value.SetValue(item, convertedValue);
}
else
{
// Explicitly set null values to prevent properties being initialized with their default values
mapping.Value.SetValue(item, null);
}
}
return(item);
}
public void MapTest()
{
var reader = new ExcelService(xlsFile);
var service = new ExcelMap <SampleModel>(reader);
var map = service.Map();
Assert.Collection(map,
x =>
{
Assert.Contains("MYMENSINGH", x.Region);
Assert.Contains("MYMENSINGH", x.Area);
Assert.Contains("13", x.UserId);
Assert.Contains("MR.MD. SHAKHWAT HOSSAIN", x.Name);
Assert.Contains("RSM", x.Designation);
},
x =>
{
Assert.Contains("MYMENSINGH", x.Region);
Assert.Contains("MYMENSINGH", x.Area);
Assert.Contains("19", x.UserId);
Assert.Contains("MR.REZAUL KARIM", x.Name);
Assert.Contains("DSM", x.Designation);
},
x =>
{
Assert.Contains("MYMENSINGH", x.Region);
Assert.Contains("MYMENSINGH", x.Area);
Assert.Contains("930", x.UserId);
Assert.Contains("MR. ZINNATH ALI", x.Name);
Assert.Contains("PSO", x.Designation);
}
);
}
public static TItem GetRecord <TItem>(this ExcelWorksheet sheet, int rowOrColumn, ExcelMap <TItem> map = null)
where TItem : class
{
if (sheet == null)
{
return(null);
}
if (map == null)
{
map = GetMap <TItem>(sheet);
}
if (rowOrColumn <= map.Header ||
(map.MappingDirection == ExcelMappingDirectionType.Horizontal && rowOrColumn > sheet.Dimension.End.Row) ||
(map.MappingDirection == ExcelMappingDirectionType.Vertical && rowOrColumn > sheet.Dimension.End.Column))
{
return(null);
}
return(GetItem(sheet, rowOrColumn, map));
}
