/// <summary>
/// Adds a mapping from a column name to a property.
/// </summary>
/// <param name="t">The type that contains the property to map to.</param>
/// <param name="columnIndex">Index of the column.</param>
/// <param name="propertyName">Name of the property.</param>
public void AddMapping(Type t, int columnIndex, string propertyName)
{
var typeMapper = TypeMapperFactory.Create(t);
var prop = t.GetProperty(propertyName, BindingFlags.Instance | BindingFlags.Public);
typeMapper.ColumnsByIndex[columnIndex] = new ColumnInfo(prop);
}
void Save(Stream stream, ISheet sheet)
{
var objects = Objects[sheet.SheetName];
var typeMapper = TypeMapperFactory.Create(objects.First().Value.GetType());
var columnsByIndex = GetColumns(sheet, typeMapper);
foreach (var o in objects)
{
var i = o.Key;
var row = sheet.GetRow(i);
if (row == null)
{
row = sheet.CreateRow(i);
}
foreach (var col in columnsByIndex)
{
var cell = row.GetCell(col.Key, MissingCellPolicy.CREATE_NULL_AS_BLANK);
col.Value.SetCellStyle(cell);
col.Value.SetCell(cell, col.Value.GetProperty(o.Value));
}
}
Workbook.Write(stream);
}
/// <summary>
/// Adds a mapping from a column index to a property.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="columnIndex">Index of the column.</param>
/// <param name="propertyExpression">The property expression.</param>
public void AddMapping <T>(int columnIndex, Expression <Func <T, object> > propertyExpression)
{
var typeMapper = TypeMapperFactory.Create(typeof(T));
var prop = GetPropertyInfo(propertyExpression);
typeMapper.ColumnsByIndex[columnIndex] = new ColumnInfo(prop);
}
IEnumerable Fetch(ISheet sheet, Type type)
{
var typeMapper = TypeMapperFactory.Create(type);
var firstRow = sheet.GetRow(HeaderRow ? HeaderRowNumber : MinRowNumber);
var cells = Enumerable.Range(0, firstRow.LastCellNum).Select(i => firstRow.GetCell(i, MissingCellPolicy.CREATE_NULL_AS_BLANK));
var columns = cells
.Where(c => !HeaderRow || (c.CellType == CellType.String && !string.IsNullOrWhiteSpace(c.StringCellValue)))
.Select(c => new
{
c.ColumnIndex,
ColumnInfo = GetColumnInfo(typeMapper, c)
})
.Where(c => c.ColumnInfo != null)
.ToDictionary(c => c.ColumnIndex, c => c.ColumnInfo);
var i = MinRowNumber;
IRow row = null;
if (TrackObjects)
{
Objects[sheet.SheetName] = new Dictionary <int, object>();
}
while (i <= MaxRowNumber && (row = sheet.GetRow(i)) != null)
{
// optionally skip header row and blank rows
if ((!HeaderRow || i != HeaderRowNumber) && (!SkipBlankRows || row.Cells.Any(c => !IsCellBlank(c))))
{
var o = Activator.CreateInstance(type);
foreach (var col in columns)
{
var cell = row.GetCell(col.Key);
if (cell != null && (!SkipBlankRows || !IsCellBlank(cell)))
{
var cellValue = GetCellValue(cell, col.Value);
try
{
col.Value.SetProperty(o, cellValue);
}
catch (Exception e)
{
throw new ExcelMapperConvertException(cellValue, col.Value.PropertyType, i, col.Key, e);
}
}
}
if (TrackObjects)
{
Objects[sheet.SheetName][i] = o;
}
yield return(o);
}
i++;
}
}
/// <summary>
/// Adds a mapping from a column name to a property.
/// </summary>
/// <param name="t">The type that contains the property to map to.</param>
/// <param name="columnName">Name of the column.</param>
/// <param name="propertyName">Name of the property.</param>
public ColumnInfo AddMapping(Type t, string columnName, string propertyName)
{
var typeMapper = TypeMapperFactory.Create(t);
var prop = t.GetProperty(propertyName, BindingFlags.Instance | BindingFlags.Public);
var columnInfo = new ColumnInfo(prop);
typeMapper.ColumnsByName[columnName] = columnInfo;
return(columnInfo);
}
/// <summary>
/// Adds a mapping from a column name to a property.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="columnName">Name of the column.</param>
/// <param name="propertyExpression">The property expression.</param>
public ColumnInfo AddMapping <T>(string columnName, Expression <Func <T, object> > propertyExpression)
{
var typeMapper = TypeMapperFactory.Create(typeof(T));
var prop = GetPropertyInfo(propertyExpression);
var columnInfo = new ColumnInfo(prop);
typeMapper.ColumnsByName[columnName] = columnInfo;
return(columnInfo);
}
IEnumerable <T> Fetch <T>(ISheet sheet) where T : new()
{
var typeMapper = TypeMapperFactory.Create(typeof(T));
var columns = sheet.GetRow(HeaderRow ? HeaderRowNumber : MinRowNumber).Cells
.Where(c => !HeaderRow || (c.CellType == CellType.String && !string.IsNullOrWhiteSpace(c.StringCellValue)))
.Select(c => new { c.ColumnIndex, ColumnInfo = HeaderRow ? typeMapper.GetColumnByName(c.StringCellValue) : typeMapper.GetColumnByIndex(c.ColumnIndex) })
.Where(c => c.ColumnInfo != null)
.ToDictionary(c => c.ColumnIndex, c => c.ColumnInfo);
var i = MinRowNumber;
IRow row = null;
if (TrackObjects)
{
Objects[sheet.SheetName] = new Dictionary <int, object>();
}
while (i <= MaxRowNumber && (row = sheet.GetRow(i)) != null)
{
CurrentRow = row;
// optionally skip header row and blank rows
if ((!HeaderRow || i != HeaderRowNumber) && (!SkipBlankRows || row.Cells.Any(c => c.CellType != CellType.Blank)))
{
var o = new T();
foreach (var col in columns)
{
var cell = row.GetCell(col.Key);
if (cell != null)
{
var cellValue = GetCellValue(cell, col.Value);
try
{
col.Value.SetProperty(o, cellValue);
}
catch (Exception e)
{
throw new ExcelMapperConvertException(cellValue, col.Value.PropertyType, i, col.Key, e);
}
}
else
{
}
}
if (TrackObjects)
{
Objects[sheet.SheetName][i] = o;
}
yield return(o);
}
i++;
}
}
/// <summary>
/// Ignores a property.
/// </summary>
/// <param name="t">The type that contains the property to map to.</param>
/// <param name="propertyName">Name of the property.</param>
public void Ignore(Type t, string propertyName)
{
var typeMapper = TypeMapperFactory.Create(t);
var prop = t.GetProperty(propertyName, BindingFlags.Instance | BindingFlags.Public);
var kvp = typeMapper.ColumnsByName.FirstOrDefault(c => c.Value.Property == prop);
if (kvp.Key != null)
{
typeMapper.ColumnsByName.Remove(kvp.Key);
}
}
/// <summary>
/// Ignores a property.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="propertyExpression">The property expression.</param>
public void Ignore <T>(Expression <Func <T, object> > propertyExpression)
{
var typeMapper = TypeMapperFactory.Create(typeof(T));
var prop = GetPropertyInfo(propertyExpression);
var kvp = typeMapper.ColumnsByName.FirstOrDefault(c => c.Value.Property == prop);
if (kvp.Key != null)
{
typeMapper.ColumnsByName.Remove(kvp.Key);
}
}
void Save <T>(Stream stream, ISheet sheet, IEnumerable <T> objects)
{
var typeMapper = TypeMapperFactory.Create(typeof(T));
var columnsByIndex = GetColumns(sheet, typeMapper);
var i = MinRowNumber;
SetColumnStyles(sheet, columnsByIndex);
foreach (var o in objects)
{
if (i > MaxRowNumber)
{
break;
}
if (HeaderRow && i == HeaderRowNumber)
{
i++;
}
var row = sheet.GetRow(i);
if (row == null)
{
row = sheet.CreateRow(i);
}
foreach (var col in columnsByIndex)
{
var cell = row.GetCell(col.Key, MissingCellPolicy.CREATE_NULL_AS_BLANK);
col.Value.SetCellStyle(cell);
col.Value.SetCell(cell, col.Value.GetProperty(o));
}
i++;
}
if (SkipBlankRows)
{
while (i <= sheet.LastRowNum && i <= MaxRowNumber)
{
var row = sheet.GetRow(i);
while (row.Cells.Any())
{
row.RemoveCell(row.GetCell(row.FirstCellNum));
}
i++;
}
}
Workbook.Write(stream);
}
IEnumerable <T> Fetch <T>(ISheet sheet) where T : new()
{
var typeMapper = TypeMapperFactory.Create(typeof(T));
var columns = sheet.GetRow(0).Cells
.Where(c => !HeaderRow || !string.IsNullOrWhiteSpace(c.StringCellValue))
.Select(c => new { ColumnIndex = c.ColumnIndex, ColumnInfo = HeaderRow ? typeMapper.GetColumnByName(c.StringCellValue) : typeMapper.GetColumnByIndex(c.ColumnIndex) })
.Where(c => c.ColumnInfo != null)
.ToDictionary(c => c.ColumnIndex, c => c.ColumnInfo);
var i = HeaderRow ? 1 : 0;
IRow row = null;
if (TrackObjects)
{
Objects[sheet.SheetName] = new Dictionary <int, object>();
}
while ((row = sheet.GetRow(i)) != null)
{
if (!SkipBlankRows || row.Cells.Any(c => c.CellType != CellType.Blank))
{
var o = new T();
foreach (var col in columns)
{
var cell = row.GetCell(col.Key);
if (cell != null)
{
col.Value.SetProperty(o, GetCellValue(cell));
}
}
if (TrackObjects)
{
Objects[sheet.SheetName][i] = o;
}
yield return(o);
}
i++;
}
}