/// <summary> /// GetWorkbookWithAutoSplitSheet /// </summary> /// <typeparam name="TEntity">entity type</typeparam> /// <param name="entityList">entity list</param> /// <param name="excelFormat">excel format</param> /// <returns>excel workbook with data</returns> public static IWorkbook GetWorkbookWithAutoSplitSheet <TEntity>(this IList <TEntity> entityList, ExcelFormat excelFormat) { var configuration = InternalHelper.GetExcelConfigurationMapping <TEntity>(); var workbook = ExcelHelper.PrepareWorkbook(excelFormat, configuration.ExcelSetting); var maxRowCount = excelFormat == ExcelFormat.Xls ? InternalConstants.MaxRowCountXls : InternalConstants.MaxRowCountXlsx; maxRowCount -= configuration.SheetSettings[0].StartRowIndex; var sheetCount = (entityList.Count + maxRowCount - 1) / maxRowCount; do { workbook.CreateSheet(); } while (workbook.NumberOfSheets < sheetCount); if (entityList.Count > maxRowCount) { for (var sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { workbook.GetSheetAt(sheetIndex) .ImportData(entityList.Skip(sheetIndex * maxRowCount).Take(maxRowCount), 0); } } else { workbook.GetSheetAt(0).ImportData(entityList); } return(workbook); }
/// <summary> /// import dataTable to workbook first sheet /// </summary> /// <typeparam name="TEntity">TEntity</typeparam> /// <param name="workbook">workbook</param> /// <param name="dataTable">dataTable</param> /// <param name="sheetIndex">sheetIndex</param> /// <returns>the sheet LastRowNum</returns> public static int ImportData<TEntity>([NotNull] this IWorkbook workbook, [NotNull] DataTable dataTable, int sheetIndex) { if (sheetIndex >= InternalConstants.MaxSheetNum) { throw new ArgumentException( string.Format(Resource.IndexOutOfRange, nameof(sheetIndex), InternalConstants.MaxSheetNum), nameof(sheetIndex)); } var configuration = InternalHelper.GetExcelConfigurationMapping<TEntity>(); while (workbook.NumberOfSheets <= sheetIndex) { if (workbook.NumberOfSheets == sheetIndex) { var sheetName = typeof(TEntity).Name; if (configuration.SheetSettings.TryGetValue(sheetIndex, out var sheetSetting)) { sheetName = sheetSetting.SheetName; } workbook.CreateSheet(sheetName); } else { workbook.CreateSheet(); } } var sheet = NpoiHelper.DataTableToSheet<TEntity>(workbook.GetSheetAt(sheetIndex), dataTable, sheetIndex); return sheet.LastRowNum; }
/// <summary> /// Load mapping profile for TEntity /// </summary> /// <typeparam name="TEntity">entity type</typeparam> /// <typeparam name="TMappingProfile">entity type mapping profile</typeparam> public static void LoadMappingProfile <TEntity, TMappingProfile>() where TMappingProfile : IMappingProfile <TEntity>, new() { var profile = new TMappingProfile(); profile.Configure(InternalHelper.GetExcelConfigurationMapping <TEntity>()); }
/// <summary> /// EntityList2ExcelStream /// </summary> /// <typeparam name="TEntity">EntityType</typeparam> /// <param name="entityList">entityList</param> /// <param name="stream">stream where to write</param> /// <param name="excelFormat">excelFormat</param> /// <param name="sheetIndex">sheetIndex</param> public static void ToExcelStream<TEntity>([NotNull] this IEnumerable<TEntity> entityList, [NotNull] Stream stream, ExcelFormat excelFormat, int sheetIndex) { var configuration = InternalHelper.GetExcelConfigurationMapping<TEntity>(); var workbook = ExcelHelper.PrepareWorkbook(excelFormat, configuration.ExcelSetting); workbook.ImportData(entityList.ToArray(), sheetIndex); workbook.Write(stream); }
/// <summary> /// EntityList2ExcelBytes /// </summary> /// <typeparam name="TEntity">EntityType</typeparam> /// <param name="entityList">entityList</param> /// <param name="excelFormat">excelFormat</param> /// <param name="sheetIndex">sheetIndex</param> public static byte[] ToExcelBytes<TEntity>([NotNull] this IEnumerable<TEntity> entityList, ExcelFormat excelFormat, int sheetIndex) { var configuration = InternalHelper.GetExcelConfigurationMapping<TEntity>(); var workbook = ExcelHelper.PrepareWorkbook(excelFormat, configuration.ExcelSetting); workbook.ImportData(entityList.ToArray(), sheetIndex); return workbook.ToExcelBytes(); }
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);
/// <summary> /// EntityList2ExcelFile /// </summary> /// <typeparam name="TEntity">EntityType</typeparam> /// <param name="entityList">entityList</param> /// <param name="excelPath">excelPath</param> /// <param name="sheetIndex">sheetIndex</param> public static void ToExcelFile<TEntity>([NotNull] this IEnumerable<TEntity> entityList, [NotNull] string excelPath, int sheetIndex) { var configuration = InternalHelper.GetExcelConfigurationMapping<TEntity>(); var workbook = ExcelHelper.PrepareWorkbook(excelPath, configuration.ExcelSetting); workbook.ImportData(entityList.ToArray(), sheetIndex); workbook.WriteToFile(excelPath); }
/// <summary> /// Load mapping profile for TEntity /// </summary> /// <param name="profile">profile</param> private static void LoadMappingProfile(IMappingProfile profile) { Guard.NotNull(profile, nameof(profile)); var profileInterfaceType = profile.GetType() .GetImplementedInterfaces() .FirstOrDefault(x => x.IsGenericType && x.GetGenericTypeDefinition() == s_profileGenericTypeDefinition); if (profileInterfaceType is null) { return; } var entityType = profileInterfaceType.GetGenericArguments()[0]; var configuration = InternalHelper.GetExcelConfigurationMapping(entityType); var method = profileInterfaceType.GetMethod(MappingProfileConfigureMethodName, new[] { typeof(IExcelConfiguration <>).MakeGenericType(entityType) }); method?.Invoke(profile, new object[] { configuration }); }
// 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); }
/// <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);
/// <summary> /// convert csv file data to entity list /// </summary> /// <param name="csvBytes">csv bytes</param> public static List <TEntity> ToEntityList <TEntity>(byte[] csvBytes) { 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 configuration = InternalHelper.GetExcelConfigurationMapping <TEntity>(); 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; } else { var entity = NewFuncHelper <TEntity> .Instance(); 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); } } } } } if (configuration.DataValidationFunc != null && !configuration.DataValidationFunc(entity)) { // data invalid continue; } entities.Add(entity); } } } } } return(entities); }
/// <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; } // entities.Add(strLine.Trim().To <TEntity>()); } } } } else { var propertyColumnDictionary = InternalHelper.GetExcelConfigurationMapping <TEntity>().PropertyConfigurationDictionary.Where(_ => !_.Value.PropertySetting.IsIgnored).ToDictionary(_ => _.Key, _ => _.Value.PropertySetting); 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 = strLine.Split(new[] { InternalConstants.CsvSeparatorCharactor }, StringSplitOptions.RemoveEmptyEntries); if (isFirstLine) { for (int i = 0; i < cols.Length; i++) { var col = propertyColumnDictionary.GetPropertySetting(cols[i].Trim()); if (null != col) { col.ColumnIndex = i; } } isFirstLine = false; } else { var entity = new TEntity(); if (typeof(TEntity).IsValueType) { var obj = (object)entity;// boxing for value types foreach (var key in propertyColumnDictionary.Keys) { var colIndex = propertyColumnDictionary[key].ColumnIndex; key.GetValueSetter().Invoke(obj, cols[colIndex]); } entity = (TEntity)obj;// unboxing } else { foreach (var key in propertyColumnDictionary.Keys) { var colIndex = propertyColumnDictionary[key].ColumnIndex; key.GetValueSetter().Invoke(entity, cols[colIndex]); } } entities.Add(entity); } } } } } return(entities); }
/// <summary> /// SettingFor /// </summary> /// <typeparam name="TEntity">TEntity</typeparam> /// <returns></returns> public static IExcelConfiguration <TEntity> SettingFor <TEntity>() => InternalCache.TypeExcelConfigurationDictionary.GetOrAdd(typeof(TEntity), t => InternalHelper.GetExcelConfigurationMapping <TEntity>()) as IExcelConfiguration <TEntity>;
/// <summary> /// Load mapping profile for TEntity /// </summary> /// <param name="profile">profile</param> /// <typeparam name="TEntity">entity type</typeparam> public static void LoadMappingProfile <TEntity>(IMappingProfile <TEntity> profile) { Guard.NotNull(profile, nameof(profile)); profile.Configure(InternalHelper.GetExcelConfigurationMapping <TEntity>()); }
internal NpoiHelper() { _excelConfiguration = (ExcelConfiguration <TEntity>)InternalCache.TypeExcelConfigurationDictionary.GetOrAdd(typeof(TEntity), t => InternalHelper.GetExcelConfigurationMapping <TEntity>()); _sheetSettings = _excelConfiguration.SheetSettings.AsReadOnly(); //AutoAdjustIndex var colIndexList = new List <int>(_excelConfiguration.PropertyConfigurationDictionary.Count); foreach (var item in _excelConfiguration.PropertyConfigurationDictionary.Values.Where(_ => !_.PropertySetting.IsIgnored)) { while (colIndexList.Contains(item.PropertySetting.ColumnIndex)) { item.PropertySetting.ColumnIndex++; } colIndexList.Add(item.PropertySetting.ColumnIndex); } _propertyColumnDictionary = _excelConfiguration.PropertyConfigurationDictionary.Where(_ => !_.Value.PropertySetting.IsIgnored).ToDictionary(_ => _.Key, _ => _.Value.PropertySetting); }
/// <summary> /// SettingFor /// Fluent Settings /// </summary> /// <typeparam name="TEntity">TEntity</typeparam> /// <returns></returns> public static IExcelConfiguration <TEntity> SettingFor <TEntity>() => InternalHelper.GetExcelConfigurationMapping <TEntity>();
internal NpoiHelper() { _entityType = typeof(TEntity); _excelConfiguration = (ExcelConfiguration <TEntity>)InternalCache.TypeExcelConfigurationDictionary.GetOrAdd(_entityType, t => InternalHelper.GetExcelConfigurationMapping <TEntity>()); _sheetSettings = _excelConfiguration.SheetSettings.AsReadOnly(); InternalHelper.AdjustColumnIndex(_excelConfiguration); _propertyColumnDictionary = _excelConfiguration.PropertyConfigurationDictionary.Where(_ => !_.Value.PropertySetting.IsIgnored).ToDictionary(_ => _.Key, _ => _.Value.PropertySetting); }