public static List <TEntity> SheetToEntityList <TEntity>([NotNull] ISheet sheet, int sheetIndex) where TEntity : new()
{
if (sheet.FirstRowNum < 0)
{
return(new List <TEntity>(0));
}
var configuration = InternalHelper.GetExcelConfigurationMapping <TEntity>();
var sheetSetting = GetSheetSetting(configuration.SheetSettings, sheetIndex);
var entities = new List <TEntity>(sheet.LastRowNum - sheetSetting.HeaderRowIndex);
var propertyColumnDictionary = InternalHelper.GetPropertyColumnDictionary(configuration);
var propertyColumnDic = sheetSetting.HeaderRowIndex >= 0
? propertyColumnDictionary.ToDictionary(_ => _.Key, _ => new PropertyConfiguration()
{
ColumnIndex = -1,
ColumnFormatter = _.Value.ColumnFormatter,
ColumnTitle = _.Value.ColumnTitle,
ColumnWidth = _.Value.ColumnWidth,
IsIgnored = _.Value.IsIgnored
})
: propertyColumnDictionary;
var formulaEvaluator = sheet.Workbook.GetFormulaEvaluator();
for (var rowIndex = sheet.FirstRowNum; rowIndex <= (sheetSetting.EndRowIndex ?? sheet.LastRowNum); rowIndex++)
{
var row = sheet.GetRow(rowIndex);
if (rowIndex == sheetSetting.HeaderRowIndex) // readerHeader
{
if (row != null)
{
for (var i = row.FirstCellNum; i < row.LastCellNum; i++)
{
if (row.GetCell(i) == null)
{
continue;
}
var col = propertyColumnDic.GetPropertySetting(row.GetCell(i).StringCellValue.Trim());
if (null != col)
{
col.ColumnIndex = i;
}
}
}
//
if (propertyColumnDic.Values.All(_ => _.ColumnIndex < 0))
{
propertyColumnDic = propertyColumnDictionary;
}
}
else if (rowIndex >= sheetSetting.StartRowIndex)
{
if (row == null)
{
entities.Add(default);
// export via template
public static ISheet EntityListToSheetByTemplate <TEntity>(
[NotNull] ISheet sheet,
IEnumerable <TEntity> entityList,
object extraData = null)
{
if (null == entityList)
{
return(sheet);
}
var configuration = InternalHelper.GetExcelConfigurationMapping <TEntity>();
var propertyColumnDictionary = InternalHelper.GetPropertyColumnDictionary(configuration);
var globalDictionary = extraData.ParseParamInfo()
.ToDictionary(x => TemplateOptions.TemplateGlobalParamFormat.FormatWith(x.Key), x => x.Value);
foreach (var propertyConfiguration in propertyColumnDictionary)
{
globalDictionary.Add(TemplateOptions.TemplateHeaderParamFormat.FormatWith(propertyConfiguration.Key.Name), propertyConfiguration.Value.ColumnTitle);
}
var dataFuncDictionary = propertyColumnDictionary
.ToDictionary(x => TemplateOptions.TemplateDataParamFormat.FormatWith(x.Key.Name), x => x.Key.GetValueGetter <TEntity>());
foreach (var key in propertyColumnDictionary.Keys)
{
if (InternalCache.OutputFormatterFuncCache.TryGetValue(key, out var formatterFunc) && formatterFunc?.Method != null)
{
dataFuncDictionary[TemplateOptions.TemplateDataParamFormat.FormatWith(key.Name)] = entity =>
{
var val = key.GetValueGetter <TEntity>()?.Invoke(entity);
try
{
var formattedValue = formatterFunc.Invoke(new[] { entity, val });
return(formattedValue);
}
catch (Exception e)
{
Debug.WriteLine(e);
InvokeHelper.OnInvokeException?.Invoke(e);
}
return(val);
};
}
}
// parseTemplate
int dataStartRow = -1, dataRowsCount = 0;
for (var rowIndex = sheet.FirstRowNum; rowIndex <= sheet.LastRowNum; rowIndex++)
{
var row = sheet.GetRow(rowIndex);
if (row == null)
{
continue;
}
for (var cellIndex = row.FirstCellNum; cellIndex < row.LastCellNum; cellIndex++)
{
var cell = row.GetCell(cellIndex);
if (cell == null)
{
continue;
}
var cellValue = cell.GetCellValue <string>();
if (!string.IsNullOrEmpty(cellValue))
{
var beforeValue = cellValue;
if (dataStartRow <= 0 || dataRowsCount <= 0)
{
if (dataStartRow >= 0)
{
if (cellValue.Contains(TemplateOptions.TemplateDataEnd))
{
dataRowsCount = rowIndex - dataStartRow + 1;
cellValue = cellValue.Replace(TemplateOptions.TemplateDataEnd, string.Empty);
}
}
else
{
if (cellValue.Contains(TemplateOptions.TemplateDataBegin))
{
dataStartRow = rowIndex;
cellValue = cellValue.Replace(TemplateOptions.TemplateDataBegin, string.Empty);
}
}
}
foreach (var param in globalDictionary.Keys)
{
if (cellValue.Contains(param))
{
cellValue = cellValue
.Replace(param,
globalDictionary[param]?.ToString() ?? string.Empty);
}
}
if (beforeValue != cellValue)
{
cell.SetCellValue(cellValue);
}
}
}
}
if (dataStartRow >= 0 && dataRowsCount > 0)
{
foreach (var entity in entityList)
{
sheet.ShiftRows(dataStartRow, sheet.LastRowNum, dataRowsCount);
for (var i = 0; i < dataRowsCount; i++)
{
var row = sheet.CopyRow(dataStartRow + dataRowsCount + i, dataStartRow + i);
if (null != row)
{
for (var j = 0; j < row.LastCellNum; j++)
{
var cell = row.GetCell(j);
if (null != cell)
{
var cellValue = cell.GetCellValue <string>();
if (!string.IsNullOrEmpty(cellValue) && cellValue.Contains(TemplateOptions.TemplateDataPrefix))
{
var beforeValue = cellValue;
foreach (var param in dataFuncDictionary.Keys)
{
if (cellValue.Contains(param))
{
cellValue = cellValue.Replace(param,
dataFuncDictionary[param]?.Invoke(entity)?.ToString() ?? string.Empty);
}
}
if (beforeValue != cellValue)
{
cell.SetCellValue(cellValue);
}
}
}
}
}
}
//
dataStartRow += dataRowsCount;
}
// remove data template
for (var i = 0; i < dataRowsCount; i++)
{
var row = sheet.GetRow(dataStartRow + i);
if (null != row)
{
sheet.RemoveRow(row);
}
}
sheet.ShiftRows(dataStartRow + dataRowsCount, sheet.LastRowNum, -dataRowsCount);
}
return(sheet);
}
private static string GetCsvText <TEntity>(this IEnumerable <TEntity> entities, bool includeHeader)
{
if (entities == null)
{
return(string.Empty);
}
var data = new StringBuilder();
var isBasicType = typeof(TEntity).IsBasicType();
if (isBasicType)
{
if (includeHeader)
{
data.AppendLine(InternalConstants.DefaultPropertyNameForBasicType);
}
foreach (var entity in entities)
{
data.AppendLine(Convert.ToString(entity));
}
}
else
{
var dic = InternalHelper.GetPropertyColumnDictionary <TEntity>();
var props = InternalHelper.GetPropertiesForCsvHelper <TEntity>();
if (includeHeader)
{
for (var i = 0; i < props.Count; i++)
{
if (i > 0)
{
data.Append(CsvSeparatorCharacter);
}
data.Append(dic[props[i]].ColumnTitle);
}
data.AppendLine();
}
foreach (var entity in entities)
{
for (var i = 0; i < props.Count; i++)
{
var propertyValue = props[i].GetValueGetter <TEntity>()?.Invoke(entity);
if (InternalCache.OutputFormatterFuncCache.TryGetValue(props[i], out var formatterFunc) && formatterFunc?.Method != null)
{
try
{
// apply custom formatterFunc
propertyValue = formatterFunc.Invoke(new[] { entity, propertyValue });
}
catch (Exception e)
{
Debug.WriteLine(e);
InvokeHelper.OnInvokeException?.Invoke(e);
}
}
if (i > 0)
{
data.Append(CsvSeparatorCharacter);
}
// https://stackoverflow.com/questions/4617935/is-there-a-way-to-include-commas-in-csv-columns-without-breaking-the-formatting
var val = propertyValue?.ToString().Replace("\"", "\"\"");
if (!string.IsNullOrEmpty(val))
{
data.Append(val.IndexOf(CsvSeparatorCharacter) > -1 ? $"\"{val}\"" : val);
}
}
data.AppendLine();
}
}
return(data.ToString());
}
/// <summary>
/// convert csv file data to entity list
/// </summary>
/// <param name="csvBytes">csv bytes</param>
public static List <TEntity> ToEntityList <TEntity>(byte[] csvBytes) where TEntity : new()
{
if (null == csvBytes)
{
throw new ArgumentNullException(nameof(csvBytes));
}
var entities = new List <TEntity>();
if (typeof(TEntity).IsBasicType())
{
using (var ms = new MemoryStream(csvBytes))
{
using (var sr = new StreamReader(ms, Encoding.UTF8))
{
string strLine;
var isFirstLine = true;
while ((strLine = sr.ReadLine()).IsNotNullOrEmpty())
{
if (isFirstLine)
{
isFirstLine = false;
continue;
}
//
Debug.Assert(strLine != null, nameof(strLine) + " is null");
entities.Add(strLine.Trim().To <TEntity>());
}
}
}
}
else
{
var propertyColumnDictionary = InternalHelper.GetPropertyColumnDictionary <TEntity>();
var propertyColumnDic = propertyColumnDictionary.ToDictionary(_ => _.Key, _ => new PropertyConfiguration()
{
ColumnIndex = -1,
ColumnFormatter = _.Value.ColumnFormatter,
ColumnTitle = _.Value.ColumnTitle,
ColumnWidth = _.Value.ColumnWidth,
IsIgnored = _.Value.IsIgnored
});
using (var ms = new MemoryStream(csvBytes))
{
using (var sr = new StreamReader(ms, Encoding.UTF8))
{
string strLine;
var isFirstLine = true;
while ((strLine = sr.ReadLine()).IsNotNullOrEmpty())
{
var entityType = typeof(TEntity);
var cols = ParseLine(strLine);
if (isFirstLine)
{
for (var index = 0; index < cols.Count; index++)
{
var setting = propertyColumnDic.GetPropertySetting(cols[index]);
if (setting != null)
{
setting.ColumnIndex = index;
}
}
if (propertyColumnDic.Values.All(_ => _.ColumnIndex < 0))
{
propertyColumnDic = propertyColumnDictionary;
}
isFirstLine = false;
continue;
}
else
{
var entity = new TEntity();
if (entityType.IsValueType)
{
var obj = (object)entity;// boxing for value types
foreach (var key in propertyColumnDic.Keys)
{
var colIndex = propertyColumnDic[key].ColumnIndex;
if (colIndex >= 0 && colIndex < cols.Count && key.CanWrite)
{
var columnValue = key.PropertyType.GetDefaultValue();
var valueApplied = false;
if (InternalCache.ColumnInputFormatterFuncCache.TryGetValue(key, out var formatterFunc) && formatterFunc?.Method != null)
{
var cellValue = cols[colIndex];
try
{
// apply custom formatterFunc
columnValue = formatterFunc.Invoke(new object[] { cellValue });
valueApplied = true;
}
catch (Exception e)
{
Debug.WriteLine(e);
InvokeHelper.OnInvokeException?.Invoke(e);
}
}
if (valueApplied == false)
{
columnValue = cols[colIndex].ToOrDefault(key.PropertyType);
}
key.GetValueSetter()?.Invoke(entity, columnValue);
}
}
entity = (TEntity)obj;// unboxing
}
else
{
foreach (var key in propertyColumnDic.Keys)
{
var colIndex = propertyColumnDic[key].ColumnIndex;
if (colIndex >= 0 && colIndex < cols.Count && key.CanWrite)
{
var columnValue = key.PropertyType.GetDefaultValue();
var valueApplied = false;
if (InternalCache.ColumnInputFormatterFuncCache.TryGetValue(key, out var formatterFunc) && formatterFunc?.Method != null)
{
var cellValue = cols[colIndex];
try
{
// apply custom formatterFunc
columnValue = formatterFunc.Invoke(new object[] { cellValue });
valueApplied = true;
}
catch (Exception e)
{
Debug.WriteLine(e);
InvokeHelper.OnInvokeException?.Invoke(e);
}
}
if (valueApplied == false)
{
columnValue = cols[colIndex].ToOrDefault(key.PropertyType);
}
key.GetValueSetter()?.Invoke(entity, columnValue);
}
}
}
if (null != entity)
{
foreach (var propertyInfo in propertyColumnDic.Keys)
{
if (propertyInfo.CanWrite)
{
var propertyValue = propertyInfo.GetValueGetter()?.Invoke(entity);
if (InternalCache.InputFormatterFuncCache.TryGetValue(propertyInfo, out var formatterFunc) && formatterFunc?.Method != null)
{
try
{
// apply custom formatterFunc
var formattedValue = formatterFunc.Invoke(new[] { entity, propertyValue });
propertyInfo.GetValueSetter()?.Invoke(entity, formattedValue);
}
catch (Exception e)
{
Debug.WriteLine(e);
InvokeHelper.OnInvokeException?.Invoke(e);
}
}
}
}
}
entities.Add(entity);
}
}
}
}
}
return(entities);
}
/// <summary>
/// Import sheet data to entity list
/// </summary>
/// <typeparam name="TEntity">entity type</typeparam>
/// <param name="sheet">excel sheet</param>
/// <param name="sheetIndex">sheetIndex</param>
/// <returns>entity list</returns>
public static List <TEntity?> SheetToEntityList <TEntity>(ISheet?sheet, int sheetIndex) where TEntity : new()
{
if (sheet is null || sheet.PhysicalNumberOfRows <= 0)
{
return(new List <TEntity?>());
}
var configuration = InternalHelper.GetExcelConfigurationMapping <TEntity>();
var sheetSetting = GetSheetSetting(configuration.SheetSettings, sheetIndex);
var entities = new List <TEntity?>(sheet.LastRowNum - sheetSetting.HeaderRowIndex);
var propertyColumnDictionary = InternalHelper.GetPropertyColumnDictionary(configuration);
var propertyColumnDic = sheetSetting.HeaderRowIndex >= 0
? propertyColumnDictionary.ToDictionary(_ => _.Key, _ => new PropertyConfiguration()
{
ColumnIndex = -1,
ColumnFormatter = _.Value.ColumnFormatter,
ColumnTitle = _.Value.ColumnTitle,
ColumnWidth = _.Value.ColumnWidth,
IsIgnored = _.Value.IsIgnored
})
: propertyColumnDictionary;
var formulaEvaluator = sheet.Workbook.GetFormulaEvaluator();
var pictures = propertyColumnDic
.Any(p => p.Key.CanWrite &&
(p.Key.PropertyType == typeof(byte[]) || p.Key.PropertyType == typeof(IPictureData)))
? sheet.GetPicturesAndPosition()
: new Dictionary <CellPosition, IPictureData>();
for (var rowIndex = sheet.FirstRowNum; rowIndex <= (sheetSetting.EndRowIndex ?? sheet.LastRowNum); rowIndex++)
{
var row = sheet.GetRow(rowIndex);
if (rowIndex == sheetSetting.HeaderRowIndex) // readerHeader
{
if (row != null)
{
// adjust column index according to the imported data header
for (var i = row.FirstCellNum; i < row.LastCellNum; i++)
{
if (row.GetCell(i) is null)
{
continue;
}
var col = propertyColumnDic.GetPropertySetting(row.GetCell(i).StringCellValue.Trim());
if (null != col)
{
col.ColumnIndex = i;
}
}
}
// use default column index if no headers
if (propertyColumnDic.Values.All(_ => _.ColumnIndex < 0))
{
propertyColumnDic = propertyColumnDictionary;
}
}
else if (rowIndex >= sheetSetting.StartRowIndex)
{
if (sheetSetting.RowFilter?.Invoke(row) == false)
{
continue;
}
if (row is null)
{
entities.Add(default);
private static string GetCsvText <TEntity>(this IEnumerable <TEntity> entities, bool includeHeader)
{
if (entities == null)
{
return(string.Empty);
}
var data = new StringBuilder();
var isBasicType = typeof(TEntity).IsBasicType();
if (isBasicType)
{
if (includeHeader)
{
data.AppendLine(InternalConstants.DefaultPropertyNameForBasicType);
}
foreach (var entity in entities)
{
data.AppendLine(Convert.ToString(entity));
}
}
else
{
var dic = InternalHelper.GetPropertyColumnDictionary <TEntity>();
var props = InternalHelper.GetPropertiesForCsvHelper <TEntity>();
if (includeHeader)
{
for (var i = 0; i < props.Count; i++)
{
if (i > 0)
{
data.Append(CsvSeparatorCharacter);
}
data.Append(dic[props[i]].ColumnTitle);
}
data.AppendLine();
}
foreach (var entity in entities)
{
for (var i = 0; i < props.Count; i++)
{
var propertyValue = props[i].GetValueGetter <TEntity>().Invoke(entity);
var entityType = typeof(TEntity);
var formatterFunc = InternalCache.OutputFormatterFuncCache.GetOrAdd(props[i], p =>
{
var propertyType = typeof(PropertySetting <,>).MakeGenericType(entityType, p.PropertyType);
return(propertyType.GetProperty(InternalConstants.OutputFormatterFuncName)?.GetValueGetter()
.Invoke(dic[props[i]]));
});
if (null != formatterFunc)
{
var funcType =
typeof(Func <, ,>).MakeGenericType(entityType, props[i].PropertyType, typeof(object));
var method =
funcType.GetProperty("Method")?.GetValueGetter().Invoke(formatterFunc) as MethodInfo;
var target = funcType.GetProperty("Target")?.GetValueGetter().Invoke(formatterFunc);
if (null != method && target != null)
{
// apply custom formatterFunc
propertyValue = method.Invoke(target, new[] { entity, propertyValue });
}
}
if (i > 0)
{
data.Append(CsvSeparatorCharacter);
}
// https://stackoverflow.com/questions/4617935/is-there-a-way-to-include-commas-in-csv-columns-without-breaking-the-formatting
var val = propertyValue?.ToString().Replace("\"", "\"\"");
if (!string.IsNullOrEmpty(val))
{
data.Append(val.IndexOf(CsvSeparatorCharacter) > -1 ? $"\"{val}\"" : val);
}
}
data.AppendLine();
}
}
return(data.ToString());
}
/// <summary>
/// convert csv file data to entity list
/// </summary>
/// <param name="csvBytes">csv bytes</param>
public static List <TEntity> ToEntityList <TEntity>(byte[] csvBytes) where TEntity : new()
{
if (null == csvBytes)
{
throw new ArgumentNullException(nameof(csvBytes));
}
var entities = new List <TEntity>();
if (typeof(TEntity).IsBasicType())
{
using (var ms = new MemoryStream(csvBytes))
{
using (var sr = new StreamReader(ms, Encoding.UTF8))
{
string strLine;
var isFirstLine = true;
while ((strLine = sr.ReadLine()).IsNotNullOrEmpty())
{
if (isFirstLine)
{
isFirstLine = false;
continue;
}
//
Debug.Assert(strLine != null, nameof(strLine) + " is null");
entities.Add(strLine.Trim().To <TEntity>());
}
}
}
}
else
{
var propertyColumnDictionary = InternalHelper.GetPropertyColumnDictionary <TEntity>();
var propertyColumnDic = propertyColumnDictionary.ToDictionary(_ => _.Key, _ => new PropertySetting()
{
ColumnIndex = -1,
ColumnFormatter = _.Value.ColumnFormatter,
ColumnTitle = _.Value.ColumnTitle,
ColumnWidth = _.Value.ColumnWidth,
IsIgnored = _.Value.IsIgnored
});
using (var ms = new MemoryStream(csvBytes))
{
using (var sr = new StreamReader(ms, Encoding.UTF8))
{
string strLine;
var isFirstLine = true;
while ((strLine = sr.ReadLine()).IsNotNullOrEmpty())
{
var entityType = typeof(TEntity);
var cols = ParseLine(strLine);
if (isFirstLine)
{
for (var index = 0; index < cols.Count; index++)
{
var setting = propertyColumnDic.GetPropertySetting(cols[index]);
if (setting != null)
{
setting.ColumnIndex = index;
}
}
if (propertyColumnDic.Values.All(_ => _.ColumnIndex < 0))
{
propertyColumnDic = propertyColumnDictionary;
}
isFirstLine = false;
continue;
}
else
{
var entity = new TEntity();
if (entityType.IsValueType)
{
var obj = (object)entity;// boxing for value types
foreach (var key in propertyColumnDic.Keys)
{
var colIndex = propertyColumnDic[key].ColumnIndex;
if (colIndex >= 0 && cols.Count > colIndex)
{
key.GetValueSetter()?.Invoke(obj, cols[colIndex].ToOrDefault(key.PropertyType));
}
}
entity = (TEntity)obj;// unboxing
}
else
{
foreach (var key in propertyColumnDic.Keys)
{
var colIndex = propertyColumnDic[key].ColumnIndex;
if (colIndex >= 0 && cols.Count > colIndex)
{
key.GetValueSetter()?.Invoke(entity, cols[colIndex].ToOrDefault(key.PropertyType));
}
}
}
if (null != entity)
{
foreach (var propertyInfo in propertyColumnDic.Keys)
{
if (propertyInfo.CanWrite)
{
var propertyValue = propertyInfo.GetValueGetter().Invoke(entity);
var formatterFunc = InternalCache.InputFormatterFuncCache.GetOrAdd(propertyInfo, p =>
{
var propertyType = typeof(PropertySetting <,>).MakeGenericType(entityType, p.PropertyType);
return(propertyType.GetProperty(InternalConstants.InputFormatterFuncName)?.GetValueGetter().Invoke(propertyColumnDictionary[propertyInfo]));
});
if (null != formatterFunc)
{
var funcType = typeof(Func <, ,>).MakeGenericType(entityType, propertyInfo.PropertyType, typeof(object));
var method = funcType.GetProperty("Method")?.GetValueGetter().Invoke(formatterFunc) as MethodInfo;
var target = funcType.GetProperty("Target")?.GetValueGetter().Invoke(formatterFunc);
if (null != method && target != null)
{
// apply custom formatterFunc
var formattedValue = method.Invoke(target, new[] { entity, propertyValue });
propertyInfo.GetValueSetter().Invoke(entity, formattedValue);
}
}
}
}
}
entities.Add(entity);
}
}
}
}
}
return(entities);
}