/// <summary> /// Get csv text /// </summary> public 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.DynamicInvoke(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.DynamicInvoke(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.DynamicInvoke(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.DynamicInvoke(entity, propertyValue); propertyInfo.GetValueSetter()?.Invoke(entity, formattedValue); } catch (Exception e) { Debug.WriteLine(e); InvokeHelper.OnInvokeException?.Invoke(e); } } } } } entities.Add(entity); } } } } } return(entities); }
// 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.DynamicInvoke(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); }
/// <summary> /// convert csv file data to entity list /// </summary> /// <param name="filePath">csv file path</param> public static List <TEntity> ToEntityList <TEntity>(string filePath) where TEntity : new() { if (!File.Exists(filePath)) { throw new ArgumentException(Resource.FileNotFound, nameof(filePath)); } var entities = new List <TEntity>(); if (typeof(TEntity).IsBasicType()) { using (var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { using (var sr = new StreamReader(fs, 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 { IReadOnlyList <PropertyInfo> props = InternalHelper.GetPropertiesForCsvHelper <TEntity>(); using (var fs = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { using (var sr = new StreamReader(fs, Encoding.UTF8)) { string strLine; var isFirstLine = true; while ((strLine = sr.ReadLine()).IsNotNullOrEmpty()) { var cols = ParseLine(strLine); if (isFirstLine) { isFirstLine = false; continue; } else { var entity = new TEntity(); if (typeof(TEntity).IsValueType) { var obj = (object)entity;// boxing for value types for (var i = 0; i < props.Count; i++) { props[i].GetValueSetter().Invoke(obj, cols[i].ToOrDefault(props[i].PropertyType)); } entity = (TEntity)obj;// unboxing } else { for (var i = 0; i < props.Count; i++) { props[i].GetValueSetter().Invoke(entity, cols[i].ToOrDefault(props[i].PropertyType)); } } entities.Add(entity); } } } } } return(entities); }
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 (sheetSetting.RowFilter?.Invoke(row) == false) { continue; } if (row == null) { entities.Add(default);
/// <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);